Author: Kevin Trent

As I mentioned in my article Exchange & Outlook Custom Room Reservations, Guide & Template , the trouble with using custom forms for room reservations in Microsoft Exchange is that you have to retrain your users to open the room’s calendar directly. This is because the default room invitation process never displays a form at all. It only shows an EWS (Exchange Web Service) generated list of available rooms and their free / busy information. When the user selects one, the room’s mailbox is sent an invitation email which is processed by the attendant service or the room’s moderator. The reserver never gets a chance to see your custom form or complete any of the extra data fields you’ve painstakingly created.

Re-training your employees to open the room’s calendar isn’t too difficult if you’re setting up the service for a small organization. If you work for a company with hundreds or thousands of employees you’ll likely end up frustrated and so will your users. There are quite a few 3rd party add-ons for Exchange that claim to fix this limitation. You don’t need them. In this article we’ll explore using a few lines of HTML, JavaScript, PowerShell, and Outlook to allow the normal invitation process, while still compelling your users to complete the custom form.

My solution involves 4 basic components that, when executed correctly, allows the room’s mailbox to send an email back to the user requesting they complete your custom form. The auto response message will include a link that directly opens the room’s calendar showing their appointment. We’ll use PowerShell to alter the appointments created by the invitation so that they use our custom form.

• Enable automated processing for the room invitations
• Alter the message class of the tentative appointments
• Create web pages that open each room
• Create an invitation auto response with a link to the web pages

Enable Invitation Auto-update

In order for my technique to work, the room’s invitation must be processed from an email to a tentative appointment before the user receives the automated response. To accommodate this requirement we need to tell Exchange to automatically update the invitation on receipt (it can wait for hours otherwise).

1. Create a txt file that contains only your room’s (assuming more than one) email addresses (you can see them in the EMC).
2. Open the Exchange Management Shell or import an Exchange session in to your PowerShell.
3. Run the following script (change the path to match the location of your text file)

   $ccrooms = Get-Content -path "C:\Users\MyUserName\Documents\ccroom.txt"
   Foreach ($ccroom in $ccrooms) {Set-CalendarProcessing -identity $ccroom -AutomateProcessing Autoupdate}
   

4. If you ever add more rooms you’ll need to update them as well.

Alter the Message Class of the tentative appointments

Which form Exchange uses to open a message is determined by an attribute stored in Active Directory called the message class; it gets added when you alter the schema for the installation of Exchange. We want to alter all of the room’s tentative appointments so that they use our custom form. For this to work you’ll need to set up a few things ahead of time.

1. A machine with Outlook installed (I use a VM). You could also use CDO on an Exchange 2013 or older server but you will need to alter the COM object in the script.
   • I haven’t been succesful in changing the attribute via EWS. You can set the default message class for a mailbox but not for each individual appointment; which is what we need for this solution to work.  If you know how to do this in EWS leave a comment, I’d really appreciate it.
2. The AD account you logon to the machine with must have full permissions to all the room’s mailboxes.
3. Create an Outlook profile for each room’s mailbox on the machine (individually); you probably think that you know better and can just open the mailboxes from one profile but you don’t / can’t. Just trust me.

Now that you have everything staged, we’re going to execute a PowerShell script that runs in a loop every 30 seconds and changes all the appointment’s message class attributes to our custom form.

Here’s the code:

Function Change-Class
{
$mbxs = Get-Content -path "C:\Users\Myusername\Documents\ccroom.txt"
Foreach ($ccroom in $ccrooms)
{
$outlook = new-object -com Outlook.Application
$namespace = $outlook.GetNamespace("MAPI")
$room = $namespace.CreateRecipient("$ccroom")
$room.Resolve()
$roomcal = $outlook.Session.GetSharedDefaultFolder($room, 9)
$appointments = $roomcal.items

Foreach ($appointment in $appointments)
   {
     If ($appointment.messageclass -eq "IPM.Appointment")
     {
      $appointment.messageclass = "IPM.Appointment.Conference Center"
      $appointment.save()
     }
   }
}
}
While ($true)
{
Change-Class
Sleep -Seconds 30
}

Notice that we’re using the same list of the room’s email addresses; don’t forget to change the path to match the location you’ve stored the file in. You’ll also need to alter the name of the form on the line $appointment.messageclass = “IPM.Appointment.Conference Center” to match the name of your published custom form. You must publish your form to each room with the same name or alter the script accordingly.

Create Web Pages that Open each room from a hyperlink

We’ll setup an auto responce to room invites in the next step. We’ll want to include a hyperlink in that auto-responce that opens the room’s calendar in Outlook for the user. This makes the process appear more seamless. You could accomplish this same goal in other ways, but I found this approach to be effective and simple.

You’ll need an IIS instance to host the pages on. Since we’re talking about Exchange you’ll have OWA and EWS installed, the IIS instance that hosts those services will do just fine here. Just create a new file based site and place the pages you create into the folder. I’m not including full instructions for this, but if you get stuck leave a comment.

Now we need to create one page for each room. You be using the email address of the room to identify it. Paste the code below (change the email address) into notepad and save it with the HTML extension.

<!DOCTYPE html>
<html>
<head>

function OpenConferenceCenter()
{
var outlookApp = new ActiveXObject(‘Outlook.Application’);
var pmapi = outlookApp.getNameSpace(“MAPI”);
var recipient = pmapi.CreateRecipient(“room@mydomain.com”);
recipient.Resolve();
var calendar = pmapi.GetSharedDefaultFolder(recipient, 9)
calendar.display ()
}

</head>
<body onload =”OpenConferenceCenter ()” ;>
</body>
</html>

As you can see, we are creating an Active X object in the web pages to open Outlook and then the room’s calendar, which means this only works in Internet Explorer.

Create an invitation auto response with a link to the web pages

Now is when those Outlook profiles we setup earlier will come into play. We want to use Outlook to create a rule to repsond to calendar inviations in each room’s mailbox. This rule is not visible in Outlook for shared mailboxes, hence the profiles.

For each room:

• Open the Room’s mailbox in an Outlook profile
• Click on the Rules button in the ribbon and then select Manage Rules and Alerts.
• Click the New Rule button
• Select Apply rule on messages I receive
• Check which is a meeting invitation or update
• Click Next
• Check; Have the server reply using a specific message.
• In the bottom pane click underlined words “a specific message” and enter your message. I reccomend something like the text below(change the hyperlink location).

Your tentative reservation request has been received. Approval of your reservation request is based on receipt of the meeting information form. Please use the link below to locate your appointment, open it, and complete the Conference Center form by clicking its icon in the Ribbon.

The web link below only works in Internet Explorer. If your default browser is set to something else, you’ll need to right click and copy the they hyperlink below. Then paste it into Internet Explorer.

Click here to open the room’s calendar and adjust your appointment.

• Click Save and Close on the Message
• Finish the Rule

You should be ready to test now. If everything has gone according to plan the user’s experience should go like this:

1. The meeting’s organizer selects a room from the default appointment form in the Outlook.
2. Once they submit the invitation the room’s mailbox will reply with our message to finish the form.
3. When the click the link embedded in the message the room’s calendar will open, they should double-click the appointment they just made to open it.
4. All that is left is to click the form’s button to display it, complete it and save it.

This seems complicated but I’ve implemented it in an environment with 15,000 + users without any training and encountered very few issues. If you end up using this method in your own business I’d appreciate you leaving a comment.

If we want to move beyond the abilities afforded to PowerShell via its module system; we’ll eventually need to interact with other applications in our own code. For example; have you ever tried to load the “Outlook” module for PowerShell? There isn’t one, but does that mean we can’t interact with the software? Of course not. PowerShell can interact with applications in a myriad of ways, you’ve heard all the buzz words; “REST API”, “SOAP API”, “COM” but how do we use these tools? What are they really? In simplest terms they’re what us old school techs call “hooks”, built-in ways for applications to interact and exchange data. PowerShell is capable of using all of the popular methods including REST with JSON but in this episode we’ll discuss COM.

The Component Objet Model (COM) was introduced by Microsoft in 1993 as a non language specific standard for inter-process communication. It serves as the basis for some of Microsoft’s most popular technology; OLE, Active X, Direct X, and the Windows Runtime are all directly based on it. Using COM can be both extremely simple and frustratingly complex depending on what you are trying to accomplish but since nobody is going to read a 200 page blog post we’ll start with the easy stuff. If you would like a more detailed explanation of COM refer to https://msdn.microsoft.com/en-us/library/windows/desktop/ms680573(v=vs.85).aspx.

All of the examples and code that follows interacts with Outlook, I am an email admin after all, the techniques work with pretty much any application that runs on Windows. First we need to create an object to hold our application in memory while we work with it. How do you hold something in memory? With a variable of course, $outlook = new-object -com outlook.applicationwill essentially open a hidden copy of Outlook and store it in the $outlook variable so that we can send commands and exchange data with it (Get it? Outlook, Exchange data, I’m so funny). How does one find out what commands you can send to a COM object? You might be tempted to try help and that’s a good instinct but we’ve actually switched to C# here (called encapsulation) so PowerShell’s help won’t know anything about Outlook. We need to ask PowerShell to ask the Outlook COM object what commands (methods) it has available. We use the Get-Member command to do that; new-object -com outlook.application|gm will return a list of all the options at our disposal. Note that we had to create the object before we can get its member methods.

 I always try to keep these posts short enough to read and obviously we could spend the next year just going over all the different options in Outlook alone so lets look at a specific scenario. We’re writing a script/application and want our user’s Outlook contacts to pop-up on the screen.

$outlook = new-object -com outlook.application
$contacts = $outlook.session.GetDefaultFolder(10)
$contacts.display()

In the short script above we have loaded our application into memory, then selected the contacts folder. I just happen to know that each Outlook folder type (calendar, inbox, contacts, appointments, etc.) has an assigned number, GetDefaultFolder is saying get the top folder of this type and the display method is opening the selected item on the user’s screen.

Just for fun, lets create an Excel spreadsheet. Just to prove we can.

$Excel = New-Object -com Excel.Application
$Excel.Visible =$True
$Workbook = $Excel.workbooks.add()

Exchange has a decent built-in room reservation system. It has some nice features like Free/Busy information, a robust address book, advanced room lists, automatic time selection, and an excellent invitation and approval system. However, it leaves a lot to be desired when it comes to information about the rooms themselves. It doesn’t track the information required by managed conference centers at all. There’s no layout or seating information, no pictures or directions, no way to alert IT staff or room managers that custom setup is required and no catering tracking.

At the heart of Microsoft’s Exchange Server software is a series of databases. These databases can be modified to store both the fields you see and use in Outlook as well as custom data. Outlook itself provides the tools we need to add our required fields but it also will allow us to create custom forms to input and access the new data with. Many Exchange admins and Outlook power users are already familiar with Outlook’s custom forms trick. There are a lot of articles and excellent information on-line about using them. I like Outlookcode.com and have learned much from reading the site. Microsoft also publishes several TechNet articles on the subject. Using custom forms, Outlook transforms from a CRM and PIM tool to full database application solution.

Create a Room Mailbox

A room mailbox is pre-configured to handle meeting requests and approvals. A room mailbox must exist before publishing and testing the form. More information about resource mailboxes is available @ https://technet.microsoft.com/en-us/library/bb124374(v=exchg.141).aspx

To create room:

• Open the Exchange Management Console or Exchange Web Console
• Navigate to Recipient Configuration ->Mailbox
• Right click on Mailbox and choose New Mailbox

• In the New Mailbox wizard select Room Mailbox then click Next

• On the User Type screen select New User then click Next
  • Please note: this option will create an Active Directory account for this mailbox and then disable it once permissions are set, automatically. You should not delete the disabled account the process leaves behind but it is wise to move them to an OU in AD that is protected against accidental deletion. 
• On the User Information screen, select the check box for Specify the organizational……..
• Click the Browse button
• Navigate to the OU that your room mailboxes occupy
• In the Name Field enter the room’s name as you would like it to appear
• Enter a username in the User Logon name field (name of temporary AD account)
• Verify that the Alias field is populated with the room name or some variation of it
• Select the database location and any applicable policies
• Click Next

• Click the New button on the next page
• Click Finish
• Your new room mailbox will be visible in the EMC after enough time for replication has occurred

Room Mailbox Options

Options for room mailboxes include things like: who are the room administrators, should requests be processed automatically, and who can override the default settings. The options below are a good place start, but you will need to customise them to fit your specific requirements.

• Locate the conference room you wish to configure in the EMC. 
• Right click on it and select Properties
• On the Resource General tab enter the Room’s capacity (this will be visible in the room finder)
• On the Resource Policy tab enter delegates if this room is to be moderated by a human
• Uncheck the Forward meeting requests to delegates box (this setting can be annoying)
• On the Resource In-Policy Requests tab choose Selected Recipients for the first option and All Users for the second option This configuration will make sure your users follow the policies you set.
• On the Address and Phone tab complete the location information fields
• On the Resource Out-of-Policy Requests add the Room administrator, this will allow them to override a policy when required.

 Outlook Developer Mode

To create custom forms, fields, edit code, etc. you need to enable Outlook’s developer mode. Outlook in Developer Mode, functions as an IDE (Integrated Development Environment) and a pretty powerful one at that.

• Open Outlook
• Click on File
• Click on Options
• Select Customize Ribbon
• Put a check mark in the box marked Developer
• Click the OK button

A new Developer Tab will be visible in Outlook’s ribbon, from this tab you can design and publish forms, edit Visual Basic Scripts (VBS) and Visual Basic for application (VBA) add-ins.

Download and Edit the Template

In this article we’ll walk through editing a template I’ve already made, you’ll see how the tool works and be able to make your own with ease. Plus, it’s a good template even if I do say so my self, LOL. You can download it here: Conference Center Template. Unzip the file to your hard drive. Opening a form template in Outlook the first time can be a little confusing.

• Open Outlook
• Click the Developer Tab
• Click the Design a Form button
• Use the drop down to select User Templates in the File System
• Click the Browse button and locate the file you saved

The template will open in Edit mode. It opens to the default Appointment tab because this form is based on the built-in meeting maker. Click on the tab named Conference Center to see the customizations.

Change the Pictures

• Right click on the picture (use tabs to select) and pick advanced properties
• Select the Picture field and click the … button in the upper right
• Browse to the picture you wish to insert
• Click Open

• After inserting an image, size it by selecting PictureSizeMode in the properties
• Choose Stretch from the drop down at the top, this will cause your pic to fill the frame

Edit the Equipment List

• Right click the field you wish to edit and select Properties
• Use the controls to adjust the field properties accordingly
  • You can choose whether the boxes are checked by default or not. Enter Yes for checked or No for not checked

Edit the VBS Code (AV Notification)

This template includes VBS code that emails a copy of the appointment when the “Request Live Streaming……” box is selected. You can remove this section of the form if you don’t need it. Otherwise, you’ll need to edit the email address or change the code to better suite your particular requirements.

• In the Ribbon click the View Code button

• Change the highlighted Email address or otherwise adopt the code for your purposes. There is no save option in the editor. Once you are finished close its window.

After you’ve completed all of your changes save the form. File -> Save.

Publish the form to the Room’s Mailbox

Your users cannot access the form until you publish it to the mailbox we created at the beginning of this article. Before proceeding you’ll need to open the room’s mailbox in Outlook. If you don’t, it’s path will not be available in the publishing dialog. I prefer to setup an Outlook profile just for working with the room’s calendar, but this is optional.

• Select the drop-down chevron under the Publish button
• Choose Publish Form As from the menu

• Select the room’s calendar from the Look In drop down
• Provide the Display Name and Form Name
• Click the Publish button

Make the New Form Default

To prevent users from having to manually open the form we’ll want to replace the default appointment form.

• Open the room’s folder in your Outlook (you must be displaying the folder list)
• Right click on the room’s calendar folder
• Click Properties
• In the When posting to this folder use drop down box select the form you published
• Click Ok

Congratulations you’ve upgraded your room reservation system. After you’ve wowed all your co-workers with the results of your hard work and get around to fully testing everything, you will notice a usability flaw. The form only works if you open the room’s calendar directly in Outlook.

Most Outlook users create an appointment and then invite the room to it. This invitation is based on the Free/Busy system in Exchange which is a subset of the Exchange web services. EWS never presents a form at all. Just a list of rooms and their availability. When the user selects a room, an e-mail is sent to the room’s mailbox which is processed by the attendant and added to the calendar. You’re left with a couple of choices.

1. Train your users to open the room’s calendar and book their appointments from there. This isn’t a bad option for smaller organizations, but if you have thousands of employees, retraining all them could be problematic.
2. Read my next article about how to use a few lines of HTML, some PowerShell, and Outlook to allow the normal invitaiton process while still compelling your users to complete the custom form. Exchange & Outlook Custom Form Room Reservations; Retain Invitations

I recieve more email in a day than I could possibly read. My job doesn’t allow for things falling through the cracks just because I’m busy. I use Outlook’s conditional formatting feature to make important messages stand out from the ocean of junk.

Conditional formatting will change the color and / or font of an email in your message list based on parameters you specify. You can easily change emails from your boss to red, team members blue, the big project to large yellow, etc.. Personally, I assign each project I’m involved with a color. This makes it easy to find relevant information quickly. 

1. Open Outlook. 
2. In the ribbon click View and then the View Settings button. 
3. Click the Conditional Formatting button. 
4. Select Add to start a new rule and assign an appropriate name.
5. Use the Font button to choose the text effects your rule should trigger.
6. The Condition button presents a list of filters to trigger your font change with. Choose the message sender, subject, keywords, and more. The three tabs across the top provide advanced options. 

Good organization of your inbox can be the difference between drowning in email or sailing through your workload. Spend a couple of hours upfront to automate as much as possible. You won’t regret it. 

If you use PowerShell long enough you will inevitably need to combine data from more than one module into a report. As an example, My co-worker ran into a situation where she needed to show mailbox sizes by city. Exchange doesn’t have the city but we know Active Directory does. How do you combine the information from the Exchange Module with the information from the Active Directory Module?

SQL, SharePoint, Lync, pretty much all MS server products have a module at this point. The examples below are Exchange and AD but you can use the technique to combine data into a report from any or all of them. First get the data you need from the Exchange module; Get-MailboxStatistics youremailaddress|fl totalitemsize should do the trick.

We know that Active Directory has our address in it. 

How in the world do we combine the data? There are lots of ways to do this programmatically but one of the easiest is to use a hash table. 

What’s a hash table? In the simplest terms it is an array (a spot in memory) that stores key/value pairs. In our case we going to store a key (the name of our column) and a value (the results of another command) in the array and then display only the results. It sound complicated but trust me; it is easy once you get the hang of it.
The trick with hash tables in PowerShell is to get the syntax right. @{ <name> = <value>; [<name> = <value> ] is the formula. I know it looks complicated but if we plug in the data we’re looking for in, it will make more sense.
Get-MailboxStatistics dt208703|fl totalitemsize,@{Name=”City”;expression={(Get-ADUser -Identity dt208703 -Properties city).city}} see that comma between our first command and the @ that starts our hash table? You need a comma separator for each new hash table in your command. English teachers may be trying to get rid of the comma in written language but if you forget them in programming you’ll be sorry.

So what’s up with the ().city in our hash table? The syntax didn’t say anything about that. Turns out it is just some formatting. Who remembers grade school math; what does (3×2)+6 mean? For those of us that are a little rusty, it means multiply 3 and 2 and add 6 to the answer; the () mean do this first. Same thing in programming languages (they’re all just subsets of Algebra). So do this first (Get-ADuser –Identity dt208703 –properties city) and then display only the value named city. 

Wow, people’s mailboxes are a mess. Who has time to read and sort the thousands of mostly irrelevant messages that have built up in there over the years? We can’t just delete the stuff because as soon as you commit to saying good-bye the laws of corporate Karma will cause an executive to ask for one of the messages you just dumped. 

What’s a busy person supposed to do? I get Outlook to put all my mail into folders by year, only leaving the current year’s mail sitting in my inbox. It’s easy to setup and really helps. 

1. Open Outlook.

2. Create folders for each year under your Inbox. I personally create a folder named Archive and then folders for each year under it.

3. In the Outlook Ribbon click on Rules -> Manage Rules and Alerts.

4. Click the New Rule button.

5. Choose “Apply rule on messages I receive”.

6. Select the box next to “received in a specific date range”.

7. In the Step 2 window (lower); click on the underlined words “in a specific date span”. 

8. Use the pop up to enter the start date (Jan 1st of the year you are building the rule for) and the after date (Dec 31 of the same year).  

9. Click Next and select the check box for “Move it to the specified folder”. 

10. Click the word specified and browse to the year’s folder to select it. 

11. Click Next to add any exceptions. 

12. Click Next again and check the “Run this rule now…..” check box. 

Repeat the process to create rules for each year. Your inbox will be cleaner, your Outlook will respond quicker, and your email administrator will get off your back.