?
Solved

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

Posted on 2013-11-16
3
Medium Priority
?
377 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Changing a few Outlook Options can help keep you organized!
This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

719 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