In excel I have three columns:
A: B: C:
UserName UsermailAddress RecordToBeDeleted


I'm using the code below in excel to send and email from Outlook (using an OFT file) to each of the email addresses in Column B of the excel spread sheet.

The code I have works as far as sending to each of the email addresses in Column B

The problem I have is that in the email message (the OFT file) I have two keywords (UserName and RecordToBeDeleted) that I need to replace in each email that goes with that mail address.

The code finds the two key words in the OFT file but just removes them or replaces them with a null entry.

What am I doing wrong, so that I can replace the words from the excel file in the OFT mail message.

The VBA I have in the excel sheet is:

Sub RunEmails()
    Dim i As Integer
    Dim rngCell As Range
    
    For Each rngCell In Range("A2", "A" & CStr(Cells(Rows.Count, "A").End(xlUp).Row))
        Call DoTest(rngCell.Offset(0, 1).Value, "JDoe@abc.com", rngCell.Offset(0, 2).Value, rngCell.Value)
    Next rngCell
    
End Sub

Sub DoTest(EmailAddress As String, CCAddress As String, RecordToBeDeleted As String, UserName As String)
    Dim oApp As New Outlook.Application
    
    Dim olNewMail As Outlook.MailItem
    Const Template As String = "D:\Documents\list\list-mail.oft"

    Set olNewMail = oApp.CreateItemFromTemplate(Template)
    
    olNewMail.Recipients.Add EmailAddress
    olNewMail.HTMLBody = Replace(olNewMail.HTMLBody, ">UserName<", UserName)
    olNewMail.HTMLBody = Replace(olNewMail.HTMLBody, ">RecordToBeDeleted<", RecordToBeDeleted)
    olNewMail.VotingOptions = "Can distribution list be Deleted YES;Can distribution list be Deleted NO"
    
    olNewMail.Send
End Sub