Excel/Outlook VBA - select folders in different Mailboxes

Question

I'd like to collect some email count data from Outlook using Excel VBA. In addition to my personal email, I also have access to other mailboxes account/stores(?). The label on the Outlook mailbox/accounts are HR,Marketing, Accounting. I'd like to loop through the folders in these mailboxes/stores and perform some task.

When I write a for loop through Sessions.Accounts, it only accesses my default mailbox (i.e. not HR,Marketing,Accounting). It looks like I can access my non-default mailboxes if I loop through Session.Stores:

Sub test()
Dim olApp As Outlook.Application
Set olApp = Outlook.Application

For Each acct In olApp.Session.Stores
    MsgBox acct
'how can I access folders and emails within these Stores?
End Sub

This returns message boxes with HR, Marketing, Accounting. I am not able to select the inbox or other folders within these non-default mailboxes though.

For my default mailbox, I was able to get it:

Sub default_email_count()
Dim olApp As Outlook.Application
Dim objNS As Outlook.Namespace

Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
Set olFolder = olbNS.GetDefaultFolder(olFolderInbox)
Set olFolder = olFolder.Folders("sample_folder")

Count = olFolder.Items.Count
Msgbox Count
'this code successfully outputs the count of emails in my default mailbox within the folder "sample folder"

How can I extend the above code to work across other Stores and not just my default mailbox?

Thanks in advance.

Eugene Astafiev Eugene Astafiev · Accepted Answer · 2021-04-06T17:51:32

The following code shows how to enumerate all folders on all stores for an Outlook session.

The code sample begins by getting all the stores for the current session using the NameSpace.Stores property of the current session, Application.Session.
For each store of this session, it uses Store.GetRootFolder to obtain the folder at the root of the store.
For the root folder of each store, it iteratively calls the EnumerateFolders procedure until it has visited and displayed the name of each folder in that tree.

Sub EnumerateFoldersInStores() 
 Dim colStores As Outlook.Stores 
 Dim oStore As Outlook.Store 
 Dim oRoot As Outlook.Folder 
 
 On Error Resume Next 
 Set colStores = Application.Session.Stores 
 For Each oStore In colStores 
   Set oRoot = oStore.GetRootFolder 
   Debug.Print (oRoot.FolderPath) 
   EnumerateFolders oRoot 
 Next 
End Sub 
 
Private Sub EnumerateFolders(ByVal oFolder As Outlook.Folder) 
 Dim folders As Outlook.folders 
 Dim Folder As Outlook.Folder 
 Dim foldercount As Integer 
 
 On Error Resume Next 
 Set folders = oFolder.folders 
 foldercount = folders.Count 
 
 'Check if there are any folders below oFolder 
 If foldercount Then 
   For Each Folder In folders 
  
     Debug.Print (Folder.FolderPath) 
  
     EnumerateFolders Folder 
   Next 
 End If 
End Sub

Excel/Outlook VBA - select folders in different Mailboxes

2 Answers