In an earlier post I showed a number of ways to export Outlook messages to Excel. Mitesh saw that post and asked if it’d be possible to adapt it to export Outlook tasks instead of messages. The answer of course is “yes”. In fact you could adapt the code from the original post to export any of Outlook’s basic item types (i.e. appointments, contacts, messages, notes, tasks). All Mitesh needs to do is change the list of item properties the code will export. Where an email has properties called “Subject”, “ReceivedTime”, and “SenderEmailAddress” (to name a few), a task has “Subject”, “StartDate”, and “DueDate”. In order to change the script to handle a different item type you need to know what properties an Outlook item of that type has. Microsoft has pages on its site that list the complete set of a task item’s properties for Outlook 2007, Outlook 2010, and Outlook 2013. For this example I’ve chosen a subset of task properties. You can add, delete, and/or rearrange them to suit your needs.
Some properties (e.g. DelegationState, Ownership) are enumerations. An enumeration stores a numeric value that equates to something. For example, a 0 in DelegationState means the task is not delegated while a 2 means that the task has been delegated and the person it is delegated to has accepted it. Rather than exporting the numeric value stored in an enumerated property, I wrote functions that returns a more meaningful value in plain English. I only wrote those functions for enumerated fields I used in this example. There are other enumerated fields that would benefit from a similar conversion if you include them in your export. Once again, you can refer to Microsoft’s documentation to see which properties are enumerated and to get the available values and what they stand for.
Adding the code to Outlook.
- Start Outlook
- Press ALT+F11 to open the Visual Basic Editor
- If not already expanded, expand Microsoft Office Outlook Objects
- If not already expanded, expand Modules
- Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
- Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
- Click the diskette icon on the toolbar to save the changes
- Close the VB Editor
Sub ExportTasksToExcel() Const SCRIPT_NAME = "Export Tasks to Excel" Dim olkTsk As Object, _ excApp As Object, _ excWkb As Object, _ excWks As Object, _ lngRow As Long, _ lngCnt As Long, _ strFilename As String strFilename = InputBox("Enter a filename (including path) to save the exported tasks to.", SCRIPT_NAME) If strFilename = "" Then MsgBox "The filename is blank. Export aborted.", vbInformation + vbOKOnly, SCRIPT_NAME Else Set excApp = CreateObject("Excel.Application") Set excWkb = excApp.Workbooks.Add() Set excWks = excWkb.ActiveSheet 'Write Excel Column Headers With excWks .Cells(1, 1) = "Subject" .Cells(1, 2) = "Created On" .Cells(1, 3) = "Start Date" .Cells(1, 4) = "Due Date" .Cells(1, 5) = "Date Complete" .Cells(1, 6) = "% Complete" .Cells(1, 7) = "Delegation State" .Cells(1, 8) = "Delegator" .Cells(1, 9) = "Owner" .Cells(1, 10) = "Ownership" .Cells(1, 11) = "Role" .Cells(1, 12) = "Response State" End With lngRow = 2 'Write tasks to spreadsheet For Each olkTsk In Application.ActiveExplorer.CurrentFolder.Items 'Add a row for each task item property you want to export. Rearrange the rows as desired. Remember to edit the column number you want each property to appear in. excWks.Cells(lngRow, 1) = olkTsk.Subject excWks.Cells(lngRow, 2) = olkTsk.CreationTime excWks.Cells(lngRow, 3) = olkTsk.StartDate excWks.Cells(lngRow, 4) = olkTsk.DueDate excWks.Cells(lngRow, 5) = olkTsk.DateCompleted excWks.Cells(lngRow, 6) = olkTsk.PercentComplete excWks.Cells(lngRow, 7) = GetDelegationState(olkTsk.DelegationState) excWks.Cells(lngRow, 8) = olkTsk.Delegator excWks.Cells(lngRow, 9) = olkTsk.Owner excWks.Cells(lngRow, 10) = GetOwnership(olkTsk.Ownership) excWks.Cells(lngRow, 11) = olkTsk.Role excWks.Cells(lngRow, 12) = GetResponseState(olkTsk.ResponseState) lngRow = lngRow + 1 lngCnt = lngCnt + 1 Next Set olkTsk = Nothing excWkb.SaveAs strFilename excWkb.Close MsgBox "Process complete. A total of " & lngCnt & " tasks were exported.", vbInformation + vbOKOnly, SCRIPT_NAME End If Set excWks = Nothing Set excWkb = Nothing Set excApp = Nothing End Sub Function GetDelegationState(intState As Integer) As String Select Case intState Case 0 GetDelegationState = "Not delegated" Case 1 GetDelegationState = "Unknown" Case 2 GetDelegationState = "Accepted" Case 3 GetDelegationState = "Declined" End Select End Function Function GetOwnership(intState As Integer) As String Select Case intState Case 0 GetOwnership = "New Task" Case 1 GetOwnership = "Delegated Task" Case 2 GetOwnership = "Own Task" End Select End Function Function GetResponseState(intState As Integer) As String Select Case intState Case 0 GetResponseState = "Simple" Case 1 GetResponseState = "Reassigned" Case 2 GetResponseState = "Accepted" Case 3 GetResponseState = "Declined" End Select End Function
Using the Code.
- With Outlook open select a folder that contains tasks.
- Run the macro ExportTasksToExcel.
- When prompted, enter a file name to save the export to. You can cancel the export by leaving the file name blank.
- The macro will display a dialog-box when it’s finished. The dialog-box includes a count of the number of tasks exported.
- This code can easily be modified to export a different set of fields. To do that, change the headings written to the spreadsheet (lines 19-30) and the message fields (lines 36-47).
- If you don’t want the macro to prompt for a file name each time, then you can change line 10 to strFilename = “Path_and_File_Name”