How to Quickly Export Outlook Tasks in Different Statuses to Different Worksheets in an Excel Workbook

Sometimes, you may need to separate and export your Outlook tasks in different statuses to different worksheets in an Excel workbook. This article will introduce an effective and easy method to you.

Perhaps you have multiple tasks in Outlook. And they are in different statuses, including “Not Started”, “In Progress”, “Complete”, “Deferred” and “Waiting for someone else”. Under this circumstance, if you would like to export your tasks to an Excel workbook’s different sheets on basis of the status, you can use the way exposed thereinafter. It can let you get it in one go.

Quickly Export Tasks in Different Statuses to Different Worksheets in Excel

1. For a start, launch Outlook application.
2. Then, access Outlook VBA editor by referring to “How to Run VBA Code in Your Outlook“.
3. Later, enable the reference to “MS Excel Object Library” according to “How to Add an Object Library Reference in VBA“.
4. Next, copy the following code into an unused module.

Sub ExportTasksInDifferentStatusToDifferentSheets()
    Dim objTasks As Outlook.Items
    Dim objTask As Outlook.TaskItem
    Dim strStatus As String
    Dim objDictionary As Object
    Dim objExcelApp As Excel.Application
    Dim objExcelWorkbook As Excel.Workbook
    Dim objExcelWorksheet As Excel.Worksheet
    Dim varKey As Variant
    Dim strKey As String
    Dim i As Long
    Dim bSheetFound As Boolean
    Dim nLastRow As Integer
    Dim objSheet As Excel.Worksheet

    Set objTasks = Application.Session.GetDefaultFolder(olFolderTasks).Items
    Set objDictionary = CreateObject("Scripting.Dictionary")

    For Each objTask In objTasks
        strStatus = GetStatus(objTask)
        If objDictionary.Exists(strStatus) Then
           objDictionary(strStatus) = objDictionary(strStatus) + 1
        Else
           objDictionary.Add strStatus, 1
        End If
    Next

    Set objExcelApp = CreateObject("Excel.Application")
    Set objExcelWorkbook = objExcelApp.Workbooks.Add
    objExcelApp.Visible = True

    i = 0
    For Each varKey In objDictionary.Keys
        strKey = CStr(varKey)

        On Error Resume Next
        objExcelWorkbook.Sheets(strKey).Select
        bSheetFound = (Err = 0)
        On Error GoTo 0
        If bSheetFound = False Then
           i = i + 1
           If i < 4 Then
              Set objExcelWorksheet = objExcelWorkbook.Sheets(i)
           Else
              Set objExcelWorksheet = objExcelWorkbook.Sheets.Add(After:=objExcelWorkbook.Sheets(objExcelWorkbook.Sheets.Count))
           End If
           objExcelWorksheet.Name = strKey
        End If

        With objExcelWorksheet
            .Cells(1, 1) = strKey
            .Cells(1, 1).Font.Bold = True
            .Cells(1, 1).Font.Size = 18
            .Cells(2, 1) = "Subject"
            .Cells(2, 1).Font.Bold = True
            .Cells(2, 2) = "Start Date"
            .Cells(2, 2).Font.Bold = True
            .Cells(2, 3) = "Due Date"
            .Cells(2, 3).Font.Bold = True
       End With

       For Each objTask In objTasks
           If GetStatus(objTask) = strKey Then
              nLastRow = objExcelWorksheet.Range("A" & objExcelWorksheet.Rows.Count).End(xlUp).Row + 1
              With objExcelWorksheet
                   .Range("A" & nLastRow) = objTask.Subject
                   .Range("B" & nLastRow) = objTask.StartDate
                   .Range("C" & nLastRow) = objTask.DueDate
              End With
           End If
       Next

       objExcelWorksheet.Columns("A:C").AutoFit
   Next
End Sub

Function GetStatus(objTask As TaskItem) As String
   Select Case objTask.Status
          Case 0
               GetStatus = "Not Started"
          Case 1
               GetStatus = "In Progress"
          Case 2
               GetStatus = "Completed"
          Case 3
               GetStatus = "Waiting on someone else"
          Case 4
               GetStatus = "Deferred"
   End Select
End Function

5. After that, directly run this macro via “F5” key.
6. Immediately, a new Excel workbook will display. It contains the separate worksheets that has the exported tasks in different statuses, as shown in the following screenshot.

Keep Prepared for Outlook Recovery

Since it is hard to predicate Outlook corruption but PST file tends to be damaged, it is necessary for every Outlook user to keep well-prepared for unexpected PST corruption. For example, you ought to get hold of a specialized recovery tool, like DataNumen Outlook Repair, which is able to recover Outlook data with effortless ease.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupt sql and outlook repair software products. For more information visit www.datanumen.com