• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1425
  • Last Modified:

Outlook 2010 Macro to Export Tasks to Excel 2010

In Outlook 2010 I found some code on the internet (which I altered) to export Tasks from Outlook 2010 to Excel 2010.  It works great.

'Export Task Code
Sub GetTasksData(StartDate As Date, Optional EndDate As Date)

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 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
    If MsgBox("This could take some time. Continue anyway?", vbInformation + vbYesNo) = vbNo Then
        GoTo ExitProc
    End If
End If
Set olApp = Outlook.Application

Set olNS = olApp.GetNamespace("MAPI")
Set myTaskItems = olNS.GetDefaultFolder(olFolderTasks).Items

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
    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, 2))
    rngHeader.Value = Array("Subject", "Due Date", "Notes")
    ColCount = rngHeader.Columns.Count
    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) = Format(ThisTask.DueDate, "MM/DD/YYYY")
    Next i
    rngStart.Offset(1, 0).Resize(ItemstoCheck.Count, ColCount).Value = arrData
    xlApp.ScreenUpdating = True
    MsgBox "There are no tasks during the time you specified. Exiting now.", vbCritical
End If
    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
Function Quote(MyText)
    Quote = Chr(34) & MyText & Chr(34)
End Function
Sub GetTasks()
'  Call GetTasksData("1/1/2012", "12/31/2013")
Dim BegDate As Date
Dim EndDate As Date
    BegDate = InputBox("Enter Starting Date")
        EndDate = InputBox("Enter Ending Date")
            Call GetTasksData(BegDate, EndDate)
End Sub

I also found this code below on the internet which exports Outlook 2010 Messages to Excel 2010.  It also works great.

'Export Messages Code
Sub ExportMessagesToExcel()
Dim olkMsg As Object, _
excApp As Object, _
excWkb As Object, _
excWks As Object, _
intRow As Integer, _
intVersion As Integer, _
strFilename As String
strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", "Export Messages to Excel")
If strFilename <> "" Then
intVersion = GetOutlookVersion()
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) = "Received"
.Cells(1, 3) = "Sender"
End With
intRow = 2
'Write messages to spreadsheet
For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
'Only export messages, not receipts or appointment requests, etc.
If olkMsg.Class = olMail Then
'Add a row for each field in the message you want to export
excWks.Cells(intRow, 1) = olkMsg.Subject
excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
intRow = intRow + 1
End If
Set olkMsg = Nothing
excWkb.SaveAs strFilename
End If
Set excWks = Nothing
Set excWkb = Nothing
Set excApp = Nothing
MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
On Error Resume Next
Select Case intOutlookVersion
Case Is < 14
If Item.SenderEmailType = "EX" Then
GetSMTPAddress = SMTP2007(Item)
GetSMTPAddress = Item.SenderEmailAddress
End If
Case Else
Set olkSnd = Item.Sender
If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
Set olkEnt = olkSnd.GetExchangeUser
GetSMTPAddress = olkEnt.PrimarySmtpAddress
GetSMTPAddress = Item.SenderEmailAddress
End If
End Select
On Error GoTo 0
Set olkPrp = Nothing
Set olkSnd = Nothing
Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
Dim arrVer As Variant
arrVer = Split(Outlook.Version, ".")
GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
Dim olkPA As Outlook.PropertyAccessor
On Error Resume Next
Set olkPA = olkMsg.PropertyAccessor
SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
On Error GoTo 0
Set olkPA = Nothing
End Function

Here's my problem.  I need to export Outlook Tasks to Excel.  When I run my code for "Export Task Code", put in the date range, it opens Excel and I see my data.  From here I need to run an Excel 2010 macro to format the document, but I cannot see my macros (and no they're not hidden).  My macros are stored in my PERSONAL.XLSB file and I can't even see this file from within this spreadsheet which was opened with the Outlook code.

If I just open Excel 2010 I can see all my macros including the one I wrote for my Tasks.

If I run my code for "Export Messages Code", which dumps a file which I have to open, when I open the file, I can see my Excel macros.  I'm limited in what I know about code and am wondering if someone can help me figure out how to export my tasks and then see my Excel macros.

Thanks in advance!
  • 3
  • 3
  • 2
1 Solution
David LeeCommented:
Hi, senniger1.

That code looks very familiar.  :-)

I'm not clear on your question.  Are you asking how to run an Excel macro once the Outlook macro has finished?
Hi, Senniger1.

The issue is that your macro starts a second instance of Excel, which doesn't load your Personal.xlsb (which is probably already open, anyway).

Your easiest solution is simply to run the macro in Excel, not Outlook! Just make sure that you have a reference to the appropriate Outlook Object Library - let me know if you need more on this.

Senniger1Author Commented:
This is really hard to explain...  Perhaps it might be better if I just ask someone to covert the "Export Messages Code" to Export Tasks instead of Messages.  Perhaps this will help me figure out why I cannot see my Excel macros after I run the "Export Task Code".

Would this be doable?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


I'm guessing that you missed my earlier post where this is explained.

David LeeCommented:
Absolutely.  What fields do you want to export for each task?
Senniger1Author Commented:
Exactly what is in the code above for "Export Task Code" (Subject and Due Date).  If possible I want it to prompt for the start and end due date (just like my code), and save an Excel file.
Senniger1Author Commented:
You hit the nail on the head.  Sorry I didn't see your comment sooner.
Thanks, Senniger1. Not a problem!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now