I wrote this macro in vba because I want to be able to copy a range from an excel worksheet and paste it into the body of a new outlook email as an image. Also, in the body of the email want the include some text above the image and my default email signature below the image. Right now, when I run this macro a new email box pops up with the image copied and paste; however, there is no text and my email signature does not appear. Can someone help me figure out what I'm doing wrong? In case this matters, I'm using Microsoft Office 365.
Sub CopyandPasteIntoEmail()
Dim rng As Range
Set rng = ActiveWorkbook.Sheets(“Sheet1).Range(“A1:K44”)
Dim olApp As Object
Set olApp = CreateObject(“Outlook.Application”)
Dim olMail As Object
Set olMail = olApp.CreateItem(olMailItem)
olMail.Display
With olMail
.To = “recipient@example.com”
.Subject = “Copied image”
.HTMLBody = “Hello Mr. X, <br><br>Please see image below…” & olApp.Session.CurrentUser.Address
End With
rng.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
olMail.GetInspector.WordEditor.Range.Paste
Set olMail = Nothing
Set olApp = Nothing
Set rng = Nothing
End Sub