如何將 Outlook 中的資料夾結構匯出至 Excel？

本文將引導您逐步將 Outlook 帳戶的資料夾結構匯出至 Excel 檔案，請依照以下詳細步驟操作。

使用 VBA 程式碼將帳戶的資料夾結構從 Outlook 匯出至 Excel

---

使用 VBA 程式碼將帳戶的資料夾結構從 Outlook 匯出至 Excel

下列 VBA 程式碼可助您一臂之力，請依照以下步驟操作：

1. 啟動 Excel，並按住 ALT + F11 鍵，即可開啟 Microsoft Visual Basic for Applications 視窗。

2. 點選插入＞ 模組，並將下列程式碼貼到模組視窗中。

VBA 程式碼：將帳戶的資料夾結構從 Outlook 匯出至 Excel：

Dim xExcelApp As Excel.Application
Dim xWb As Excel.Workbook
Dim xWs As Excel.Worksheet
Dim xMainFolderCount As Long
Sub OutlookExportFolderStructureToExcel()
    Dim xFolder As Folder
    Dim xExcelFile As String
    Dim xFileDialog As FileDialog
    On Error Resume Next
    Set xFolder = Outlook.Application.Session.PickFolder
    If xFolder Is Nothing Then Exit Sub
    Set xExcelApp = New Excel.Application
    Set xWb = xExcelApp.Workbooks.Add
    Set xWs = xWb.Sheets(1)
    With xWs.Range("A1", "A1")
         .Value = "Folder Structure"
         .Font.Size = 14
         .Font.Bold = True
    End With
    xMainFolderCount = Len(xFolder.FolderPath) - Len(Replace(xFolder.FolderPath, "\", "")) + 1
    Call ExportToExcel(xFolder.FolderPath, xFolder.Name)
    Call ProcessFolders(xFolder.Folders)
    xWs.Columns("A").AutoFit
    Set xFileDialog = xExcelApp.FileDialog(msoFileDialogSaveAs)
    With xFileDialog
        .AllowMultiSelect = False
        .FilterIndex = 1
        If .Show = 0 Then
            xWb.Close False
            xExcelApp.Quit
            Set xExcelApp = Nothing
            Exit Sub
        End If
        xExcelFile = .SelectedItems.Item(1)
    End With
    xWb.Close True, xExcelFile
    MsgBox "Export complete!", vbExclamation, "Kutools for Outlook"
End Sub

Sub ProcessFolders(ByVal xFlds As Folders)
    Dim xSubFolder As Folder
    For Each xSubFolder In xFlds
        If xSubFolder.Name <> "Conversation Action Settings" And xSubFolder.Name <> "Quick Step Settings" Then
           Call ExportToExcel(xSubFolder.FolderPath, xSubFolder.Name)
           Call ProcessFolders(xSubFolder.Folders)
        End If
    Next
End Sub

Sub ExportToExcel(ByRef xFolderPath As String, xFolderName As String)
    Dim i, n As Long
    Dim xPrefix As String
    Dim xLastRow As Integer
    i = Len(xFolderPath) - Len(Replace(xFolderPath, "\", "")) - xMainFolderCount
    For n = 0 To i
        xPrefix = xPrefix & "-"
    Next
    xFolderName = xPrefix & xFolderName
    xLastRow = xWs.UsedRange.Rows.Count + 1
    xWs.Range("A" & xLastRow) = xFolderName
End Sub

3. 在 Microsoft Visual Basic for Applications 視窗中，點選工具＞ 參考項目，開啟 參考項目 - Project 1 對話方塊，並於可用參考項目清單中勾選 Microsoft Excel 物件程式庫選項，如下圖所示：

4. 接著點選確定按鈕，再按下 F5 鍵執行此程式碼，系統將彈出選擇資料夾對話方塊，請選取您要匯出資料夾結構的電子郵件帳戶，如下圖所示：

5. 接下來點選確定按鈕，隨即會彈出另存檔案視窗，請立即指定匯出檔案的儲存位置與檔名，如下圖所示：

6. 最後點選儲存按鈕，即可在您指定的 Excel 工作表中查看已匯出的資料夾結構，如下圖所示：