Be the first user to complete this post

  • 0
Add to List

Excel-VBA : Send Mail with Embedded Image in message body From MS Outlook using Excel.

In previous articles you have learned about Send a Simple Mail From MS Outlook Using Excel and how to send Attachment With the Mail MS Outlook Using Excel. ( I recommend you first read these articles to understand the basics if you are new to this)

In this tutorial you will learn how to add an image in the mail body or message and send it from Microsoft Outlook.

Ides is to attach the image in hidden manner and later add it to using image name in the HtmlBody.

See the Code.

Complete Code:

Sub sumit()
Dim mainWB As Workbook
Dim SendID
Dim CCID
Dim Subject
Dim Body
Dim olMail As MailItem

Set otlApp = CreateObject("Outlook.Application")
Set olMail = otlApp.CreateItem(olMailItem)
Set Doc = olMail.GetInspector.WordEditor
'Dim colAttach As Outlook.Attachments
Dim oAttach As Outlook.Attachment

Set mainWB = ActiveWorkbook

SendID = mainWB.Sheets("Mail").Range("B1").Value
CCID = mainWB.Sheets("Mail").Range("B2").Value
Subject = mainWB.Sheets("Mail").Range("B3").Value
Body = mainWB.Sheets("Mail").Range("B4").Value
With olMail
.To = SendID
If CCID <> "" Then
.CC = CCID
End If
.Subject = Subject
'add the image in hidden manner, position at 0 will make it hidden
.Attachments.Add "C:\Users\Sumit Jain\Pictures\11\city.jpg", olByValue, 0

'Now add it to the Html body using image name
'change the src property to 'cid:your image filename'
'it will be changed to the correct cid when its sent.
.HTMLBody = .HTMLBody & "<br><B>Embedded Image:</B><br>" _
& "<img src='cid:city.jpg'" & "width='500' height='200'><br>" _
& "<br>Best Regards, <br>Sumit</font></span>"
.Display
.Send
End With

MsgBox ("you Mail has been sent to " & SendID)

End Sub

Send Mail with Embedded Image in message body-1
Send Mail with Embedded Image in message body-1
Send Mail with Embedded Image in message body-2
Send Mail with Embedded Image in message body-2



Also Read:

  1. VBA-Excel: Working with Microsoft Word
  2. VBA-Excel: Enumerate all the opened word document
  3. Excel-VBA : Change Passwords for all the WorkSheets in one shot
  4. VBA-Excel: Create worksheets with Names in Specific Format/Pattern.