如何在Outlook中将多个文件夹/子文件夹中的邮件导出到Excel？

Author: Kelly Last Modified: 2025-05-08

如果依赖手动方法或有限的Outlook功能，从多个文件夹或子文件夹导出邮件到Excel可能是一项繁琐的任务。幸运的是，有更高效的方法可以实现这一目标。本文将探讨两种方法：基于VBA的方法和使用Kutools for Outlook更快、更友好的解决方案。虽然VBA方法提供了灵活性，但其设置和调整较为复杂。相比之下，Kutools for Outlook提供了一种简单而强大的方式，只需点击几下即可将邮件导出到Excel，为您节省时间和精力。让我们深入了解细节。

使用VBA从多个文件夹/子文件夹导出邮件到Excel （复杂但灵活）

使用Kutools for Outlook从文件夹/子文件夹导出邮件到Excel 👍（高效且用户友好）

使用VBA从多个文件夹/子文件夹导出邮件到Excel

如果您熟悉编码并需要可定制的解决方案，使用VBA会非常有效。此方法允许您指定多个文件夹或子文件夹，并将其邮件导出到单独的Excel文件中。然而，这需要技术知识并对代码进行仔细的自定义。

步骤1：打开VBA编辑器

按 Alt + F11 启动“Microsoft Visual Basic for Applications”窗口。

步骤2：插入新模块

点击“插入” > “模块”，然后将以下VBA代码粘贴到新的模块窗口中。

VBA：从多个文件夹和子文件夹导出邮件到Excel

Const MACRO_NAME = "Export Outlook Folders to Excel"
Sub ExportMain()
ExportToExcel "destination_folder_path\A.xlsx", "your_email_account\folder\subfolder_1"
ExportToExcel "destination_folder_path\B.xlsx", "your_email_account\folder\subfolder_2"
MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ExportToExcel(strFilename As String, strFolderPath As String)
Dim olkMsg As Object
Dim olkFld As Object
Dim excApp As Object
Dim excWkb As Object
Dim excWks As Object
Dim intRow As Integer
Dim intVersion As Integer

If strFilename <> "" Then
If strFolderPath <> "" Then
Set olkFld = OpenOutlookFolder(strFolderPath)
If TypeName(olkFld) <> "Nothing" Then
intVersion = GetOutlookVersion()
Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Add()
Set excWks = excWkb.ActiveSheet
With excWks
.Cells(1, 1) = "Subject"
.Cells(1, 2) = "Received"
.Cells(1, 3) = "Sender"
End With
intRow = 2
For Each olkMsg In olkFld.Items
If olkMsg.Class = olMail Then
excWks.Cells(intRow, 1) = olkMsg.Subject
excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
intRow = intRow + 1
End If
Next
Set olkMsg = Nothing
excWkb.SaveAs strFilename
excWkb.Close
Else
MsgBox "The folder '" & strFolderPath & "' does not exist in Outlook.", vbCritical + vbOKOnly, MACRO_NAME
End If
Else
MsgBox "The folder path was empty.", vbCritical + vbOKOnly, MACRO_NAME
End If
Else
MsgBox "The filename was empty.", vbCritical + vbOKOnly, MACRO_NAME
End If

Set olkMsg = Nothing
Set olkFld = Nothing
Set excWks = Nothing
Set excWkb = Nothing
Set excApp = Nothing
End Sub

Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
Dim arrFolders As Variant
Dim varFolder As Variant
Dim bolBeyondRoot As Boolean

On Error Resume Next
If strFolderPath = "" Then
Set OpenOutlookFolder = Nothing
Else
Do While Left(strFolderPath, 1) = "\"
strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
Loop
arrFolders = Split(strFolderPath, "\")
For Each varFolder In arrFolders
Select Case bolBeyondRoot
Case False
Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
bolBeyondRoot = True
Case True
Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
End Select
If Err.Number <> 0 Then
Set OpenOutlookFolder = Nothing
Exit For
End If
Next
End If
On Error GoTo 0
End Function

Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
Dim olkSnd As Outlook.AddressEntry
Dim olkEnt As Object

On Error Resume Next
Select Case intOutlookVersion
Case Is < 14
If Item.SenderEmailType = "EX" Then
GetSMTPAddress = SMTPEX(Item)
Else
GetSMTPAddress = Item.SenderEmailAddress
End If
Case Else
Set olkSnd = Item.Sender
If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
Set olkEnt = olkSnd.GetExchangeUser
GetSMTPAddress = olkEnt.PrimarySmtpAddress
Else
GetSMTPAddress = Item.SenderEmailAddress
End If
End Select
On Error GoTo 0
Set olkSnd = Nothing
Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
Dim arrVer As Variant
arrVer = Split(Outlook.Version, ".")
GetOutlookVersion = arrVer(0)
End Function

Function SMTPEX(olkMsg As Outlook.MailItem) As String
Dim olkPA As Outlook.propertyAccessor
On Error Resume Next
Set olkPA = olkMsg.propertyAccessor
SMTPEX = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
On Error GoTo 0
Set olkPA = Nothing
End Function

步骤3：自定义VBA代码

将上述代码中的“destination_folder_path”替换为实际的文件夹路径，例如“C:\Users\DT168\Documents\TEST”。
替换“your_email_account\folder\subfolder_1” 和 “your_email_account\folder\subfolder_2” 为您的Outlook文件夹路径，例如“Kelly@extendoffice.com\收件箱\A” 和 “Kelly@extendoffice.com\收件箱\B".