Hey, Scripting Guy! How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?

ScriptingGuy1

Hey, Scripting Guy! How can I schedule a meeting to occur every day, Monday through Friday, for two weeks?
— GB

Hey, GB. As it turns out, the Scripting Guy who writes this column has not moved to some place where it doesn’t snow in April. That’s not because the Seattle area didn’t get the predicted snow this past weekend; on the contrary, it did snow, and not only on Friday April 18^th, but on each of the next to days as well. The Scripting Guy who writes this column had threatened to quit his job and move if it snowed in late April in Seattle, but cooler heads – including those at the mortgage company, the University of Idaho, and the place where he got his car loam – talked him out of it.

But if it snows in May, well ….

So how bad could the weather have been this past weekend? Well, there’s an old joke in the Seattle area that goes something like this: if you don’t like the weather, just wait 15 minutes. That was definitely the case this past weekend. Sunshine? Believe it or not, at times we actually did have sunshine, and blindingly-bright sunshine at that. Snow? On Friday there was enough snow to cover the ground, and to make you brush off your car windows before you could drive anywhere. Rain? OK, that’s a silly question; after all, this is Seattle we’re talking about. Hail? You bet we had hail; in fact, we hail the size of … well, hailstones. Wind? We had so much wind that – you know, now that we think about it, we don’t remember much wind over the weekend. But just wait a second and that’s bound to change … ah, there’s your wind now, right on schedule.

Regardless, it was an absolutely miserable weekend, one of the worst the Scripting Guy who writes this column has ever been forced to endure. (And yes, that includes the weekend he spent at a bed-and-breakfast, where one morning he sat next to a couple from Chicago who said, “We had the best day yesterday. We drove out a little ways out of town and found this majestic old evergreen tree and we just sat down and stared at the tree for the rest of the day.”) Of course, as the Scripting Editor noted, “If this was January you wouldn’t think the weather was that bad.” In other words, we should be happy that we’re getting a so-so day in January in late April. Thanks, Scripting Editor; we feel much better now!

As you all know, it’s extremely rare for the Scripting Guy who writes this column to complain about anything; that’s just not his nature. On those rare occasions when he does complain, however, he usually concludes his rant by saying, “On the bright side, the fact that X occurred did give us plenty of time to write a script that answered today’s question.” However, he’s not going to say that today; when you have snow and a high temperature of 45 degrees in late April there is no bright side. Nevertheless, after a little trial-and-error he was still able to write a script that can schedule a meeting each workday (Monday through Friday) for a two-week period:

Const olAppointmentItem = 1
Const olMeeting = 1
Const OlRequired = 1
Const olRecursWeekly = 1

Set objOutlook = CreateObject("Outlook.Application")
Set objAppointment = objOutlook.CreateItem(olAppointmentItem)

objAppointment.Start = #5/5/2008 11:00 AM#
objAppointment.Duration = 60
objAppointment.MeetingStatus = olMeeting
objAppointment.Subject = "Scripting Guys meeting"
objAppointment.Body = "Meeting with Scripting Guys to discuss upcoming plans."
objAppointment.Location = "42/2039"
objAppointment.ReminderMinutesBeforeStart = 15
objAppointment.ReminderSet = True

Set objRecurrence = objAppointment.GetRecurrencePattern
objRecurrence.RecurrenceType = olRecursWeekly
objRecurrence.DayOfWeekMask = 2 OR 4 OR 8 OR 16 OR 32
objRecurrence.PatternStartDate = #5/5/2008#
objRecurrence.Occurrences = 10
 
Set colRecipients = objAppointment.Recipients
Set objRecipient = colRecipients.Add("kenmyer")
objRecipient.Type = olRequired
objAppointment.Send

As you can see – and unlike the weather in the Seattle area – we start out in reasonable fashion, defining a constant named olAppointmentItem and another constant named olMeeting; these two constants tell the script that we want to create an instance of Outlook’s AppointmentItem class and that we want this instance to be a meeting (as opposed to a plain old appointment). We then define a third constant (olRequired) which tells the script that the attendee we’re planning to invite to the meeting is a required attended.

We then create one final constant, olRecursWeekly. This is the part that threw us off at first. Because we want to create a series of meetings that convene every weekday for 2 weeks we assumed that we needed to create a daily meeting. That made sense to us, but it made no sense whatsoever to Outlook; Outlook promptly rejected our meeting request with the terse comment, “The property for the recurrence type is not valid. Verify your code.” Of course, verifying our code (whatever that means) didn’t do us much good, either; it was only when we changed our code (by changing the recurrence type to a weekly meeting) that the script worked.

After defining our constants, we next create an instance of the Outlook.Application object, then use the following line of code (and the CreateItem method) to create a new AppointmentItem object:

Set objOutlook = CreateObject("Outlook.Application")
Set objAppointment = objOutlook.CreateItem(olAppointmentItem)

At this point we have a big block of code where we configure various properties of our appointment item, including such things as the meeting Duration (60 minutes) and the fact that this is a meeting rather than an appointment. (Something we do by set the MeetingStatus property to the constant olMeeting.) We won’t discuss these properties in any detail today, in part because most of them are self-explanatory, and in part because we have an Office Space article that explains what they are and how they should be used.

Instead, we’re going to turn our attention to the following block of code:

Set objRecurrence = objAppointment.GetRecurrencePattern
objRecurrence.RecurrenceType = olRecursWeekly
objRecurrence.DayOfWeekMask = 2 OR 4 OR 8 OR 16 OR 32
objRecurrence.PatternStartDate = #5/5/2008#
objRecurrence.Occurrences = 10

This is the portion of the script where we define our recurrence pattern; that is, this is where we determine the actual days that our meeting will take place. In order to do that, we first use the GetRecurrencePattern method to retrieve the recurrence pattern object that corresponds to our appointment item:

Set objRecurrence = objAppointment.GetRecurrencePattern

Once we’ve latched onto the recurrence pattern object the first thing we do is set the RecurrenceType to weekly (using the constant olRecursWeekly). That brings us to this line of code:

objRecurrence.DayOfWeekMask = 2 OR 4 OR 8 OR 16 OR 32

What we’re doing here is defining the days of the week that the meeting will take place. The DayOfWeekMask property is a “bitmask” property, a bitmask being a single property that can hold multiple values (provided those value are assigned using Boolean logic). Outlook uses the following values to represent the individual days of the week:

In our script, we’re assigning the days Monday (2) through Friday (32) as meeting days. Admittedly, it might not look like we’re assigning all five days as meeting days; that’s because, in Boolean logic, the OR operator functions more like the word “and.” Our code looks like this:

2 OR 4 OR 8 OR 16 OR 32

However, it reads like this:

2 and 4 and 8 and 16 and 32

Or, substituting the day names for the values:

Monday and Tuesday and Wednesday and Thursday and Friday

Quick quiz: what if we only wanted to meet on Tuesdays and Thursdays? That’s right; in that case we’d assign the values 4 (Tuesday) and 16 (Thursday) to the DayOfWeekMask:

4 OR 16

By the way, good answer.

We next set the PatternStartDate property to May 5, 2008 (the day of the first meeting). That makes sense; to be honest, the next line of code makes about as much sense as snow in April:

objRecurrence.Occurrences = 10

As you just saw, we set the PatternStartDate property to indicate the day of the first meeting in the series. Logically enough, we initially assumed that we’d then set the PatternEndDate property to the day of the last meeting in the series. When we did so, however, we got some really strange results, none of which featured a series of 10 meetings, Monday through Friday, running from May 5^th through May 16^th. No matter what we tried we got nothing but gobbledygook when we assigned a value to the PatternEndDate property. When we set the Occurrences property to 10, however (indicating that we wanted to hold 10 separate meetings) then everything worked just fine.

The moral of the story? To tell you the truth, we have no idea what the moral of the story is. We just know that setting the Occurrences property gave us the meeting assignments we desired.

All that’s left at this point is to send the meeting request to the desired attendees. For example, this block of code sends a meeting invitation to kenmyer:

Set colRecipients = objAppointment.Recipients
Set objRecipient = colRecipients.Add("kenmyer")
objRecipient.Type = olRequired
objAppointment.Send

In line 1 we’re creating an instance of the Recipients collection; that should be fairly obvious. In lines 2 and 3 we use the Add method to add kenmyer to the collection, and to configure his attendance status as required (using the constant olRequired), respectively. Finally, in line 4 we use the Send method to send the meeting request to our lone attendee.

What’s that? Can we invite more than one person to a meeting? Of course we can; all we have to do is add that person to the Recipients collection, and configure their attendance type. For example, this block of code invites both kenmeyer and pilarackerman to a meeting:

Set colRecipients = objAppointment.Recipients

Set objRecipient = colRecipients.Add("kenmyer")
objRecipient.Type = olRequired
Set objRecipient = colRecipients.Add("pilarackerman")
objRecipient.Type = olRequired

objAppointment.Send

And so on.

That should do it, GB; please let us know if it doesn’t do it. As for the Seattle area, the Scripting Guy who writes this column happened to stumble upon the following paragraph in an article on Frommers.com:

“Seattle’s rainy weather may be infamous, but Seattleites have ways of dealing with the dreary days. They either put on their rain gear and head outdoors just as if the sun were shining, or they retreat to the city’s hundreds of excellent restaurants and cafes, its dozens of theaters and performance halls, its outstanding museums, its many movie theaters, and its excellent bookstores. They rarely let the weather stand in the way of having a good time, and neither should you. “

So is that true, do Seattleites rarely let the weather stand in the way of having a good time? Let’s put it this way: there’s a reason why they tell you that shouldn’t believe everything you read on the Internet.

Well, except for this column, of course.

ScriptingGuy1

Follow