Looking to run a script that produces a list box of all the templates in a file folder (must be dynamic). Then user can choose template and select OK, and template opens as new email.
Any ideas on where to start?
THANKYOU!
Looking to run a script that produces a list box of all the templates in a file folder (must be dynamic). Then user can choose template and select OK, and template opens as new email.
Any ideas on where to start?
THANKYOU!
That sounds simple enough.
Create a userform in the Outlook VBA editor (see https://www.gmayor.com/Userform.htm)
Add a list box and two command buttons.
In the userform code add the following. Change the path to the folder where the templates are stored. - This will usually be Word's user templates folder. To access the default location, if you have not changed it, enter or copy%appdata%\Microsoft\Templates
to the address window of Windows Explorer and you will be taken to the folder. Note that this folder is normally hidden.
Create a macro to call the userform e.g.Option Explicit 'Graham Mayor - https://www.gmayor.com - Last updated - 16 Feb 2021 Const strPath As String = "C:\Path\" 'the folder with the templates Private strFile As String Private Sub CommandButton1_Click() Dim strTemplate As String Dim olItem As Object strTemplate = strPath & ListBox1.Text Set olItem = CreateItemFromTemplate(strTemplate) Hide olItem.Display Unload Me End Sub Private Sub CommandButton2_Click() Unload Me End Sub Private Sub ListBox1_Click() Dim i As Integer With ListBox1 If .ListCount > 0 Then For i = 0 To .ListCount - 1 If .Selected(i) = True Then CommandButton1.Enabled = True Exit For End If Next i End If End With End Sub Private Sub UserForm_Initialize() With Me .Caption = "Create Message from Template" .CommandButton1.Enabled = False .CommandButton1.Caption = "Create Message" .CommandButton2.Caption = "Cancel" strFile = Dir$(strPath & "*.oft") While strFile <> "" .ListBox1.AddItem strFile strFile = Dir$() Wend End With End Sub
and add a button to the ribbon or QAT (Quick Access Toolbar) to call that macro.Sub CreateMessagefromTemplate() UserForm1.Show End Sub
The code uses the default names for the userform and the various controls. You can change these as required.
Graham Mayor - MS MVP (Word) 2002-2019
Visit my web site for more programming tips and ready made processes
http://www.gmayor.com
WOW, this is SO great! Thankyou!
I did note, I think you missed an ENTER in your last two lines of code :
didn't work, so I tried:Sub CreateMessagefromTemplate() UserForm1.Show End Sub
and that worked great! One last question (I'm being greedy), any chance to add to the code that rather than having to select the template and then clicking the open button, you could also just double click the template for it to open in a new email? Is that very difficult to add that functionality? Either way, thanks again! This is really awesome.Sub CreateMessagefromTemplate() UserForm1.Show End Sub
Sorry about the missing line break. This sometimes happens when pasting code into this forum and I didn't spot it . I did however spot and fix it in the following
As for the double click, add the following to the userform code
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Dim i As Integer With ListBox1 If .ListCount > 0 Then For i = 0 To .ListCount - 1 If .Selected(i) = True Then CommandButton1_Click Exit For End If Next i End If End With End Sub
Graham Mayor - MS MVP (Word) 2002-2019
Visit my web site for more programming tips and ready made processes
http://www.gmayor.com