VBA UserForm sample: Select from a list of templates

Last reviewed on December 30, 2013   —  10 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.

Written by

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 Outlookforums.

10 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

  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.

  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

  4. Pierre

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

    Any help would be much appreciated.
    Thanks

Leave a Reply

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