Using VBScript to Pull Meetings from Outlook

By Bruce Szabo (Send Email)
Posted Aug 21, 2002


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 & "<h2> Meetings This Week </h2>"
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 <= Date+1 Then
31 icount = icount + 1
32    strOutput = strOutput & icount & ". " & CurrentAppointment.Subject & vbTab & 
      " <b>Time:</b> " & CurrentAppointment.Start & 
      " <b>Duration</b> " & CurrentAppointment.Duration&  vbCRLF
33' txtNames = txtNames & CurrentAppointment.Subject & vbTab & 
      " <b>Time:</b> " & CurrentAppointment.Start & 
      " <b>duration</b> " & CurrentAppointment.Duration&  vbCRLF
34 End If
35 End If
36           Next
37
38         
39            Set objMsg =  objOutlook.CreateItem(olMailItem)
40
41            objMsg.To = "manager@domain.com" ' your reminder notification address
42            objMsg.Subject = "Status Report - " & Date()
43            objMsg.Display
44            strOutput = replace(strOutput,vbCrLF,"<br>")
45            objMsg.HTMLBody = strOutput
46
47        'Display results to user, if any.
48'            If strOutput > "" 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.


Page 1 of 1


Comment and Contribute

Your name/nickname

Your email

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