PDA

View Full Version : [SOLVED] Embed an image and range to outlook using excel vb



spittingfire
07-20-2017, 10:13 AM
Hi All,

I need some help with adding an image and a range of text to outlook using excel vb.

I found the below code from http://excel-macro.tutorialhorizon.com/excel-vba-send-mail-with-embedded-image-in-message-body-from-ms-outlook-using-excel/


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("FirstEmailEnglish").Range("A1").Value
CCID = mainWB.Sheets("FirstEmailEnglish").Range("B1").Value
'Subject = mainWB.Sheets("Mail").Range("B3").Value
Body = mainWB.Sheets("FirstEmailEnglish").Range("A4:J29").SpecialCells(xlCellTypeVisible)
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 "B:\rogers.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 = "<img src='cid:rogers.jpg'" & "width='150' height='40'><br>" _

.Display
'.Send
End With

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

End Sub

What I will like to do is after the image source is to added an excel range ("A4:J29") to display the values.

Any help will be appreciated

spittingfire
07-20-2017, 02:49 PM
I found a solution.

I went with a template from Ron de Bruin - https://www.rondebruin.nl/

Thanks