Tuesday, July 2, 2013

VBA: Get the contents of a search folder (Outlook 2013)

There's a different way of accessing search folders with VBA versus regular mail folders in Outlook. This seems to be poorly documented online so I thought I'd help out with that.

First, you will need to determine the display name of the Outlook store which contains your search folders.
Use this code to list your stores.


Sub FindYourStore()
    Dim colStores As Outlook.Stores
    Dim oStore As Outlook.store

    On Error Resume Next
    Set colStores = Application.Session.Stores
'You may use Session.Stores as shortcut
        For Each oStore In colStores
            Debug.Print oStore.DisplayName
        Next
End Sub


With the name of the Outlook store, you plug that into the next subroutine. In my case, I was using this routine to parse emails in this folder. This routine will list the subject lines of every email in the designated search folder.


THIS CODE ONLY WORKS WITH SEARCH FOLDERS.


Sub ListSubjectLinesOfEmailsInASearchFolder()
    Dim StoreName As String
    Dim FolderName As String
    StoreName = "myemail@mycorp.com"
    FolderName = "Pending Terminations"
    Dim colStores As Outlook.Stores
    Dim oStore As Outlook.store
    Dim oSearchFolders As Outlook.Folders
    Dim oFolder As Outlook.Folder
    Dim mail As Outlook.MailItem

    On Error Resume Next
    Set oFolder = Session.Stores.Item(StoreName).GetSearchFolders(FolderName)
        For Each mail In oFolder.Items
            Debug.Print mail.Subject
        Next
End Sub

I hope that helps someone else out there.

1 comment:

Scott said...

Thanks! It was quite helpful. I even went a bit further and implemented a bit of a dynamic selection process (the next Sub is about the same, but wth the StoreName and FolderStore moved to passed variables):

Sub FindYourStore()
Dim colStores As Outlook.Stores
Dim oStore As Outlook.Store
Dim oFolder As Outlook.Folder
Dim StoreName As String
Dim FolderName As String
Dim FolderList As String

Set oFolder = Application.Session.PickFolder
StoreName = oFolder.Name

FolderList = ""

On Error Resume Next
Set colStores = Application.Session.Stores
For Each oStore In colStores
If oStore.DisplayName = StoreName Then
Set oSearchFolders = oStore.GetSearchFolders
For Each oFolder In oSearchFolders
' Debug.Print (oFolder.FolderPath)
FolderList = oFolder.Name & Chr(10) & FolderList
Next
End If
Next

FolderName = InputBox("Enter the search folder from the list:" & Chr(10) & FolderList)
ListSubjectLinesOfEmailsInASearchFolder StoreName, FolderName
End Sub