Export Outlook Tasks to Excel with VBA

The Seamless Integration of Microsoft Office Applications

Regardless of personal preferences regarding Microsoft, a notable strength of MS Office lies in the effortless interoperability between its constituent programs.

Consider the efficiency gained when incoming emails trigger the automatic creation of new tasks or calendar events.

Furthermore, imagine a completed task automatically notifying your supervisor via email, complete with the updated status report derived directly from the task description.

Strategic automation, implemented intelligently, has the potential to significantly reduce daily workloads.

Previous Automation Explorations

Readers familiar with my previous articles will recall discussions on topics such as incorporating web browser functionalities into Excel, automating application window maximization, and streamlining chart updates within Excel.

These explorations demonstrate the power of leveraging Office's interconnectedness.

Automating Excel Updates with Outlook Tasks

This article will detail another automation process – one I’ve found particularly useful recently – focused on automatically populating an Excel spreadsheet with a comprehensive list of all currently active Outlook tasks at the close of each day.

This allows for a consolidated view of ongoing responsibilities.

Transferring Outlook Tasks to an Excel Spreadsheet

Numerous motivations might lead you to want to perform this data transfer. Perhaps you need to monitor your pending tasks on a daily basis in a format easily shared via email – a task not readily achievable with Outlook tasks directly. Alternatively, the data could be integrated into a more extensive report being prepared in Word.

Regardless of the specific reason, the capability to capture and export information regarding incomplete Outlook tasks is demonstrably valuable.

For illustrative purposes, consider the following sample Outlook task list, containing five tasks that remain unfinished.

All operations detailed herein will be conducted using VBA (Visual Basic for Applications). Within Outlook, access to the VBA editor is gained by selecting "Tools", then "Macro", and subsequently choosing "Visual Basic Editor".

The code required to capture your task list and export it to Excel is, in reality, less complex than one might anticipate. The initial step involves establishing connections to both Outlook and Excel objects through the definition of necessary variables. Subsequently, utilizing the workbook object created, begin by constructing the header row within 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"

As a result, the new spreadsheet will appear as shown. Your Outlook application will generate a new Excel file named "MyActiveTasks.xls" in the C:\temp directory, and establish a header for the tasks that will be inserted.

Now, the extraction of your tasks and their insertion into the Excel file can commence. A "y" variable is initialized to two to ensure that the first row is not overwritten, as it is reserved for 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

This script iterates through each task item in your Outlook list, verifies whether the item has been completed, and, if not, inserts the task details into four cells of the spreadsheet. Additional information can be included if desired; simply explore the available task properties by typing "tsk." and browsing the resulting list.

The resulting spreadsheet is displayed below.

However, a minor refinement is needed. Observe how the last task subject is truncated in column A. This is undesirable. Therefore, additional code will be added to automatically adjust all column widths 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 these final lines will save the spreadsheet and close it, preventing it from remaining locked by the application and allowing for easy access to the Excel file after Outlook is closed.

The finished spreadsheet is shown below.

When should the script be executed? I have configured it to run on the "Application.Close()" event, which triggers when Outlook is closed at the end of the day. This ensures that the Excel spreadsheet report is automatically generated each day upon Outlook's closure.

Are there other potential applications for this technique? Perhaps automatically sending an email containing the task list, or outputting the data to an HTML file and uploading it to a web server?

With a degree of ingenuity, a remarkable amount can be achieved through scripting automation. Feel free to share your own ideas and insights in the comments section below!

Shutterstock