Exporting Outlook Tasks to Excel

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.

1. Start Outlook
2. Press ALT+F11 to open the Visual Basic Editor
3. If not already expanded, expand Microsoft Office Outlook Objects
4. If not already expanded, expand Modules
5. 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.
6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
7. Click the diskette icon on the toolbar to save the changes
8. 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.

1. With Outlook open select a folder that contains tasks.
2. Run the macro ExportTasksToExcel.
3. When prompted, enter a file name to save the export to. You can cancel the export by leaving the file name blank.
4. The macro will display a dialog-box when it’s finished. The dialog-box includes a count of the number of tasks exported.

Notes.

• 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”

About these ads