Outlook 2016 File / Folder Browser

[Paul_Hossler]

I have a OL macro that opens and reads a CSV, but I want to replace the hard-coded path with a dialog box that allows the user ( = Me) to pick a file and return the path to the macro

Apparently there is no inherent OL VBA capability like Excel's GetOpenFileName

I found some snippets via Google, but they don't seem to work, at least not with 2016

Does anyone have OL macros that allow the user to pick a folder and a file?

Thanks

Edit:

The typical approach uses this technique

http://www.robvanderwoude.com/vbstech_ui_fileopen.php

but the Set objDialog line throws error 429, ActiveX component can't create object

 ' Dependencies:
 ' Requires NUSRMGRLib (nusrmgr.cpl), available in Windows XP and later.
 ' To use the default "My Documents" WScript.Shell is used, which isn't
 ' available in HTAs.
 '
 ' Written by Rob van der Woude
 ' http://www.robvanderwoude.com
     ' Standard housekeeping
     Dim objDialog
     ' Create a dialog object
     Set objDialog = CreateObject("UserAccounts.CommonDialog")

I'm assuming that it has something to do with NUSRMGRLib

[gmayor]

It's an unfortunate omission from the Outlook code set, but you can use the one from Excel and if Excel is already running or you have a SSD drive it is quite fast

Option Explicit

Function BrowseForFolder(Optional sFolder As String) As String
Dim exApp As Object
Dim strPath As String: strPath = ""
Dim fldr As FileDialog
Dim bStarted As Boolean
    On Error Resume Next
    Set exApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    If exApp Is Nothing Then
        Set exApp = CreateObject("Excel.Application")
        bStarted = True
    End If
    Set fldr = exApp.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .InitialFileName = sFolder
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo lbl_Exit
        strPath = .SelectedItems(1) & Chr(92)
    End With
lbl_Exit:
    BrowseForFolder = strPath
    If bStarted = True Then exApp.Quit 'Optional
    Set exApp = Nothing
    Exit Function
End Function

[Paul_Hossler]

I did find something that I think I made work (at least it hasn't failed in 24 hours) to pick a file to open. Still looking for same for folder

Option Explicit
'http://www.vbforums.com/showthread.php?400121-Outlook-2003-How-to-show-a-filedialog-from-Outlook
Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type
Private Declare Function GetOpenFileNameAPI Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Function GetOpenFileName(Optional sFolder As String = vbNullString, _
    Optional sMask As String = "CSV Files (*.csv)|*.csv, All files (*.*)|*.*", _
        Optional sTitle As String = "Select File to Open") As String
    
    Dim OFName As OPENFILENAME
    Dim s As String
    
    With OFName
        .lStructSize = Len(OFName)
        .hwndOwner = 0
        .hInstance = 0
        
        s = sMask
        s = Replace(s, ",", Chr(0))
        s = Replace(s, "|", Chr(0))
        .lpstrFilter = s
        .lpstrFile = Space$(254)
        .nMaxFile = 255
        .lpstrFileTitle = Space$(254)
        .nMaxFileTitle = 255
        
        If Len(sFolder) = 0 Then
            .lpstrInitialDir = CreateObject("Shell.Application").NameSpace(CVar(0)).Self.Path
        Else
            .lpstrInitialDir = sFolder
        End If
        
        .lpstrTitle = sTitle
        .flags = 0
    
        If GetOpenFileNameAPI(OFName) Then
            GetOpenFileName = Trim$(.lpstrFile)
        Else
            GetOpenFileName = vbNullString
        End If
    End With
End Function