Email saved .oft template from excel

[jrobertson83]

Hello,

I have pieced this code together from various sites that will go down the excel file and email all the email addresses I have populated.
My question is, the code just sends an email with whatever the body is in that column. Is there a way I can have those messages/body a saved .oft template?

Code i Have:

Set sh = ThisWorkbook.Sheets("Sheet1")


Dim OA As Object
Dim msg As Object


Set OA = CreateObject("Outlook.Application")


Dim i As Integer
Dim last_row As Integer


last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))


For i = 2 To last_row
Set msg = OA.Createitem(0)


msg.to = sh.Range("A" & i).Value
msg.cc = sh.Range("B" & i).Value
msg.Subject = sh.Range("C" & i).Value
msg.body = sh.Range("D" & i).Value


If sh.Range("E" & i).Value <> "" Then
msg.Attachments.Add sh.Range("E" & i).Value
End If


msg.Send


Next i




MsgBox "Update Request Sent"


End Sub

[gmayor]

Yes you can use a template


Set msg = OA.CreateItemFromTemplate("D:\Path\templatename.oft")
however if you use the line

msg.body = sh.Range("D" & i).Valueany text in the template will be overwritten by that content.
If you want to edit the body of the message to include your Excel data you need a different approach - see the code I posted at http://www.vbaexpress.com/forum/showthread.php?63392-search-and-send-email-from-outlook-using-macro-from-excel