A user had a request: an automated way of telling people a meeting full. It's not an unusual request, 3 or 4 people ask me how to do this each year. This time it sounded like a fun project to do.

I am in charge of organizing meetings for my company (150+ people). However, depending on the conference room, there may only be space for a certain amount of people who accept meeting invitations (say 50ish people). But because the meetings are first come-first serve, once the room capacity is met I have to send individual emails to those who still accepted, but are now technically on a waiting list, alerting them to the fact there is no space left in the room.

I thought of using an automated response rule based on subject line "meeting name" and "accepted", but cannot figure out how to delay sending the response until i have 50 "accepts" in a certain folder.

While you can set the capacity in Exchange meeting rooms, you can't automatically reject a meeting when the room is full. But you can use a macro to count the replies and send a reply to those who accept after the meeting is full.

You will need to store the accepted count; to do this you can write the count to the registry or to a text file as acceptances arrive.

Which is better? Assuming your Windows account can write to the registry, I think it's the better option, in part because you won't accidentally delete it. I also think it's a bit faster.

The room counts are added to the registry at HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Outlook\Meeting Counts

I put two macros together to create this macro: the registry code from Create sequential numbers or random character keywords and Outlook AutoReplies: One Script, Many Responses. For the version that writes values to a text file, I used the invoice code from Add Sequential Numbers to a Word Document.

This is an ItemAdd macro and will process messages as they are added to the Inbox (or other folder you designate as the folder to watch.) As such, you can test it by sending a meeting request to another address and accepting it. Copy and paste the acceptance to trigger the macro.

Save Count to Registry

ItemAdd macros need the folder they are watching set in the Application_Startup macro. As written, it sets the default Inbox as the folder to watch. This can be changed. See "Working with VBA and non-default Outlook Folders" for details.

Place this code in ThisOutlookSession.

June 14 2018: Updated the macro to use the meeting location (room names), not the meeting subject. The registry entry uses the room name, start date, and subject to insure counts are unique. The original macro that used the meeting subject is here.

The meeting location and room size is stored as an array in the arrRoomName and arrRmSize variables. Add or remove locations and room sizes as needed.

The macro gets the location, start date and meeting subject using PropertyAccessors.

To save the count to a text file, replace the code block between the ' Start Registry and ' End Registry lines with the code to use a text file following this macro.

Option Explicit
Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()
Dim Ns As Outlook.NameSpace
Set Ns = Application.GetNamespace("MAPI")
Set Items = Ns.GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub Items_ItemAdd(ByVal Item As Object)
If InStr(1, Item.Subject, "Accepted: ") Then
GoTo SendReply
Else
Exit Sub
End If

SendReply:
Dim oRespond As Outlook.MailItem

Dim RmSize As String
Dim strSubject As String
Dim arrRoomName As Variant
Dim arrRmSize As Variant
Dim i As Long
Dim MeetingLocation As String
Dim MeetingStart
Dim MeetingSubject As String

Dim propertyAccessor As Outlook.propertyAccessor
' location http://schemas.microsoft.com/mapi/id/{00062002-0000-0000-C000-000000000046}/8208001E
' start http://schemas.microsoft.com/mapi/proptag/0x00600040
' subject http://schemas.microsoft.com/mapi/proptag/0x0E1D001E

Set propertyAccessor = Item.propertyAccessor
 MeetingLocation = propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/id/{00062002-0000-0000-C000-000000000046}/8208001E")
 MeetingStart = propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x00600040")
 MeetingSubject = propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E1D001E")

' Set up the array using room names and sizes
arrRoomName = Array("Room 1", "Alpha", "Board Room", "Conference Room", "Delta", "Meeting Room - North")
arrRmSize = Array("2", "3", "6", "4", "50", "35")

' Go through the array and look for a match, then do something
For i = LBound(arrRoomName) To UBound(arrRoomName)
If InStr(MeetingLocation, arrRoomName(i)) Then
RmSize = arrRmSize(i)
strSubject = MeetingLocation & Format(MeetingStart, " yyyymmddhhnn ") & MeetingSubject

' Start Registry method
' Replace with text code if desired

Dim sAppName As String
Dim sSection As String
Dim sKey As String
Dim lRegValue As Long
Dim iDefault As Integer
sAppName = "Outlook"
sSection = "Meeting Counts"
sKey = strSubject

' The default starting number.
iDefault = 0 ' adjust as needed

' Get stored registry value, if any.
lRegValue = GetSetting(sAppName, sSection, sKey, iDefault)
Debug.Print sAppName, sSection, sKey, iDefault
' If the result is 0, set to default value.
If lRegValue = 0 Then lRegValue = iDefault

' Increment and update number.
SaveSetting sAppName, sSection, sKey, lRegValue + 1

If lRegValue > RmSize Then

' End Registry code

Set oRespond = Application.CreateItem(olMailItem)
With oRespond
.Recipients.Add Item.SenderEmailAddress
.Subject = "Sorry: " & MeetingSubject
.Body = " Sorry, the room is full. You're on the waiting list. " & "You are " & lRegValue - RmSize & " on the waiting list."
' use .display for testing, .send after it is working as desired
'.Display
.Send
End With

Set oRespond = Nothing
End If
End If
Next i
End Sub

Save Count to a Text File

To save the count to a text file, replace the code between the ' Start and ' End lines with the following code.

Create a blank text file on your hard drive and update the macro with the correct filepath.

You'll also need to change this line in the macro to get the correct count:
.Body = " Sorry, the room is full. You're on the waiting list. " & "You are " & lRegValue - RmSize & " on the waiting list."

With this:
.Body = " Sorry, the room is full. You're on the waiting list. " & "You are " & Accepted - RmSize & " on the waiting list."

'replace registry code with this
Dim Accepted As String
Dim File As String
File = "C:\Users\me\Documents\macro-test.txt"

Dim objWord As Word.Application
Set objWord = New Word.Application
Accepted = objWord.System.PrivateProfileString(File, strSubject, "Accepted")

If Accepted = "" Then
Accepted = 1
Else
Accepted = Accepted + 1
End If
objWord.System.PrivateProfileString(File, strSubject, "Accepted") = Accepted

If Accepted > RmSize Then
' end text code

Run a Script Rule

To change the ItemAdd macro to use in a Run a Script rule, you only need the ItemAdd macro, not the Application_Startup section. You will need to change the macro name from
Private Sub Items_ItemAdd(ByVal Item As Object)
to
Public Sub RoomFull(Item as Outlook.MailItem)

Paste the macro in a new module (and change the name) then create a Rule in the Rules Wizard and select the RoomFull macro as the script. See "Outlook's Rules and Alerts: Run a Script" for screenshots and more information on using Run a Script Rules.

How to use the macro

First: You will need macro security set to low during testing.

To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security. If Outlook tells you it needs to be restarted, close and reopen Outlook. Note: after you test the macro and see that it works, you can either leave macro security set to low or sign the macro.

Now open the VBA Editor by pressing Alt+F11 on your keyboard.

To use the macro code in ThisOutlookSession:

1. Expand Project1 and double click on ThisOutlookSession.
2. Copy then paste the macro into ThisOutlookSession. (Click within the code, Select All using Ctrl+A, Ctrl+C to copy, Ctrl+V to paste.)

Application_Startup macros run when Outlook starts. If you are using an Application_Startup macro you can test the macro without restarting Outlook by clicking in the first line of the Application_Startup macro then clicking the Run button on the toolbar or pressing F8.

More information as well as screenshots are at How to use the VBA Editor.