VBA Code Bank – Get the Name of all SubFolders for an Outlook Folder

In this post, I am going to show you how to pull out the outlook’s subfolders name to Excel.

When you use VBA in an Office Application, in this example – Outlook, a reference to the Outlook Object Library is set by default. When you Automate to work with Outlook objects from another application, in this example – Excel, you can add a reference to the Outlook object library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Outlook’s predefined constants. Once this reference is added, a new instance of Outlook application can be created by using the New keyword.

Public Function OutlookFolderNames(MailboxName As String) _
As String()

‘********************************************************
‘PARAMETER: MailboxName = Name of Parent Outlook Folder for
‘the current user: Usually in the form of
‘”Mailbox – Doe, John” or
‘”Public Folders

‘RETURNS: Array of SubFolders in Current User’s Mailbox
‘Or unitialized array if error occurs

‘Because it returns an array, it is for VB6 only.
‘Change to return a variant or a delimited list for
‘previous versions of vb

‘EXAMPLE:
‘Dim sArray() As String
‘Dim ictr As Integer

‘sArray = OutlookFolderNames(“Mailbox – Doe, John”)
‘On Error Resume Next
‘For ictr = 0 To UBound(sArray)
‘ Debug.Print sArray(ictr)
‘Next

‘*********************************************************

Dim sArray() As String
Dim oMAPI As Outlook.NameSpace
Dim oParentFolder As Outlook.MAPIFolder
Dim i As Integer
Dim iElement As Integer

ReDim sArray(0) As String

On Error GoTo ErrorHandler

Set oMAPI = GetObject(“”, “Outlook.application”).GetNamespace(“MAPI”)
Set oParentFolder = oMAPI.Folders(MailboxName)
If oParentFolder.Folders.Count Then

For i = 1 To oParentFolder.Folders.Count
If Trim(oParentFolder.Folders(i).Name) ;; “” Then
iElement = IIf(sArray(0) = “”, 0, UBound(sArray) + 1)
ReDim Preserve sArray(iElement) As String
sArray(iElement) = oParentFolder.Folders(i).Name
End If
Next i
Else

sArray(0) = oParentFolder.Name

End If

OutlookFolderNames = sArray
Set oMAPI = Nothing
Exit Function

ErrorHandler:
Set oMAPI = Nothing
End Function
============================================

Check out instructor-led Excel VBA Course here and learn to connect Excel to Outlook full-fledged using VB Programming.

Happy Excelling
Team Excelgoodies

Leave a Reply

Your email address will not be published. Required fields are marked *