You don't have HTML, You have an HTML file, so you first need to read the file's content into a string variable and pass that, raw HTML, to your e-mail function and use it as the body of your message. See: https://www.devhut.net/2012/05/14/vba-read-file-into-memory/
Private Sub SendEmails(ws As Worksheet, emailQueue As Object)
' Local variables
Dim emailTo As Variant
Dim emailData As OwnerData
Dim emailBody As String
Dim emailTable As String
Dim emailDetail As String
Dim row As Variant
Dim outlookApp As Object
Dim total As Double
' Connect to Outlook
Set outlookApp = CreateObject("Outlook.Application")
' Process each email that needs a reminder sent
For Each emailTo In emailQueue.Keys
' Get the email address
Set emailData = emailQueue(emailTo)
emailTable = ""
' Add second reminders (if there are any)
If emailData.ExpiredSecond <> "" Then
emailDetail = ""
total = 0
' Add details for each license
For Each row In Split(emailData.ExpiredSecond, ",")
' Generate the HTML for this detail line and add to this table
emailDetail = emailDetail & BodyDetail(ws, CLng(row))
total = total + ws.Cells(row, colTotalPayment).value
Next
' Generate the HTML for this table and add it to the tables for this email
emailTable = emailTable & BodyTable(SecondReminderDays, Format(total, "Currency"), emailDetail)
End If
' Add first reminders (if there are any)
If emailData.ExpiredFirst <> "" Then
emailDetail = ""
total = 0
' Add details for each license
For Each row In Split(emailData.ExpiredFirst, ",")
' Generate the HTML for this detail line and add to this table
emailDetail = emailDetail & BodyDetail(ws, CLng(row))
total = total + ws.Cells(row, colTotalPayment).value
Next
' Generate the HTML for this table and add it to the tables for this email
emailTable = emailTable & BodyTable(FirstReminderDays, Format(total, "Currency"), emailDetail)
End If
' Generate the HTML for this email body
emailBody = BodyMain(emailData.Name, emailTable)
' Create an email and send it (via Outlook)
With outlookApp.CreateItem(0)
.to = emailTo
.Subject = "Software Maintenance license(s)"
.HtmlBody = emailBody
.Send
End With
Next
' Disconnect from Outlook
Set outlookApp = Nothing
End Sub
Take a look at the attached Word document, I took some code from one of the prior questions I mentioned above, and highlighted in yellow the things I think you should be looking at / adding. I also highlighted in blue the section where I replace "place holder" test in the template with actual data values, you may or may not want to do something like that.
»bp
Sub BulkMail()
Application.ScreenUpdating = False
ThisWorkbook.Activate
Dim outApp As Outlook.Application
Dim outMail As Outlook.MailItem
Dim sendTo, subj, atchmnt, msg, ccTo, bccTo As String
Dim lstRow As Long
ThisWorkbook.Sheets("marketing").Activate
lstRow = Cells(Rows.Count, 3).End(xlUp).Row
Dim rng As Range
Set rng = Range("C2:C" & lstRow)
Set outApp = New Outlook.Application
On Error GoTo cleanup 'to handle any error during creation of object.
For Each cell In rng
sendTo = Range(cell.Address).Offset(0, 0).Value2
subj = Range(cell.Address).Offset(0, 1).Value2 & "-MS"
msg = Range(cell.Address).Offset(0, 2).Value2
atchmnt = Range(cell.Address).Offset(0, -1).Value2
ccTo = Range(cell.Address).Offset(0, 3).Value2
bccTo = Range(cell.Address).Offset(0, 4).Value2
On Error Resume Next 'to hand any error during creation of below object
Set outMail = outApp.CreateItem(0)
With outMail
.To = sendTo
.cc = ccTo
.BCC = bccTo
.Body = msg
.Subject = subj
.Attachments.Add atchmnt
.Send 'this send mail without any notification. If you want see mail
End With
On Error GoTo 0 'To clean any error captured earlier
Set outMail = Nothing 'nullifying outmail object for next mail
Next cell 'loop ends
cleanup: 'freeing all objects ceated
Set outApp = Nothing
Application.ScreenUpdating = True
Application.ScreenUpdating = True
End Sub
You can either get into Outlook Automation or using CDO Mail. Although the example is for Access, both are demonstrated and would work in Excel as well, so look at https://www.devhut.net/2018/02/16/ms-access-how-to-send-an-email/