Skip to main content

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?

Export outlook message counts to Excel workbook with VBA code


Export outlook message counts to Excel workbook with VBA code

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:

doc export items count 1

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:

doc export items count 2

5. Then click OK, and another Browse For Folder is displayed, please choose a folder to put the Excel file, see screenshot:

doc export items count 3

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:

doc export items count 4


Best Office Productivity Tools

Kutools for Outlook - Over 100 Powerful Features to Supercharge Your Outlook

📧 Email Automation: Out of Office (Available for POP and IMAP)  /  Schedule Send Emails  /  Auto CC/BCC by Rules When Sending Email  /  Auto Forward (Advanced Rules)   /  Auto Add Greeting   /  Automatically Split Multi-Recipient Emails into Individual Messages ...

📨 Email Management: Easily Recall Emails  /  Block Scam Emails by Subjects and Others  /  Delete Duplicate Emails  /  Advanced Search  /  Consolidate Folders ...

📁 Attachments ProBatch Save  /  Batch Detach  /  Batch Compress  /  Auto Save   /  Auto Detach  /  Auto Compress ...

🌟 Interface Magic: 😊More Pretty and Cool Emojis   /  Boost Your Outlook Productivity with Tabbed Views  /  Minimize Outlook Instead of Closing ...

👍 One-click Wonders: Reply All with Incoming Attachments  /   Anti-Phishing Emails  /  🕘Show Sender's Time Zone ...

👩🏼‍🤝‍👩🏻 Contacts & Calendar: Batch Add Contacts From Selected Emails  /  Split a Contact Group to Individual Groups  /  Remove Birthday Reminders ...

Over 100 Features Await Your Exploration! Click Here to Discover More.

Read More       Free Download      Purchase
 

 

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
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations