Consulting

Results 1 to 2 of 2

Thread: Email saved .oft template from excel

  1. #1

    Email saved .oft template from excel

    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

  2. #2
    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).Value
    any 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/show...cro-from-excel
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •