I have a VBA script to take a snip of dashboards from Excel and send it to peers in the company.
My MS Office was updated to Office LTSC 2021 and since then the recipients get a message instead of the snip.
Error Message
If I send the email to a gmail account then the image shows up as an attachment but not embedded as it used to.
If I send it to myself then the email comes with embedded image as it used to.
Code to create a JPG.
MakeJPG = CopyRangeToJPG("Sheet name","Range")
Function CopyRangeToJPG(NameWorksheet As String, RangeAddress As String) As String
Dim PictureRange As Range
With ActiveWorkbook
On Error Resume Next
.Worksheets(NameWorksheet).Activate
Set PictureRange = .Worksheets(NameWorksheet).Range(RangeAddress)
PictureRange.CopyPicture
With .Worksheets(NameWorksheet).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(NameWorksheet).ChartObjects(.Worksheets(NameWorksheet).ChartObjects.Count).Delete
End With
CopyRangeToJPG = Environ$("temp") & Application.PathSeparator & "NamePicture.jpg"
Set PictureRange = Nothing
End Function
Code to send the email.
With OutMail
.To = Range("C1").Value
.CC = Range("C2").Value
.BCC = Range("C3").Value
.Subject = Range("C4").Value
.Attachments.Add MakeJPG, 1, 0
'Note: Change the width and height as needed
.HTMLBody = "<html><p>" & strbody & "</p><img src=""cid:NamePicture.jpg"" width=1000 height=1000 ></html>"
.Display 'or use .Send
End With
