CODEWITHSUNDEEP
excelimagevbaoutlook
Cs1422
Cs1422

Reputation: 1

Copy picture in Excel to Outlook body

I am trying to figure out how to get a picture from a spreadsheet in Excel into the body of an Outlook message. Here's what I have so far:

It creates the picture from the grouping of data that I need,
copies it below, and
opens the mail with the initial message that I need

but I can't figure out how to link the two created images into the body of the email. As of now, I've been cutting them from each tab once created and just pasting them into the body.

Sub EmailDashboards()

Dim OutApp As Object
Dim outMail As Object
Sheets("CAM Dashboard Burdened").Select
Range("C1:J47").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Range("K36").Select
ActiveSheet.Paste

Sheets("CAM Dashboard Direct").Select
Range("C1:J47").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Range("K36").Select
ActiveSheet.Paste

Sheets("CAM Dashboard Burdened").Select
Range("K36").Select

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set outMail = OutApp.CreateItem(0)

With outMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "CCM EV Dashboard"
    .Body = "Here are the latest Burdened and Direct EV Dashboards for your area: "
    .Display
End With

On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

Set outMail = Nothing
Set OutApp = Nothing

End Sub

Upvotes: 0

Views: 12931

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49397

By calling the following line of code:

 ActiveSheet.Paste

you paste images on the Excel sheet, not in Outlook.

You may try to use the Word object model to get images pasted into the mail item. The Outlook object model provides three main ways for working item bodies:

  1. Body - a string representing the clear-text body of the Outlook item.
  2. HTMLBody - a string representing the HTML body of the specified item.
  3. Word editor - the Microsoft Word Document Object Model of the message being displayed. The WordEditor property of the Inspector class returns an instance of the Document class from the Word object model which you can use to set up the message body.

You can read more about all these ways in the Chapter 17: Working with Item Bodies.

The Paste method of the Selection class inserts the contents of the Clipboard at the specified selection.

Also you can attach images to the mail item, mark them as hidden setting an appropriate low-level property and mention them in the HTML markup of the body. 

  Attachment attachment = newMail.Attachments.Add(
 @"E:\Pictures\image001.jpg"
, OlAttachmentType.olEmbeddeditem
, null
, "Some image display name"
);

string imageCid = "image001.jpg@123";

attachment.PropertyAccessor.SetProperty(
 "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
, imageCid
);

newMail.HTMLBody = String.Format(
 "<body><img src=\"cid:{0}\"></body>"
, imageCid
);

See how to embed image in html body in c# into outlook mail for more information.

Upvotes: 1

Related Questions