Excel to Outlook Template help

[Romuline]

Hi there I am struggling with a bit of coding that hopefully someone can help me with. Basically I am trying to create an email using a template but I would like to populate parts of the template with data that is contained within cells in Excel.
Now I have the code and can create the email, populate the subject and the To fields from Excel and can populate the body of the email if it is empty, but I want to populate certain areas of the body that have pre determined text. E.g

Reporting Site : {Data imported from Excel}

Here is the code I have.

Sub SaveAsDraft()
Dim objOutlook As Object
Dim objMailMessage As Outlook.MailItem
Dim emlBody, sendTo As String
Dim wkbook As String
Application.ScreenUpdating = False
Set objOutlook = CreateObject("Outlook.Application")
Set objMailMessage = objOutlook.CreateItemFromTemplate("C:\Documents and Settings\Work\Desktop\Alert.oft")
sendTo = "#All Users"
emlBody = Range("A6")
With objMailMessage
.To = sendTo
.Body = emlBody
.Subject = "Sev " & Range("A1") & Range("A2") & Range("A3") & Range("A4")
.Display
.Save
.Close olPromtForSave
End With
End Sub

Thanks in advance for any help

 

[John_w]

One way is by putting placeholders in the template's email body, such as [A1], [A2], denoting the cell which populates that part of the template. Then use the VBA Replace function to replace each placeholder with the appropriate cell value, like this:

        .Body = Replace(.Body, "[A1]", Range("A1").Value)
        .Body = Replace(.Body, "[A2]", Range("A2").Value)