Note: The other languages of the website are Google-translated. Back to English

How to count emails by category in Outlook?

You might have highlighted emails with color categories in Outlook. But, do you know how to count emails by categories? This article will share two solutions:

Office Tab - Enable Tabbed Editing and Browsing in Office, and Make Work Much Easier...
Kutools for Outlook - Brings 100 Powerful Advanced Features to Microsoft Outlook
  • Auto CC/BCC by rules when sending email; Auto Forward Multiple Emails by rules; 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 at once; Auto Add Greeting when reply; Auto Add Date&Time into subject...
  • Attachment Tools: Auto Detach, Compress All, Rename All, Auto Save All... Quick Report, Count Selected Mails, Remove Duplicate Mails and Contacts...
  • More than 100 advanced features will solve most of your problems in Outlook 2021 - 2010 or Office 365. Full features 60-day free trial.

arrow blue right bubble Count emails by category in Outlook with Instant Search feature

This method will guide yo to find out all emails marked with the specified color categories by the Instant Search feature, and then easily get the total number of all search results at the Status bar.

1. In the Mail view, press Ctrl + E keys simultaneously to activate the Search Tools.

2. Click Search > Categorized, and then select the specified color category you will count emails by.

3. Now all emails marked with the specified color category are found out and listed in the mail list. And you can easily get the total number of all search results at the status bar. See screenshot:

Notes:

(1) For counting emails marked with Yellow category and Orange category simultaneously, please type the search criteria category:="Yellow Category" AND "Orange Category" into the Instant Search box directly:

(2) For counting emails marked with Yellow category or Orange category simultaneously, please type the search criteria category:="Yellow Category" OR "Orange Category" into the Instant Search box directly.


arrow blue right bubble Count emails by category in Outlook with VBA

This method will introduce a VBA to count emails by category in a custom date range in Outlook. Please do as follows:

1. In the Mail view, select the email folder that you will count emails.

2. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and then paste below VBA code into the new opening Module window:

VBA: Count emails by category in Outlook

Sub CategoriesEmails()

Dim oFolder As MAPIFolder
Dim oDict As Object
Dim sStartDate As String
Dim sEndDate As String
Dim oItems As Outlook.Items
Dim sStr As String
Dim sMsg As String


On Error Resume Next
Set oFolder = Application.ActiveExplorer.CurrentFolder

Set oDict = CreateObject("Scripting.Dictionary")

sStartDate = InputBox("Type the start date (format MM/DD/YYYY)")
sEndDate = InputBox("Type the end date (format MM/DD/YYYY)")

Set oItems = oFolder.Items.Restrict("[Received] >= '" & sStartDate & "' And [Received] <= '" & sEndDate & "'")
oItems.SetColumns ("Categories")

For Each aitem In oItems
sStr = aitem.Categories
If Not oDict.Exists(sStr) Then
oDict(sStr) = 0
End If
oDict(sStr) = CLng(oDict(sStr)) + 1
Next aitem

sMsg = ""
For Each aKey In oDict.Keys
sMsg = sMsg & aKey & ":   " & oDict(aKey) & vbCrLf
Next
MsgBox sMsg

Set oFolder = Nothing

 End Sub

4. Press the F5 key or click the Run button to run this VBA.

5. (1) In the first popping out Microsoft Outlook dialog box, please type the start date of the custom date range you will count emails within, and click the OK button. (2) In the second Microsoft Outlook dialog box, please type the end date, and click the OK button. See screenshots:

And now a dialog box comes out and displays the numbers of emails marked with color categories. See screenshot:


arrow blue right bubbleRelated Articles


Kutools for Outlook - Brings 100 Advanced Features to Outlook, and Make Work Much Easier!

  • 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.
shot kutools outlook kutools tab 1180x121
shot kutools outlook kutools plus tab 1180x121
 
Comments (17)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This is perfect, but how can I change the VBA to include subfolders of the current folder in the count?
This comment was minimized by the moderator on the site
Hi Thanks, it works perfectly, just one quick query- Can we get the Msgbox results to be displayed in excel, as whenever i want to run the macro it should be displayed in excel.
every time one below the previous results.
This comment was minimized by the moderator on the site
This would be great!
This comment was minimized by the moderator on the site
Hi , thanks it workd perfectly for month of June, but when I run this for July, it only shows the total number of emails... any idea?
This comment was minimized by the moderator on the site
is there a way you can covert this into an excel table?
This comment was minimized by the moderator on the site
Hi Dylan,
Below VBA code will place the count results in a new Excel workbook.
FYI, please enable the reference of “Microsoft Excel 16.0 Object Library” before running the VBA.
Sub CategoriesEmails()
Dim xFolder As MAPIFolder
Dim xDict As Object
Dim xStartDate As String, xEndDate As String
Dim xItems As Outlook.Items
Dim xStr As String, xMsg As String
Dim xExlApp As Object
Dim xWb As Workbook, xWs As Worksheet
Dim I As Integer
On Error Resume Next
Set xExlApp = CreateObject("Excel.Application")
Set xWb = xExlApp.Workbooks.Add
Set xWs = xWb.Sheets.Item(1)
Set xFolder = Application.ActiveExplorer.CurrentFolder
Set xDict = CreateObject("Scripting.Dictionary")
xStartDate = InputBox("Type the start date (format MM/DD/YYYY)")
xEndDate = InputBox("Type the end date (format MM/DD/YYYY)")
Set xItems = xFolder.Items.Restrict("[ReceivedTime] >= '" & xStartDate & "' And [ReceivedTime] <= '" & xEndDate & "'")
'xItems.SetColumns ("Categories")
For Each xItem In xItems
xStr = xItem.Categories
If Not xDict.Exists(xStr) Then
xDict(xStr) = 0
End If
xDict(xStr) = CLng(xDict(xStr)) + 1
Next xItem
xMsg = ""
xWs.Range("A1", "B1").Font.Bold = True
With xWs.Range("A1", "B1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
xWs.Cells(1, 1) = "Categories"
xWs.Cells(1, 2) = "Count"
I = 2
For Each xKey In xDict.Keys
If xKey <> "" Then
xWs.Cells(I, 1) = xKey
xWs.Cells(I, 2) = xDict(xKey)
I = I + 1
End If
Next
xWs.Range("A1", "B" + CStr(I)).Columns.AutoFit
xExlApp.Visible = True
Set xFolder = Nothing
End Sub
This comment was minimized by the moderator on the site
This was helpful for me also, thank you so much for sharing!
This comment was minimized by the moderator on the site
I know this is an older thread, but if anyone can assist it would be greatly appreciated. When running the code I'm hitting a compile error "User-defined type not defined" for the 8th line "Dim xWB As Workbook, xWs As Worksheet"
This comment was minimized by the moderator on the site
Hi
Did you manage to solve this ?
I got the same problem
Solution:
Simply delete the row:Dim xWb As Workbook, xWs As Worksheet
This comment was minimized by the moderator on the site
Can you add a way to breakout the count columns by day? So I could run a full month and then have counts broken out by day.
This comment was minimized by the moderator on the site
Hi Hannah,

Please try the code below:

<div data-tag="code">Sub CategoriesEmails()
Dim oFolder As MAPIFolder
Dim oDict As Object
Dim sStartDate As String
Dim sEndDate As String
Dim oItems As Outlook.Items
Dim sStr As String
Dim sMsg As String
Dim xStr, xStrDate, xStrDate_D As String
Dim xArr As Variant
Dim xNum As Integer
Dim xRlt As String
On Error Resume Next
Set oFolder = Application.ActiveExplorer.CurrentFolder
xNum = 0
Set oDict = CreateObject("Scripting.Dictionary")
sStartDate = InputBox("Type the start date (format MM/DD/YYYY)")
sEndDate = InputBox("Type the end date (format MM/DD/YYYY)")
Set oItems = oFolder.Items.Restrict("[ReceivedTime] >= '" & sStartDate & "' And [ReceivedTime] <= '" & sEndDate & "'")
oItems.SetColumns ("ReceivedTime")
xStrDate_D = ""
xRlt = ""
For Each aitem In oItems
xStr = aitem.ReceivedTime
If Trim(xStr) <> "" Then
xArr = Split(xStr, " ")
xStrDate = xArr(0)
If xStrDate_D = "" Then
xStrDate_D = xStrDate
xNum = 1
Else
If xStrDate <> "" Then
If xStrDate = xStrDate_D Then
xNum = xNum + 1
Else
If xRlt = "" Then
xRlt = xStrDate_D & ":" & xNum
Else
xRlt = xRlt & vbCrLf & xStrDate_D & ":" & xNum
End If
xStrDate_D = xStrDate
xNum = 1
End If
End If
End If
End If
Next
If xRlt <> "" Then
MsgBox xRlt
Else
MsgBox "No data found."
End If
End Sub
Also, for more statistic analysis, please go to the tab Kutools Plus > Statistics if you have installed Kutools for Outlook. If not, please <a href="https://www.extendoffice.com/download/kutools-for-outlook.html">click here to download</a> and get a 30-day trail for free.
Amanda
This comment was minimized by the moderator on the site
Thank you so much for your help!! I don't think I was clear enough in my initial question. Is there a way to have it break out by category and then give a count by day? So X Category would be broken down by count into date columns?
This comment was minimized by the moderator on the site
Hi guys,
To make the above code run porperly, please first enable the reference of “Microsoft Excel 16.0 Object Library”.
To do so, please do as follows:1. In your Outlook, go to the tab Developer > Visual Basic.2. In the pop-up window, click Tools.3. In the pop-up dialog, check Microsoft Excel 16.0 Object Library, then click OK, see screenshot.
Note that the 16.0 indicates version number, so if it's not the same in your dialog, it doesn't matther. However, if you cannot find the Microsoft Excel (version number) Object Library, make sure that you have Excel installed in your computer.
After you enable the reference, please use the code above.
If you have any further questions, please don't hesitate to let me know.
Amanda
This comment was minimized by the moderator on the site
Hello, is there is a possibility to count letters marked with categories if in one letter we have few cases and they must be counted: for example green category - 1 letter with 3 cases inside = 3 green category cases ?


ThANK YOU
This comment was minimized by the moderator on the site
Hi,
Instead of only calculating from the selected folder, is there a way to include all the sub folders as well?
This comment was minimized by the moderator on the site
Hi
Did you manage to solve this ?
I would like to count in subfolders to.
This comment was minimized by the moderator on the site
The total number of emails just used to appear beside the Name of the catagory. Does anyone know how to set this?
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL