Sending Emails with Image of Excel Table as Message Body

Question

My goal is to send emails containing an Excel table.

I found a macro to send emails with CC, subject, attachment, and text as the body of the message.

What I need is to send an image as a message body.

Option Explicit

Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Mails")
Dim i As Integer

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")

Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))

For i = 2 To last_row
    Set msg = OA.createitem(0)
    msg.To = sh.Range("A" & i).Value
    msg.cc = sh.Range("B" & i).Value
    msg.Subject = sh.Range("C" & i).Value
    msg.Body = sh.Range("D" & i).Value 

    If sh.Range("E" & i).Value <> "" Then
        msg.Attachments.Add sh.Range("E" & i).Value
    End If

    msg.send

    sh.Range("F" & i).Value = "Sent"

Next i

MsgBox "All the mails have been sent successfully"

End Sub

What office version do you have? Take a look at this post: https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba — Ricardo Diaz, Oct 11 '19 at 09:03

Atul Singh · Answer 1 · 2019-10-11T12:59:04.407

I am using the below code to send the attachment.Check if it works for you Code courtesy @HirSinghRajput

Set objEmail = objOutlook.CreateItem(olMailItem)
 With objEmail
  .To = Sheets("DataSheet").Cells(itr, 2)' Add "To" Email Id

  .Subject = Sheets("DataSheet").Cells(itr, 3)' Add Subject Line

  .Body = Sheets("DataSheet").Cells(itr, 4)' Add Email Body

  For iAttachmentCol = 5 To 9
   strPath = CStr(Sheets("DataSheet").Cells(itr, iAttachmentCol)) ' Copy the attachment Path

   If strPath <> "" Then' Verify attachment path is empty or not

    .Attachments.Add strPath' Attach the attachment if path is not empty

   End If

  Next

  .Send

score 0 · Answer 2 · answered Oct 11 '19 at 12:31

You need to attach an image and then set a CID which you can use in the message 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 );

Sending Emails with Image of Excel Table as Message Body

2 Answers2