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
- To start with, launch your Outlook application.
- Then, in the main Outlook window, press “Alt + F11” key buttons.
- Next, you will get into the Outlook VBA editor, in which you ought to open an unused module.
- 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
- After that, you should ensure that Outlook has enabled macros.
- 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.
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
I am having the same problem.
I followed the steps, and I am getting an error message immediately. “Compile Error: User-defined type not defined”. That error message does not appear if I remove the first 3 ‘Dim’ lines. It asks me to pick a location in Outlook. I picked the Inbox, but then I get a, “Run-time error ‘424’” Object required” on the 3rd-to-last row in the script, the one that’s “nLastRow = obj…” I don’t know what to do about that.