Automating the outlook scheduling assistant to find the perfect meeting day

McKnife
McKnife used Ask the Experts™
on
Hi fellow experts.
**I have just now received a response in my german admin forum which looks just right. Will share it after testing**
--
Imagine the following scenario: I want to invite half the company (about 40 persons) to a meeting some day in october and I am trying to find the perfect day=the day when most people are available. I only know, it should be from 11-12 AM. Short of testing every day in outlook's scheduling assistant, is there maybe a powershell command for the exchange management shell, that I can run that will retrieve the day that has the lowest number of conflicts?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
Use a Doodle poll. Made for this

https://doodle.com/
Distinguished Expert 2018

Author

Commented:
Right, but that is not within the scope of the question. We are not talking about alternatives to outlook but about a scripted way to use what is already there.
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
Understand, but given the situation and description, it seemed an alternative was a reasonable approach.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2018

Author

Commented:
Not really. If I wanted to know this today, how would I make people use doodle "now"? Don't forget that many people are not even in the office, today (and might be on vacation). Our guys rely on their calendar heavily, so it's reasonable to stay within outlook/Exchange.
AmitIT Architect
Distinguished Expert 2017

Commented:
Distinguished Expert 2018

Author

Commented:
Thank you Amit, but for reasons given before, I need something that works right away, without having to wait for users to use it.
AlexSenior Infrastructure Analyst

Commented:
Would https://findtime.microsoft.com/OfficePlugin/PluginCompose?_host_Info=Outlook$Win32$16.02$en-US

That work for you MK?

You need o365 for it though as a prerequisite, if not then give me a minute and I'll check again.

https://www.groovypost.com/howto/use-microsoft-findtime-addin-outlook/ Full breakdown of what it does there.

Regards,
Alex
Distinguished Expert 2018

Author

Commented:
Alex, thanks, but findtime is not suitable and was suggested before.
AlexSenior Infrastructure Analyst

Commented:
The other users don't need to use findtime, just you :-)

I missed that one btw, but essentially just you need it
Distinguished Expert 2018

Author

Commented:
Please read my previous response as to why it is not suitable. It is not applicable anyway as we don't have O365, but Office 2016 Standard/ProPlus.
Distinguished Expert 2018

Author

Commented:
I have just now received a response in my german admin forum which looks just right. Will share it after testing.
AlexSenior Infrastructure Analyst

Commented:
Ok,

Utilizing powershell I don't think is going to happen unfortunately, the main reason for this is the calendar commands available within powershell for exchange don't take the actual meetings into consideration. I mean you could make it run on all their machines to pull down dates.... But yeah, this is quite an interesting one.

Give me a bit and I'll see if I can't find something.
Distinguished Expert 2018
Commented:
# FQDN of the Exchange Server
$EXCHANGESERVER = 'ex.domain.tld'
# Array of meeting attendants
$users = 'someuser@domain.tld','anotheruser@domain.tld'
# Begin of Meeting
$starttime = [datetime]'11:00:00'
# duration of Meeting in minutes
$duration = 60
# check how many days in advance
$daysinadvance = 30

if ($PSVersionTable.PSVersion.Major -lt 3){write-host "ERROR: Minimum Powershell Version 3.0 is required!" -F Yellow; return}

# Funktion zum Verbinden mit dem Exchange EWS Dienst
function ConnectTo-EWS {
    param(
        [parameter(mandatory=$false)][bool]$UseScriptCredentials = $true,
        [parameter(mandatory=$false)][pscredential]$Credential,
        [parameter(mandatory=$false)][ValidateScript({Test-Path $_})][string]$ewsdll = "$psscriptroot\Microsoft.Exchange.WebServices.dll",
        [parameter(mandatory=$false)][string]$mailbox = $null,
        [parameter(mandatory=$true)][string]$server
    )

    # Allen Zertifikaten vertrauen
    try{
        Add-Type '
            using System.Net;
            using System.Security.Cryptography.X509Certificates;
            public class TrustAllCertsPolicy : ICertificatePolicy {
                public bool CheckValidationResult(
                    ServicePoint srvPoint, X509Certificate certificate,
                    WebRequest request, int certificateProblem) {
                    return true;
                }
            }
        '
        # Trust all certs policy dem ServicePointManager zuweisen
        [System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
    }catch{}

    # EWS DLL laden
    Add-Type -Path $ewsdll

    # EWS-Service Objekt erstellen
    $ews = new-object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010)
    $ews.Url = "https://$server/ews/exchange.asmx"
    $ews.UseDefaultCredentials = $UseScriptCredentials
    if (!$UseScriptCredentials){
        $ews.Credentials = $Credential.GetNetworkCredential()
    }
    if($mailbox){
        $ews.ImpersonatedUserId = New-Object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $mailbox)
    }
    return $ews
}


# Mit Service verbinden
$svc = ConnectTo-EWS -Server $EXCHANGESERVER
# collection für Teilnehmer erstellen
[Microsoft.Exchange.WebServices.Data.AttendeeInfo[]]$attendees = new-object "System.Collections.Generic.List[Microsoft.Exchange.WebServices.Data.AttendeeInfo]"
# Teilnehmer der Collection hinzufügen
$users | %{
    $a = new-object Microsoft.Exchange.WebServices.Data.AttendeeInfo
    $a.SmtpAddress = $_
    $a.AttendeeType = [Microsoft.Exchange.WebServices.Data.MeetingAttendeeType]::Required
    $attendees += $a
}
# Availability Options festlegen
[Microsoft.Exchange.WebServices.Data.AvailabilityOptions]$options = New-Object Microsoft.Exchange.WebServices.Data.AvailabilityOptions
$options.GoodSuggestionThreshold = 49
$options.MaximumNonWorkHoursSuggestionsPerDay = 0
$options.MaximumSuggestionsPerDay = 24
$options.MeetingDuration = $duration
$options.MinimumSuggestionQuality = [Microsoft.Exchange.WebServices.Data.SuggestionQuality]::Good
$options.DetailedSuggestionsWindow = New-Object Microsoft.Exchange.WebServices.Data.TimeWindow((get-date),(get-date).AddDays($daysinadvance))
$options.CurrentMeetingTime = $starttime
$options.RequestedFreeBusyView = [Microsoft.Exchange.WebServices.Data.FreeBusyViewType]::FreeBusy

# FreeBusy mit den Optionen abfragen
$result = $svc.GetUserAvailability($attendees,$options.DetailedSuggestionsWindow,[Microsoft.Exchange.WebServices.Data.AvailabilityData]::FreeBusyAndSuggestions,$options)
# Vorschläge ausfiltern
$suggestions = $result.Suggestions.TimeSuggestions | ?{($_.Quality -eq 'Excellent' -or (($_.Conflicts.FreeBusyStatus | ?{$_ -eq 'Free'}).Count -ge $users.Count-1)) -and $_.MeetingTime.toString('HHmm') -eq $starttime.toString('HHmm') -and $_.MeetingTime.DayOfWeek -in 1..5}  | sort MeetingTime | select MeetingTime,@{n='Conflicts';e={($_.Conflicts.FreeBusyStatus | ?{$_ -ne 'Free'}).Count}}
# und darstellen
$suggestions | ft -AutoSize

Open in new window


That is the solution. It requires the Microsoft.Exchange.WebServices.dll from https://www.microsoft.com/en-us/download/details.aspx?id=42951 (don't install it on your server as it destroys its performance - you just need the dll).
AmitIT Architect
Distinguished Expert 2017

Commented:
Thanks for sharing the solution.
Distinguished Expert 2018

Author

Commented:
Welcome. To clarify it even more: the dll just needs to be in the same folder as this powershell script.
The script takes the days monday to friday into account, not weekends. I have not translated all script comments - if you need to, its german.

Commented:
thanks for sharing, is this for Exchange Server 2013 or can be executed against Exchange Online (Office 365) ?
Distinguished Expert 2018

Author

Commented:
As far as I know and understand, it can be used on on-premises Exchange 2013 and Exchange 2016. No experience with O365. You can try it, it will not hurt.

Commented:
Cool, many thanks for the clarification McKnife.
Distinguished Expert 2018

Author

Commented:
And I should have given credit to https://www.administrator.de/user/colinardo/ who really deserves it. A true grandmaster at scripting.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial