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:
Kutools for Outlook
More than 100 Advanced Functions for Outlook 2010, 2013, 2016, 2019 and 365