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:
Then I use OutApp.CreateItem(olMailItem) to compose an email with the image being "pasted" in the body of the email:
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!
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!