How to export outlook message counts to Excel workbook?
Normally, you can export the Outlook messages to Excel file by using the Import /Export feature quickly and easily. But, have you ever tried to count the items in all folders of your specific Email account and export the count results to Excel workbook?
The following VBA code can help you to export the count results from all folders in a specific Email account to Excel workbook, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Export outlook items count to Excel workbook:
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. And, still in the Microsoft Visual Basic for Applications window, click Tools > References to go to the References-Project1 dialog box, and check Microsoft Excel Object Library option from the Available References list box, see screenshot:
4. Then click OK, and then press F5 key to run this code, a Select Folder is popped out, please select an Email account that you want to export the items count, see screenshot:
5. Then click OK, and another Browse For Folder is displayed, please choose a folder to put the Excel file, see screenshot:
6. At last, click OK button, and the items count in all folder of the selected account has been exported to an Excel workbook, you can open the Excel file to view the result, see screenshot:
- Auto CC/BCC by rules when sending email; Auto Forward Multiple Emails by custom; Auto Reply without exchange server, and more automatic features...
- BCC Warning - show message when you try to reply all if your mail address is in the BCC list; Remind When Missing Attachments, and more remind features...
- Reply (All) With All Attachments in the mail conversation; Reply Many Emails in seconds; Auto Add Greeting when reply; Add Date into subject...
- Attachment Tools: Manage All Attachments in All Mails, Auto Detach, Compress All, Rename All, Save All... Quick Report, Count Selected Mails...
- Powerful Junk Emails by custom; Remove Duplicate Mails and Contacts... Enable you to do smarter, faster and better in Outlook.