Pinterest Stumbleupon Whatsapp

export outlook tasks to excelWhether or not you are a fan of Microsoft, one good thing that can be said about MS Office products, at least, is how easy it is to integrate each of them with one another.

Just think of the power that comes from having incoming emails automatically generating new tasks or new calendar appointments, or having a completed task automatically email your boss with the updated status report from the task description.

If you do it right, you can cut your entire day’s workload by a boatload just by automating things in an intelligent and efficient way.

If you follow my writing here, then you know that in the past I’ve covered things like integrating web browser features into Excel How To Make Your Own Basic Internet Browser Using VBA How To Make Your Own Basic Internet Browser Using VBA When you really stop to think about it, an Internet browser in its simplest form isn't really that impressive an application. I mean, yes, the Internet is amazing by anyone's standards. The concept of linking... Read More , automatically maximizing application windows 3 Ways To Open Applications & Windows Maximized With VB Script [Windows] 3 Ways To Open Applications & Windows Maximized With VB Script [Windows] If there is one thing I love to do, it's write VB scripts. Whether it's a Windows Script file or a VBA script inside an application, scripting provides the ability to add functionality to applications... Read More , or automating chart updates in Excel How To Create Self-Updating Excel Charts In Three Easy Steps How To Create Self-Updating Excel Charts In Three Easy Steps Read More .

Well, in this article I’m going to cover another automation task – actually one that I’ve used often more recently – to automatically update an Excel spreadsheet with all of your remaining active Outlook tasks at the end of the day.

Feeding Outlook Tasks To An Excel Spreadsheet

There are a lot of reasons you may want to do this. Maybe you want to track your unfinished tasks on a daily basis in a format that you can quickly mail off to someone (not so easy to do with Outlook tasks). Or maybe it’ll become part of a larger report that you’ll be typing up in Word.


Whatever the case may be, the ability to capture and output uncompleted Outlook task information is a useful thing.

For this example, here’s my sample Outlook task list with 5 remaining tasks that I still haven’t completed yet.

export outlook tasks to excel

Everything we’re going to do here, is in VBA. In Outlook, you get to the VBA editor by clicking on “Tools“, then “Macro” and then choosing the “Visual Basic Editor“.

export outlook tasks to csv

The code that you’re going to use to capture your task list and export it to Excel is actually not quite as complicated as you might think. The first step is to plug into both Outlook objects and Excel objects by creating the necessary variable definitions. Then, using the workbook object you’ve created, start off by creating the header in your spreadsheet.

Dim strReport As String
  Dim olnameSpace As Outlook.NameSpace
  Dim taskFolder As Outlook.MAPIFolder
  Dim tasks As Outlook.Items
  Dim tsk As Outlook.TaskItem
  Dim objExcel As New Excel.Application
  Dim exWb As Excel.Workbook
  Dim sht As Excel.Worksheet

  Dim strMyName As String
  Dim x As Integer
  Dim y As Integer

  Set exWb = objExcel.Workbooks.Open("c:\temp\MyActiveTasks.xls")

'  exWb.Sheets(strMyName).Delete
'  exWb.Sheets.Add (strMyName)

  Set olnameSpace = Application.GetNamespace("MAPI")
  Set taskFolder = olnameSpace.GetDefaultFolder(olFolderTasks)

  Set tasks = taskFolder.Items

  strReport = ""

  'Create Header
  exWb.Sheets("Sheet1").Cells(1, 1) = "Subject"
  exWb.Sheets("Sheet1").Cells(1, 2) = "Due Date"
  exWb.Sheets("Sheet1").Cells(1, 3) = "Percent Complete"
  exWb.Sheets("Sheet1").Cells(1, 4) = "Status"

So, here’s what the new spreadsheet looks like. Your Outlook app just created a new Excel file called “MyActiveTasks.xls” in the C:\temp directory, and created a header for the tasks that you’re about to insert.

export outlook tasks to csv

So, now it’s time to extract your tasks and insert them into the Excel file. I use a “y” variable starting at two in order to make sure the first row that’s used isn’t the first, because I don’t want to overwrite the header.

y = 2

  For x = 1 To tasks.Count

       Set tsk = tasks.Item(x)

       'strReport = strReport + tsk.Subject + "; "

       'Fill in Data
       If Not tsk.Complete Then

        exWb.Sheets("Ryan").Cells(y, 1) = tsk.Subject
        exWb.Sheets("Ryan").Cells(y, 2) = tsk.DueDate
        exWb.Sheets("Ryan").Cells(y, 3) = tsk.PercentComplete
        exWb.Sheets("Ryan").Cells(y, 4) = tsk.Status
        y = y + 1

       End If

  Next x

What this script does is searches through your entire list of task items in Outlook, checks to see whether the item is completed yet, and if it isn’t, then it inserts that task information into 4 cells of the spreadsheet. If you wanted to, you could insert more information. Just explore what task information is available by typing “tsk.” and then browsing through the list of properties that pop up.

Now here’s what the sheet looks like.

export outlook tasks to csv

Being a bit of a perfectionist, there’s still a problem. Notice how column A clipped the last task subject?” I don’t like that. So let’s add a little bit more code to autofit all columns in the Excel table.

'Autofit all column widths

For Each sht In ActiveWorkbook.Worksheets
Next sht


Set exWb = Nothing

The Save and Close methods in those last few lines will save the sheet and close it so that it doesn’t remain locked by the application, otherwise it would be difficult to open the Excel file until you closed Outlook.

So, now here’s what the finished spreadsheet looks like.

export outlook tasks to excel

When do you set the script to run? Well, I set it up to run on the “Application.Close()” event, which runs when you exit Outlook at the end of the day. This will make outlook produce the Excel spreadsheet report at the end of the day, all on its own.

Can you think of any other cool uses for this technique? Maybe automatically firing off an email with the list of tasks, or outputting them to an HTML file and FTPing it to your web server?

With a little creativity, it’s amazing what you can pull off with a bit of scripting automation. Share your own thoughts and ideas in the comments section below!


Leave a Reply

Your email address will not be published. Required fields are marked *

  1. TobyC
    January 31, 2017 at 3:57 pm

    Here is a macro I added to OUTLOOK 2016 to export a selected calendar to excel.
    it need a date filter as it exports all entries.

    Sub TimesheetExport()
    'MsgBox ("test")

    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlSheet As Object
    Dim olFolder As Object
    Dim olApt As Object
    Dim NextRow As Long

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err 0 Then
    Application.StatusBar = "Please wait while Excel source is opened ... "
    Set xlApp = CreateObject("Excel.Application")
    bXStarted = True
    End If
    On Error GoTo 0
    'Open the workbook to input the data
    Set xlWB = xlApp.Workbooks.Add
    Set xlSheet = xlWB.Sheets("Sheet1")
    Set nms = Application.GetNamespace("MAPI")
    Set olFolder = nms.PickFolder

    'Column TITLES
    xlSheet.Range("A1:E1").Value = Array("Subject", "Start", "End", "Duration", "Category")

    NextRow = 2

    For Each olApt In olFolder.Items
    xlSheet.Cells(NextRow, "A").Value = olApt.Subject
    xlSheet.Cells(NextRow, "B").Value = olApt.Start
    xlSheet.Cells(NextRow, "C").Value = olApt.End
    xlSheet.Cells(NextRow, "D").Value = olApt.Duration
    xlSheet.Cells(NextRow, "E").Value = olApt.Categories
    NextRow = NextRow + 1
    Next olApt

    Set olApt = Nothing
    Set olFolder = Nothing
    Set olNS = Nothing
    Set olApp = Nothing

    xlApp.Application.Visible = True

    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSheet = Nothing

    End Sub

  2. Clive
    August 3, 2012 at 8:19 am

    Ryan, is it possible to this the other way round? , i.e. I have a spreadsheet with a bunch of tasks for a number of different people and I'd like to be able to assign these tasks to their individual outlook to-do lists. Any idea how to achieve that?

  3. Asif
    July 21, 2012 at 3:22 am

    Can we use this in OWA or any other way to track the uncompleted items

  4. Michael
    June 15, 2012 at 9:18 pm

    New to VBA design....can't get this to you have a cookbook version?

  5. Jason
    April 7, 2012 at 3:34 am

    Is this limited to uncompleted tasks?
    Can I export all tasks regardless of whether they are completed or not?

    • Ryan Dube
      April 7, 2012 at 3:57 am

      No - you can see all of the available task parameters you can use by typing "tsk." and checking the dropdown list.

      I'm referring to the section of code that goes: "If Not tsk.Complete Then"

      The "tsk" object as defined in the code will show you all of the available task parameters you can check against.

      Hope that helps.

  6. Brandon Colon
    April 6, 2012 at 7:14 pm

    I am getting an 'Out of Range' runtime error that is referring to this part of the code -
    exWb.Sheets("Ryan").Cells(y, 1) = tsk.Subject. Been troubleshooting for a while, but can't seem to come up with anything...

    • Ryan Dube
      April 15, 2012 at 11:15 pm

      Hi Brandon - you need to change "Ryan" to the name of the sheet in your workbook.

  7. Jane Parker
    February 29, 2012 at 4:42 am

    Can I use this code with my PDA or mobile phone Outlook application?

    • Tina
      March 13, 2012 at 8:38 pm

      You should just try it, Jane. Please do let us know whether or not it worked!

    • Ryan Dube
      March 14, 2012 at 12:25 am

      Hi Jane - sorry I haven't answered earlier. I'm not certain because it depends whether the mobile version can handle VBA. If there is a VBA editor, then likely the code in this article will work. I would be very interested to know if it works for you though - please do let us know!

  8. Nasirmohajel
    February 10, 2012 at 11:12 pm

    Can someone sync this excel file over Sky-drive to another computer and write another code there to automatically import tasks to outlook and keep his tasks synced? 

    • Ryan Dube
      February 19, 2012 at 2:04 am

      Good question. You'll notice that halfway through the article the Outlook code writes to MyActiveTasks.xls to the c:temp directory. You can have the code write to any path you want. As far as the syncing part - I'm not familiar with Sky-Drive, so not sure about that part of it. But if you can map it as just a virtual drive or something like that - you can have the application write to it.