Solved

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

Posted on 2013-11-16
3
333 Views
Last Modified: 2013-11-16
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
Comment
Question by:Tocogroup
  • 2
3 Comments
 
LVL 6

Expert Comment

by:Michael
ID: 39653345
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
 
LVL 6

Accepted Solution

by:
Michael earned 500 total points
ID: 39653373
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
 

Author Closing Comment

by:Tocogroup
ID: 39653414
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you don't know how to downgrade, my instructions below should be helpful.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now