VBA to send email, does not display on Mac

Reed9050

New Member
Joined
Aug 14, 2019
Messages
8
I have a macro that has been working like a champ for for a decent bit, but it has come to my attention that mac users are unable to see the image. I am guessing it is a security setting in outlook for Mac that is causing the HTML email to convert to text.

To copy a range of the report I use Ron De Bruin's Function:

VBA Code:
Function CopyRangeToJPG(NameWorksheet As String, RangeAddress As String) As String
    'Ron de Bruin, 25-10-2019
    Dim PictureRange As Range

    With ActiveWorkbook
        On Error Resume Next
        .Worksheets("Summary").Activate
        Set PictureRange = .Worksheets("Summary").Range("AF13:AX57")
        
        If PictureRange Is Nothing Then
            MsgBox "Sorry this is not a correct range"
            On Error GoTo 0
            Exit Function
        End If
        
        PictureRange.CopyPicture
        With .Worksheets("Summary").ChartObjects.Add(PictureRange.Left, PictureRange.Top, PictureRange.Width, PictureRange.Height)
            .Activate
            .Chart.Paste
            .Chart.Export Environ$("TEMP") & Application.PathSeparator & "NamePicture.jpg", "JPG"
        End With
        .Worksheets("Summary").ChartObjects(.Worksheets("Summary").ChartObjects.Count).Delete
    End With
    
    CopyRangeToJPG = Environ$("TEMP") & Application.PathSeparator & "NamePicture.jpg"
    Set PictureRange = Nothing
End Function

Then I use OutApp.CreateItem(olMailItem) to compose an email with the image being "pasted" in the body of the email:

VBA Code:
  MakeJPG = CopyRangeToJPG("Summary", "AF13:AX54")
  
   If MakeJPG = "" Then
        MsgBox "Something went wrong, we can't create the mail"
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        Exit Sub
    End If
  
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(olMailItem)
 
  With OutMail
    .To = "Email@email.com"
    .CC = ""
    .BCC = ""
    .Subject = "Sales Report as of " & Format(Date - 1, "mm.dd.yyyy")
    .Attachments.Add MakeJPG, 1, 0
    .HTMLBody = "<html><p>" & TopBody & "</p><img src=""cid:NamePicture.jpg"" width=2000 height=1150><p>" & BotBody & Signature & "</p></html>"
    .Display
  End With
  On Error GoTo 0
  Application.EnableEvents = True
  Set OutMail = Nothing
End Sub

Has anyone else experienced issues with Outlook for Mac not displaying an image in the email. My best guess is that something with how VBA is creating the HTML code for the email is causing Macs to convert it to text. If I copy and paste the image manually Macs will display the image, but they will not do this if I use VBA to create the email. Is there a simple workaround of using a different method to open Outlook that would work or do I need to go back to the manual process of creating an email in outlook and copy/pasting the range to the email?

Thanks!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,147,574
Messages
5,741,901
Members
423,693
Latest member
Excelquestion35

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top