Using VBA to send email template with Replace Method

[petaaalouise]

Hey Guys,

I've been searching the forums and have found answers that should technically work however they don't and I'm not to sure where I'm going wrong.

I have a code set from excel to open a email template and email specific contacts in my data sheet.

The Email template in HTML format.

When I open the email template without the replace method it works perfectly with the correct formatting. However, when I open it with replace methods in the code it takes away the formatting of the template.

I have change the .Body Section to .HTMLBody and it then has correct text formatting but the replace method does not work.

Can anyone help


Private Sub CommandButton1_Click()
Dim olApp As Object
Dim olMail As Object

With Sheets("Tender List")
For Each Cell In Range("A2:A1000")
If Cell.Value = True Then

toBCC = toBCC & ";" & Cell.Offset(0, 7).Value
bPackage = Cell.Offset(0, 2).Value
caName = TextBox3.Value
tDate = TextBox1.Value
tTime = TextBox2.Value
tProject = TextBox4.Value
tDropbox = TextBox5.Value

End If

Next
End With

toBCC = Mid(toBCC, 2)

On Error Resume Next
Set olApp = GetObject("Outlook.Application")
If Error Then
Set olApp = CreateObject("Outlook.Application")
IsCreated = True
End If
olApp.Visible = True
On Error GoTo 0

On Error Resume Next
Set olMail = olApp.CreateItemFromTemplate("R:\Quality\1. Resource Documents\Outlook Templates\Invitation to Tender.oft")
If Error Then
Set olMail = olApp.CreateItemFromTemplate("F:\Data\Quality\1. Resource Documents\Outlook Templates\Invitation to Tender.oft")
End If
olMail.Visible = True
On Error GoTo 0

With olMail
.to = ""
.CC = ""
.Subject = "Invitation to Tender of Package - " & tProject
.BCC = toBCC
.Body = Replace(.Body, "<<Package Name>>", bPackage)
.Body = Replace(.Body, "<<Name>>", caName)
.Body = Replace(.Body, "<<Date>>", tDate)
.Body = Replace(.Body, "<<Time>>", tTime)
.Body = Replace(.Body, "<<Project Name>>", tProject)
.Body = Replace(.Body, "<<Dropbox Link>>", tDropbox)
.Display

End With

Unload Me

 

[mrmmickle1]

My guess would be that your overwriting the .body portion.

When you want to add something to a string you use code like this....

   StrSQL = "blah blah blah"
   StrSQL = StrSQL & vbLf & "more blah blah blah"

It looks like you are completely replacing things....

You may try to string everything together and then use

.body = MYFINALSTRING