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:
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:
(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.
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:
- 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.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 4 months agoThe total number of emails just used to appear beside the Name of the catagory. Does anyone know how to set this?
- To post as a guest, your comment is unpublished.· 7 months agoHi,
Instead of only calculating from the selected folder, is there a way to include all the sub folders as well?
- To post as a guest, your comment is unpublished.· 7 months agoHello, 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 ?
- To post as a guest, your comment is unpublished.· 8 months agois there a way you can covert this into an excel table?
- To post as a guest, your comment is unpublished.· 8 months agoHi 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.
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 & "'")
For Each xItem In xItems
xStr = xItem.Categories
If Not xDict.Exists(xStr) Then
xDict(xStr) = 0
xDict(xStr) = CLng(xDict(xStr)) + 1
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
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
xWs.Range("A1", "B" + CStr(I)).Columns.AutoFit
xExlApp.Visible = True
Set xFolder = Nothing
- To post as a guest, your comment is unpublished.· 7 months agoThis was helpful for me also, thank you so much for sharing!
- To post as a guest, your comment is unpublished.· 1 years agoHi , 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?
- To post as a guest, your comment is unpublished.· 1 years agoHi 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.
- To post as a guest, your comment is unpublished.· 1 years agoThis would be great!
- To post as a guest, your comment is unpublished.· 3 years agoThis is perfect, but how can I change the VBA to include subfolders of the current folder in the count?