The following code exports Tasks to Excel from Outlook.
The code is late bound, so first we need to place the following enumeration into a standard Excel module when using this code.
Public Enum OlDefaultFolders olFolderCalendar = 9 olFolderConflicts = 19 olFolderContacts = 10 olFolderDeletedItems = 3 olFolderDrafts = 16 olFolderInbox = 6 olFolderJournal = 11 olFolderJunk = 23 olFolderLocalFailures = 21 olFolderNotes = 12 olFolderOutbox = 4 olFolderSentMail = 5 olFolderServerFailures = 22 olFolderSyncIssues = 20 olFolderTasks = 13 olPublicFoldersAllPublicFolders = 18 End Enum
This code should be placed at the top of a standard module. See Where do I paste the code that I want to use in my workbook if you need to know where to put this code.
Export Tasks
This code will return Tasks in the local default Tasks folder and put them into an array. You can then put them into a listbox, or dump them onto a worksheet, or whatever else you can think of.
An optional header row is also provided, so you can see what each column of values represents. It also makes it easier for pasting onto a worksheet when you have the header row as part of the array. I chose to grab every possible property of the TaskItem Object where possible.
Function ExportTasks(Optional headerRow As Boolean = False) As String()
Dim olApp As Object ' Outlook.Application
Dim olNS As Object ' Outlook.Namespace
Dim tasksFolderItems As Object ' Outlook.items
Dim folderItem As Object
Dim task As Object ' Outlook.TaskItem
Dim tempString() As String
Dim i As Long
Dim numRows As Long
Dim startRow As Long
Set olApp = GetOutlookApp
Set olNS = GetNS(olApp)
Set tasksFolderItems = GetItems(olNS, olFolderTasks)
' if calling procedure wants header row
If headerRow Then
startRow = 1
Else
startRow = 0
End If
numRows = tasksFolderItems.count
' resize array
ReDim tempString(1 To (numRows + startRow), 1 To 33)
' loop through folder items
For i = 1 To numRows
Set folderItem = tasksFolderItems.item(i)
If IsTask(folderItem) Then
Set task = folderItem
End If
With task
tempString(i + startRow, 1) = .ActualWork
tempString(i + startRow, 2) = .BillingInformation
tempString(i + startRow, 3) = .Body
tempString(i + startRow, 4) = .CardData
tempString(i + startRow, 5) = .Categories
tempString(i + startRow, 6) = .Companies
tempString(i + startRow, 7) = .Complete
tempString(i + startRow, 8 ) = .ContactNames
tempString(i + startRow, 9) = .CreationTime
tempString(i + startRow, 10) = .DateCompleted
tempString(i + startRow, 11) = .DelegationState
tempString(i + startRow, 12) = .Delegator
tempString(i + startRow, 13) = .DueDate
tempString(i + startRow, 14) = .Importance
tempString(i + startRow, 15) = .IsRecurring
tempString(i + startRow, 16) = .LastModificationTime
tempString(i + startRow, 17) = .Mileage
tempString(i + startRow, 18) = .Owner
tempString(i + startRow, 19) = .Ownership
tempString(i + startRow, 20) = .PercentComplete
tempString(i + startRow, 21) = .ReminderSet
tempString(i + startRow, 22) = .ReminderTime
tempString(i + startRow, 23) = .ResponseState
tempString(i + startRow, 24) = .Role
tempString(i + startRow, 25) = .Sensitivity
tempString(i + startRow, 26) = .Size
tempString(i + startRow, 27) = .StartDate
tempString(i + startRow, 28) = .Status
tempString(i + startRow, 29) = .StatusOnCompletionRecipients
tempString(i + startRow, 30) = .StatusUpdateRecipients
tempString(i + startRow, 31) = .Subject
tempString(i + startRow, 32) = .TeamTask
tempString(i + startRow, 33) = .TotalWork
End With
Next i
' first row of array should be header values
If headerRow Then
tempString(1, 1) = "ActualWork"
tempString(1, 2) = "BillingInformation"
tempString(1, 3) = "Body"
tempString(1, 4) = "CardData"
tempString(1, 5) = "Categories"
tempString(1, 6) = "Companies"
tempString(1, 7) = "Complete"
tempString(1, 8 ) = "ContactNames"
tempString(1, 9) = "CreationTime"
tempString(1, 10) = "DateCompleted"
tempString(1, 11) = "DelegationState"
tempString(1, 12) = "Delegator"
tempString(1, 13) = "DueDate"
tempString(1, 14) = "Importance"
tempString(1, 15) = "IsRecurring"
tempString(1, 16) = "LastModificationTime"
tempString(1, 17) = "Mileage"
tempString(1, 18) = "Owner"
tempString(1, 19) = "Ownership"
tempString(1, 20) = "PercentComplete"
tempString(1, 21) = "ReminderSet"
tempString(1, 22) = "ReminderTime"
tempString(1, 23) = "ResponseState"
tempString(1, 24) = "Role"
tempString(1, 25) = "Sensitivity"
tempString(1, 26) = "Size"
tempString(1, 27) = "StartDate"
tempString(1, 28) = "Status"
tempString(1, 29) = "StatusOnCompletionRecipients"
tempString(1, 30) = "StatusUpdateRecipients"
tempString(1, 31) = "Subject"
tempString(1, 32) = "TeamTask"
tempString(1, 33) = "TotalWork"
End If
ExportTasks = tempString
End Function
Helper Functions
Visit Utility Functions for IsTask, GetNS, GetOutlookApp, GetItems.
Sample Usage
This sample procedure gets the Tasks from the local default Tasks folder and pastes them into Excel, along with header row:
Sub GetTaskInfo() Dim results() As String ' get contacts results = ExportTasks(True) ' paste onto worksheet Range(cells(1, 1), cells(UBound(results), UBound(results, 2))).value = results End Sub
The sample workbook includes the code and a userform so you can see how the results would be put into a listbox.
