Scripting Solutions with WSH and COM: Automating Reports with Outlook and Excel

As an administrative team leader for a large team, my friend Bill assigns tasks to team members and produces regular reports that detail the progress of those tasks. These progress reports take a lot of time to create, so Bill asked me to help him shorten the time he had to spend on them. Here's how we solved this problem.

Understanding the Old Processes
I first asked Bill to describe the processes he was using to assign tasks and create progress reports. Bill was using email to send the tasks to the team members, which worked fine. However, when it came to creating the progress reports, he ran into problems. Initially, the team members reported their progress in an email and Bill compiled the data into one large report. However, the team members used different formats to report their progress, so Bill had the team members use a Microsoft Word document template to report their progress. That change helped a bit, but Bill still spent far too much time consolidating the data into one report.

Developing the New Processes
Working together, we determined that Bill needed to take advantage of the most underused feature in Microsoft Outlook: Tasks. With Tasks, Bill could assign new tasks and track the progress of existing tasks automatically.

Using Tasks is simple. To assign a new task to someone, you just go to the Tasks folder. On the Actions menu, click New Task Request. Figure 1 shows a sample task request. When the recipient receives the task request, the person can accept the task, decline the task (with or without a comment), or assign the task to someone else. When a person accepts a task, Outlook adds the task to his or her task list.

To track the progress of the assigned tasks, Bill developed guidelines that specified how and when the team members needed to send him updates noting their progress. To send an update about a task, the team members simply had to open that task in their task list, then select Send Status Report from the Actions menu. At a minimum, the team members were to note their progress in the % Complete field and click Send. Outlook would then automatically update Bill's task list with the status report. Figure 2 shows a sample status report.

Automating the Data Extraction and Compilation
At this point, Bill had the tools and processes in place to assign the tasks and automatically collect status reports. Now, Bill needed a simple way to extract the data from the status reports and compile the data into a report. To fill this need, I created the script CreateReport.vbs, which Listing 1, page 8, shows. CreateReport.vbs automates not only Outlook but also Microsoft Excel. If you're unfamiliar with how to automate Outlook or Excel, you might want to review several articles I've written about these topics. The sidebar "Excel and Outlook Automation Resources," page 9, lists those articles.

CreateReport.vbs has three main sections. Section 1 declares the constants and variables. Section 2 creates and formats an Excel worksheet. Section 3 fills the worksheet with the task data.

Making the Declarations
Section 1 begins by declaring constants that the script uses. The first two constants are for the columns in the Excel worksheet. By default, Excel's object model references columns by numbers. Referencing columns by names rather than numbers makes the script much easier to understand.

In addition to creating the column constants, I created the olFolderTasks constant and set it to the value of 13. This value represents the default Tasks folder. Similarly, I created the xlCenter constant and set it to the value of &HFFFFEFF4. This value tells Excel to center the data in the worksheet cells. To find both values, I used the Object Browser in Excel's macro editor. (In Excel, press Alt+F11, then press F2.)

Next, I declared the script's variables. As callout A in Listing 1 shows, one of those variables is a counter called intRowCount. In Section 3, the script uses the intRowCount variable to count the number of rows in the worksheet. I started the count off at 2 (i.e., the second row) because the first row will contain the headings for the columns.

Building the Worksheet
Section 2 creates the Excel worksheet and sets the column headings. I began this section by calling the CreateObject method of Windows Script Host's (WSH's) WScript object to create an instance of Excel's Application object. After setting the Application object's Visible property to True to make the Excel application visible for easier debugging, I used the Add method of the Application object's WorkBooks method to create a workbook.

The code at callout B sets up the column headings. The five lines that begin with objXL.Range set the first eight cells of Row 1 to a blue background with centered, boldfaced white text. The lines that begin with objXL.Cells set the text for those eight cells (i.e., set the columns' headings).

I ended Section 2 by enabling Excel's Freeze Panes feature. In Excel 2000 or later, you can use this feature to freeze row or column headings so that the headings are always visible, no matter where you scroll in the worksheet. To freeze column headings, you need to select the row below those headings. Thus, I selected row 2, then set the FreezePanes property to True.

Filling the Worksheet
Although Section 3 is the longest, it isn't hard to understand if you break the code into smaller segments. I use comments to flag those segments.

I began Section 3 by connecting to Outlook. To make this connection, I created an instance of Outlook's Application object, then retrieved the MAPI NameSpace object. To access Outlook's default Tasks folder, I used the NameSpace object's GetDefaultFolder method with the olFolderTasks constant I created in Section 1. After accessing the Tasks folder, I used the Items property to obtain the folder's collection of tasks. I set this collection to the olTaskList variable.

Next, I used a For Each...Next loop to iterate through the collection of tasks. The olTask variable represents the specific task at that particular iteration of the loop. While in the loop, I manipulated the data in the Due date, Priority, and Status fields.

Manipulating the Due date data. The If...Then...Else statement that callout C shows might seem a bit strange if you're unfamiliar with a certain Outlook practice. If you don't set a due date when you create a task or task request (i.e., you leave None selected in the Due date field), Outlook automatically sets the value of January 1, 4501, as the due date. This date is called the sentinel value. I used the If...Then...Else statement to check for this sentinel value. If the sentinel value is present, the statement assigns the string None to the strDueDate variable. Otherwise, the statement assigns the value in the Due date field to the strDueDate variable.

Manipulating the Priority data. Outlook stores information about a task's priority as integers, even though the drop-down list for the Priority field displays the options of Low, Normal, and High. To make the progress reports more readable, I used a Select Case statement to convert the numeric values to text.

Manipulating the Status data. Like the drop-down list for the Priority field, the drop-down list for the Status field displays descriptions, but Outlook stores the information as integers. Once again, I used a Select Case statement to convert those numeric values to text.

With the necessary data manipulated, I added the data to the worksheet, as callout D shows. To reference a specific cell and set that cell's value, I used the syntax

objXL.Cells.(row,col).Value

where row is the row number and col is the column number for the cell you want to fill. In this case, I used the counter (i.e., intRowCount) to specify the rows and the column constants that I created in Section 1 to specify the columns. After adding the task's data to the worksheet, I incremented the intRowCount variable. I then ended the For Each...Next statement with the Next clause, which prompts the olTask variable to represent the next task in the collection.

The code at callout E is important because it enables Excel's AutoFit feature. The cells' contents might not fit in the column's default width. By enabling the AutoFit feature, Excel automatically adjusts each column to the appropriate width.

Printing Adaptions
Bill didn't need to print the resulting worksheet, but you might need to. If you want to print the worksheet, I suggest that you adjust the column widths individually rather than using the AutoFit feature. Otherwise, all of the worksheet's columns might not fit on a printed page.

To adjust a column's width, you can set the ColumnWidth property with code such as

objXL.Columns("A:A").ColumnWidth=10

To fit even more columns on a page, you can reduce the text's font size to 8 with the code

objXL.Columns("A:H").Font.Size=8

Another option is to wrap text in a cell with code such as

objXL.Columns("A:H").WrapText=True

After you've made the necessary adjustments to get all the columns to fit on one page, you need to include code that sets up the worksheet for printing, then prints the worksheet. You can use objXL.ActiveSheet.PageSetup commands for this purpose. For example, Listing 2 contains code that sets the page's orientation to landscape, prints the gridlines, and inserts a header on the right side of each page. The final line prints the selected worksheet of the active window. The first parameter specifies the number of copies (in this case, 1). The second parameter specifies that you want to collate the worksheets.

A Happy Ending
By using Tasks and the script CreateReport.vbs, Bill no longer has to spend a lot of his time creating progress reports. A task that used to take hours now takes only minutes.