0

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
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
BannyM
  • 212
  • 2
  • 8

0 Answers0