For multiple messages, as Paul suggests, mailmerge is the obvious approach - see
E-Mail Merge Add-in
For individual messages rather than use html codes, the better approach is to use the Outlook word editor. You will need to copy a function from the web page indicated in the code to ensure Outlook is started correctly.
Using this method is simpler as it does not require long strings of html code, and is similar to VBA programming in Word.
Insert the appropriate Excel ranges for name, e-mail address and invoice number in place of the fixed values in the code.
Code:
Public Sub CreateEmail()
'Graham Mayor - https://www.gmayor.com - Last updated - 18 May 2021
'Requires the code - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to either retrieve an open instance of Outlook or open Outlook if it is closed.
Dim olApp As Object
Dim olMail As Object ' Outlook.MailItem
Dim olInsp As Object ' Outlook.Inspector
Dim wdDoc As Object ' Word.Document
Dim wdRange As Object ' Word.Range
Set olApp = OutlookApp()
Set olMail = olApp.CreateItem(0)
With olMail
.BodyFormat = 2
.Display
.To = "someone@somewhere.com"
.Subject = "Message Subject"
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set wdRange = wdDoc.Range
With wdRange
.collapse 1
.Font.Color = RGB(0, 0, 0)
.Font.Size = 11
.Font.Bold = False
.Text = "Hi, " & "Name of person" & vbCr & vbCr & _
"Here is your invoice # " & "123456"
.collapse 0
.Text = " PAID "
.Font.Color = RGB(255, 0, 0)
.Font.Size = 16
.Font.Bold = True
.collapse 0
.Text = "more text and end of email" 'signature associated with account is retained.
.Font.Color = RGB(0, 0, 0)
.Font.Size = 11
.Font.Bold = False
End With
End With
lbl_Exit:
Set wdRange = Nothing
Set wdDoc = Nothing
Set olInsp = Nothing
Set olMail = Nothing
Set olApp = Nothing
Exit Sub
End Sub