Solved

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

Posted on 2013-11-16
3
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

623 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