Create an Outlook Add-in that Consolidates Disparate Task Data into a Single View

After reading this article, you will know how to: Create an Outlook Add-in using Visual Studio 2005 Tools for Office (VSTO) Extract XML data from an Excel workbook Extract XML data from an InfoPath file Build XML files using the .NET Framework's System.XML namespace Merge several XML files into a single XML file Populate a DataGridView control with XML data Here are the prerequisites for building the projects in this article: Microsoft .NET Framework 2.0 Windows SharePoint Services Visual Studio 2005 Visual Studio 2005 Tools for Office Office Professional Edition 2003 Office Primary Interop Assemblies (PIAs)

In today's business environment, information arrives on the information worker's desktop at an alarming rate of ever-increasing speed. These snippets of info come in the form of e-mail, documents, Webcasts, podcasts, ad infinitum. These various data formats make tracking actionable items that reside outside of Outlook difficult. My personal experience proves the theory that nothing gets done unless it is first captured as a Task in Outlook. So in this article, I will show you how to build an Outlook add-in that consolidates task data from three Microsoft Office applications: Outlook, Excel, and InfoPath.

The idea for this project is a product of my daily life. I rely heavily on Outlook's task management capabilities to manage my priorities throughout the day, oftentimes utilizing more than one task folder to track tasks for different projects. This scenario works great as long as I have to deal with my stuff in my own way.

However, recently, I have begun to use the meeting minutes form included with InfoPath to help plan and run meetings with clients and other project team members. In fact, the use of this form has caught on and I now receive meeting minutes from other people on my team. The trouble here is that the form contains a section for recording "action items" (a.k.a. Tasks) and on most occasions I have been assigned one or more actions. So instead of recording these manually I wanted a way to incorporate them into my Outlook experience without manually creating an Outlook Task.

Figure 1. The Consolidated Tasks Data Feed

As Figure 1 illustrates, the Task Consolidator utilizes different Office data sources to create a consolidated view of task data. A single class, TaskXMLReader, contains functions for reading the contents of each data source, scrubbing the data, and turning it into a standard XML format required by the project. Listing 1 shows the required TaskList.xsd schema for each XML document created. You will need to either create this file or use the one provided with the article source code.

Once the add-in creates XML documents for each data source, it merges all documents into a single XML document and feeds it to the add-in's WinForm (TaskView) where it populates a DataGridView control for display to the user. The end result is a single, consolidated view of all defined data sources. For the purposes of this article, all data sources are hard-coded in the add-in's application settings. That said, it's easy enough to extend this solution to allow a user to define additional Office file "data sources".

This project is a Visual Studio 2005 Tools for Office add-in. To create the project, open Visual Studio 2005 and click the New Project button to open the New Project dialog box. Select Visual Basic > Office > Outlook Add-in from the listing of available project templates.

Once the project loads into Visual Studio, two steps must be completed before building the project. The first is to add a reference to Excel. Select Project > Add Reference from the Visual Studio menu to open the Add Reference dialog box. Select the COM tab. From the listing of available COM components select Microsoft Excel 11 Object Library. Click the Okay button.

The project will need a couple of files to use as data sources. I recommend downloading the files provided with the source code for this article, however, they are easy enough to recreate by following these steps:

Figure 2. Excel Workbook with Random Task Data

1. Project Tasks.xls: Create a new Excel workbook. Select Data > XML > XML Source from the Excel Menu. The XML Source Task Plane will display. Click the XML Maps button. In the XML Maps dialog box click Add and select TaskList.xsd from the location where it is stored on your file system. Click Open. Click OK. Once the XML Source Task Pane displays the schema, select the TaskList node and drag it onto the Excel workbook. Add as many records as you like and save the file as Project Tasks.xls. Figure 2 shows what the workbook should look like.
2. Meeting Minutes.xml: Open InfoPath and select Minute Agenda from the listing of sample forms. Complete the form as much as you like, but be sure to add a few Action Items. Save the form as Meeting Minutes.xml. Figure 3 shows what the Action Items section of the form could look like.

   Figure 3. Action Items from the Latest Meeting

The last step before adding code to the project is to configure the data source settings. As I mentioned previously, these are hard coded using the file system locations of the files just created. Open the project's properties form by clicking Project > [project name] Dependencies from the Visual Studio menu. Add two application level properties as follows:

Name	Type	Scope	Value
xlTaskFile	String	Application	C:\[file system location]\Project Tasks.xls
ipTaskFile	String	Application	C:\[file system location]\Meeting Minutes.xml

The thrux of the work in this project is to extract the task data from the Office data sources and insert them into XML files that correspond to the TaskList.xsd schema. The TaskXMLReader contains three functions that read task data from each type of Office data the project works with. Each function follows the same process but is tailored to unique circumstance of the data source. Each function extracts the task data from the data source and creates an XML file conforming to the TaskList.xsd schema. Once the .xml file is built, each function saves the XML to a location on the file system. The files are meant to be temporary, as they will be merged later into a single file. Listing 2 contains the code for the TaskXMLReader class.

Figure 4. The TaskView Form at Design Time

The TaskView form displays the consolidated task view to add-in's user. TaskView is simple form that contains five Button controls and a single DataGridView control. Add a new Windows Form to the project and add the Button and DataGridView controls to the form. The buttons should be named btnExcel, btnIP, btnOL, btnAll, and btnImport. Name the grid dgvTasks. Figure 4 shows what the TaskView form could resemble.

Each button of the TaskView form populates the form's grid with Task data from the button's corresponding data source: Outlook (Task folder), Excel (Project Tasks.xls), or InfoPath (Meeting Minutes.xml). In addition, the All Tasks button loads the task data from all data sources into the grid. Listing 3 shows the code for each button's Click event as well as the MergeAndLoad method. MergeAndLoad creates a consolidated task XML file for display in the grid when the All Tasks button is clicked.

Figure 5. The TaskView Form populated with task records

When the form executes the data grid fills with records from each of the data source files to present a consolidated view of all tasks (Figure 5). The TaskView form provides a nice and simple of the different sources of Task data but this isn't enough. This is why I have included a an import routine that is triggered by the import button (btnImport). The import button calls the ImportNonOLTasks. This method opens the XML data files created earlier and uses the data to create Outlook tasks. After importing the data the tasks folder in Outlook will contain the new task items (Figure 6).

Figure 6. The default Outlook Tasks folder displaying the newly imported XML task records.

The Startup method of the add-in is the ideal location for adding a button to Outlook's standard command bar. This button resides on the standard Outlook command bar and provides the method for opening the form and viewing the consolidated task data. Listing 4 shows the code for ThisApplication.

The data residing inside Office documents contains valuable bits of information that information workers need to see and use during the workday. Using VSTO and the extensive XML features supported in Office, it is now possible to build solutions that consolidate this information and allows for higher visibility to the user.

Ty Anderson runs Cogent Company, a consultancy in Dallas specializing in leveraging technology to enable business strategy. Ty is a regular contributor to the Microsoft Developer Network (MSDN) and has recently written a book, Office Programming 2003: Real World Applications, focused entirely on building applications with the Microsoft Office System 2003.

Submit article to: