dcsimg

Status Report Script for Outlook

By Bruce Szabo (Send Email)
Posted Jul 30, 2001


Introduction Status reports seem to be the bane of many IT jobs. In his latest article, Bruce Szabo presents a script that uses 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 automatically create and send status reports.

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.

Dim objOutlook
Dim objNameSpace
Dim objFolder
Dim MyItems
Dim CurrentTask
Dim strOutput

Const olMailItem = 0
Const olTaskItem = 3
Const olFolderTasks = 13

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.

Set objOutlook = CreateObject("Outlook.application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
Set objFolder = objNameSpace.GetDefaultFolder(olFolderTasks)
Set MyItems = objFolder.Items

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.

dtLastWeek = DateAdd("d", -7, date)
dtNextWeek = DateAdd("d", +7, date)

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.

1            strOutput = strOutput & "<b>Due This Week</b>"
2             icount = 0
3             For Each CurrentTask in MyItems
4                 If currentTask.DueDate >= dtLastWeek And CurrentTask.DueDate <= Date Then
5                 icount = icount + 1
6                     strOutput = strOutput & icount & ". " & CurrentTask.Subject
7                     if CurrentTask.Complete then
8                        strOutput = strOutput & "-<b> COMPLETED</b>-" & vbCrLf
9                     else
10                       strOutput = strOutput & vbCrLf
11                    end if
12                    if len(currentTask.Body) > 0 then
13                       strOutput = strOutput & "<blockquote><b>Notes: </b>" &
                         CurrentTask.body & "</blockquote>" &  vbCrLF & vbCrLF
14                    else
15                       strOutput = strOutput & vbCrLf
16                    end if
17                End If
18            Next
19
20            strOutput = strOutput & "<b>Due Next Week</b>"
21            icount = 0
22            For Each CurrentTask in MyItems
23                If currentTask.DueDate > date And CurrentTask.DueDate <= dtNextWeek Then
24                icount = icount + 1
25                    strOutput = strOutput & icount & ". " & CurrentTask.Subject
26                    if CurrentTask.Complete then
27                       strOutput = strOutput & "-<b> COMPLETED</b>-" & vbCrLf
28                    else
29                       strOutput = strOutput & vbCrLf
30                    end if
31                    if len(currentTask.Body) > 0 then
32                       strOutput = strOutput & "<blockquote><b>Notes: </b>" &
                         CurrentTask.body & "</blockquote>" &  vbCrLF & vbCrLF
33                    else
34                       strOutput = strOutput & vbCrLf
35                    end if
36                End If
37            Next
38
39            strOutput = strOutput & "<b>Future Tasks</b>"
40            icount = 0
41            For Each CurrentTask in MyItems
42                If currentTask.DueDate >= dtNextWeek Then
43                icount = icount + 1
44                    strOutput = strOutput & icount & ". " & CurrentTask.Subject
45                       strOutput = strOutput & " Due -<b> " & currentTask.DueDate &
                         "</b>" & vbCrLf
46                    if len(currentTask.Body) > 0 then
47                       strOutput = strOutput & "<blockquote><b>Notes: </b>" &
                         CurrentTask.body & "</blockquote>" &  vbCrLF & vbCrLF
48                    else
49                       strOutput = strOutput & vbCrLf
50                    end if
51                End If
52            Next

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.

1	Set objMsg =  objOutlook.CreateItem(olMailItem)
2	objMsg.To = "ourmanager@ourcompany.com" ' your reminder notification address
3	objMsg.Subject = "Status Report - " & Date()
4	objMsg.Display
5	strOutput = replace(strOutput,vbCrLF,"
") 6 objMsg.HTMLBody = strOutput 7 8 9 Set objFolder = Nothing 10 Set objNameSpace = Nothing 11 set objOutlook = Nothing 12 set objMsg = Nothing

Conclusions

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.

Page 1 of 1


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.