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 exports. 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 the properties of that item type. 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, the values in the enumeration, and what each value stands for.

Instructions.

Follow these instructions to add 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 InsertModule.
  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 Solution.

  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”

Revisions.

Revision 1.

A reader named Corey asked for a couple of modifications to the original code. Corey needs to export both the Importance and Categories fieds, and he’d like to sort the exported tasks by Importance (descending) and Categories (ascending).

Corey will use the instructions from the original post to add the code to Outlook.

Sub ExportTasksToExcel()
    Const SCRIPT_NAME = "Export Tasks to Excel"
    Const xlYes = 1
    Const xlAscending = 1
    Const xlDescending = 2
    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"
            .Cells(1, 13) = "Priority"
            .Cells(1, 14) = "Categories"
        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)
            excWks.Cells(lngRow, 13) = olkTsk.Importance
            excWks.Cells(lngRow, 14) = olkTsk.Categories
            lngRow = lngRow + 1
            lngCnt = lngCnt + 1
        Next
        Set olkTsk = Nothing
        lngRow = excWks.UsedRange.rows.Count
        excWks.Range("A1:N" & lngRow).Sort Key1:="Categories", Order1:=xlAscending, Key2:="Priority", Order2:=xlDescending, Header:=xlYes
        excWks.Columns("A:N").AutoFit
        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

Revision 2.

I put this revision together for a reader named Ken who needs three fields added to the export. Ken needs the recurrence pattern, recurrence range, and notes. He also only wants to export active (i.e. uncompleted) tasks. This revision implements all of those. Ken can use the instructions from the original post to add the code to Outlook.

Sub ExportTasksToExcel()
    Const SCRIPT_NAME = "Export Tasks to Excel (Rev 1)"
    Dim olkFld As Outlook.Folder, _
        olkFlt As Outlook.Items, _
        olkTsk As TaskItem, _
        olkPat As Outlook.RecurrencePattern, _
        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"
            .Cells(1, 13) = "Recurrence Pattern"
            .Cells(1, 14) = "Recurrence Range"
            .Cells(1, 15) = "Notes"
        End With
        lngRow = 2
        'Write tasks to spreadsheet
        Set olkFld = Application.ActiveExplorer.CurrentFolder
        olkFld.Items.Sort "StartDate"
        Set olkFlt = olkFld.Items.Restrict("[Complete] = False")
        For Each olkTsk In olkFlt
            '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)
            If olkTsk.IsRecurring Then
                Debug.Print olkTsk.Subject
                Set olkPat = olkTsk.GetRecurrencePattern
                excWks.Cells(lngRow, 13) = GetPattern(olkPat)
                excWks.Cells(lngRow, 14) = olkPat.PatternStartDate & " to " & IIf(olkPat.NoEndDate, "No end date", olkPat.PatternEndDate)
            End If
            excWks.Cells(lngRow, 15) = olkTsk.Body
            lngRow = lngRow + 1
            lngCnt = lngCnt + 1
        Next
        Set olkTsk = Nothing
        excWks.Columns("A:O").AutoFit
        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

Function GetPattern(olkPat As Outlook.RecurrencePattern) As String
    Select Case olkPat.RecurrenceType
        Case olRecursDaily
            GetPattern = "Daily every " & olkPat.Interval & " day(s)"
        Case olRecursWeekly
            GetPattern = "Weekly on " & ConvertDaysOfWeekMask(olkPat.DayOfWeekMask)
        Case olRecursMonthly
            GetPattern = "Every " & olkPat.Interval & " month(s) on the " & NumericSuffix(olkPat.DayOfMonth)
        Case olRecursMonthNth
            GetPattern = "Every " & olkPat.Interval & " month(s) on the " & NumericSuffix(olkPat.Instance) & " " & ConvertDaysOfWeekMask(olkPat.DayOfWeekMask)
        Case olRecursYearly
            GetPattern = "Yearly on the " & NumericSuffix(olkPat.DayOfMonth) & " of " & MonthName(olkPat.MonthOfYear)
        Case olRecursYearNth
            GetPattern = "Yearly on the " & NumericSuffix(olkPat.Instance) & " " & ConvertDaysOfWeekMask(olkPat.DayOfWeekMask) & " of " & MonthName(olkPat.MonthOfYear)
    End Select
End Function

Function ConvertDaysOfWeekMask(intMask As Integer) As String
    If intMask And olSunday Then
        ConvertDaysOfWeekMask = ConvertDaysOfWeekMask & "Sun,"
    End If
    If intMask And olMonday Then
        ConvertDaysOfWeekMask = ConvertDaysOfWeekMask & "Mon,"
    End If
    If intMask And olTuesday Then
        ConvertDaysOfWeekMask = ConvertDaysOfWeekMask & "Tue,"
    End If
    If intMask And olWednesday Then
        ConvertDaysOfWeekMask = ConvertDaysOfWeekMask & "Wed,"
    End If
    If intMask And olThursday Then
        ConvertDaysOfWeekMask = ConvertDaysOfWeekMask & "Thu,"
    End If
    If intMask And olFriday Then
        ConvertDaysOfWeekMask = ConvertDaysOfWeekMask & "Fri,"
    End If
    If intMask And olSaturday Then
        ConvertDaysOfWeekMask = ConvertDaysOfWeekMask & "Sat,"
    End If
    If Len(ConvertDaysOfWeekMask) > 0 Then
        ConvertDaysOfWeekMask = Left(ConvertDaysOfWeekMask, Len(ConvertDaysOfWeekMask) - 1)
    End If
End Function

Function NumericSuffix(intVal As Integer) As String
    Select Case intVal
        Case 1, 21, 31
            NumericSuffix = "st"
        Case 2, 22
            NumericSuffix = "nd"
        Case 3, 23
            NumericSuffix = "rd"
        Case 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 24, 25, 26, 27, 28, 29, 30
            NumericSuffix = "th"
    End Select
    NumericSuffix = intVal & NumericSuffix
End Function
Advertisements

132 comments on “Exporting Outlook Tasks to Excel

  1. David,

    I am trying to export by date range for the purpose of calculating the time to complete the task. I am using LastModificationTime in order to get a time stamp as DateCompleted does not provide a time stamp. I have encountered two issues with the code I am using:

    1. The code does not seem to be restricting the export based on the range.

    2. I would like to export from archived folders and it seems that Outlook considers archiving to be a modification of the task and inserts the archived date/time as the LastModifiedTime. The amount of tasks delegated necessitates that I archive very frequently and have had to resort to exporting before every archive to make sure I don’t lose the data.

    Lastly, is it possible for something to be written into the code that will calculate this for me and then export to a column. If the latter is possible, it would need to take business operation hours into consideration as well as weekends. This would be ideal, but I understand that it may not be possible.

    Here is the code that I am using currently. At the very least I need to figure out why the date range is not working, but if you could offer help on the other two items, I may just do cartwheels.

    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, _
            strDateRange As String, _
            arrTemp As Variant, _
            datStart As Date, _
            datEnd As Date
        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
        End If
        If strFilename  "" Then
            strDateRange = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
            arrTemp = Split(strDateRange, "to")
            datStart = IIf(IsDate(arrTemp(0)), arrTemp(0), Date) & " 12:00am"
            datEnd = IIf(IsDate(arrTemp(1)), arrTemp(1), Date) & " 11:59pm"
            Set excApp = CreateObject("Excel.Application")
            Set excWkb = excApp.Workbooks.Add()
            Set excWks = excWkb.ActiveSheet
            With excWks
                .Cells(1, 1) = "Subject"
                .Cells(1, 2) = "Created On"
                .Cells(1, 3) = "Team"
                .Cells(1, 4) = "Due Date"
                .Cells(1, 5) = "Date Complete"
                .Cells(1, 6) = "Owner"
    
            End With
            lngRow = 2
            For Each olkTsk In Application.ActiveExplorer.CurrentFolder.Items
                excWks.Cells(lngRow, 1) = olkTsk.Subject
                excWks.Cells(lngRow, 2) = olkTsk.CreationTime
                excWks.Cells(lngRow, 3) = olkTsk.Categories
                excWks.Cells(lngRow, 4) = olkTsk.DueDate
                excWks.Cells(lngRow, 5) = olkTsk.LastModificationTime
                excWks.Cells(lngRow, 6) = olkTsk.Owner
                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
    
    • Hi, John.

      The reason the export isn’t filtering on the date range is because there was no code for filtering. The code was just getting the date range information. There wasn’t any code to actually filter on the date range entered. I’ve added that code in this version.

      Sub ExportTasksToExcel()
          Const SCRIPT_NAME = "Export Tasks to Excel"
          Dim olkFld As Outlook.MAPIFolder, _
              olkFlt As Outlook.Items, _
              olkTsk As Object, _
              excApp As Object, _
              excWkb As Object, _
              excWks As Object, _
              lngRow As Long, _
              lngCnt As Long, _
              strFil As String, _
              strRng As String, _
              arrTemp As Variant, _
              datBeg As Date, _
              datEnd As Date
          strFil = InputBox("Enter a filename (including path) to save the exported tasks to.", SCRIPT_NAME)
          If strFil = "" Then
              MsgBox "The filename is blank.  Export aborted.", vbInformation + vbOKOnly, SCRIPT_NAME
          Else
              strRng = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", SCRIPT_NAME, VBA.Date & " to " & VBA.Date)
              arrTemp = Split(strRng, "to")
              datBeg = IIf(VBA.IsDate(arrTemp(0)), arrTemp(0), VBA.Date) & " 12:00am"
              datEnd = IIf(VBA.IsDate(arrTemp(1)), arrTemp(1), VBA.Date) & " 11:59pm"
              Set excApp = CreateObject("Excel.Application")
              Set excWkb = excApp.Workbooks.Add()
              Set excWks = excWkb.ActiveSheet
              With excWks
                  .Cells(1, 1) = "Subject"
                  .Cells(1, 2) = "Created On"
                  .Cells(1, 3) = "Team"
                  .Cells(1, 4) = "Due Date"
                  .Cells(1, 5) = "Date Complete"
                  .Cells(1, 6) = "Owner"
              End With
              lngRow = 2
              Set olkFld = Application.ActiveExplorer.CurrentFolder
              Set olkFlt = olkFld.Items.Restrict("[LastModificationTime] >= '" & VBA.Format(datBeg, "ddddd h:nn AMPM") & "' AND [LastModificationTime] <= '" & VBA.Format(datEnd, "ddddd h:nn AMPM") & "'")
              For Each olkTsk In olkFlt
                  If olkTsk.Class = olTask Then
                      excWks.Cells(lngRow, 1) = olkTsk.Subject
                      excWks.Cells(lngRow, 2) = olkTsk.CreationTime
                      excWks.Cells(lngRow, 3) = olkTsk.Categories
                      excWks.Cells(lngRow, 4) = olkTsk.DueDate
                      excWks.Cells(lngRow, 5) = olkTsk.LastModificationTime
                      excWks.Cells(lngRow, 6) = olkTsk.Owner
                      lngRow = lngRow + 1
                      lngCnt = lngCnt + 1
                  End If
              Next
              Set olkTsk = Nothing
              excWkb.SaveAs strFil
              excWkb.Close
              MsgBox "Process complete.  I exported " & lngCnt & " tasks.", 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
      

      There’s nothing I can do about Outlook changing the last modification time when you archive an item. We could add a user-defined field to your tasks that you’d populate with the actual date complete. You’d have to remember to fill in that field when you complete a task and we can’t use the standard filtering method on a user-defined field. We can still filter, I’ll just have to write some additional code and the process of filtering will take longer.

      I can probably write some code to calculate the time for you, I just need to know the rules to apply for that calculation.

  2. Dave – Love your work! Thank you for sharing.

    How might you go about integrating a user-defined field say “Urgent” with Data Type = “Y/N” into the code? For the sake of discussion suppose you added this to the 13th column immediately after response state?

    -G

    • Hi, Glen.

      Thanks! Glad you like it.

      Here’s how you’d retrieve the value of a user-defined field named “Urgent” and place it in column 13.

      excWks.Cells(lngRow, 13) = olkTsk.UserProperties.Item("Urgent").Value
      
  3. David,

    This is great code and I have this working and even export custom properties. What I would like to do is export all of my shared tasks as I am a supervisor and I have everyone’s tasks shared with me, but I want to export it to look at forward loading as a group. I tried using Outlook.SharedItem.TaskItem, but that was a no go. I looked through the whole list and did not find a solution. Any advice you could give me would be great.

    • Hi, Trevor.

      Thanks!

      I’m not clear on what you mean when you say “I have everyone’s tasks shared with me”. Does that mean everyone has shared their Tasks folder with you or that you have a copy of their tasks in your Tasks folder?

    • Everyone has shared their tasklists with me. Myself and all of the Project Managers have full permissions so that we can add tasks and prioritize them as they become available.

    • Hi, Trevor.

      Please try this version.

      ' On the next line, edit the list of names of the staff you want to export tasks from.  The list is comma separated.
      Const MY_NAMES = "John Doe,Sally Smith,Tom Jones"
      
      Sub ExportTasksToExcel()
          Const SCRIPT_NAME = "Export Tasks to Excel"
          Dim olkTsk As Object, _
              olkRcp As Object, _
              olkFol As Object, _
              excApp As Object, _
              excWkb As Object, _
              excWks As Object, _
              lngRow As Long, _
              lngCnt As Long, _
              strFilename As String, _
              arrNam As Variant, _
              varNam As Variant
          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) = "List"
                  .Cells(1, 2) = "Subject"
                  .Cells(1, 3) = "Created On"
                  .Cells(1, 4) = "Start Date"
                  .Cells(1, 5) = "Due Date"
                  .Cells(1, 6) = "Date Complete"
                  .Cells(1, 7) = "% Complete"
                  .Cells(1, 8) = "Delegation State"
                  .Cells(1, 9) = "Delegator"
                  .Cells(1, 10) = "Owner"
                  .Cells(1, 11) = "Ownership"
                  .Cells(1, 12) = "Role"
                  .Cells(1, 13) = "Response State"
              End With
              lngRow = 2
              arrNam = Split(MY_NAMES, ",")
              For Each varNam In arrNam
                  Set olkRcp = Session.CreateRecipient(varNam)
                  olkRcp.Resolve
                  If olkRcp.Resolved Then
                      Set olkFol = Session.GetSharedDefaultFolder(olkRcp, olFolderTasks)
                      If TypeName(olkFol) <> "Nothing" Then
                          'Write tasks to spreadsheet
                          For Each olkTsk In olkFol.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 ) = varNam
                              excWks.Cells(lngRow, 2) = olkTsk.Subject
                              excWks.Cells(lngRow, 3) = olkTsk.CreationTime
                              excWks.Cells(lngRow, 4) = olkTsk.StartDate
                              excWks.Cells(lngRow, 5) = olkTsk.DueDate
                              excWks.Cells(lngRow, 6) = olkTsk.DateCompleted
                              excWks.Cells(lngRow, 7) = olkTsk.PercentComplete
                              excWks.Cells(lngRow, 8) = GetDelegationState(olkTsk.DelegationState)
                              excWks.Cells(lngRow, 9) = olkTsk.Delegator
                              excWks.Cells(lngRow, 10) = olkTsk.Owner
                              excWks.Cells(lngRow, 11) = GetOwnership(olkTsk.Ownership)
                              excWks.Cells(lngRow, 12) = olkTsk.Role
                              excWks.Cells(lngRow, 13) = GetResponseState(olkTsk.ResponseState)
                              lngRow = lngRow + 1
                              lngCnt = lngCnt + 1
                          Next
                          Set olkTsk = Nothing
                          Set olkFol = Nothing
                      End If
                  End If
                  DoEvents
              Next
              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
      
    • I am getting an automation error on this line: “For Each olkTsk In olkFol.Items”
      olkTsk is set at nothing, not sure if this needs to be defined in another way? I tried setting it as “TaskItem” instead of “Object” but this did not work. Again, Vb is not my strength so I apologize if I am missing something obvious.

    • Trevor,

      Please give the exact wording of the error message and any number associated with it. olkTsk should be defined “As Object”.

    • Error is:” Run-time ‘-659537915 (d8b04005)’: Automation error ” the debig takes me to the line “For Each olkTsk In olkFol.Items”

  4. Hi David, after running the code second time, I am unable to open XLS file as it is locked by me. This code generate windows process EXCEL.EXE *32 and after killing the process xls file is not saved. Any idea?

    • Hi, Juraj.

      Something is happening that’s preventing the code from closing the spreadsheet. When you kill the Excel process the spreadsheet the code was creating is lost. Which version of the code are you using?

  5. Hi David,
    I want to use your code here for my project, with some modification. I would like to extract Outlook task from specific date, unfortunately I am struggling with code. I’ll appreciate your help.

    • Hi, Juraj.

      Which version of the code do you want to use? Once I know that I can make the necessary changes to export for a date range.

    • David, I’ve managed to export tasks as I needed with the help of your code with some modifications. What I want to accomplish now is to copy Outlook tasks with specific date range to shared task folder every week on Monday. I am sure you know the tricks… I need to do for whole team so at the end of the month we have all tasks centralized in the shared task folder.

    • Hi, Juraj.

      Will each person be running their own tasks over or will you be copying them for everyone? If it’s the latter, then do you have access to everyone’s task folder in Outlook? You must have for that to work.

    • David, for each team member is running macro locally to create tasks with the help of userform either from email or without. I would like to add another macro which will run at the end of month and will make copy of existing tasks with specific date range (help of input form) to our shared outlook task folder, where I will extract all centralized tasks to excel and analyze, make calculation etc… I don’t have access to colleague’s task folder, every colleague will run macro by own

    • Hi, Juraj.

      Outlook doesn’t have a built-in way of scheduling something to run. How about a script that runs from outside of Outlook? You can schedule it to run using Windows’ built-in task scheduler. Will that work for you?

    • Hello David, really appreciate your time, effort and experience! Well, if this code (windows built in scheduler) outside the Outlook will do the job – copy of all tasks of my colleague to shared task folder within specific date range, will be more than great!

    • Hello David, I found the code which is moving selected tasks to shared task folder. I am now trying to modify this code to work with input box where I enter date range of tasks and these will be moved to shared folder. Hence no success. Here is the code:

      Set ns = Application.GetNamespace("MAPI")
      Set DefTask = GetFolderPath("Mailbox - XXXXXX\Tasks")
      
      'Define path to the target folder
      Set objSharedFolder = GetFolderPath("Mailbox - YYYYYYY\Tasks") 'for Central and South
      'Set objFolder = GetFolderPath("Mailbox - ZZZZZZZ\Tasks") 'for UK
      
       'Set date range
       strDateRange = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date &amp; " to " &amp; Date)
              arrTemp = Split(strDateRange, "to")
              datStart = IIf(IsDate(arrTemp(0)), arrTemp(0), Date) &amp; " 12:00am"
              datEnd = IIf(IsDate(arrTemp(1)), arrTemp(1), Date) &amp; " 11:59pm"
              
              datStartF = Format(datStart, "Short Date")
              datEndF = Format(datEnd, "Short Date")
       
       Set olkLst = DefTask.Restrict("[StartDate] &gt;= '" &amp; datStartF &amp; "'" &amp; " AND [StartDate] &lt;= '&quot; &amp; datEndF &amp; &quot;'&quot;)
              For Each objItem In olkLst
                  If objItem.Class = olTask Then 'And objSharedFolder.DefaultItemType = olTaskItem Then
               objItem.Move objSharedFolder
            End If
        'End If
      Next
      

      It's looks like the code doesn't select task within date range… Any idea?
      Thanks a lot!

    • Hi, Juraj.

      These two lines are the problem.

              datStartF = Format(datStart, "Short Date")
              datEndF = Format(datEnd, "Short Date")
      

      They’re converting the date to a format that the Restrict command can’t use. Remove those two lines and change line 17 to

       Set olkLst = DefTask.Restrict("[StartDate] >= '" & datStart & "'" & " AND [StartDate] <= '" & datEnd & "'")
      

      datStart and datEnd are already formatted correctly for the Restrict command.

    • Hello David, unfortunately removing lines indicated by you doesn’t change the result. Still need to find out how to select tasks within date range and copy it to shared folder 😦

    • Hi, Juraj.

      So you removed lines 14 and 15, and then changed line 17 accordingly. Is that correct?

    • Hello David, yes, I modified script as per your advice and still nothing. Looking for any similar code on net, hence without success. Any idea?

  6. Hi David,

    This code is great, and I’ve found it very helpful for exporting a long list of tasks to Excel for monthly reporting purposes. However, I’ve noticed that when I export the body, it pasts everything into a single cell. How can I split the body text so that each line (separated by a vbCrLf) is inserted into a separate row?

    Thanks for the help,
    Rachelle

    • Hi, Rachelle.

      Thanks, I’m glad you like it.

      I can probably devise a way of doing that, but why would you want each line of the body to be on its own row? Wouldn’t that make the output very difficult to read?

    • Hi David,

      The reason for this is that each line in the task body is a new “entry” representing a different step in the process. I need to be able to calculate the number of days between each step (each line) in order to trend how long the various processes take.

      For Example:
      Step 1 – 10 Oct 2015
      Step 2 – 23 Dec 2015
      Step 3 – 06 Jan 2016

      Once the information is exported to Excel, each date will appear on a new line, so I can calculate the NETWORKDAYS between them.

    • The code I’m using (some from yours; some from elsewhere) looks like this:

      Sub DocumentProcessingTimes()
      Dim olkFld As Outlook.Folder
      Dim olkFlt As Outlook.Items
      Dim olkTsk As TaskItem
      Dim excApp As Object
      Dim excWkb As Object
      Dim excWks As Object
      Dim repMo As String
      Dim tskMo As String
      Dim lngRow As Long
      Dim lngCnt As Long
      Dim FileName As String

      FileName = “C:\Users\rcous\Desktop\Doc Processing Times.xlsm”
      repMo = InputBox(“Reporting Month”)

      Set excApp = CreateObject(“Excel.Application”)
      Set excWkb = excApp.Workbooks.Open(FileName)
      Set excWks = excWkb.Sheets.Add
      excWks.Move After:=excWkb.Sheets(excWkb.Sheets.Count)
      excWks.Name = Format(repMo, “MMM-yyyy”)

      excApp.Visible = True

      ‘Write Excel Column Headers
      With excWks
      .Cells(1, 1) = “Report for:”
      .Cells(1, 3) = Format(repMo, “MMM-yyyy”)
      .Cells(2, 1) = “VFD”
      .Cells(2, 2) = “DocID”
      .Cells(2, 3) = “Notes”
      End With
      lngRow = 3

      ‘Write tasks to spreadsheet
      Set olkFld = Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders(“Tasks”).Folders(“USPF Documentation Tasks”)
      Set olkFlt = olkFld.Items.Restrict(“[Categories] = Doc8 Finish”)

      For Each olkTsk In olkFlt
      ‘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.BillingInformation
      excWks.Cells(lngRow, 2) = olkTsk.ContactNames
      excWks.Cells(lngRow, 3) = olkTsk.Body

      Call excApp.Run(“SplitCells”)

      lngRow = lngRow + 1
      lngCnt = lngCnt + 1
      Next
      Set olkTsk = Nothing

      excWks.Columns(“A:C”).WrapText = False
      excWks.Columns(“A:C”).AutoFit
      excWks.Rows.AutoFit

      MsgBox “Process complete. A total of ” & lngCnt & ” tasks were exported.”, vbInformation + vbOKOnly, SCRIPT_NAME
      Set excWks = Nothing
      Set excWkb = Nothing
      Set excApp = Nothing
      End Sub

    • Sorry for the delayed response. I thought I responded a couple of days ago, but it looks like it didn’t post for some reason.

      Anyway, the code I am currently using is shown below. I’ve managed to make it work by running an Excel macro within the spreadsheet that splits the lines and copies them to new rows using the transpose feature. However, I’m stuck at how to get it to then recognize the last row and begin the next task there. It still finds the row below the last task and over-wrights the new rows.

      Outlook Macro:

      Sub DocumentProcessingTimes()
          Dim olkFld As Outlook.Folder
          Dim olkFlt As Outlook.Items
          Dim olkTsk As TaskItem
          Dim excApp As Object
          Dim excWkb As Object
          Dim excWks As Object
          Dim repMo As String
          Dim tskMo As String
          Dim lngRow As Long
          Dim lngCnt As Long
          Dim FileName As String
      
          FileName = "C:\Users\rcous\Desktop\Doc Processing Times.xlsm"
          repMo = InputBox("Reporting Month")
      
          Set excApp = CreateObject("Excel.Application")
          Set excWkb = excApp.Workbooks.Open(FileName)
          Set excWks = excWkb.Sheets.Add
              excWks.Move After:=excWkb.Sheets(excWkb.Sheets.Count)
              excWks.Name = Format(repMo, "MMM-yyyy")
              
              excApp.Visible = True
      
             'Write Excel Column Headers
              With excWks
                  .Cells(1, 1) = "Report for:"
                  .Cells(1, 3) = Format(repMo, "MMM-yyyy")
                  .Cells(2, 1) = "VFD"
                  .Cells(2, 2) = "DocID"
                  .Cells(2, 3) = "Notes"
                  .Columns.WrapText = False
      
              End With
              lngRow = 3
              
              'Write tasks to spreadsheet
              Set olkFld = Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Tasks").Folders("USPF Documentation Tasks")
              Set olkFlt = olkFld.Items.Restrict("[Categories] = Doc8 Finish")
              
              For Each olkTsk In olkFlt
                  '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.BillingInformation
                  excWks.Cells(lngRow, 2) = olkTsk.ContactNames
                  excWks.Cells(lngRow, 3) = olkTsk.Body
                  
                  excWks.Range("C1").End(xlDown).Select
                  excWkb.Application.Run "Module1.SplitCells"
                  
                  lngRow = lngRow + 1
                  lngCnt = lngCnt + 1
                  
              Next
              Set olkTsk = Nothing
      
              excWks.Columns.AutoFit
              excWks.Rows.AutoFit
              
              excWkb.Save
              
              MsgBox "Process complete.  A total of " &amp; lngCnt &amp; " tasks were exported.", vbInformation + vbOKOnly
          Set excWks = Nothing
          Set excWkb = Nothing
          Set excApp = Nothing
      End Sub
      
      Excel Macro:
      
      Sub SplitCells()
      Dim Rng As Range
      Dim WorkRng As Range
      On Error Resume Next
      Set WorkRng = Application.Selection
      For Each Rng In WorkRng
          lLFs = VBA.Len(Rng) - VBA.Len(VBA.Replace(Rng, vbLf, ""))
          If lLFs &gt; 0 Then
              Rng.Offset(0, 1).Resize(lLFs).Insert shift:=xlShiftRight
              Rng.Resize(lLFs + 1).Value = Application.WorksheetFunction.Transpose(VBA.Split(Rng, vbLf))
          End If
      Next
      Set Rng = Nothing
      Set WorkRng = Nothing
      End Sub
      
    • Hi, Rachelle.

      Here’s my solution for this. Please try it out and let me know if that’s what you wanted.

      Sub DocumentProcessingTimes()
          Dim olkFld As Outlook.Folder
          Dim olkFlt As Outlook.Items
          Dim olkTsk As TaskItem
          Dim excApp As Object
          Dim excWkb As Object
          Dim excWks As Object
          Dim repMo As String
          Dim tskMo As String
          Dim lngRow As Long
          Dim lngCnt As Long
          Dim FileName As String
          Dim arrLin As Variant
       
          FileName = "C:\Users\rcous\Desktop\Doc Processing Times.xlsm"
          repMo = InputBox("Reporting Month")
       
          Set excApp = CreateObject("Excel.Application")
          Set excWkb = excApp.Workbooks.Open(FileName)
          Set excWks = excWkb.Sheets.Add
              excWks.Move After:=excWkb.Sheets(excWkb.Sheets.count)
              excWks.Name = Format(repMo, "MMM-yyyy")
               
              excApp.Visible = True
       
             'Write Excel Column Headers
              With excWks
                  .Cells(1, 1) = "Report for:"
                  .Cells(1, 3) = Format(repMo, "MMM-yyyy")
                  .Cells(2, 1) = "VFD"
                  .Cells(2, 2) = "DocID"
                  .Cells(2, 3) = "Notes"
                  .Columns.WrapText = False
       
              End With
              lngRow = 3
               
              'Write tasks to spreadsheet
              Set olkFld = Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Tasks").Folders("USPF Documentation Tasks")
              Set olkFlt = olkFld.Items.Restrict("[Categories] = Doc8 Finish")
               
              For Each olkTsk In olkFlt
                  '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.BillingInformation
                  excWks.Cells(lngRow, 2) = olkTsk.ContactNames
                  arrLin = Split(olkTsk.Body, vbCrLf)
                  For Each varLin In arrLin
                      excWks.Cells(lngRow, 3) = varLin
                      lngRow = lngRow + 1
                  Next
                               
                  excWks.Range("C1").End(xlDown).Select
                  excWkb.Application.Run "Module1.SplitCells"
                   
                  lngCnt = lngCnt + 1
                   
              Next
              Set olkTsk = Nothing
       
              excWks.Columns.AutoFit
              excWks.Rows.AutoFit
               
              excWkb.Save
               
              MsgBox "Process complete.  A total of " & lngCnt & " tasks were exported.", vbInformation + vbOKOnly
          Set excWks = Nothing
          Set excWkb = Nothing
          Set excApp = Nothing
      End Sub
      
  7. Hi David,

    Can you tell me what needs to be added to get the time stamp of the completion date of the task?

    Thanks in advance and have a great day!!

    -John

    Sub ExportTasksToExcel()
        Const SCRIPT_NAME = "Export Tasks to Excel"
        Dim olkTsk As Object, _
            olkAtt As Outlook.Attachment, _
            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
            With excWks
                .Cells(1, 1) = "Subject"
                .Cells(1, 2) = "Body"
                .Cells(1, 3) = "Created On"
                .Cells(1, 4) = "Start Date"
                .Cells(1, 5) = "Due Date"
                .Cells(1, 6) = "Date Complete"
                .Cells(1, 7) = "Owner"
            End With
            lngRow = 2
            For Each olkTsk In Application.ActiveExplorer.CurrentFolder.Items
                excWks.Cells(lngRow, 1) = olkTsk.Subject
                excWks.Cells(lngRow, 2) = olkTsk.Body
                excWks.Cells(lngRow, 3) = olkTsk.CreationTime
                excWks.Cells(lngRow, 4) = olkTsk.StartDate
                excWks.Cells(lngRow, 5) = olkTsk.DueDate
                excWks.Cells(lngRow, 6) = olkTsk.DateCompleted
                excWks.Cells(lngRow, 7) = olkTsk.Owner
            Next
            Set olkTsk = Nothing
            excWkb.SaveAs strFilename
            excWkb.Close
            MsgBox "Process complete.  A total of " &amp; lngCnt &amp; " tasks were exported.", vbInformation + vbOKOnly, SCRIPT_NAME
        End If
        Set excWks = Nothing
        Set excWkb = Nothing
        Set excApp = Nothing
    End Sub
    
    • Hi, John.

      The DateCompleted field which is already part of the exported data has the time stamp. Are you saying that you want just the time and not the date portion? If not, then I’m not clear on what it is you want.

  8. David,

    I am using the original code you provided and it works perfectly. The only additional thing I need is a time stamp on the DateCompleted.

    Any help you can offer would be greatly appreciated.

    Have a great holiday!

    Best,

    John

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s