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.
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
I absolutely adore your post, and without fail, I faithfully follow each of its recommendations whenever there’s a new one. Not only that, but I enthusiastically share it across all my social media platforms, and my dedicated followers always give a resounding liking and engaging with it. Continue the outstanding work and informative posts coming! By the way, I found an article from sites.google.com/view/career-shift/makethfate on how to make money online after being made laidoff, and I would like to share my own experience how I have managed to overcome my financial woes in just a fortnight with this 3 pronged effective strategy and guidance that didn’t cost me single cent!
o.web20.services