I recently decided to create an Outlook Template file to try and save some time when sending some standardized e-mails.

Once created, I was surprised the number of clicks required to simply start a new e-mail based on a template file

1. From the Home tab in the Ribbon Click New items
2. Click on More items
3. Click on Choose Form …
4. Then change the Look in combo box to User Templates in File System
5. Make template my selection
6. Click the Open button

and this was going to be something I’d need to do frequently.  Stop the insanity!

What could I do to save myself all these clicks, selections …??

VBA, of course!

A quick look at Outlook documentation allowed me to quick see that Outlook offered the CreateItemFromTemplate method to create a new item based on the specified template file.

Thus, I quick came up with

    Dim oOutlook                 As Application
    Dim oMailItem             As Outlook.MailItem

    Set oOutlook = Outlook.Application
    Set oMailItem = oApp.CreateItemFromTemplate("C:\Users\Daniel\AppData\Roaming\Microsoft\Templates\Coding-Help-Mail.oft")
    oMailItem.Display

    Set oMailItem = Nothing
    Set oOutlook = Nothing

Although it worked, wanting to create code that would be more flexible, universal, I wanted VBA to determine the path to the user’s AppData\Roaming folder automatically and so I modified the original version to:

    Dim oOutlook                 As Application
    Dim oMailItem             As Outlook.MailItem

    Set oOutlook = Outlook.Application
    Set oMailItem = oOutlook.CreateItemFromTemplate(Environ("AppData") & "\Microsoft\Templates\Coding-Help-Mail.oft")
    oMailItem.Display

    Set oMailItem = Nothing
    Set oOutlook = Nothing

I should however mention that technically speaking Environ() can be spoofed, so perhaps a more reliable method should be used and you can explore my article on Getting The Path Of System Folders, link provided below, should you wish to harden the code.

Once I had the code taken care of and functional, all that was left to do was add a button to call it to my QAT.

1. Right-Click on the QAT  and select Customize Quick Access Toolbar …
2. Change the Choose commands from combo box to Macros
3. Select the Macro
4. Click on the Add >> button (to add it to my QAT)
5. Click OK

Now, I have an easily accessible button to create a new e-mail from my template anytime I want that only requires a single click!

Not bad for a couple minutes of work, 6 steps down to 1!

Easy, once you know how.

Useful Resources

Create an email message template - Microsoft Support

Create a template for email messages that you frequently send with similar contents.

Application.CreateItemFromTemplate method (Outlook)

Creates a new Microsoft Outlook item from an Outlook template (.oft) and returns the new item.

VBA - Getting The Path Of System Folders

Whether it is to save, retrieve files, define the initial file dialog folder, … we often want to work with existing system folders. In this article, I thought I’d showcase a few possible ways to retrieve the path of system folders, or as some people refer to them as special folders. Specifically, I will cover: Environ Function WScript.Shell Shell.Application PowerShell SHGetFolderPath SHGetKnownFolderPath