Skip to main content

How To Export Your Outlook Tasks To Excel With VBA



Whether 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, automatically maximizing application windows, or automating chart updates in Excel.
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.
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“.
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.
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.
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
    sht.Columns("A").EntireColumn.AutoFit
    sht.Columns("B").EntireColumn.AutoFit
    sht.Columns("C").EntireColumn.AutoFit
    sht.Columns("D").EntireColumn.AutoFit
Next sht
 
exWb.Save
exWb.Close
 
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.
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!

Popular posts from this blog

How To Hide Text In Microsoft Word 2007, Reveal It & Protect It

Sometimes what we hide is more important than what we reveal. Especially, documents with sensitive information, some things are supposed to be ‘for some eyes only’. Such scenarios are quite common, even for the more un-secretive among us. You want to show someone a letter composed in MS Word, but want to keep some of the content private; or it’s an official letter with some part of it having critical data. As important as these two are, the most common use could involve a normal printing job. Many a time we have to print different versions of a document, one copy for one set of eyes and others for other sets. Rather than creating multiple copies and therefore multiple printing jobs, what if we could just do it from the same document?  That too, without the hassle of repeated cut and paste. We can, with a simple feature in MS Word – it’s just called Hidden and let me show you how to use it to hide text in Microsoft Word 2007. It’s a simple single click process. Open the docum...
1 comment
Read more

Boom, the startup that wants to build supersonic planes, just signed a massive deal with Virgin

Have you heard about Boom? Boom is a relatively new startup that’s aiming to build something pretty crazy. They’re not building an app… or a social network… or even some new gadget for the Kickstarter crowd. Boom wants to build planes. Really, really, really fast planes. Specifically, they’re trying to design and build a supersonic passenger plane that goes 2.2x the speed of sound. If all goes to plan, they’ll be able to shuttle people from New York to London in 3.5 hours, and SF to Tokyo in 4.5. Sound crazy? I wouldn’t disagree. It’s worth noting that the company is in the very early days for something as intensive, massive, and hugely expensive as designing and producing a passenger aircraft. They’re still working on their first prototype, and hope to fly it by late next year. But it’s also worth noting that the team behind the plane has some serious talent in its blood: the company’s 11 employees have collectively contributed to over 30 aircrafts — having worked on thin...
Post a Comment
Read more

Fun Tools to Translate Your Name into Japanese Calligraphy

Japanese calligraphy is an artistic writing style of the Japanese language. Its Chinese origins can be traced back to the twenty-eighth century BCE. Calligraphy found its way into Japanese culture in 600 CE and is known as the karayo tradition. For Westerners, calligraphy is forever fascinating. However, it takes years to learn how to properly draw the signs. Two basic principles must be known to understand Japanese writing: there are different writing styles and different alphabets. Kaisho for example, is a writing style most commonly used in print media. Tensho on the other hand is used in signatures. Other writing styles are Reisho, Gyosho and Sousho. The alphabets include Kanji, Hiragana, and Katakana. Katakana is used for writing foreign words. It can also serve to highlight words, in analogy to capital letters as we know them from the Roman / Latin alphabet (Romaji in Japanese). Each Kanji character has a meaning of its own, while Hiragana or Katakana characters merely repres...
Post a Comment
Read more