Status Report Script for Outlook
July 30, 2001
Status reports seem to be the bane of any IT job I have had. To this point I have explained some real technical problems to managers who never quite understand the breadth of the problem. Even though this is bad, what is worse is that in some cases after I have submitted my status report I can never find my old reports to look up what I had just done.
It always seemed that it would be possible to use the tasks feature of Outlook to facilitate my status report creation. With the Outlook 2000 object model I was able to write a script that organized my tasks and generated an e-mail to my manager for my weekly status report. A copy of the script can be downloaded here. Another useful reference for programming with Outlook objects is a Visio diagram of the Outlook 2000 object model.The Script
As with most scripts the initial portion of the script initializes the needed variables. The variables in this case are used to access the needed outlook objects which include the tasks folder and items within that folder.
The four lines that instantiate the objects are listed below. An Outlook Application object is created and the MAPI namespace is specified. The folder and items used to create the status report are accessed by creating the objFolder object and the MyItems object.
The status report that needed to be created had to list tasks completed in the past week,
the tasks that were due next week and long term tasks. Because of this layout two variables
were created to specify the past week and the current week.
The variable strOutput is created to hold all the results as a string. When the script is finished this variable will be used for the body of an email message object that can be sent to the desired recepient. Looping through the tasks makes it possible to group the tasks in the needed date areas.
The code below lists the section of code that cycles through the task items and organizes them. I could not find a routine to sort the tasks the way I need to organize them, so the alternative was to loop through the list of tasks three times and pull the needed events for each section of the e-mail. A closer look at the code reveals that lines 1-18 are functionally equivalent to lines 20-37 and 39-52. Line 1 starts the initial string creation. A string is created and stored in the strOutput variable. In this case the header is the only thing added to the string. It should be noted that I am going to create an HTML mail object, this allows for some formatting of the status report. The H2 tags are used to label the Due This Week heading. The icount variable is used to number each of the tasks for the report.
When the MyItems object was created in the early part of the script it was based on the Outlook Application object. Some assumptions made to this point are that there is one profile on the computer and the user logged in has access to the profile. By creating the objects in the manner here, the tasks listed will belong to the user logged on the computer.
Line 3 starts the loop by enumerating the tasks. This enumeration allows one to walk through each task item and perform the needed actions on each task. In line 4, for example, the due date of the task is compared to see if this task is due this week by checking the DueDate property of the task item. If the task falls in the specified date range, line 5 increases the count and line 6 adds the icount variable and the subject name of the task to the output variable. Line 7 checks the complete attribute of the task to see if it is marked complete. If the task is marked complete line 8 adds a bolded "completed" to our strOutput variable, and if it is not complete a hard return is added to the output variable. Although the hard return is a text-based hard return this will be converted to HTML later in the script. Line 12 checks the body attribute of the item to see if it exists. It uses the blockquote HTML tag to format the text and adds a "Notes:" string in front to set things out. If there is no body text in the task a return is added.
The other two sections of script follow the same procedure; the only changes are the date ranges for which the tasks are gathered and the header information for each section. The last part of the script creates the e-mail message.
The final part of the script also does some formatting and displays the message. Although the message could be sent from the script, it is displayed for proof-reading and any other changes that may need to be added.
Line 1 in this section of code creates a mail message item. Line 2 enters the recepients e-mail address. The subject is added in line 3 and the message is displayed on line 4. Before the strOutput variable is pasted in the body all the hard returns (vbCRLF) are replaced by the HTML hard return code <br> this is done on line 5. In line 6 the strOutput variable which is nothing more than a large string is placed in the body of the message. After some editing the status report is able to be submitted.
A script is presented to use an Outlook task list to create a status report. The script uses the Outlook Application Object to get access to a user's task items and to create a report. The report is formatted using HTML so comments can be added to the task notes using HTML code and they will appear as HTML.