Excel vba: Looping through all subfolders in Outlook email to find an email with certain subject
The below code cycles through all folders in Outlook, to the level one beneath the Inbox. You can just look at the inbox by specifying the initial folder to look at. Thus you can search the folder as you loop through. you can add further sub folders by looping deeper, or by saying folders.count > X.
I have always found Outlook from Excel frustrating so have made this Early Bound to make coding easier. This means that you will need to go to Tool/References and add Microsoft Outlook 16(x).0 Object Library
You can change it back to late bound after coding, as early binding will give you IntelliSense and make life a whole lot easier.
Sub GetEmail()
Dim OutApp As Outlook.Application
Dim Namespace As Outlook.Namespace
Dim Mfolder As Outlook.MAPIFolder
Dim myMail As Outlook.Items
Dim Folder As Outlook.MAPIFolder
Dim SubFolder As Outlook.MAPIFolder
Dim UserFolder As Outlook.MAPIFolder
Set OutApp = New Outlook.Application
Set Namespace = OutApp.GetNamespace("MAPI")
On Error Resume Next
For Each Folder In Namespace.Folders
For Each SubFolder In Folder.Folders
For Each UserFolder In SubFolder.Folders
Debug.Print Folder.Name, "|", SubFolder.Name, "|", UserFolder.Name
Next UserFolder
Next SubFolder
Next Folder
On Error GoTo 0
End Sub
The on error is to skip any issues with outlook mapping Archive pst files.
Trs
Updated on June 04, 2022Comments
-
Trs almost 2 years
I have written the following code in Excel VBA that opens an email with the given subject if located in the default inbox folder in Outlook.
However, I would like to search for this email in all inbox subfolders.
Because the code will be used by several users, I do not know the number and the name of their outlook inbox subfolders. Any ideas on how I could search this email in all subfolders?
Sub GetEmail() Dim OutApp as Object Dim Namespace as Object Dim Folder as Object Dim myMail as Object Set OutApp = CreateObject("Outlook.Application") Set Namespace = OutApp.GetNamespace ("MAPI") Set Folder = Namespace.GetDefaultFolder(6) Set myMail = Folder.Items.Find ("[Subject] = ""Test""") myMail.Display End Sub