ServersStatus Report Script for Outlook

Status Report Script for Outlook

ServerWatch content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.




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 & "Due This Week"
2             icount = 0
3             For Each CurrentTask in MyItems
4                 If currentTask.DueDate >= dtLastWeek And CurrentTask.DueDate  0 then
13                       strOutput = strOutput & "
Notes: " & CurrentTask.body & "
" & vbCrLF & vbCrLF 14 else 15 strOutput = strOutput & vbCrLf 16 end if 17 End If 18 Next 19 20 strOutput = strOutput & "Due Next Week" 21 icount = 0 22 For Each CurrentTask in MyItems 23 If currentTask.DueDate > date And CurrentTask.DueDate 0 then 32 strOutput = strOutput & "
Notes: " & CurrentTask.body & "
" & vbCrLF & vbCrLF 33 else 34 strOutput = strOutput & vbCrLf 35 end if 36 End If 37 Next 38 39 strOutput = strOutput & "Future Tasks" 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 - " & currentTask.DueDate & "" & vbCrLf 46 if len(currentTask.Body) > 0 then 47 strOutput = strOutput & "
Notes: " & CurrentTask.body & "
" & 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
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.


This article was originally published on Jul 30, 2001

Page
1 of
1

Thanks for your registration, follow us on our social networks to keep up-to-date

Get the Free Newsletter!

Subscribe to Daily Tech Insider for top news, trends & analysis

Latest Posts

Related Stories