How to Export the Folder Structure of Your Outlook File to Excel

If you would like to quickly export the folder structure of your Outlook data file to an Excel workbook, you can utilize the method introduced in this article.

For some reasons, such as logging current Outlook folders and subfolders, many users hope to export the folder structure of an Outlook file to an external file, like an Excel workbook. In the followings, we will share you a piece of VBA code, which can help you achieve it in a jiffy.

Export the Folder Structure of Your Outlook File to Excel

1. To start with, launch your Outlook application.
2. Then, in the main Outlook window, press “Alt + F11” key buttons.
3. Next, you will get into the Outlook VBA editor, in which you ought to open an unused module.
4. Subsequently, you can copy the following VBA code into this module.

Dim objExcelApp As Excel.Application
Dim objExcelWorkbook As Excel.Workbook
Dim objExcelWorksheet As Excel.Worksheet
Dim lMainFolder As Long

Sub ExportFolderStructureToExcel()
    Dim objSourcePSTFile As Folder
 
    'Add a new Excel workbook
    Set objExcelApp = CreateObject("Excel.Application")
    Set objExcelWorkbook = objExcelApp.Workbooks.Add
    Set objExcelWorksheet = objExcelWorkbook.Sheets(1)
 
    With objExcelWorksheet
         .Cells(1, 1) = "Folder Structure"
         .Cells(1, 1).Font.Size = 14
         .Cells(1, 1).Font.Bold = True
    End With
 
    'Select an Outlook PST file
    Set objSourcePSTFile = Application.Session.PickFolder
 
    lMainFolder = Len(objSourcePSTFile.FolderPath) - Len(Replace(objSourcePSTFile.FolderPath, "\", "")) + 1
 
    Call ExportToExcel(objSourcePSTFile.FolderPath, objSourcePSTFile.Name)
    Call ProcessFolders(objSourcePSTFile.Folders)
 
    'Save this Excel workbook
    objExcelWorksheet.Columns("A").AutoFit
    strExcelFile = "E:\Folder Structure (" & Format(Now, "yyyymmddhhmmss") & ").xlsx"
    objExcelWorkbook.Close True, strExcelFile

    MsgBox "Complete!", vbExclamation
End Sub

Sub ProcessFolders(ByVal objFolders As Folders)
    Dim objFolder As Folder
 
    'Process all folders recursively
    For Each objFolder In objFolders
        If objFolder.Name <> "Conversation Action Settings" And objFolder.Name <> "Quick Step Settings" Then
           Call ExportToExcel(objFolder.FolderPath, objFolder.Name)
           Call ProcessFolders(objFolder.Folders)
        End If
    Next
End Sub

Sub ExportToExcel(ByRef strFolderPath As String, strFolderName As String)
    Dim i, n As Long
    Dim strPrefix As String
    Dim nLastRow As Integer
 
    i = Len(strFolderPath) - Len(Replace(strFolderPath, "\", ""))
    For n = lMainFolder To i
        strPrefix = strPrefix & "-"
    Next
    strFolderName = strPrefix & strFolderName
 
    'Input the folder name in Excel
    nLastRow = objExcelWorksheet.Range("A" & objExcelWorksheet.Rows.Count).End(xlUp).Row + 1
    objExcelWorksheet.Range("A" & nLastRow) = strFolderName
End Sub

5. After that, you should ensure that Outlook has enabled macros.
6. Eventually, you can take a shot:

• In the current macro window, press F5 key button.
• After the macro finishes, you’ll get an alert prompting “Complete”.
• Later, you can head to the predefined local folder to find a new Excel file. Open it and it will look like the following screenshot:

Never Disregard Any Outlook Errors

Despite with quantities of capabilities, Outlook is the same susceptible to errors and corruption as other email clients. Therefore, you should attach importance to all errors in your Outlook. Don’t disregard them, please. Otherwise, accumulating errors can lead to Outlook corruption finally. If confronted with knotty errors, it is suggested to utilize a powerful tool, such as DataNumen Outlook Repair, which can repair Outlook errors within seconds.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and outlook repair software products. For more information visit www.datanumen.com