Here is part of the add-in code that exports Outlook Tasks into Excel. It works the same as the previous code that exports Calendar items, except we set a reference to the default Tasks folder (not Calendar), and I've also incorporated some new code which makes the sub more efficient by writing the data to an array, which is then dumped to the worksheet in one pass.

Note that the Object Model Guard (OMG) is in play here, depending on the properties you reference. For example, the Body Property of a TaskItem in the code below will trigger the OMG. There are probably other properties that will also trigger the OMG, like trying to access the names or email addresses of anyone associated with the task (Owner, Status update recipients, etc).

The code below (and the code to export Calendar items) can be ported to Outlook almost as-is, which should avoid the OMG. In fact, I went ahead and did so with the Tasks code, and the Body Property did not trigger the OMG.

Excel version:

Sub GetTasksData(StartDate As Date, Optional EndDate As Date)

' -------------------------------------------------
' Notes:
' If Outlook is not open, it still works, but much slower (~8 secs vs. 2 secs w/ Outlook open).
' End Date is optional, if you want to pull from only one day, use: Call GetTasksData("7/14/2008")
' -------------------------------------------------

Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim myTaskItems As Outlook.Items
Dim ItemstoCheck As Outlook.Items
Dim ThisTask As Outlook.TaskItem

Dim rng As Excel.Range
Dim rngStart As Excel.Range
Dim rngHeader As Excel.Range
Dim MyBook As Excel.Workbook

Dim i As Long
Dim NextRow As Long
Dim ColCount As Long

Dim MyItem As Object
Dim StringToCheck As String
Dim arrData() As Variant

Application.ScreenUpdating = False

' if no end date is specified, EndDate variable will be "12:00:00 AM"
' the requestor only wants one day, so set EndDate = StartDate
If EndDate = "12:00:00 AM" Then
    EndDate = StartDate
End If

If EndDate < StartDate Then
    MsgBox "Those dates seem switched, please check them and try again.", vbInformation
    GoTo ExitProc
End If

If EndDate - StartDate > 28 Then
    ' ask if the requestor wants so much info
    If MsgBox("This could take some time. Continue anyway?", vbInformation + vbYesNo) = vbNo Then
        GoTo ExitProc
    End If
End If

' get or create Outlook object and make sure it exists before continuing
On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
        Set olApp = CreateObject("Outlook.Application")
    End If
On Error GoTo 0
If olApp Is Nothing Then
    MsgBox "Cannot start Outlook.", vbExclamation
    GoTo ExitProc
End If

' hook into default Tasks folder
Set olNS = olApp.GetNamespace("MAPI")
Set myTaskItems = olNS.GetDefaultFolder(olFolderTasks).Items

' ------------------------------------------------------------------
' the following code adapted from:
' http://www.outlookcode.com/article.aspx?id=30
' http://weblogs.asp.net/whaggard/archive/2007/03/21/retrieving-your-
' outlook-appointments-for-a-given-date-range.aspx
'
With myTaskItems
    .Sort "[StartDate]", False
    .IncludeRecurrences = True
End With
'
StringToCheck = "[StartDate] >= " & Quote(StartDate) & " AND [DueDate] <= " & Quote(EndDate)
Debug.Print StringToCheck
'
Set ItemstoCheck = myTaskItems.Restrict(StringToCheck)
Debug.Print ItemstoCheck.Count
' ------------------------------------------------------------------

If ItemstoCheck.Count > 0 Then
    ' we found at least one task
    ' check to make sure we have actual tasks, not infinite recurrence issues
    If ItemstoCheck.item(1) Is Nothing Then GoTo ExitProc

    Set MyBook = Excel.Workbooks.Add
    MyBook.Sheets(1).Name = Format(StartDate, "MMDDYYYY") & " - " & Format(EndDate, "MMDDYYYY")
    Set rngStart = MyBook.Sheets(1).Range("A1")

    Set rngHeader = Range(rngStart, rngStart.Offset(0, 3))

    ' with assistance from Jon Peltier http://peltiertech.com/WordPress and
    ' http://support.microsoft.com/kb/306022

    rngHeader.Value = Array("Subject", "Body", "Start Date", "Due Date")

    ColCount = rngHeader.Columns.Count

    ' now that we know how many rows and columns we need,
    ' resize the array accordingly
    ReDim arrData(1 To ItemstoCheck.Count, 1 To ColCount)

    For i = 1 To ItemstoCheck.Count

          Set ThisTask = ItemstoCheck.item(i)

            arrData(i, 1) = ThisTask.Subject
            arrData(i, 2) = ThisTask.Body
            arrData(i, 3) = Format(ThisTask.StartDate, "MM/DD/YYYY HH:MM AM/PM")
            arrData(i, 4) = Format(ThisTask.DueDate, "MM/DD/YYYY HH:MM AM/PM")

    Next i

    rngStart.Offset(1, 0).Resize(ItemstoCheck.Count, ColCount).Value = arrData

Else
    MsgBox "There are no tasks during the time you specified. Exiting now.", vbCritical
End If

ExitProc:
Set myTaskItems = Nothing
Set olNS = Nothing
Set olApp = Nothing
StringToCheck = vbNullString
Set ItemstoCheck = Nothing
Set MyBook = Nothing
Set rngStart = Nothing
Set rngHeader = Nothing
Set ThisTask = Nothing
Erase arrData
Application.ScreenUpdating = True
End Sub

Function Quote(MyText)
' from Sue Mosher's excellent book "Microsoft Outlook Programming"
    Quote = Chr(34) & MyText & Chr(34)
End Function

Don't forget to include the Quote UDF when copying this code.

I used an array declared as Variant to store the data returned from Outlook. It's a simple matter to determine the size of the array; since each row represents a separate task, the number of rows is going to be the number of tasks returned by the Restrict Method (ItemstoCheck.Count). The number of columns is even easier; we know how many fields we want to export, so it's just a count of those fields. In the above example, we are exporting four fields, so a count of the number of columns will return the needed value.

The ReDim statement is used here to re-size the array to make it exactly as large as needed.

Everything is structured, variable-wise, so that we can easily add or remove columns without needing to re-work large parts of the code. The array size is automatically calculated based on the number of items found and the number of fields we want to export, so if you wanted to export more (or less), simply edit the string values in the Array() Function and add corresponding lines inside the For Loop for arrData to manage.

To call the code above, simply pass two dates to it, as follows:

Sub GetTasks()
  Call GetTasksData("8/11/2008", "9/12/2008")
End Sub

Here is the Outlook version, which is merely a slightly reworked version of the above code. This code also requires the Quote UDF, shown above. The OMG is so heinous that I recommend coding inside Outlook whenever possible, if you need to access anything protected from it (like email addresses). You can always instantiate Excel, Word, or Access from it and do what you need.

Outlook version:

Sub GetTasksData(StartDate As Date, Optional EndDate As Date)
' -------------------------------------------------
' Notes:
' End Date is optional, if you want to pull from only one day, use: Call GetTasksData("7/14/2008")
' -------------------------------------------------

Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim myTaskItems As Outlook.Items
Dim ItemstoCheck As Outlook.Items
Dim ThisTask As Outlook.TaskItem

Dim xlApp As Excel.Application
Dim rng As Excel.Range
Dim rngStart As Excel.Range
Dim rngHeader As Excel.Range
Dim MyBook As Excel.Workbook

Dim i As Long
Dim NextRow As Long
Dim ColCount As Long

Dim MyItem As Object
Dim StringToCheck As String
Dim arrData() As Variant

' if no end date is specified, EndDate variable will be "12:00:00 AM"
' the requestor only wants one day, so set EndDate = StartDate
If EndDate = "12:00:00 AM" Then
    EndDate = StartDate
End If

If EndDate < StartDate Then
    MsgBox "Those dates seem switched, please check them and try again.", vbInformation
    GoTo ExitProc
End If

If EndDate - StartDate > 28 Then
    ' ask if the requestor wants so much info
    If MsgBox("This could take some time. Continue anyway?", vbInformation + vbYesNo) = vbNo Then
        GoTo ExitProc
    End If
End If

Set olApp = Outlook.Application

' hook into default Tasks folder
Set olNS = olApp.GetNamespace("MAPI")
Set myTaskItems = olNS.GetDefaultFolder(olFolderTasks).Items

' ------------------------------------------------------------------
' the following code adapted from:
' http://www.outlookcode.com/article.aspx?id=30
' http://weblogs.asp.net/whaggard/archive/2007/03/21/retrieving-your-
' outlook-appointments-for-a-given-date-range.aspx
'
With myTaskItems
    .Sort "[StartDate]", False
    .IncludeRecurrences = True
End With
'
StringToCheck = "[StartDate] >= " & Quote(StartDate) & " AND [DueDate] <= " & Quote(EndDate)
Debug.Print StringToCheck
'
Set ItemstoCheck = myTaskItems.Restrict(StringToCheck)
Debug.Print ItemstoCheck.Count
' ------------------------------------------------------------------

If ItemstoCheck.Count > 0 Then
    ' we found at least one task
    ' check to make sure we have actual tasks, not infinite recurrence issues
    If ItemstoCheck.Item(1) Is Nothing Then GoTo ExitProc

    Set xlApp = Excel.Application

    xlApp.ScreenUpdating = False

    Set MyBook = xlApp.Workbooks.Add

    xlApp.Visible = True

    MyBook.Sheets(1).Name = Format(StartDate, "MMDDYYYY") & " - " & Format(EndDate, "MMDDYYYY")
    Set rngStart = MyBook.Sheets(1).Range("A1")

    Set rngHeader = Range(rngStart, rngStart.Offset(0, 3))

    ' with assistance from Jon Peltier http://peltiertech.com/WordPress and
    ' http://support.microsoft.com/kb/306022

    rngHeader.Value = Array("Subject", "Body", "Start Date", "Due Date")

    ColCount = rngHeader.Columns.Count

    ' now that we know how many rows and columns we need,
    ' resize the array accordingly
    ReDim arrData(1 To ItemstoCheck.Count, 1 To ColCount)

    For i = 1 To ItemstoCheck.Count

          Set ThisTask = ItemstoCheck.Item(i)

            arrData(i, 1) = ThisTask.Subject
            arrData(i, 2) = ThisTask.Body
            arrData(i, 3) = Format(ThisTask.StartDate, "MM/DD/YYYY HH:MM AM/PM")
            arrData(i, 4) = Format(ThisTask.DueDate, "MM/DD/YYYY HH:MM AM/PM")

    Next i

    rngStart.Offset(1, 0).Resize(ItemstoCheck.Count, ColCount).Value = arrData

    xlApp.ScreenUpdating = True

Else
    MsgBox "There are no tasks during the time you specified. Exiting now.", vbCritical
End If

ExitProc:
Set myTaskItems = Nothing
Set olNS = Nothing
Set olApp = Nothing
Set xlApp = Nothing
StringToCheck = vbNullString
Set ItemstoCheck = Nothing
Set MyBook = Nothing
Set rngStart = Nothing
Set rngHeader = Nothing
Set ThisTask = Nothing
Erase arrData

End Sub

Note that the code above is early bound and requires a reference to the Excel library. The key changes are: We reference the Outlook Application Object directly, instead of using GetObject or CreateObject. We have to qualify Excel references with the Excel.Application object, xlApp, instead of "Application." Otherwise it's nearly identical, and avoids the OMG considerations.

Enjoy,
JP

Related Articles:

• September 6, 2008 -- Export Outlook Contacts to Excel
• September 2, 2011 -- Percentage Possible vs. Percentage of Total
• August 31, 2011 -- How'd They Do That: Points to Reach Next Level (Actual)