Outlook 2010 Macro to Export Tasks to Excel 2010

Posted on 2013-01-31
Last Modified: 2013-01-31
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("")
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!
Question by:Senniger1
  • 3
  • 3
  • 2
LVL 76

Expert Comment

by:David Lee
ID: 38839698
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?
LVL 26

Accepted Solution

redmondb earned 500 total points
ID: 38839713
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.


Author Comment

ID: 38839720
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?
LVL 26

Expert Comment

ID: 38839735

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 76

Expert Comment

by:David Lee
ID: 38839738
Absolutely.  What fields do you want to export for each task?

Author Comment

ID: 38839780
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.

Author Closing Comment

ID: 38839902
You hit the nail on the head.  Sorry I didn't see your comment sooner.
LVL 26

Expert Comment

ID: 38840084
Thanks, Senniger1. Not a problem!

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Read this checklist to learn more about the 15 things you should never include in an email signature.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now