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