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
- For a start, launch Outlook application.
- Then, access Outlook VBA editor by referring to “How to Run VBA Code in Your Outlook“.
- Later, enable the reference to “MS Excel Object Library” according to “How to Add an Object Library Reference in VBA“.
- 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
- After that, directly run this macro via “F5” key.
- 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.
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
Leave a Reply