Open Windows Explorer from Outlook to choose template

Question

I am trying to write a macro that allows a user to select a .oft from a directory. I have been able to get a simple macro working that will open a specific .oft and modify the "from" field - this is what I have so far.

Sub EmailTemplateW10()
    Set msg = Application.CreateItemFromTemplate("c:\test\test.oft")
    msg.SentOnBehalfOfName = "user@domain.com"
    msg.Display
    Set msg = Nothing
End Sub

That works great, but there are numerous .oft files in the directory I am working with. I did find some articles that talk about setting up a toolbar with drop-downs and creating a macro for each .oft in the directory. I assume there has to be a better way than making a macro for each template.

I tried using this :

Sub EmailTemplateW10()

    Set msg = Application.CreateItemFromTemplate(Demo)
    msg.SentOnBehalfOfName = "user@domain.com"
    msg.Display
    Set msg = Nothing
End Sub

Function Demo()

Call Shell("explorer.exe" & " " & "C:\test\", vbNormalFocus)

End Function

Windows Explorer will come up - but the macro throws an error and stops when you acknowledge the error.

Is there perhaps some way to have the user select the file through explorer.exe and save the path of the selected file to a variable, and then pipe that into .CreateItemFromTemplate?

Does this answer your question? [Filepicker VBA to select file and store the file name not working](https://stackoverflow.com/questions/51828080/filepicker-vba-to-select-file-and-store-the-file-name-not-working) — HackSlash, Feb 12 '20 at 18:43
The thing you want is called the `FileDialog` or File picker dialog. It's a built in function that allows the user to select a file and returns the filename — HackSlash, Feb 12 '20 at 18:44
Thank you very much for the reply! Unfortunately Outlook does not support the ```FileDialog``` object. I did find another stack overflow thread with a possible work around however - https://stackoverflow.com/questions/43919275/outlook-application-filedialog-not-found -- testing this now. — Richard Lyman, Feb 12 '20 at 18:57
Thank you HackSlash, you pointed me in the right direction and I was able to figure it out. Thank you! — Richard Lyman, Feb 12 '20 at 20:18

score 1 · Accepted Answer · answered Feb 12 '20 at 20:12

After some digging around I got this to work. I am obviously a VBA noob so i take no credit for the code - I was just able to mash it together and get it to work. Maybe someone in the future will find it helpful.

Public Function aBrowseForFile(aStartFolder As String) As String

On Error GoTo Err_txtBrowseForFile

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
Dim fDialog As Office.FileDialog
Dim varfile As Variant
Dim strPath As String
Dim strFilter As String, strFileName As String
Dim Main_Dir As String, DefFolder As String

Set fDialog = xlApp.Application.FileDialog(msoFileDialogFilePicker)

With fDialog
    .InitialView = msoFileDialogViewThumbnail
    .AllowMultiSelect = False
    .Title = "Please select one or more files"
    .InitialFileName = aStartFolder
    .InitialView = msoFileDialogViewThumbnail
    .Filters.Clear
    .Filters.Add "all files", "*.*"

    If .Show = True Then
         aBrowseForFile = .SelectedItems(1)
    Else
        MsgBox "You clicked Cancel in the file dialog box."
    End If
End With

Exit_txtBrowseForFile:
       Exit Function

Err_txtBrowseForFile:
       MsgBox Err.Description, vbCritical, "MyApp"
       Resume Exit_txtBrowseForFile

End Function

Sub EmailTemplateW10()
    Dim MyFileURL As String
    MyFileURL = aBrowseForFile("C:\users\")
    Set msg = Application.CreateItemFromTemplate(MyFileURL)
    msg.SentOnBehalfOfName = "user@domain.com"
    msg.Display
    Set msg = Nothing
End Sub

Mark this as the answer by clicking the green check mark. That closes the question and gives you points! — HackSlash, Feb 13 '20 at 00:22
Unfortunately, your code opens Excel just to use the file dialog.... — HackSlash, Feb 13 '20 at 00:23
I think I had to? Outlook can't use the ```Filedialog``` object. I tried doing this without launching a hidden excel - outlook throws an error that the object is not valid. Also - unfortunately the site won't let me accept my own answer for two days. I will do that when I can for sure. Thanks again for your help! — Richard Lyman, Feb 13 '20 at 01:58

Open Windows Explorer from Outlook to choose template

1 Answers1