VBA script - List box of OFT templates in a file folder

[pwnyo1]

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!

[gmayor]

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.


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


Create a macro to call the userform e.g.

Sub CreateMessagefromTemplate() UserForm1.Show
End Sub
and add a button to the ribbon or QAT (Quick Access Toolbar) to call that macro.
The code uses the default names for the userform and the various controls. You can change these as required.

[pwnyo1]

WOW, this is SO great! Thankyou!
I did note, I think you missed an ENTER in your last two lines of code :


Sub CreateMessagefromTemplate() UserForm1.Show
End Sub


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.

[gmayor]

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