Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

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
0
Tocogroup
Asked:
Tocogroup
  • 2
1 Solution
 
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
 
MichaelBusiness 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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now