跳至主要内容

如何将Outlook中的文件夹结构导出到Excel?

Author: Xiaoyang Last Modified: 2025-05-07

在本文中,我将介绍如何将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窗口中,点击工具 > 引用以打开“引用 - Project1”对话框,并从可用引用列表框中勾选 Microsoft Excel对象库选项,见截图:

doc export folder structure to excel 1

4. 然后点击确定按钮,并按F5键运行此代码,会弹出一个选择文件夹对话框,请选择要导出其文件夹结构的邮件账号,见截图:

doc export folder structure to excel 2

5. 接着,点击确定按钮,会显示一个 文件保存窗口,请为该导出的文件指定位置和文件名称,见截图:

doc export folder structure to excel 3

6. 最后,点击保存按钮,您可以前往已保存的具体Excel工作簿查看导出的文件夹结构,见截图:

doc export folder structure to excel 4