The Project File

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Technique

Outlook | Excel

Use Outlook Tasks and Excel to Keep Clients Up-to-Date

By Ty Anderson

Perhaps the hardest part of any development project is communications. The development team, end-users, managers, and any other stakeholders need to be kept apprised of the status of the various tasks and milestones. You could deliver such a report in numerous ways. For example, you could maintain a Microsoft Project file that contains all the project tasks, timelines, assignments, and so forth. Or, if your project is on the smaller side, you could use Excel for the same purpose.

Another solution is to use Outlook's task items. As an Office developer, you probably have grown accustomed to using the Tasks folder within Outlook to track project deliverables. Typically, developers create a task for each project requirement. Updating team members is simple - as long as you only want to send a single status report for each task item. This built-in feature provides a quick summary that includes the task's subject, its status, the percentage of the task that's complete, and the estimated hours and actual hours worked to date.

However, because projects typically have numerous tasks, keeping everyone updated can be a laborious process. To simplify it, you can distribute a single file that quickly summarizes the status of all project deliverables. A simple solution is to take advantage of the VBA features within Outlook. By creating a few functions in VBA, you can export all task items into an Excel spreadsheet quickly. That spreadsheet will detail all tasks for the project along with their statuses and other relevant information.

Outlook provides a good export function under the File | Import and Export utility, but it simply performs a data dump of all tasks from the specified Tasks folder. Outlook does not offer any advanced functions, such as formatting or category filtering. Before going further, I should note that portions of this code are from Total Visual SourceBook, an FMS, Inc. product, and are used with permission.

Accessing Excel

Using the Visual Basic Editor in Outlook, create a new class by clicking on Insert | Class Module from the menu and renaming the class module clsXL. This will instantiate the Excel functions. The export code will be contained within a module, so create a new module by selecting Insert | Module and renaming the module basTaskExport.

The final step before you start writing code is to create an external reference to Excel. Open the References dialog box by selecting Tools | References from the editor's menu. Check the box for Microsoft Excel 10.0 Object Library.

To handle the mechanics of Excel automation, the clsXL class handles the functions of starting and stopping Excel, creating a new workbook, inserting values to cells, formatting the workbook, and closing Excel. The StartExcel subprocedure initializes Excel and assigns it to the m_objExcel variable. The procedure accepts a parameter named fShowWindow. This is a Boolean value that specifies whether to show the Excel application window during execution:

  Public Sub StartExcel(fShowWindow As Boolean)
   Set m_objExcel = New Excel.Application
  m_objExcel.Visible = fShowWindow
End Sub

Once Excel has started, the CreateNewWorkbook subprocedure creates a new workbook using the passed file name in strFileName:

  Public Sub CreateNewWorkbook(strFileName AsString)
m_objExcel.DisplayAlerts = False
   Set m_objWorkbook = m_objExcel.Workbooks.Add
  m_objWorkbook.SaveAs strFileName
  m_objExcel.DisplayAlerts = True
End Sub

The InsertValue procedure simply inserts the passed value into the passed range of cells. This will make more sense when you understand the ExportTasks procedure:

  Public Sub InsertValue(strRange As String, varValue _
  As Variant)
  m_objWorkbook.ActiveSheet.Range(strRange).Value = _
   varValue
End Sub

Once the data has been exported, the FormatAsColumnHeaders and ResizeToFit procedures format the column titles and resize all worksheet columns to ensure all the data in each of the cells is viewable. Here's the FormatAsColumnHeaders code:

  Sub CreateNewWorkbook(strFileName As String)
   m_objExcel.DisplayAlerts = False
   Set m_objWorkbook = m_objExcel.Workbooks.Add
   m_objWorkbook.SaveAs strFileName
   m_objExcel.DisplayAlerts = True
End Sub

  Public Function FormatAsColumnHeaders()
   With m_objExcel.Range("A4:F4") 
    .Font.Bold = True
    .Interior.ColorIndex = 1
    .Font.ColorIndex = 2
    .HorizontalAlignment = xlCenter
   End With
End Function
  

Setting Interior.ColorIndex to 1 makes the background color black and setting FontColorIndex to 2 makes the font white. The final setting aligns the heading in the center. The CloseWorkbook and CloseExcel routines handle the clean-up required for Excel.

A Tisket a Taskit

Before I explain the export routines, you should have a brief explanation of how task items are used. As client requirements are gathered, the details of each requirement are kept within an Outlook task. This is a natural fit because a task item has fields for status, priority, start date, due date, and notes.

In addition, Outlook 2000 and 2002 enable you to link a task item to a contact. The Contacts field is used to specify who is responsible for the completion of a task. You have the option of choosing one or more contacts. By using this feature, it is possible to keep track of each project member's responsibilities (including the client's).

For a summary of how each task field is used, see FIGURE 1.

FIGURE 1: These are the various task fields and their purpose.

Open the basTaskExport module and create a new subprocedure named ExportTasks. ExportTasks begins by initializing clsXL and saving it to the variable clsXLTasks. This simplifies the code and allows you to invoke all the methods of the clsXL class:

  Set clsXLTasks = New clsXL

Once Excel has been initialized (by calling the StartExcel method), a new Excel workbook is created by calling the CreateNewWorkbook method listed previously. This method creates the workbook and saves it to the location specified by the constant FileName. This constant is defined at the beginning of ExportTasks. The specific location is not important because the file is inserted into a new e-mail automatically after it is created. What is important is that the directory you specify exists. You may want to change the value of the FileName constant to suit your needs better:

  With clsXLTasks
  .StartExcel (False)
  .CreateNewWorkbook (FileName) 
End With

Next, create a reference to a task folder by calling the GetTaskFolder function (see FIGURE 2). This procedure should be created within basTaskExport.

  Public Function GetTaskFolder()As MAPIFolder
   Dim nms As NameSpace
   Dim fld As MAPIFolder
   Set nms = Application.GetNamespace("MAPI") 
PickFolder: 
   Set fld = nms.PickFolder
   If fld Is Nothing Then
     Exit Function
   Else
     If fld.DefaultItemType  olTaskItem Then
      MsgBox "Please pick a Task Folder." 
       GoTo PickFolder: 
     Else
       Set GetTaskFolder = fld
     End If
   End If
End Function

FIGURE 2: The GetTaskFolder function implements the PickFolder function of the Namespace object, which allows you to ask the user which folder he or she would like to export.

Export License

GetTaskFolder implements the PickFolder function of the Namespace object. This allows you to ask the user which folder he would like to export. Once the user selects a folder, GetTaskFolder checks the DefaultItemType of the folder to determine if the selected folder is a task folder. Any folder that has set Task Items as its default item will return the built-in Outlook constant value of olTaskItem (or 3).

Once you know which Task Folder to export, you set a reference to the folder's Items collection. With this in hand, you can loop through each task and send its values to Excel. To determine how many items will be in the loop, call the Count property of the itmItems variable. Because this variable is referenced to the selected Task folder's items, you now have the number of items for processing. The looping code is shown in FIGURE 3.

  Set itmItems = fldTask.Items
Set itmTask = itmItems.GetFirst
iTaskCount = itmItems.Count
For iCount = 1 To iTaskCount
   If itmTask.Complete = False Then
    clsXLTasks.InsertValue "a" & CStr(iNextRow), iCount
    clsXLTasks.InsertValue "b" & CStr(iNextRow), _
     itmTask.Importance
    clsXLTasks.InsertValue "c" & CStr(iNextRow), _
     itmTask.Subject
    clsXLTasks.InsertValue "d" & CStr(iNextRow), _
     itmTask.ContactNames
    clsXLTasks.InsertValue "e" & CStr(iNextRow), _
     IIf(itmTask.DueDate = "1/1/4501", Null, _
     itmTask.DueDate) 
     ' This is weird, if there is not a completed date it 
    ' has a date in the future, Insert Null instead
    clsXLTasks.InsertValue "f" & CStr(iNextRow), _
     IIf(itmTask.DateCompleted = "1/1/4501", Null, _
     itmTask.DateCompleted) 
    iNextRow = iNextRow + 1
  End If
   Set itmTask = itmItems.GetNext
Next iCount

FIGURE 3: This code loops through each task and sends its values to Excel.

Before you export the task items, it is necessary to set up a Titles row for the Excel spreadsheet. To do this, invoke the InsertValue method of the clsXL variable. Then, you'll need to pass two parameters. The first parameter is the range within Excel in which you want to insert a value. This parameter must be a string, so you implement the CStr function to convert any numerical characters to a string value. The second parameter is the actual value to be inserted into the range. For these purposes, you insert six column titles into the fourth row of the spreadsheet:

  clsXLTasks.InsertValue "a" & CStr(4), "#" 
clsXLTasks.InsertValue "b" & CStr(4), "!!!" 
clsXLTasks.InsertValue "c" & CStr(4), "Description" 
clsXLTasks.InsertValue "d" & CStr(4), "Who" 
clsXLTasks.InsertValue "e" & CStr(4), "Due" 
clsXLTasks.InsertValue "f" & CStr(4), "Completed"

The next step is to loop through all task items in the folder and insert a new Excel row for each one. You will continue to use the clsXL.InsertValue method for this purpose. The main trick here is to keep track of your position within Excel. Because you inserted the column titles in the fourth row, you will begin the export at the fifth row. The row position is maintained in the variable iNextRow. At the beginning of the ExportTasks procedure, it is set to five. As you loop through each task item in itmItems, the value of iNextRow is incremented by one before moving to the next item. In effect, this moves you to a new row within Excel.

The date fields (due date and date completed) require some explanation. Everything works well whether a date has been assigned or not. However if they have not been assigned a value, Outlook will store a value of 1/1/4501 for each of these two fields. This is not a meaningful value, so you want to test for the existence of 1/1/4501 and insert a null value into the spreadsheet instead. The Immediate IF (IIF) function fulfills this need by testing the two date fields to determine if they equal 1/1/4501. If the result of the tested expression is True, a null value is returned and inserted into the spreadsheet. If the IIF expression returns False, the value of the date field is inserted.

Once all of the task items have been exported, call the ResizeToFit and FormatAsColumnHeaders methods of clsXL. These two methods resize all the columns in the spreadsheet to achieve a better fit and apply formatting to the column titles.

Out It Goes

Finally, you complete the export by calling the SendStatusReport procedure. This procedure takes the passed strFileName parameter and attaches the spreadsheet to a new Outlook mail item:

  Public Sub SendStatusReport(strFileName As String)
   Dim omiMail As MailItem
   Set omiMail = Outlook.Application.CreateItem(olMailItem) 
  omiMail.Attachments.Add (strFileName) 
  omiMail.Display
End Sub

The best way to make the ExportTasks procedure easily available is to create a new button on one of the Outlook command bars. The easiest way to do this is to select Tools | Customize from the menu. Be sure to close the Visual Basic Editor because Outlook will display an error message if it is open. A dialog box will display and allow you to add buttons to an Outlook menu. Select the Commands tab and, on the left-hand side, scroll down until you find Macros. On the right-hand side, the ExportTasks procedure will be visible. Drag and drop it to the desired location. You also can change the Caption and Image properties, by right-clicking on the new button and editing them as desired.

Now you can extend Outlook's task features by creating a task-status report in Excel. Although this is a very simple solution, there are many ways to take it even further. For example, implementing this feature set in a COM add-in would make for easy distribution. In addition, you could add the ability to specify the location for the export.

Because this was developed in Outlook 2002, a dialog box will appear when the code executes and asks permission to allow access to Contact Items. This can be overcome if you use it in conjunction with Microsoft Exchange, but that's a topic for another article. The code will work as is in Outlook 2000, but you may need to change the external reference of Excel 10.0 to Excel 9.0.

Ty Anderson, a certified public accountant, is a founding partner of Credera in Richardson, TX. Credera is a strategically focused application-development firm that provides software and services to the private and public sectors. Ty serves as the chief technology officer of Credera and leads the development of tailored solutions using Microsoft technologies.

Tell us what you think! Please send any comments about this article to mailto:feedback@msOfficeMag.net. Please include the article title and author.