The goal is to send out mass emails using an outlook template as basis. I must be able to replace one placeholder term in the body on the outlook template.
The replace function does not work in the code below. The macro generates the e-mails, but the placeholder term "%CONTACT%" remains unchanged.
Sub send_mass_email()
Dim I As Integer
Dim OutApp As Object
Dim OutMail As Object
Dim NewMail As Outlook.MailItem
For I = 2 To Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set NewMail = OutApp.CreateItemFromTemplate("Path/path/testemail.oft")
On Error Resume Next
With NewMail
.To = Cells(I, 2).Value
.HTMLBody = Replace(NewMail.HTMLBody, "%CONTACT%", "TESTING")
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Next
End Sub
Tried numerous solutions without success. What am I missing?
CodePudding user response:
First of all, there is no need to create a new mail item in the code if you use the Application.CreateItemFromTemplate method which creates a new Microsoft Outlook item from an Outlook template (.oft) and returns the new item. So, you need to remove the following line of code:
Set OutMail = OutApp.CreateItem(0)
If the following line of code can't find the keyword:
.HTMLBody = Replace(NewMail.HTMLBody, "%CONTACT%", "TESTING")
Then the message body doesn't contain it. Try using the InStr function which returns a variant (Long
) specifying the position of the first occurrence of one string within another. Here is an example how it works:
Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".
' A textual comparison starting at position 4. Returns 6.
MyPos = Instr(4, SearchString, SearchChar, 1)
' A binary comparison starting at position 1. Returns 9.
MyPos = Instr(1, SearchString, SearchChar, 0)
' Comparison is binary by default (last argument is omitted).
MyPos = Instr(SearchString, SearchChar) ' Returns 9.
MyPos = Instr(1, SearchString, "W") ' Returns 0.
Also you can read more about the CreateItemFromTemplate
method in the How To: Create a new Outlook message based on a template article.
CodePudding user response:
I have found the error.
The template (.oft) was located in a shared folder that prevented the macro from being able to edit the template. For this reason, the program was unable to replace the text.
Changed the path, problem solved.