excel - une - vba send email without outlook




Copier une plage Excel sous forme d'image dans Outlook (2)

Comment utiliser la commande "Collage spécial - en tant qu'image" à laquelle vous accédez dans Excel à partir du menu contextuel?

J'ai consulté divers messages, mais ils semblent être dépassés lors de l'utilisation d'Excel 2016. Il semble que cela doit être dans cette section,

With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select

Comment puis-je modifier pour autoriser le copier-coller en tant qu'image?

En utilisant le code original ci-dessous, je perds tous les redimensionnements des colonnes et des lignes dans le corps du message

Dim rng As Range
Dim OutApp As Object
Dim outMail As Object

Set rng = Nothing
' Only send the visible cells in the selection.

Set rng = Sheets("Dashboard").Range("B4:L17").SpecialCells(xlCellTypeVisible)

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set outMail = OutApp.CreateItem(0)

With outMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = ""
    .HTMLBody = RangetoHTML(rng)
    .Display
End With
On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

Set outMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' By Ron de Bruin.
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Quelque chose comme ça devrait marcher:

Dim ol As Object 'Outlook.Application
Dim olEmail As Object 'Outlook.MailItem
Dim olInsp As Object 'Outlook.Inspector
Dim wd As Object 'Word.Document

Sheets("Dashboard").Range("B4:L17").SpecialCells(xlCellTypeVisible).Copy

Set ol = GetObject(, "Outlook.Application") '/* if outlook is running, create otherwise */
Set olEmail = ol.CreateItem(0) 'olMailItem

With olEmail
    Set olInsp = .GetInspector
    If olInsp.EditorType = 4 Then 'olEditorWord
        Set wd = olInsp.WordEditor
        wd.Range.PasteAndFormat 13 'wdChartPicture
    End If
    .Display
End With

Si vous êtes certain que votre version d'Outlook utilise Word Editor, vous pouvez le faire comme suit:

With olEmail
    .GetInspector.WordEditor.Range.PasteAndFormat 13
    .Display
End With

Si vous voulez ajouter du texte, utilisez ce code.

Dim ol As Object 'Outlook.Application
Dim olEmail As Object 'Outlook.MailItem
Dim olInsp As Object 'Outlook.Inspector
Dim wd As Object 'Word.Document

Sheets("Dashboard").Range("B4:L17").SpecialCells(xlCellTypeVisible).Copy

Set ol = GetObject(, "Outlook.Application") '/* if outlook is running, create otherwise */
Set olEmail = ol.CreateItem(0) 'olMailItem

With olEmail
    Set olInsp = .GetInspector
    If olInsp.EditorType = 4 Then 'olEditorWord
        Set wd = olInsp.WordEditor
        wd.Range.PasteAndFormat 13 'wdChartPicture
    End If

    wd.Paragraphs(1).Range.InsertAfter "Hi, There" & Chr(10)

    Sheets("chart").Range("B4:L17").SpecialCells(xlCellTypeVisible).Copy
    wd.Paragraphs(wd.Paragraphs.Count).Range.Characters.First.PasteAndFormat 13
    wd.Paragraphs.Add

    Sheets("chart").Range("B4:L17").SpecialCells(xlCellTypeVisible).Copy
    wd.Paragraphs(wd.Paragraphs.Count).Range.Characters.First.PasteAndFormat 13
    wd.Paragraphs.Add

    wd.Paragraphs(wd.Paragraphs.Count).Range.InsertAfter Chr(10) & Chr(10) & "BR"

    .Display
End With




outlook-vba