Solved

Outlook 2010 Macro to Export Tasks to Excel 2010

Posted on 2013-01-31
8
1,287 Views
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
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
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
Next
Set olkMsg = Nothing
excWkb.SaveAs strFilename
excWkb.Close
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)
Else
GetSMTPAddress = Item.SenderEmailAddress
End If
Case Else
Set olkSnd = Item.Sender
If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
Set olkEnt = olkSnd.GetExchangeUser
GetSMTPAddress = olkEnt.PrimarySmtpAddress
Else
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!
0
Comment
Question by:Senniger1
  • 3
  • 3
  • 2
8 Comments
 
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?
0
 
LVL 26

Accepted Solution

by:
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.

Regards,
Brian.
0
 

Author Comment

by:Senniger1
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?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38839735
Senniger1,

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

Regards,
Brian.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 76

Expert Comment

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

Author Comment

by:Senniger1
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.
0
 

Author Closing Comment

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

Expert Comment

by:redmondb
ID: 38840084
Thanks, Senniger1. Not a problem!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Outlook Free & Paid Tools
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

757 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

22 Experts available now in Live!

Get 1:1 Help Now