Creating and Deleting Meetings: Adding and removing meetings in Outlook using PowerShell part 3

In the previous post Getting PowerShell ready to work with Exchange: Adding and removing meetings in Outlook using PowerShell part 2 we looked at preparing the script to create meetings in an Exchange calendar. Now that the connection has been created to the appropriate calendar we can start creating meetings (which is the easy bit) and deleting them when necessary (which is not so easy).

There are a number of tutorials on using PowerShell to create a meeting in an Exchange calendar, and include the necessary participants.  Two I found particularly helpful were Aman Dhally’s Powershell and Outlook: Create Calendar Meetings using Powershell function and Mike Pfeiffer’s Creating Calendar Items with PowerShell and the EWS Managed API (which includes an interactive script that is great for testing).

In our case the details of the appointment were captured through a separate system and held in a SQL Server table.  The script used a view of that table to select the appointments that had not yet been processed and read them into an ADO.net data table. This is not really the focus of this series of posts so I have not included the detail of accessing the data from the database not of constructing the body of the meeting record using token substitutions. Instead we will focus on the mechanics of creating a meeting record.

Meeting Parameters

A meeting record consists of 5 parameters added to the Exchange Appointment object ($appointment):

  • The title of the meeting record: $appointment.Subject
  • The body of the meeting record: $appointment.Body
    • Note: this accepts HTML content by default (unlike a regular email record)
  • The meeting start date/time value: $appointment.Start
  • The meeting end date/time value: $appointment.End
  • And what makes it a meeting rather than an appointment is the inclusion of participants. In our case required participants: $appointment.RequiredAttendees

Typically these values will be generated by the PowerShell script, and in our case the values come from the SQL Server table. The following code snippet assumes that these values are in the $row object.

Notes:

The $exchService is the Exchange connection, see the previous post on creating this object.

Because RequiredAttendees is a multivalue construct it needs to be assigned with a pipe rather than a simple assignment as in the other parameters:

The meeting record is created, and Required Attendees notified, when the $appointment.Save command is executed.

Deleting Meeting Record

Most tutorials stop with the creation of the meeting (or appointment), and in many cases this is enough.  Unfortunately in our case not only were meetings initiated in the online booking system queried by this script, meetings could also be deleted in the same online booking system. We therefore needed a mechanism for the script to delete them from the Exchange calendar.

Designing a view in SQL Server to identify the meetings that had been cancelled was trivial. The real problem was at the Exchange end. In order for the script to delete the meetings we needed to be able to find which meeting records had been created in Exchange for each booking in the online system. This is where advice on the internet started to run very thin.

Step 1: Identify the Meeting Created in Exchange and store the identifier

There are a couple of different ways to identify a meeting in Exchange, but the one described here should retrieve a globally unique identifier that will persist even if a meeting is edited in Exchange. it also retrieves a text string that can easily be stored in whatever system underpins the script, in our case the table of bookings in SQL Server.

To capture the GUID we first need to extend the Exchange connection object with an extended property set

With this in place we can then capture the GUID after the meeting is created

The variable $CalIdVal64 is a base 64 encoded text string which represents to binary GUID. The text string can now be stored with the booking information and can be used to find and delete the meeting if necessary.

The full code snippet is available at the end of this post.

Step 2: Using the GUID to delete a meeting from Exchange

As with creating meetings, the script uses a database view to access details of meetings that need to be deleted from Exchange. In the code snippet these details are in the $row object

  • $CalIdVal64 is the base 64 encoded GUID captured when the meeting was created
  • $CleanGlobalObjectId is the extended property object created when defining the Exchange connection
  • $Calendar is the Exchange calendar object the script binds to

The meeting is cancelled when the $a.CancelMeeting() command executes and all Required Attendees are notified by email automatically.

The Full Script Skeleton

Because the script relies on an external store of meeting details it is difficult to provide a drop in and run script.  It would be possible to write the commands to create and delete a meeting as functions, and this is what we have done in production, but it does not make the code significantly more reusable and it is more difficult to follow. So what follows should be seen as a skeleton that can be fleshed out with your own external data sources, or dismembered and put into separate scripts to create and delete meetings.