Search code examples
excelvbaoutlookhtml-email

Place image in HTML Body at the bottom of the email


I'm using an Excel file to create an Outlook email with all our contacts in a list within the Excel file.

There's a single image (objshape) within Worksheet1, all by itself.
The image is pasted at Range (0, 0) which ends up at the beginning of my email.
I want it at the bottom, after the main HTML body.

VBA coding I have so far:

Sub CopyImagesToMail()
Dim objWorksheet As Excel.Worksheet
Dim objOutlookApp As Object
Dim objMail As Object
Dim objMailDocument As Object
Dim objShape As Excel.Shape
            
Set objWorksheet = ThisWorkbook.Worksheets(1)

Set objOutlookApp = CreateObject("Outlook.application")
Set objMail = objOutlookApp.CreateItem(objOutlookAppobjMailItem)
Set objMailDocument = objMail.GetInspector.WordEditor

For Each objShape In objWorksheet.Shapes
    objShape.Copy
Next

With objMail
    .To = ""
    .CC = ""
    .BCC = Sheets("Principal").Range("DistributionList")
    .Subject = "Enter subject here"
    .HTMLBody = "<html>" & _
            "<br/>" & _
            "<p style=""text-align:left"">Enter greetings here</p>" & _
            "<p style=""text-align:left"">Enter text here </p>" & _
            "<p style=""text-align:left"">Enter text here </p>" & _
            "<p style=""text-align:left"">Enter text here </p>" & _
            "<p style=""text-align:left"">Enter text here </b>" & _
            "<br/>" & _
            "<br/>" & _
            "<p style=""text-align:left"">Thank you</p>" & _
            "<br/>" & _
            "<p style=""text-align:left"">Announce Website here (CTRL + Click) </p>" & _
            "<p style=""text-align:left""><a href=""https://Website.com/""> Hypertext description here</a></p>" & _
            "</html>"
    objMailDocument.Range(0, 0).Paste

End With

objMail.Display

End Sub

Solution

  • First of all, I'd suggest using the one or another way of setting the message body. If you decide to go with the HTMLBody property then construct your string based on the Excel data. If you want to deal with Word you can use its object model. Try to use the following code to paste the content to the end of documents:

    objMailDocument.Content.Select ' selects the main text story
    objMailDocument.Selection.Collapse wdCollapseEnd
    objMailDocument.Selection.Paste