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.
Leave a Reply
51 Comments on "VBA UserForm sample: Select from a list of templates"
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.
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.
This is the change you need to make
-- snip --
Dim badAddresses As String
Dim fn As String, ff As Integer, txt As String
fn = "C:\Users\Diane\Documents\list.txt" '= 1 Then
-- snip --
you need to do something like this (add after the last DIM line in the code above) - but it needs some tweaking
fn = "D:\Documents\list.txt" '< --- .txt file path
txt = Space(FileLen(fn))
ff = FreeFile
Open fn For Binary As #ff
Get #ff, , txt
Close #ff
Dim myArray() As String
'Use Split function to return a zero based one dimensional array.
myArray = Split(txt, vbCrLf)
Dear Diana,
Unfortunately, the code provided gives me error:
Compile error: Ambiguous name detected: lstNum
Private Sub btnOK_Click()
lstNum = ComboBox1.ListIndex
Unload Me
End Sub
I am using MS Office 2013.
Any ideas what my cause the error?
that means something, possibly the property lstNum, is used elsewhere publicly and the macro doesn't know which one to use. Do you have any other macros in the module?
Ok, that is right, I double checked and rectified the error. Well now, when I run the coder, I am getting different mistake, which is as follows:
Run-time error ‘-2147287038 (80030002)’:
We can’t open ‘c:\@MS Outlook templates\External.oft.oft’. It’s possible the file is already open, or you don’t have permission to open it.
To check your permissions, right-click the file folder, then click Properties.
Dear Diana,
Yes, I removed the oft extension from the strTemplate, now I am getting another mistake. It is as follows:
Run-time error ‘91’
Object variable or With block variable not set
Did you copy the macros exactly and change only the template names? if so, did you delete any lines?
Did you set the reference to the Forms library in Tools, References? (The error message for not doing that is usually something different tho).
Add msgbox strTemplate right after the template is set in Set strTemplate line. Is it showing the correct path. (This is another one that is usually a different error)
That error means either the template is not in that folder, the template name is not correct, or it's already open (close outlook & reopen to fix the last)
‘c:\@MS Outlook templates\External.oft.oft’ leads me to believe it's the template name - two extensions. The code ads the extension:
strTemplate = "C:\Users\me\Templates\" & strTemplate & ".oft" - either remove & ".oft" from this line or use only the template name earlier in the code.
Hello Diana,
I finally was able to run the macro. It works great. May I request for some additional help: how can the code be changed so that instead of selecting a contact, I need to select an email from inbox or any other folder (could be pst) and when I click over user-created button, something like Reply with Template, the macro fires off? Basically, I need to use this macro for replying to incoming messages.
it just a few modifications to change it to use mail item - it should work with any selected message, in any folder.
change this line - you can change oContact to something else - if you do, replace all instances with the new object name.
Dim oContact As Outlook.mailitem
Remove this and the matching End if:
If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
change this to
.To = oContact.senderAddress
.Body = "Hi " & oContact.displayname & "," & vbCrLf & vbCrLf & oMail.Body
Thanks! Worked like a charm!
Fields are fields; properties and user forms are not listed in fields. I'm not are of any way to hijack the check address function.