Setting up a Dynamic Reference to an Outlook MAPI Folder within VBA

[brandonsvac]

Hey all -

So I've written a Macro to pull in the body of an email from outlook into a worksheet, and I want to be able to allow others to use the macro as well without having to redefine the Outlook MAPI Folder that the email will be drawn from for each individual. Currently, I am using this:

    Dim olAPP As Outlook.Application
    Dim olNS As Outlook.Namespace
    Dim olFLDR As Outlook.MAPIFolder
    Dim olITMS As Outlook.Items
    Dim olMAIL As Variant
      
    Set olAPP = New Outlook.Application
    Set olNS = Outlook.GetNamespace("MAPI")
    Set olFLDR = olNS.GetDefaultFolder(olFolderInbox).Folders("SPG").Folders("Mortgage Presentments") 
    Set olITMS = olFLDR.Items
     
    olITMS.Sort “Subject”

As you can see, the current folder I am accessing is the "Mortgage Presentments" folder within "SPG" within my default Inbox. What I would like to do is have a sheet in the document called "Users" that will hold all of the various file paths that users utilize and have a cell reference VLOOKUP that path.

For purposes of the code, I was hoping to change the back-end of the "Set olFLDR" to set the olFLDR to the path specified by the cell reference.

Any help would be greatly appreciated. Thanks.

 

[John_w]

Welcome to MrExcel forums.

Try this Get_Outlook_Folder function. It accepts a folder path string with folder names separated by the back slash character "\". The folder ".." represents the parent folder of the preceding folder name. A folder name of "olFolderInbox" (case-insensitive) denotes the default Inbox folder. A folder path beginning "olFolderInbox\.." allows you to reference any folder within the default Outlook account without having to know the Outlook account name.

Public Function Get_Outlook_Folder(outNs As Outlook.Namespace, outlookFolderPath As String) As Outlook.MAPIFolder

    Dim subFolder As Outlook.MAPIFolder
    Dim folderNames As Variant, i As Long
        
    folderNames = Split(outlookFolderPath, "\")
    
    If StrComp(folderNames(0), "olFolderInbox", vbTextCompare) = 0 Then
        Set Get_Outlook_Folder = outNs.GetDefaultFolder(olFolderInbox)
    Else
        On Error Resume Next   'trap error if first folder name doesn't exist
        Set Get_Outlook_Folder = outNs.Folders(folderNames(0))
        On Error GoTo 0
    End If
    
    i = 0
    While Not Get_Outlook_Folder Is Nothing And i < UBound(folderNames)
        i = i + 1
        If folderNames(i) = ".." Then
            Set Get_Outlook_Folder = Get_Outlook_Folder.Parent
        Else
            Set subFolder = Nothing
            On Error Resume Next   'trap error if subfolder doesn't exist
            Set subFolder = Get_Outlook_Folder.Folders(folderNames(i))
            On Error GoTo 0
            Set Get_Outlook_Folder = subFolder
        End If
    Wend
    
End Function

Here is a test procedure showing example folder paths:

Public Sub Test()

    Dim outApp As Outlook.Application
    Dim outNamespace As Outlook.Namespace
    Dim outFolder As Outlook.MAPIFolder
    Dim folderPath As String
    
    Set outApp = New Outlook.Application
    Set outNamespace = outApp.GetNamespace("MAPI")
    
    'folderPath = "olFolderInbox"
    'folderPath = "The Outlook Account Name\Top folder1\Sub folder1"
    'folderPath = "olFolderInbox\..\Top folder1\Sub folder1"  'same as above, but without needing to know Outlook account name
    folderPath = "olFolderInbox\SPG\Mortgage Presentments"
    
    Set outFolder = Get_Outlook_Folder(outNamespace, folderPath)
    
    If Not outFolder Is Nothing Then
        MsgBox "Folder '" & folderPath & "' contains " & outFolder.Items.Count & " items"
    Else
        MsgBox "Folder '" & folderPath & "'  not found"
    End If
    
End Sub

 

[xenou]

Seems like a pretty standard requirement would be to specify what the folder names should be. For instance, just make it a rule that the user have this particular kind of email in a Mortgage Presentments folder under SPG in the default inbox.

Otherwise, it can be done but you will incur extra logic and more complication because folders can be nested to an arbitrary degree (Folder/SubFolder/Subfolder/Subfolder ....) and I'm not sure you need or want that kind of complexity.