Tasks can have five statuses – “Not Started”, “In Progress”, “Completed”, “Waiting for someone else” and “Deferred”. If you want to rapidly count all tasks by status, you can use the way introduced in this article.
Outlook supports users to assign status to the tasks or flagged items. There are five statuses – respectively “Not Started”, “In Progress”, “Completed”, “Waiting for someone else” and “Deferred”. Perhaps you have a great amount of tasks in your Outlook and they are in different statuses. Now, if you would like to count all the tasks by status, you can use the following way.
Quickly Count All Tasks by Status
- First off, launch Outlook VBA editor by referring to “How to Run VBA Code in Your Outlook“.
- Then, in the VBA editor, enable the reference to “MS Excel Object Library” as well as “MS Scripting Runtime” according to “How to Add an Object Library Reference in VBA“.
- Next, copy the following code into an unused module.
Dim objDictionary As New Scripting.Dictionary Sub CountTasksByStatus() Dim objStore As Outlook.Store Dim objOutlookFile As Outlook.Folder Dim objFolder As Outlook.Folder Dim objExcelApp As Excel.Application Dim objExcelWorkbook As Excel.Workbook Dim objExcelWorksheet As Excel.Worksheet Dim varStatuses As Variant Dim varTaskCounts As Variant Dim i As Integer Dim nLastRow As Integer 'Count by Dictionary Set objDictionary = CreateObject("Scripting.Dictionary") 'Process All Outlook data files For Each objStore In Application.Session.Stores Set objOutlookFile = objStore.GetRootFolder For Each objFolder In objOutlookFile.Folders If objFolder.DefaultItemType = olTaskItem Then Call ProcessTaskFolders(objFolder) End If Next Next 'Export the counts to an Excel worksheet Set objExcelApp = CreateObject("Excel.Application") objExcelApp.Visible = True Set objExcelWorkbook = objExcelApp.Workbooks.Add Set objExcelWorksheet = objExcelWorkbook.Sheets(1) With objExcelWorksheet .Cells(1, 1) = "Status" .Cells(1, 1).Font.Bold = True .Cells(1, 2) = "Task Count" .Cells(1, 2).Font.Bold = True End With varStatuses = objDictionary.Keys varTaskCounts = objDictionary.Items For i = LBound(varStatuses) To UBound(varStatuses) nLastRow = objExcelWorksheet.Range("A" & objExcelWorksheet.Rows.Count).End(xlUp).Row + 1 With objExcelWorksheet .Cells(nLastRow, 1) = varStatuses(i) .Cells(nLastRow, 2) = varTaskCounts(i) End With Next objExcelWorksheet.Columns("A:B").AutoFit End Sub Sub ProcessTaskFolders(ByVal objCurFolder As Outlook.Folder) Dim objTask As Outlook.TaskItem Dim strStatus As String Dim objSubfolder As Outlook.Folder 'Count tasks by status For Each objTask In objCurFolder.Items Select Case objTask.Status Case olTaskNotStarted strStatus = "Not Started" If objDictionary.Exists(strStatus) Then objDictionary(strStatus) = objDictionary(strStatus) + 1 Else objDictionary.Add strStatus, 1 End If Case olTaskInProgress strStatus = "In Progress" If objDictionary.Exists(strStatus) Then objDictionary(strStatus) = objDictionary(strStatus) + 1 Else objDictionary.Add strStatus, 1 End If Case olTaskComplete strStatus = "Completed" If objDictionary.Exists(strStatus) Then objDictionary(strStatus) = objDictionary(strStatus) + 1 Else objDictionary.Add strStatus, 1 End If Case olTaskWaiting strStatus = "Waiting on someone else" If objDictionary.Exists(strStatus) Then objDictionary(strStatus) = objDictionary(strStatus) + 1 Else objDictionary.Add strStatus, 1 End If Case olTaskDeferred strStatus = "Deferred" If objDictionary.Exists(strStatus) Then objDictionary(strStatus) = objDictionary(strStatus) + 1 Else objDictionary.Add strStatus, 1 End If End Select Next 'Process all subfolders recursively If objCurFolder.Folders.Count > 0 Then For Each objSubfolder In objCurFolder.Folders Call ProcessTaskFolders(objSubfolder) Next End If End Sub
- After that, click into the first subroutine and press “F5” key.
- At once, when macro completes, a new Excel worksheet will display, like the screenshot below. It contains the counts of tasks in different statuses.
Beware of Pitfalls around Outlook
There are a great amount of risks surrounding Outlook, including viruses, human errors, software faults as well as hardware failures, etc. Therefore, if don’t want to lose your valuable Outlook data, you have to keep cautious all the time. Simply put, you should never download unknown attachments or suspicious links in the emails. Besides, if possible, it is advisable to keep a remarkable Outlook fix utility, like DataNumen Outlook Repair, in vicinity, instead of totally relying on the inbox repair tool.
Author Introduction:
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover sql and outlook repair software products. For more information visit www.datanumen.com
Hello, this looks great, but I cannot get it to work. I inserted a new module (there were none there when I started), and I pasted the code into that new Module1. I wasn’t sure what it meant to “click into the first subroutine,” but I have clicked in various places within the module and then pressed F5 and I’ve gotten the same result every time. It starts, sits for a while, but I then get a pop-up that says:
Run-time error ’13’:
Type mismatch
If I click on Debug, it takes me to the second to last “Next” in the second subroutine of the code, the 8th line up from the very end. (It’s the next line after “End Select” and above “‘Process all subfolders recursively”.
I’m completely out of my depth to know what the issue is here.
1