Using VBScript to Pull Meetings from Outlook

Introduction
In his latest article, Bruce Szabo presents an appointment/meeting script for use with Microsoft Outlook that creates an e-mail with the current week’s appointments listed.

It is always suprising how many people read what is written on a Web site and how helpful the Internet
can be. A number of problems I have faced have been solved by asking people on Internet groups how
they did something. The task script article
I wrote over a year ago recently prompted a reader to write me. The reader was very complimentary and
then asked a rather straight-forward question. Could a script be written that would pull meetings in the
same fashion that the tasks were pulled?

The meeting script presented here was a fun script to write, and once it was created, I thought it would make for an interesting
article. The script was not too difficult to write once the needed properties of the Appointment Object were found.
Below is an explanation of what was done to create the script. In addition to the script explained by this article, I have also included
a script which modeled the task script exactly. The code for the artices can be found
here.

Using the Script

As with the task script, the appointment/meeting script simple creates an e-mail with the current weeks’ appointments listed. With the task script and the meeting script, it is now possible to combine both and create
a script that pulls more complete status information from Outlook.

Line 1 demonstrates how to use the script. This script is simply a subroutine that can be called to create the e-mail that can be sent. Although it is not done here, it is possible using the outlook object model to actually send the e-mail as well. Sometimes, however, it is useful to proofread
a status report before sending it. For this reason, rather than send the status report, it is displayed to the user.

How the Script Works

In previous articles I have presented a script and then annotated it to demonstrate how the script works.
In the download I provide
not only an example of the script presented here but also an additional script upon which one can expand.
In the explanation that follows there is some additional information beyond what is needed
for the script to run. Where appropriate, I will try and explain the purpose of the additional information.

Lines 5-10 intialize the variables that will be needed for this script. One of the things that is difficult
about scripting against the Outlook object model versus using Visual Basic is that it
is difficult to find the value of needed constants. Lines 12-15 show some constants
that can be used when scripting against the Outlook object model.
The task folder item is listed in line 14 and the task item is listed at line 13
for reference (however, they are not needed for the appointment script).
The key constant in this case is the calendar item in line 15. In order
to get the calendar appointments it will be necessary to look through the calendar folder from Outlook.

Lines 19-22 instantiate the objects that are needed for this script. If there are multiple profiles on
a computer, it is these lines which will cause outlook to prompt the user for a profile. It is useful
to mention that this script can be used against either an Exchange server or a PST file.

Lines 24 and 25 set up two variables to account for this week and last week. Line 26 starts the construction
of the strOutput variable which will be used to create the body of the e-mail message. The icount
variable on line 27 is used to number the appointments. Line 28 begins the loop through the individual
appointments in the calendar. In this case, each appoinment in the
collection of appointments is analyzed. In line 29 the requirement is set so only appoinments that have
a busy status and that are not marked as private are added to the list. No sense showing the boss what
we are doing in our free time.

Line 30 compares the date of the appointment to see that it is within the past week. Line 31 keeps track of
which appointment it is. Line 32 adds the number of the appointment, the subject, time and duration of the appointment
to the strOutput variable.
Line 33 is not needed for the script to work and is commented out. Occasionally, in order to troubleshoot an
appointment it is necessary to have some variables to see what information the script is pulling.

Lines 40-45 were described in the task script. These lines create the e-mail and display it to the user for review.
Line 41 would need to be edited to send it to the appropriate person. Line 42 would be editied to change the
subject. Line 44 replaces the carriage return, line feed with the HTML equivalent. Line 45 sets the body of the
message to the strOutput string that was created by the script.

Lines 47 to 52 are another routine that is commented out but can be used to display the results to the user.
If a user wanted to see what the meetings were without sending an e-mail, these lines can be used. Lines 54-59
destroy the objects that were created by the script.

 
1 Call subOutlookAppointments()        'Creates an e-mail with Outlook Appointments
2
3 Sub subOutlookAppointments
4
5        Dim objOutlook
6        Dim objNameSpace
7        Dim objFolder
8        Dim MyItems
9        Dim CurrentAppointment
10       Dim strOutput
11
12        Const olMailItem = 0
13        Const olTaskItem = 3
14        Const olFolderTasks = 13
15        Const olFolderCalender = 9
16
17
18        'Create Outlook, Namespace, Folder Objects and Task Item
19            Set objOutlook = CreateObject("Outlook.application")
20            Set objNameSpace = objOutlook.GetNameSpace("MAPI")
21            Set objFolder = objNameSpace.GetDefaultFolder(olFolderCalender)
22            Set MyItems = objFolder.Items
23
24            dtLastWeek = DateAdd("d", -7, date)
25            dtNextWeek = DateAdd("d", +7, date)
26            strOutput = strOutput & "
 Meetings This Week 
"
27            icount = 0
28            For Each CurrentAppointment in MyItems
29 If currentAppointment.BusyStatus = 2 and currentAppointment.Sensitivity=0 then
30 If CurrentAppointment.Start >=  dtLastWeek And CurrentAppointment.Start  "" Then
49'                Msgbox strOutput, vbExclamation, "Meetings for this week"
50'            Else
51'                Msgbox "No Tasks Today", vbInformation,"No Meetings for this week"
52'            End If
53
54        'Clean up
55            Set objFolder = Nothing
56            Set objNameSpace = Nothing
57            set objOutlook = Nothing
58
59            set objMsg = Nothing            
60End Sub

Conclusions

This script demonstrates how meetings can be extracted from Outlook. This script, when combined with
the previously published task script, can be used to create a more comprehensive status report.
The tasks and meetings need to be tracked in Outlook, and these two scripts will allow one access to the information. In future
articles, I would like to show how the scripts can be combined and additionally, how they might be added
to a macro in Microsoft Word.

One limitation of the script that needs to be overcome is recurring meetings. Recurring meetings are handled
differently by Outlook, and, in some cases, they are not displayed in the status report. This topic will be
covered in a future article.