How to use Windows filepaths in a macro

Last reviewed on May 8, 2014   —  2 comments

When you use a VBA macro to save messages or files you can hard code the file path, use the user account's My Documents folder, or select a folder on the hard drive. The first two methods are fairly short and sweet.

Note: this function will work in any Office application, it is not specific to Outlook. (Actually, it's not specific to Office either, it's a general VB function.)

Dim strFolderpath  as String
strFolderpath = "C:\OLAttachments\"

Or use this to save to a folder using the user's profile in the path (example, C:\Users\Diane\)

Dim enviro As String
enviro = CStr(Environ("USERPROFILE"))
strFolderpath = enviro & "\OLAttachments\"

For more examples, see Using Windows environment variables in Outlook macros

If you want to bring up a folder picker dialog, you need to use a function, such as the one below.

Browse for a folder to save files toAdd the function to your project and call the folderpath using the following format. You can use any valid file path, either a drive letter or network path (\\). If the path is not valid, the folder picker will show all folders (desktop level), as seen in the screenshot.

strFolderpath = BrowseForFolder("C:\Users\username\documents\")

Use this to save the file:
strFile = strFolderpath & "\" & strFile

(The lines above can be used in Save Attachments to the hard drive)

BrowseForFolder Function

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
  Dim ShellApp As Object
  Set ShellApp = CreateObject("Shell.Application"). _
 BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
 
 On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
 On Error GoTo 0
 
 Set ShellApp = Nothing
    Select Case Mid(BrowseForFolder, 2, 1)
        Case Is = ":"
            If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
        Case Is = "\"
            If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
        Case Else
            GoTo Invalid
    End Select
 Exit Function
 
Invalid:
 BrowseForFolder = False
End Function

Written by

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlookforums.

2 responses to “How to use Windows filepaths in a macro”

  1. ABE

    i am sorry on Microsoft outlook 2010 i am using window 7
    i need to create a Macro
    just need to say ( GOT IT )
    not computer savy still need screen shot or some one to walk me step by step

Leave a Reply

If the Post Coment button disappears, press your Tab key.