How do I mail an Outlook message template from an Excel procedure ?

Hi,

Where do I start with this ? I want to create an Excel 2010 VBA procedure which takes an Outlook 2010 message template from the \AppData\Roaming\Templates folder, modifies it, attaches a file, and then sends it.

The procedure will need to modify the template as follows:
1. Supply email address in the 'To:' field
2. Supply subject line in the 'Subject:' field
3. Supply customer name in the HTML body.

Thanks in anticipation
Toco
TocogroupAsked:
Who is Participating?
 
MichaelConnect With a Mentor Business AnalystCommented:
I forgot to mention: the code will just generate the mail and display it. Once you're happy with the result, you can comment out the .Display command and uncomment the .Send command to let Excel send the message automatically.

Additionally, below I added code to attach a file to the message (change the path accordingly) and an example how you can add a customer's name to the htmlbody, where the customer's name would be in cell A1 of the active worksheet:

Sub CreateFromTemplate()
    Dim myOlApp As Object
    Dim MyItem As Object
    Dim MyPath As String, MyFilePath as String
    
    MyPath = Environ("USERPROFILE") & "\AppData\Roaming\Templates\template.oft"
    MyFilePath = "C:\attachment.xls"

    Set myOlApp = CreateObject("Outlook.Application")
    Set MyItem = myOlApp.CreateItemFromTemplate(MyPath)
    With MyItem
        .to = "myaddress@mail.com"
        .Subject = "This is my subject"
        .HTMLBody = "<span style=""font-family : verdana;font-size : 10pt"">" & _
                "<p>Hello " & _
                Range("A1").Value & _
                " and thank You for your order.</p></span>" & _
                .HTMLBody
        .Attachments.Add MyFilePath
        .Display
        '.Send 'Uncomment to send automatically
    End With
End Sub

Open in new window

Joop
0
 
MichaelBusiness AnalystCommented:
Hi Toco,

one way is to start here:
Sub CreateFromTemplate()
    Dim myOlApp As Object
    Dim MyItem As Object
    Dim MyPath As String
    
    MyPath = Environ("USERPROFILE") & "\AppData\Roaming\Templates\template.oft"
    Set myOlApp = CreateObject("Outlook.Application")
    Set MyItem = myOlApp.CreateItemFromTemplate(MyPath)
    With MyItem
        .to = "myaddress@mail.com"
        .Subject = "This is my subject"
        .Display
        '.Send 'Uncomment to send automatically
    End With
    
End Sub

Open in new window


It creates a mail from a specified template. Adjust the path,recipient and subject accordingly.

Joop
0
 
TocogroupAuthor Commented:
That's excellent. Didn't realise there was a separate CreateItem command for templates.

Thanks a lot for your help.
Much appreciated
0
All Courses

From novice to tech pro — start learning today.