How to Quickly Export Total Time Spent on the Outlook Appointments in Each Color Category

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

1. In the first place, launch your Outlook application.
2. Then, after entering the Outlook window, you could tap on the “Alt + F11” key buttons.
3. Subsequently, you’ll get access to the “Microsoft Visual Basic for Applications” window.
4. Next, you’d be required to enable “Microsoft Excel Object Library”. You could click the “Tools” > “Reference” to achieve it.
5. Then, you ought to find and open a module which is not in use.
6. 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

7. Finally you can trigger this macro, no matter via clicking the “Run” icon in the toolbar or pressing the “F5” key button.
8. Then, you will be demanded to select a specific calendar.
9. 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.
10. 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