• Outlook User
  • Exchange Admin
  • Office 365
  • Outlook Developer
  • Outlook.com
  • Outlook Mac
  • Common Problems
    • Outlook BCM
    • Utilities & Addins
    • Video Tutorials
    • EMO Archives
    • Outlook Updates
    • Outlook Apps
    • Outlook & iCloud Issues
    • Forums

VBA UserForm sample: Select from a list of templates

Slipstick Systems

› Developer › Code Samples › VBA UserForm sample: Select from a list of templates

Last reviewed on January 6, 2018   —  59 Comments

January 26, 2013 by Diane Poremsky 59 Comments

This is a version of the macro and userform from Select from a list of subjects before sending a message and uses a userform to display a list of templates to select from to create a new message to the selected contact.

Create the Userform

  1. Right click on Project1 and select Insert > UserForm
  2. Open the control Toolbox and select a ComboBox and add it to the UserForm.
  3. Add a Command button. Create a userform and add controls to it
  4. Right click on the Command button and choose Properties.
  5. Type OK (or Use Template) in the Caption field.
  6. Type btnOK in the (Name) field.
  7. add controls to a user form

  8. Right click on the UserForm and choose View Code.
  9. Paste the code below into the code window.
  10. Change the Template display names as desired. This list is for your reference only, not the actual template file name. The filename is set in the VBA macro code.

Private Sub UserForm_Initialize()
  With ComboBox1
    .AddItem "Potential client"
    .AddItem "New client welcome letter"
    .AddItem "Work order approval"
    .AddItem "Existing client"
    .AddItem "Payment overdue"
    .AddItem "Invoice"
  End With
End Sub

Private Sub btnOK_Click()
    lstNum = ComboBox1.ListIndex
    Unload Me
End Sub

Note, you will need to have a reference to the Forms library in Tools, References.
Add the forms library as a reference
If you receive a "User-defined type not defined" you are missing the reference to Microsoft Forms 2.0 Object Library. If its not listed, add C:\Windows\System32\FM20.dll or C:\Windows\FM20.dll as a reference.

Macro to call the UserForm

  1. Right click on Project1 and choose Insert > Module.
  2. Paste the code below into the Module.
  3. Change the template filenames in strTemplate.

Select a contact then run the macro to test it.

Public lstNum As Long

Public Sub ChooseTemplate()

Dim oMail As Outlook.MailItem
Dim oContact As Outlook.ContactItem

If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
 Set oContact = ActiveExplorer.Selection.Item(1)

Dim strTemplate As String
   UserForm1.Show

    Select Case lstNum
    Case -1
'  -1 is what you want to use if nothing is selected
         strTemplate = "template-1"
    Case 0
         strTemplate = "template-1"
    Case 1
        strTemplate = "template-2"
    Case 2
         strTemplate = "template-3"
    Case 3
         strTemplate = "template-4"
    Case 4
         strTemplate = "template-5"
    End Select

    strTemplate = "C:\Users\me\Templates\" & strTemplate & ".oft"
    Set oMail = Application.CreateItemFromTemplate(strTemplate)

With oMail
  .To = oContact.Email1Address
  .ReadReceiptRequested = True
  .Subject = "My Macro test"
  .Body = "Hi " & oContact.FirstName & "," & vbCrLf & vbCrLf & oMail.Body
  .Display
End With
  End If
Set oMail = Nothing

End Sub

Video tutorial

Create a toolbar button

You can create a ribbon or QAT button for the ChooseTemplate macro so it's easier to start the macro.

More Information

How to use Outlook's VBA Editor

VBA UserForm sample: Select from a list of templates was last modified: January 6th, 2018 by Diane Poremsky
  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Google+ (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Share on Skype (Opens in new window)
  • Click to share on Pocket (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to print (Opens in new window)

Related Posts:

  • Select from a List of Subjects before Sending a Message
  • Outlook VBA: Use a Text File to Populate a ListBox
  • Add Email Addresses to a Contact Group
  • Mail Merge to Email using an Outlook Macro

About Diane Poremsky

A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Leave a Reply

59 Comments on "VBA UserForm sample: Select from a list of templates"

2500
Photo and Image Files
 
 
 
Audio and Video Files
 
 
 
Other File Types
 
 
 
2500
Photo and Image Files
 
 
 
Audio and Video Files
 
 
 
Other File Types
 
 
 

  Subscribe  
newest oldest most voted
Notify of
Dan S
Dan S
Share On TwitterShare On Google

Diane
I am also having the problem where the value for lstnum is not getting passed back to the main macro. I do have "Public lstnum as long" at the top of the macro. I can see the value in the locals window but once i step back to the main macro, the lstnum variable is gone. Any ideas?

Vote Up00Vote Down Reply
December 14, 2017 10:54 am
Les hunter
Les hunter
Share On TwitterShare On Google

Hi Diane.
I have Used your code to create a macro to call a list of templates and it works fine.
However I have a problem in so much as in my templates I have the email address and some greeting text in the body.
I can't get the macro to run from a button on the ribbon, how can I call the contact email address, which is already on the template, from within the macro,

Vote Up00Vote Down Reply
November 10, 2017 10:44 am
Diane Poremsky
Diane Poremsky
Share On TwitterShare On Google

you need to be able to find the address, either using mid/instr functions or regex. once you find the address and put it in a variable, you can use it in the to field.

Vote Up00Vote Down Reply
November 20, 2017 12:27 am
Les Hunter
Les Hunter
Share On TwitterShare On Google

Hi Diane
Thank you for your response everything works fine now,
Thanks again, have a good day
Les hunter

Vote Up00Vote Down Reply
November 20, 2017 3:01 am
Michael-Javier Popig
Michael-Javier Popig
Share On TwitterShare On Google

Hi Diane,
Your macro does not seem to work properly at my system.
Error code: Run time error'-2147352567 (80020009)': Array-Index out of range

Debugging in line:
If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then

Dou you have a tip?

Thank you!
Regards,
Michael

Vote Up00Vote Down Reply
September 11, 2017 11:01 am
Diane Poremsky
Diane Poremsky
Share On TwitterShare On Google

Did you select a contact before you ran it?

Vote Up00Vote Down Reply
September 11, 2017 2:57 pm
Michael-Javier Popig
Michael-Javier Popig
Share On TwitterShare On Google

Yes I did. Unfortunately "nothing" happens. Macro security is set on low. I am working with Outlook 2013 (15.0.4953.1001)

Vote Up00Vote Down Reply
September 13, 2017 9:02 am
Diane Poremsky
Diane Poremsky
Share On TwitterShare On Google

Can you post the code you are using? The error comes up before any of the other code is touched - it really looks like there is no contact selected.

Vote Up00Vote Down Reply
September 13, 2017 10:52 pm
Ajinkya Rasal
Ajinkya Rasal
Share On TwitterShare On Google

Hello Diane,
Same as what you demonstrated,I am trying to access the a values selected by the user in the combo box.
But for some reason I am not able to access the same.I followed the steps as follows

1.I have a userForm with comboBox and a module.
2.I initialized the combobox element in the module
3.In userform code,assigned the selected value to a varible ,"cValue", in btnOk_click() event without declaring it
4.Then in module I am trying to access the cValue variable but It's returning me empty nothing.

Thats the same thing what your video suggests.Could you please help

Vote Up00Vote Down Reply
July 5, 2017 3:50 am
Diane Poremsky
Diane Poremsky
Share On TwitterShare On Google

At the top of the module, use Public cValue As Long then use select case or if statements in the macro to match the cValue to what you want to do.

Vote Up00Vote Down Reply
July 5, 2017 7:42 am
Bob Brown
Bob Brown
Share On TwitterShare On Google

Hi Diane. Love your work and wondering if you can solve my problem. I'm using excel 2013.

I am using simple user form which puts customers and other data in separate columns in data base from col. 1 to col. 6
e.g. date, docket no, client, deposit, cost, delivery etc.
I want to put the same layout database on my interface and display all data relating to a single date vertically. I can do it with a vlookup function but it only returns the first value. I prefer not to use pivot tables if possible.
Many thanks
Bob

Vote Up00Vote Down Reply
February 21, 2017 7:08 am
Diane Poremsky
Diane Poremsky
Share On TwitterShare On Google

You want to do this in an userform or on the Excel sheet? In a userform, you'll need to use a multicolumn listbox. In Excel, i'm not sure why vlookup is not working, but it's an my area of expertise either. MrExcel forums would be a better place for help with Excel-specific questions.

Vote Up00Vote Down Reply
February 21, 2017 12:28 pm
Bob Brown
Bob Brown
Share On TwitterShare On Google

Morning again. Many thanks for your prompt reply.

I will do as you suggest and post in Mrexcel.

Kind regards Bob

Vote Up00Vote Down Reply
February 21, 2017 4:23 pm
Pedro Alves
Pedro Alves
Share On TwitterShare On Google

Hi Diane, Is there any way to submit a form to a client, have him/her select radio options or write text in a text box, and send the results back to me?
Many thanks. PEdro

Vote Up00Vote Down Reply
February 17, 2017 2:11 pm
Diane Poremsky
Diane Poremsky
Share On TwitterShare On Google

Not easily within outlook - it would be easier to send a link to a web form for them to fill out. This would work with any email client too. If you use an outlook form, you'd need to send the form definition (greatly increaes message size) but it would only work in outlook and may have restrictions due to security settings.

Vote Up00Vote Down Reply
February 19, 2017 11:22 am
Gary Lewis
Share On TwitterShare On Google
Hi Diane; I am trying to use your code example to populate the .TO with values from a string variable set in my macro, e.g. strP0list = "List1-1@somewhere.com;List1-2@somewhere.com" I then insert your case statement with the following changes Select Case lstNum Case -1 ' -1 is what you want to use if nothing is selected strDistroList = "Please enter a valid email" Case 0 strDistroList = strP0list Case 1 strDistroList = strP1list Case 2 strDistroList = strP2list Case 3 strDistroList = strP3list end select I then call my code OtlNewMail.HTMLBody = Signature With OtlNewMail .To = strDistroList .CC = "someone@somewhere.com" The problem is that I keep getting the same value back the string that is in my first defined variable strP0list no mater what item I pick on the form. I have posted all the code for this as "How do I create a custom pick list in VB for an outlook automated email?" on stackoverflow. Would you be able to take a look and tell me where I have gone wrong? BTW I am very new to this stuff and an old dog trying to learn new tricks...
Vote Up00Vote Down Reply
January 9, 2017 2:19 pm
Diane Poremsky
Diane Poremsky
Share On TwitterShare On Google

Did you get this solved? Do you have this at the very top of the module?
Public lstNum As Long
and this to hold the count from the form?
Select Case lstNum
Case -1
-- snip --

Vote Up00Vote Down Reply
February 21, 2017 12:25 pm
Brent Schneider
Brent Schneider
Share On TwitterShare On Google

Hey Diane, Is there anyway that you know of that would alone you to share a create userforms among users in Outlook? I am solely trying to avoid having to recreate the form on other computers. Thoughts?
Take care,
Brent

Vote Up00Vote Down Reply
December 2, 2016 4:47 pm
Diane Poremsky
Diane Poremsky
Share On TwitterShare On Google

You'd need to compile the project using visual studio then users would install it like any other addin. But... you don't have to completely recreate the userform on other computers, you can export it and the users can import it. If the form uses a macro in a module, you'll need to export that too. If no one is using macros, you could just copy the vba project - this takes about as many steps to use as importing though.

Vote Up00Vote Down Reply
December 3, 2016 12:24 am

Visit Slipstick Forums.
What's New at Slipstick.com

Latest EMO: Vol. 23 Issue 10

Subscribe to Exchange Messaging Outlook






Our Sponsors

  • Popular
  • Latest
  • Week Month All
  • This operation has been cancelled due to restrictions This operation has been cancelled due to restrictions
  • Adjusting Outlook's Zoom Setting in Email Adjusting Outlook's Zoom Setting in Email
  • How to Remove the Primary Account from Outlook How to Remove the Primary Account from Outlook
  • Pictures Don't Display in Outlook Messages Pictures Don't Display in Outlook Messages
  • To Cc or Bcc a Meeting Request To Cc or Bcc a Meeting Request
  • Outlook is Not Recognized as the Default Email Client Outlook is Not Recognized as the Default Email Client
  • The Signature or Stationery and Fonts button doesn't work The Signature or Stationery and Fonts button doesn't work
  • Remove a password from an Outlook *.pst File Remove a password from an Outlook *.pst File
  • Understanding Outlook's Auto-Complete Cache (*.NK2) Understanding Outlook's Auto-Complete Cache (*.NK2)
  • Exchange Account Set-up Missing in Outlook 2016 Exchange Account Set-up Missing in Outlook 2016
  • iCloud error: Outlook isn't configured to have a default profile iCloud error: Outlook isn't configured to have a default profile
  • Setting Custom Reminder Times Setting Custom Reminder Times
  • Add Additional Addresses to Room Mailboxes Add Additional Addresses to Room Mailboxes
  • Create a Task from a Message and include the Attachment Create a Task from a Message and include the Attachment
  • Forward email messages by date Forward email messages by date
  • Open multiple Outlook windows when Outlook starts Open multiple Outlook windows when Outlook starts
  • Office 365 Fraud Detection Checks Office 365 Fraud Detection Checks
  • Outlook Request: Calendar Details View Outlook Request: Calendar Details View
  • Outlook's "Not Junk" option isn't available Outlook's "Not Junk" option isn't available
  • Outlook Tip: Show all Mondays in the Calendar Outlook Tip: Show all Mondays in the Calendar
Ajax spinner

Newest VBA Samples

Open multiple Outlook windows when Outlook starts

Set most frequently used Appointment Time Zones

How to change the From field on incoming messages

VBA: File messages by client code

Update Contact Area Codes

Set a reminder on selected items in the To-Do List

Replicate GTD: Create a task after sending a message

Use VBA to read fields in attached messages

Move Outlook Folders using VBA

Replicate Smart Lookup using a macro

Recent Bugs List

Microsoft keeps a running list of issues affecting recently released updates at Fixes or workarounds for recent issues in Outlook for Windows.

Windows 10 Issues

  • iCloud, Outlook 2016, and Windows 10
  • Better Outlook Reminders?
  • Coming Soon to Windows 10: Office 365 Search
  • Outlook Links Won’t Open In Windows 10
  • BCM Errors after Upgrading to Windows 10
  • Outlook can’t send mail in Windows 10: error Ox800CCC13
  • Missing Outlook data files after upgrading Windows?

Outlook 2016 Top Issues

  • The Windows Store Outlook App
  • Emails are not shown in the People Pane (Fixed)
  • Calendars aren’t printing in color
  • The Signature or Stationery and Fonts button doesn’t work
  • Outlook’s New Account Setup Wizard
  • BCM Errors after October 2017 Outlook Update
  • Excel Files Won’t Display in Reading Pane
  • Outlook 2016: No BCM
  • Exchange Account Set-up Missing in Outlook 2016

VBA Basics

How to use the VBA Editor

Work with open item or selected item

Working with All Items in a Folder or Selected Items

VBA and non-default Outlook Folders

Backup and save your Outlook VBA macros

Get text using Left, Right, Mid, Len, InStr

Using Arrays in Outlook macros

Use RegEx to extract message text

Paste clipboard contents

Windows Folder Picker

Custom Forms

Designing Microsoft Outlook Forms

Set a custom form as default

Developer Resources

Developer Resources

Developer Tools

Outlook-tips.net Samples

VBOffice.net samples

OutlookCode.com

SlovakTech.com

Outlook MVP David Lee

MSDN Outlook Dev Forum

Repair PST

Convert an OST to PST

Repair damaged PST file

Repair large PST File

Remove password from PST

Merge Two Data Files

Sync & Share Outlook Data

  • Share Calendar & Contacts
  • Synchronize two computers
  • Sync Calendar and Contacts Using Outlook.com
  • Sync Outlook & Android Devices
  • Sync Google Calendar with Outlook
  • Access Folders in Other Users Mailboxes
  • “Live” Group Calendar Tools

Convert to / from Outlook

  • Converting Messages and Calendar or
    Address books
  • Moving Outlook to a New Computer
  • Moving Outlook 2010 to a new Windows computer
  • Moving from Outlook Express to Outlook

Recover Deleted Items

  • Recover deleted messages from .pst files
  • Are Deleted Items gone forever in Outlook?

Outlook 2013 Absolute Beginner's Guide

Diane Poremsky [Outlook MVP]

Make a donation

Calendar Tools

Schedule Management

Calendar Printing Tools

Calendar Reminder Tools

Calendar Dates & Data

Time and Billing Tools

Meeting Productivity Tools

Duplicate Remover Tools

Mail Tools

Sending and Retrieval Tools

Mass Mail Tools

Compose Tools

Duplicate Remover Tools

Mail Tools for Outlook

Online Services

Productivity

Productivity Tools

Automatic Message Processing Tools

Special Function Automatic Processing Tools

Housekeeping and Message Management

Task Tools

Project and Business Management Tools

Choosing the Folder to Save a Sent Message In

Run Rules on messages after reading

Help & Suggestions

Outlook Suggestion Box (UserVoice)

Slipstick Support Services

Contact Tools

Data Entry and Updating

Duplicate Checkers

Phone Number Updates

Contact Management Tools

Sync & Share

Share Calendar & Contacts

Synchronize two machines

Sharing Calendar and Contacts over the Internet

More Tools and Utilities for Sharing Outlook Data

Access Folders in Other Users Mailboxes

View Shared Subfolders in an Exchange Mailbox

"Live" Group Calendar Tools

Home | Outlook User | Exchange Administrator | Office 365 | Outlook.com | Outlook Developer
Outlook for Mac | Outlook BCM | Common Problems | Utilities & Addins | Tutorials
Outlook & iCloud Issues | Outlook Apps
EMO Archives | About Slipstick | Advertise | Slipstick Forums
Submit New or Updated Outlook and Exchange Server Utilities

Send comments using our Feedback page
Copyright © 2018 Slipstick Systems. All rights reserved.
Slipstick Systems is not affiliated with Microsoft Corporation.

You are going to send email to

Move Comment