The alternative ‘AutoPick’ free slot finder for Outlook Calendar
You might recognize the situation where you have to schedule meetings in Microsoft Outlook and it's impossible to find a free slot where all required participants are fully available.
Solution
To deal with this situation I wrote an Outlook Macro / Add-In that automatically finds the best slot within a pre-defined period. ‘The best slot’ is defined as the slot where most participants are at least tentatively available.
Why the ‘AutoPick’ functionality is not sufficient
Microsoft Outlook already has the feature to automatically select a slot for all required participants:
This is great functionality to find a slot where all participants are fully ‘free’ / ‘available’. In reality, though, people are hardly ever fully available, esp. higher up in the organization, unless you schedule a meeting 4 months in advance. For this reason, I created an alternative version that searches for a slot where required participants should at least be available ‘tentatively’. So a slot like this will be suggested:
Installation
In Outlook, open the code editor by pressing Alt+F11, paste the code from here, go to the scheduling assistant and right-click your ribbon, and select ‘Customize the Ribbon’
select macros:
and add the Macro to a custom ‘Macro folder’ in your ribbon:
You can rename your macro and display an icon as desired with the ‘rename’ option in this menu:
The slot finder will appear next to the regular one:
The code
Outlook exposes the calendar information of participants with the FreeBusy method. This method returns the availability of the provided participant in the next 4 weeks as a character string. Each character represents a slot of a provided amount of minutes.
Logical structure
The below code will iterate through the slots and check if all participants are fully available or partially available within a chosen time frame (users are prompted to provide the number of weeks to evaluate). If no slot is found, a new search (loop iteration) will search again with adjusted success criteria, the loop with exit (and select the slot) when the number of participants minus 1 are able to at least tentatively join the meeting. The next iteration will exit with the number of participants minus 2, etc.
High-level loop structure — with line number reference
Scroll down to view the code with line numbers.
Line 42 starts a loop that counts the number of attendees that need to be removed from the success criteria. Line 47, checks if there is sufficient availability among the participants (within the time frame). During the first round of evaluations, all participants need to be Free or Tentatively available, in the next round all participants minus 1 participant need to be available.
Line 43 starts the loop to evaluate all slots (that have been retrieved in lines 22–33).
Line 45 loops through all participants, to check if the participant is available (line 47).
If all required participants are available (line 51), then a check is done if the slot is a workday and workhour (line 54), if so, the user is notified that a suitable slot has been found (line 58).
A few more details
The meeting object is set in line 5, so the meeting attendees can be retrieved in line 6.
Lines 22–33 retrieve the FreeBusy information for the recipients and stores this information in an array. The FreeBusy information is provided as a character string. Each character (number), reflects the availability of this recipient:
When looping through the list of recipients, the character string that holds the availability information is added to an array:
Lines 36–41prompt the user to request the time frame within which the best slot needs to be found (1 to 4 weeks).
At line 43, the loop is initiated that loops through all slots that fit within the predefined time window. The ‘i ’variable represents a slot (of f.e. 30 minutes). ‘i’ also represents a character position in the availability string(s).
Delta is the number of slots from midnight of the day that this meeting has initially been set. This is required since the start date for FreeBusy has day-level granularity.
At line 45, the loop to evaluate the availability for all attendees is initiated (currently limited to 20 people, can be increased).
Line 45, is the core of the code because this line checks if the user is either 0 (Available) OR 1 (The user has a tentative appointment scheduled). The latter option (1) is where this Free Slot finder is different from the regular option in Outlook.
Conclusion
There is probably a lot that can be improved in this code. I can share that it has been a huge time saver for me and I hope it helps people out there as well. Free feel to provide feedback, many thanks!
