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:
- Expand Project1 and double click on ThisOutlookSession.
- 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.
Hi Diane, great job, spectacular how you clear step by step set all thx.
I have office 365 and it seems not to work in this version of outlook. any ideas or direction how to make it work on this version
It should work. Do you have macro security configured properly? Do you get any error messages?
Oh I forgot to mention we are using Exchange 2010/13 in out environment for now, and plan to move to 2016 this year, so perhaps better solutions will arise for this version of Exchange or perhaps still the same issue/problem to manage meeting attendies?
at this time, 2016 doesn't close crooms but i believe it is being looked at. Whether it will ever make it into Exchange is the $64Mill question...
I generally like what you came up with, and it looks fine for a single meeting room, but how about an organization with several meeting rooms, would it be possible to adapt this macro for more rooms, and keep track of counters by all initiators of a meeting? we have around 4000 users and about 24 meeting rooms to keep track of.
It will work for any room - it's based off the subject and a max number of attendees per meeting (which isn't very workable in real life, but its just a demo macro):
arrSubject = Array("Testing this meeting", "Testing the macro", "meeting subject 3", "meeting subject 4", "meeting subject 5")
arrRmSize = Array("2", "3", "6", "4", "50")
It could work off a room name - the easiest right now would be to add the room name to the subject and add the room names to the array instead of the subject - it would work "out of the box" (as long as the meeting subjects are unique).
Better would be to check the location for the room name - I'll need to tweak the macro for that. Probably should add a a date to the registry string too, in case multiple meetings use the same subject.
See if this works any better - https://www.slipstick.com/macros/close-room-macro-2.txt
it checks the location field and tracks using the start time and subject so you don't have to worry about 2 meetings with the same name. Could add the location to the string too. (I will remove accepted from the subject before i publish it on the page):
201806142130 Accepted: Testing the new macro
>> and keep track of counters by all initiators of a meeting?
Did you want a central repository for all responses? While you can write to a text file stored on a server, the big problem is rolling the macro out to 4000 users...