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.
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?
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,
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.
Hi Diane
Thank you for your response everything works fine now,
Thanks again, have a good day
Les hunter
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
Did you select a contact before you ran it?
Yes I did. Unfortunately "nothing" happens. Macro security is set on low. I am working with Outlook 2013 (15.0.4953.1001)
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.
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
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.
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
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.
Morning again. Many thanks for your prompt reply.
I will do as you suggest and post in Mrexcel.
Kind regards Bob
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
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.
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...
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 --
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
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.