Pictures pasted on the body of outlook email are not displayed

Question

I used the code below to copy a range from a file and paste it as a picture on emails, but there's a catch: if you don´t use .display before .send, the picture will not be displayed to the receiver. Does anyone know a way around this? Just to avoid the outlook window flashing on the screen.

Sub sendMail()

    Dim olApp As Object
    Dim NewMail As Object
    Dim ChartName As String
    Dim imgPath As String

    Set olApp = CreateObject("Outlook.Application")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    tmpImageName = VBA.Environ$("temp") & "\tempo.jpg"

    Workbooks.Open "C:\FilePath\File.xlsm"
    Set RangeToSend = Workbooks("File.xlsm").Sheets(Name).Range(" ")

    RangeToSend.CopyPicture Appearance:=xlScreen, Format:=xlPicture

    Set sht = Sheets.Add
    sht.Shapes.AddChart
    sht.Shapes.Item(1).Select
    Set objChart = ActiveChart

    With objChart
        .ChartArea.Height = RangeToSend.Height
        .ChartArea.Width = RangeToSend.Width
        .ChartArea.Fill.Visible = msoFalse
        .ChartArea.Border.LineStyle = xlLineStyleNone
        .Paste
        .Export Filename:=tmpImageName, FilterName:="JPG"
    End With

    sht.Delete

    Workbooks("File.xlsm").Close

    Set NewMail = olApp.CreateItem(0)

    With NewMail
        .Subject = "Latest performance report" ' Replace this with your Subject
        .To = "email@email.com" ' Replace it with your actual email
        .HTMLBody = "<span LANG=EN>" _
            & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
            & "Hello, this is the data range that you want:<br> " _
            & "<br>" _
            & "<img src=" & "'" & tmpImageName & "'/>" _
            & "<br>" _
            & "<img src=" & "'" & tmpImageName2 & "'/>" _
            & "<br>" _
            & "<br>Best Regards!</font></span>"
        .Display
        .Send

        Set olApp = Nothing
        Set NewMail = Nothing
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True

    End With


End Sub

Have you tried setting the body format `.BodyFormat = olFormatHTML`? — Kostas K., May 01 '20 at 15:27
Have you seen [THIS](https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba/44870371#44870371) — Siddharth Rout, May 01 '20 at 16:11

score 1 · Accepted Answer · answered May 01 '20 at 18:09

Looks like you are saving the picture captured in Excel to a disk. And then you are referring to the image in a newly created item body. But the image source still points to the file on your disk. So, the recipient will never get it shown correctly.

Instead, you need to attach a file and then add a reference in the message body.

Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"        
Const PR_ATTACHMENT_HIDDEN = "http://schemas.microsoft.com/mapi/proptag/0x7FFE000B" 

...

Set colAttach = mail.Attachments        
Set l_Attach = colAttach.Add(path_to_the_file)            
Set oPA = l_Attach.PropertyAccessor            

oPA.SetProperty PR_ATTACH_CONTENT_ID, "itemID"            
oPA.SetProperty PR_ATTACHMENT_HIDDEN, True

Then you can modify the message body in the following way:

.HTMLBody = "<span LANG=EN>" _
        & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
        & "Hello, this is the data range that you want:<br> " _
        & "<br>" _
        & "<img src='cid:itemID'/>" _
        & "<br>" _
        & "<br>Best Regards!</font></span>"

    .Send

Thanks for the response. This is a good alternative, although I still dont understand why I can send the pictures on the body of the email (without attaching the files) if I display the outlook window (with .display command) before sending. — wwhitman, May 07 '20 at 21:02

Pictures pasted on the body of outlook email are not displayed

1 Answers1