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
- Right click on Project1 and select Insert > UserForm
- Open the control Toolbox and select a ComboBox and add it to the UserForm.
- Add a Command button.
- Right click on the Command button and choose Properties.
- Type OK (or Use Template) in the Caption field.
- Type btnOK in the (Name) field.
- Right click on the UserForm and choose View Code.
- Paste the code below into the code window.
- 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.
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
- Right click on Project1 and choose Insert > Module.
- Paste the code below into the Module.
- 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.
Is there a way to use the drop down list and adding a macro to find a saved email template based on what the user selects from the drop down?
hey hi, can any one tell me how to retrieve the mail id`s and info of the mail in the customized user form when we click on event send.
I tried to copy your code but the form does not load with the drop down values. What might I be looking for>
Get Object required in the initialization code
hello Diane,
I would like to have 3 modules that each require access to the same form (frmStationery) to select an option from a list. I have been able to avoid 'Ambiguous name detected by making
each module having their own public string....Public stationery1 ....stationery3;
Form:-
Private Sub ok_click()
Stationery1 = filePath & Me.ListBox.Text
Stationery2 = filePath & Me.ListBox.Text
Stationery3 = filePath & Me.ListBox.Text
Me.Hide
End Sub
Module1:-
Public stationery1
Sub macroGetStationery()
frmStationery.Show
If Stationery1 = "" Then Exit Sub
Unload frmStationery
....
End Sub
I just can't work out how I can do it. Do you have any suggestions?
hello Diane, thanks for all the code & how-to's you've provided thus far.
Is it possible to create 1 userform for more than 1 macro? If yes...how?
Thank you
Yes, you can call 1 userform from multiple macros - this line is all you need: UserForm1.Show. You will may need to use variables to pass a unique value to it.
Using the macro on this page as an example, instead of hard coding the template names, you could use global variables to set it, depending on which macro called the template. The macro sets the variable and they are passed to the form.
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem strOne
.AddItem StrTwo
End With
and
Dim strTemplate As String
strOne = "Whatever"
strTwo = "Something else"
UserForm1.Show
Hi Diane,
I am trying to create a macro for public folder in which I would be able to assign emails to Associates of my team. For eg. From non-assigned emails in sequence, I want to assign 4 emails to X, 5 emails to Y & 3 emails to Z.
Any assistance for this is greatly appreciated!!!
I am using office 365. Using VBA code is it possible to create 3 question form send to 10 customers and get their reply back from them. We didn't buy license for Microsoft forms or don't want to use survey monkey, would like to use via outlook and VBA. Please help me on this.
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?