如何將多個資料夾/子資料夾中的郵件匯出到Outlook的Excel中？

Author: Kelly Last Modified: 2025-05-12

如果依靠手動方法或有限的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”。