VBA UserForm sample: Select from a list of templates

Last reviewed on December 30, 2013   —  12 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.
  4. Create a userform and add controls to it

  5. Right click on the Command button and choose Properties.
  6. Type OK (or Use Template) in the Caption field.
  7. Type btnOK in the (Name) field.
  8. add controls to a user form

  9. Right click on the UserForm and choose View Code.
  10. Paste the code below into the code window.
  11. 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

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.

About Diane Poremsky

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.

Please post long or more complicated questions at Outlook forums by Slipstick.com.

12 responses to “VBA UserForm sample: Select from a list of templates”

  1. Paul

    Hi Diane,

    This code is very close to what I am trying to achieve.

    I would like to have a Userform with 2 ComboBoxes, first to define the project, e.g. - "Boat-1", "Boat-2", Boat-3" etc. and a second to define the purpose of the email. I have 4 template emails for each project, "For Information", "For Provisional Comment", "For Construction" and "For Final Issue". Depending on the information selected in both ComboBoxes a specific template will be selected.

    I have been trying to modify the code above but I have a very limited knowledge with VBA but am keen to learn. Maybe a simpler solution would be 1 Userform to select project and 1 for email reason.

    Any help would be much appreciated.

    Regards, Paul

    1. Diane Poremsky

      I think one userform would be easiest. I'll try and make some time this weekend to put something together. It's really not hard to work with, once you get it started.

  2. Paul

    Hi Diane, I'm still working on this (albeit, slowly). After laying out the userforms I think using a combobox for selecting the project and then a project specific form with option buttons "For Information" etc. would work better.

    I have a macro that opens an email template but cannot seem to make this work within a combobox or optionbutton.

    Again, any help would be appreciated.

    Thanks, Paul.

    1. Diane Poremsky

      Sorry, I hadn't had time to spend much time on it. Will the option buttons choose a template? Will it be the same templates for each project or a different set of templates? Is the project selection going to the the subject, category etc? If so, two comboboxes will work. Not that option buttons won't...

      Another user is working on a project that uses two comboboxes in the comments here. A working macro sample is at http://sdrv.ms/YrSLkx - the userform / combobox code would look like this:

      Private Sub UserForm_Initialize()
      With ComboBox1
      .AddItem "Potential client"
      .AddItem "New client welcome letter"
      End With

      With ComboBox2
      .AddItem "whatever"
      .AddItem "more whatever"
      End With

      End Sub

      Private Sub CommandButton1_Click()
      lstNo1 = ComboBox1.ListIndex
      lstNo2 = ComboBox2.ListIndex
      Unload Me
      End Sub

  3. Paul

    no need to apologise I am grateful for any help as and when it comes. I understand you must be very busy.

    To answer your questions,
    optionbutton then press ok will choose a template;

    it will be different set of templates per project;

    I think it will be part of the subject category i.e. " Document Issue:- " etc. (if that is want you mean)

    I've had a look at the 2 combobox link and it looks quite involved. I am struggling to follow it all.

    Maybe to keep the code simpler (correct me if i'm wrong) a main userform could only have a combobox selecting projects, so I can add projects to the combobox list when required and selecting an item from this combobox only needs to take me to project specific userform which I can create when a new project arrives.

    Then the project specific userform will have 4 optionbuttons to select the template from the relevant template set.

    How does this sound? Does this make it simpler?
    Thanks, Paul

    1. Diane Poremsky

      That would make it less confusing in use. It will probably be just as confusing to program though.

      Userform1 would use

      Private Sub UserForm_Initialize()
      With ComboBox1
      .AddItem "Userform2 project name"
      .AddItem "Userform3 project name"
      End With
      End Sub

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

      The macro with the select case would be something like this - I have not tested it to see if it works to put userform.show in the case or if we need to use variables and call it later.

      Public lstNum As Long

      Public Sub ChooseTemplate()
      Dim strForm as string
      UserForm1.Show
      Select Case lstNum
      Case -1
      ' -1 is what you want to use if nothing is selected
      strForm = UserForm2.Show
      Case 0
      strForm = UserForm2.Show
      Case 1
      strForm = UserForm3.Show
      End Select
      End sub

      Then useform2 & 3 would basically repeat the process (and use the code in the original articles) with links to the templates.

      Or....
      select a project and that selection shows the available options. Unhiding the options would be better than enabling grayed out options because it's less confusion on the screen. But I don't know if its possible. Let me think on this one. If nothing else, it will make a good article. :)

  4. Pierre

    Hi Diane,
    Can I connect to SQL Server from outlook?

    Any help would be much appreciated.
    Thanks

    1. Diane Poremsky

      You can, using VBA or an odbc connector. It's often easier to go through Access - it has an outlook connector (i think it's still in Access 2013, will need to double check).

    2. Pierre

      Thanks for your reply :)
      do you have any sample code or link ? this is my first time I use VBA and ODBC.

      Any help would be much appreciated.
      Thanks

    3. Diane Poremsky
  5. Ernie Faulkner

    Hi Diane, first time contacting you but I have followed you on and off over many years :-)

    I believe you put me onto a company called email templates way back. Been using them for a long time but can't get any support now out of them which is a shame.

    I have been trying to follow one of your VBA examples but failed, kept getting an error Compile error - Ambiguous name detected:

    Private Sub CommandButton1_Click()
    lstNo = ComboBox1.ListIndex
    Unload Me
    End Sub

    This is what I am trying to achieve, I have a list of templates, I want a dropdown list box to display when I click a macro from my Ribbon (Outlook 2010) select the template and it's loaded.

    Sounds simple,

    I have been following this on http://www.slipstick.com/developer/code-samples/vba-userform-sample-select-list-templates/

    Any help you can give would be appreciated.

Leave a Reply

If the Post Comment button disappears, press your Tab key.

This site uses XenWord.