跳到主要内容

如何将Outlook邮件计数导出到Excel工作簿?

作者:晓阳 最后修改时间:2018-08-31

通常,您可以使用导入/导出功能快速而轻松地将Outlook邮件导出到Excel文件。 但是,您是否曾经尝试过对特定电子邮件帐户的所有文件夹中的项目进行计数并将计数结果导出到Excel工作簿?

使用VBA代码将Outlook消息计数导出到Excel工作簿


使用VBA代码将Outlook消息计数导出到Excel工作簿

以下VBA代码可帮助您将特定电子邮件帐户中所有文件夹的计数结果导出到Excel工作簿,请执行以下操作:

1。 按住 ALT + F11 键打开 Microsoft Visual Basic应用程序 窗口。

2。 点击 插页 > 模块,然后将以下代码粘贴到 模块 窗口。

VBA代码:将Outlook项目导出到Excel工作簿中:

Sub Export_CountOfItems_InEachFolder_toExcel()
    Dim xSourceFolder As Outlook.Folder, xSubFolder As Outlook.Folder
   Dim xFilePath As String
    Dim xExcelApp As Excel.Application
    Dim xWb As Excel.Workbook
    Dim xWs As Excel.Worksheet
    On Error Resume Next
    Set xExcelApp = New Excel.Application
    Set xWb = xExcelApp.Workbooks.Add
    Set xWs = xWb.Sheets(1)
    xWs.Cells(1, 1) = "Folder"
    xWs.Cells(1, 2) = "Count Items"
    Set xSourceFolder = Outlook.Application.Session.PickFolder
    If xSourceFolder = nill Then
        xWb.Close False
        xExcelApp.Quit
        Exit Sub
    End If
    For Each xSubFolder In xSourceFolder.Folders
        Call ProcessFolders(xWs, xSubFolder)
    Next
    xWs.Columns("A:B").AutoFit
    Set xShell = CreateObject("Shell.Application")
    Set xFolder = xShell.BrowseforFolder(0, "Select a Folder:", 0, 0)
   If TypeName(xFolder) = "Nothing" Then
        xWb.Close False
        xExcelApp.Quit
        Exit Sub
    End If
    Set xFolderItem = xFolder.Self
    xFilePath = xFolderItem.Path & "\"
    xFilePath = xFilePath & xSourceFolder.Name & "(" & Format(Now, "yyyy-mm-dd hh-mm-ss") & ").xlsx"
    xWb.Close True, xFilePath
    xExcelApp.Quit
    Set xShell = Nothing
    MsgBox "Complete!", vbExclamation, "Kutools for Outlook"
End Sub
Sub ProcessFolders(ByVal Ws As Worksheet, ByVal xCurFolder As Outlook.Folder)
    Dim xSubFld As Folder
    Dim xItemCount As Long
   Dim xRow As Integer
    xItemCount = xCurFolder.Items.Count
    xRow = Ws.UsedRange.Rows.Count + 1
    Ws.Cells(xRow, 1) = xCurFolder.FolderPath
    Ws.Cells(xRow, 2) = xItemCount
    If xCurFolder.Folders.Count > 0 Then
       For Each xSubFld In xCurFolder.Folders
           Call ProcessFolders(Ws, xSubFld)
       Next
    End If
End Sub

3。 而且,仍然在 Microsoft Visual Basic应用程序 窗口中,单击 工具 > 参考资料参考-Project1 对话框,然后检查 Microsoft Excel对象库 选项从 可用参考 列表框,请参见屏幕截图:

doc导出项目数1

4。 然后点击 OK,然后按 F5 运行此代码的关键 选择“文件夹” 弹出,请选择您要导出项目计数的电子邮件帐户,请参见屏幕截图:

doc导出项目数2

5。 然后点击 OK,另一个 浏览文件夹 显示出来,请选择一个文件夹放入Excel文件,请参见屏幕截图:

doc导出项目数3

6。 最后点击 OK 按钮,并且所选帐户所有文件夹中的项目计数已导出到Excel工作簿,您可以打开Excel文件以查看结果,请参见屏幕截图:

doc导出项目数4


最佳办公生产力工具

最新消息:Kutools for Outlook 发布 免费版本!

体验全新的 Kutools for Outlook 免费版本拥有 70 多项令人难以置信的功能,您可以永久使用! 点击立即下载!

🤖 Kutools人工智能 : 具有人工智能魔力的即时专业电子邮件——一键天才回复、完美语气、多语言掌握。轻松改变电子邮件! ...

📧 电子邮件自动化: 自动回复(适用于 POP 和 IMAP)  /  安排发送电子邮件  /  发送电子邮件时按规则自动抄送/密件抄送  /  自动转发(高级规则)   /  自动添加问候语   /  自动将多收件人电子邮件拆分为单独的消息 ...

📨 电子邮件管理: 撤回电子邮件  /  按主题和其他人阻止诈骗电子邮件  /  删除重复的电子邮件  /  高级搜索  /  合并文件夹 ...

📁 附件专业版批量保存  /  批量分离  /  批量压缩  /  自动保存   /  自动分离  /  自动压缩 ...

🌟 界面魔法: 😊更多又漂亮又酷的表情符号   /  收到重要邮件时提醒您  /  最小化 Outlook 而不是关闭 ...

👍 一键奇迹: 使用传入附件回复全部  /   反网络钓鱼电子邮件  /  🕘显示发件人的时区 ...

👩🏼‍🤝‍👩🏻 通讯录和日历: 从选定的电子邮件中批量添加联系人  /  将联系人组拆分为各个组  /  删除生日提醒 ...

只需单击一下即可立即解锁 Kutools for Outlook -永久免费. 别等了, 立即下载并提高您的效率!

kutools for outlook 功能1 kutools for outlook 功能2
 

 

 

Comments (5)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello, CETIN,
Maybe you forgot the step3 in this article, you should check the Microsoft Excel Object Library option in the Available References list box.
please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Sub ProcessFolders(ByVal Ws As Worksheet, ByVal xCurFolder As Outlook.Folder)

This line gives error ;

User -defined type not defined, after pressing F5
This comment was minimized by the moderator on the site
Thank for posting this Code works Exactly as written, Kudos
This comment was minimized by the moderator on the site
This Is Perfect, it worked exactly as it is written, thank you for posting this code
This comment was minimized by the moderator on the site
Doesn't work
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations