Matilda_Steve Matilda_SteveCreated on July 7, 2021 Excel VBA code to send bulk email with body of the email sourced from html document. Inbuilt image in html document not getting displayed in email

Question

I am trying to automate bulk email generation through Excel VBA. The body of the outlook email message is sourced from a html document (which was originally word doc saved as html). The code executes perfect and all the email messages are saved in the drafts folder as expected. But the picture which was inbuilt in the html is not showing up in the Outlook email body and has a X with the message 'Picture cannot be displayed'. How to make this work?

Here is the code:

Sub emailautomation()

Dim olAPP As Object

Dim recipient As Variant

Dim path As Variant

Dim msg1 As String

Dim msg2 As String

Dim msg3 as String

Set olAPP = CreateObject("outlook.application")

On Error GoTo ERR

'Opens the word document which has been saved as HTML file and the contents are pasted to the body of the outlook mail message

Open Cells(3, 2).Value For Input As #1

htmlstring = ""

Do While Not EOF(1)

htmlstring = htmlstring & Input(1, #1)

Loop

Close

j = 1

For i = 7 To Cells(Rows.count, j).End(xlUp).Row

recipient = Cells(i, j + 1)

path = Cells(i, j + 2)

'Creates mails and saves them in the draft folder

With olAPP.CreateItem(0)

.To = recipient

.Subject = Cells(4, 2) & " - " & Cells(i, j)

.BodyFormat = olFormatHTML

.HTMLBody = htmlstring

.Attachments.Add path

.Save

End With

Application.StatusBar = "Processing... Please wait ... !!!"

Next i

Application.StatusBar = vbNullString

msg1 = MsgBox("Mails have been saved in the drafts folder. Please check!!!", vbOKOnly, "Success!!")

msg3 = MsgBox("No. of mails drafted : " & (i - 7), vbOKOnly, "Status...")

Exit Sub

ERR:

msg2 = MsgBox("Operation cancelled due to incomplete data. Please check the source info!!!", vbOKOnly, "Oops!!")

msg3 = MsgBox("No. of mails drafted : " & (i - 6), vbOKOnly, "Status...")

End Sub