If you wish to create a report about the total time you spend on the appointments in each color category, you can utilize the method introduced in this article. It can help you accomplish this in quick time without needs to count manually.
Many users are accustomed to logging most of their activities in Outlook calendar. Also, so as to manage and differentiate them more easily, they assign categories to each of them. In this case, some users would like to generate a report showing the total time spent on the calendar items in each color category. With no doubt, it is troublesome to count and input manually. Therefore, here we’ll share a way which can accomplish this task via a simple click.
Export Total Time Spent on the Appointments in Each Color Category
- In the first place, launch your Outlook application.
- Then, after entering the Outlook window, you could tap on the “Alt + F11” key buttons.
- Subsequently, you’ll get access to the “Microsoft Visual Basic for Applications” window.
- Next, you’d be required to enable “Microsoft Excel Object Library”. You could click the “Tools” > “Reference” to achieve it.
- Then, you ought to find and open a module which is not in use.
- After that, you need to copy the following VBA code into this module.
Sub ExportTimeSpentOnAppointmentsInEachColorCategory() Dim objDictionary As Object Dim objAppointments As Outlook.Items Dim objAppointment As Outlook.AppointmentItem Dim strCategory As String Dim arrCategory As Variant Dim varCategory As Variant Dim objExcelApp As Excel.Application Dim objExcelWorkbook As Excel.Workbook Dim objExcelWorksheet As Excel.Worksheet Dim arrKey As Variant Dim arrItem As Variant Dim i As Long Dim nLastRow As Integer Set objDictionary = CreateObject("Scripting.Dictionary") Set objAppointments = Application.Session.PickFolder.Items For Each objAppointment In objAppointments arrCategory = Split(objAppointment.Categories, ",") For Each varCategory In arrCategory strCategory = Trim(varCategory) If objDictionary.Exists(strCategory) Then objDictionary.Item(strCategory) = objDictionary.Item(strCategory) + objAppointment.Duration Else objDictionary.Add strCategory, objAppointment.Duration End If Next Next 'Create a new Excel workbook Set objExcelApp = CreateObject("Excel.Application") Set objExcelWorkbook = objExcelApp.Workbooks.Add Set objExcelWorksheet = objExcelWorkbook.Sheets(1) objExcelApp.Visible = True objExcelWorkbook.Activate With objExcelWorksheet .Cells(1, 1) = "Color Category" .Cells(1, 1).Font.Bold = True .Cells(1, 1).Font.Size = 14 .Cells(1, 2) = "Total Time (min)" .Cells(1, 2).Font.Bold = True .Cells(1, 2).Font.Size = 14 End With arrKey = objDictionary.Keys arrItem = objDictionary.Items For i = LBound(arrKey) To UBound(arrKey) nLastRow = objExcelWorksheet.Range("A" & objExcelWorksheet.Rows.count).End(xlUp).Row + 1 objExcelWorksheet.Cells(nLastRow, 1) = arrKey(i) objExcelWorksheet.Cells(nLastRow, 2) = arrItem(i) Next objExcelWorksheet.Columns("A:B").AutoFit End Sub
- Finally you can trigger this macro, no matter via clicking the “Run” icon in the toolbar or pressing the “F5” key button.
- Then, you will be demanded to select a specific calendar.
- After you select and hit “OK”, the macro will continue to run. After it finishes, there will be a new Excel file that displays in the background.
- You can access it. It will look like the following screenshot:
Keep an Eye out for Potential Threats around Your Outlook
Outlook users should watch out all the potential risks, including unknown email attachments, embedded links and human errors. Otherwise, your Outlook can get corrupted at any time. Also, making regular Outlook data backups and keeping a specialized repair tool are matters of necessity. DataNumen Outlook Repair is one of the most highly recommended fix tools. It can repair Outlook problems in a jiffy.
Author Introduction:
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including damaged mdf and outlook repair software products. For more information visit www.datanumen.com
Hi — I tried to run that VBA and got this error: Compile error: user-defined type not defined
Is there a way to restrict the report to a single week? I’d like to know how much time I spent on each category for the past week.