Taming Outlook meeting requests (part 2)

Yesterday I published a post about Taming Outlook meeting requests, in which I talked through the writing of a script which checked for working hours and calendar availability, before automatically accepting any meeting which met the requirements.  And then I left you hanging... Sorry about that!

Well here I am to pick up where I left off.  We're going to look at actually running the script!

First of all, you'll need to slightly drop your security settings, whilst you're developing.  We'll come back to fix this at the end, once we're happy the script is working.  The setting I'm referring to can be found in Outlook (I'm using 2016) here...

File > Options > Trust Center > Trust Center Settings > Macro Settings

Hopefully you've either got this set to "Disable all...", which would be the most secure (but possibly a little limiting" or "Notifications for digitally signed..." which is the second most secure.  We haven't digitally signed our script (yet!) so we'll to set this to "Notifications for all macros".  

Please be careful to only click yes on prompts for this script, or others that you know about, and don't blame me if you click yes on something dodgy!

Now we need to go and create a new mail rule.  This can usually be done by choosing "Rules" from the ribbon menu and then "Create a rule", but hopefully this is something you're familiar with, feel free to skip out and Google this is you're not.  

Now you've started creating a new rule, you'll want to select the conditions "on this computer only" (because the script only exists on this computer) and "which is a meeting invitation or update" (so that it doesn't process your script for every single email, something like this...

Then click the "Next" button and you'll want to select the action "run a script".  If you're in a newer version of Outlook (anything from 2013 onwards, I believe) then you may find that you don't have this option, in which case, you'll want to add the following registry entry and then restart Outlook...

Entry: HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Outlook\Security

Dword: EnableUnsafeClientMailRules

Value: 1

You can also download the .reg file from my site, if you would like.  It should be noted that this is for Outlook 2016, and it might be different (not version 16.0) for other versions.

Once this is down and you've restarted, you should be able to select the "run a script" option.  

At the bottom you then have a link to click which says "a script", which will allow you to select the script you've written and compiled in part 1.

If you're following this blog then this is almost certainly going to only give you one script to select, but if not, look for the one that matches the name you used - for me this is "Project1.ThisOutlookSession.AutoAcceptMeetings", and click "OK".

You can then complete the wizard, adding an exceptions you want (none for me) and then setting the name (something massively original like "Auto Accept Meetings") and when it should run, etc.

You can now test, either on existing meeting requests in your inbox, or by waiting for the next one to come in!  If you've left those Debug.Print lines in then it might be worth hitting Alt+F11 in Outlook to get back to the script editor, and then pressing Ctrl+G in there.  This will bring out an "Immediate" panel at the bottom, and this is like a console, which will display all of the debug messages when they happen.

Once you've got your script working, it's time to go back and sort out your security.  The way that we do this, is by self signing the script.  There are a number of guides online about how to do this, but I'm going to quickly walk you through the steps I took on my Windows 10 machine.

Firstly, find "SelfCert.exe" - this is the Office application that we will be using to create the certificate.  For me, this was located here...

C:\Program Files (x86)\Microsoft Office\Office16\SELFCERT.EXE

Run this application and it should look like this...

There's a box at the bottom to enter your certificate name, so just enter whatever you like and click "OK".  You should get a message to say it's been successfully created.

Now go back into the script editor by pressing Alt+F11 in Outlook, and from the "Tools" menu select the "Digital Signature" menu item.  In this screen you can use the "Choose" button to find your certificate and select it, and then click "OK".  That's it, signed!

Now exit Outlook and run it again "as administrator".  You should then run your rule, manually if it doesn't happen automatically, and you'll get a popup asking you if it's ok to run the macro.  Personally I went with "Trust all documents from this publisher", as I am the publisher, and I trust myself.  You can now set your Trust Center settings back, so that only signed macros are prompted for.

You can now exit Outlook and run it again normally.  You should have Outlook meeting request bliss now, as your rule will run, which will run your script, and automatically accept all those meetings requests for you.  As you've trusted yourself, you shouldn't get any more prompts.  

Ahhhh, that's better!

If you're interested, you can download the .cls file from my site, which contains all the code.

Taming Outlook meeting requests (part 1)

If you're anything like me, and to be honest, for your sake I hope you're not, then you spend a lot of your time either in meetings, or in Outlook processing meeting requests!

Wouldn't it be nice if I could get Outlook to sort out meeting requests automatically?  Oh, but it can, I hear you cry.  But have you seen the options available?

Do I want to automatically accept everything?  Well ideally people would check my diary before inviting me to meetings, but inevitably they don't, so no, I don't want a tangled mess of double/triple bookings to sort out, thank you very much!

Do I want to automatically decline meeting requests that conflict?  No, what if someone invites me to an important meeting that conflicts with a casual and easily movable catch up meeting?  

Do I want to decline recurring meeting requests?  Why would anyone want to do this?  

There has to be a better way!!  And there is, if you're prepared to break out a little VBA (Visual Basic for Applications).  Having spent quite a lot of time doing VB Script for MediaMonkey add-ons (please excuse the rather aged site should you click that link), I was more than happy to dust off my rusty skills and give it a go.

To get started, in Outlook (and I'm using Outlook 2016, so I apologise if there are any differences in your version), click Alt+F11 to bring up the built in editor...

In the Project tree in the left pane, select "Project1 (VbaProject.OTM)", then "Microsoft Outlook Objects" and finally "ThisOutlookSession".  This will present a code window on the right which is blank, ready for you to add your code.

Outlook will call your sub procedure (more on this later!) and pass in a single parameter, the Meeting Item object for the meeting.  So let's start with a simple one that checks the object is a meeting request...

  Sub AutoAcceptMeetings(oRequest As Outlook.MeetingItem)
    On Error GoTo ErrorHandler

    If oRequest.MessageClass <> "IPM.Schedule.Meeting.Request" Then
      Debug.Print "Not a meeting request"
      Exit Sub
    End If

    Debug.Print "This is a meeting request"

    Exit Sub

  ErrorHandler:
    Debug.Print Err.Description & " [" & Err.Number & "]"
  End Sub

So we've named our sub procedure "AutoAcceptMeetings".  It takes in the Meeting Item object and checks that it's Message Class property matches what we expect, otherwise we exit out early.  We've also added in some error handling, to make debugging easier!  Whenever an error is thrown, we'll skip straight down to "ErrorHandler" and output the description and error number.

But we want a bit more than this, so instead of outputting that this is a meeting request, let's instead get the appointment that's associated with the meeting, and check that it falls within my working hours.  I don't want meetings to be automatically accepted if they're early in the morning or late in the evenings!

    Dim oAppointment As Outlook.AppointmentItem
    Set oAppointment = oRequest.GetAssociatedAppointment(True)

    Dim sStart As String
    sStart = Format(oAppointment.Start, "hh:mm:ss")
    If sStart < #9:30:00 AM# Or sStart > #4:30:00 PM# Then
      Debug.Print "Meeting outside of work hours"
      Exit Sub

    End If

This first gets the Appointment Item object and then gets the Start property, which is the date and time that the meeting starts.  From this we get just the time part as a string, using the Format function.  We can then check if this is before 9.30am or after 4.30pm.  Notice in VBA we can use the hash (#) character around the time to cast it as a time, which makes the comparison work as we want.

That's great, but what I really want to do, is check my availability in my calendar, to see if I've already got a meeting in place.  Before we can do this, we need to access our own account and check that it's an Exchange account - this part will only work with an Exchange account, and not with another type, such as SMTP.  

    Dim oRecipient As Outlook.Recipient
    Set oRecipient = Session.CreateRecipient("email@example.com")
    oRecipient.Resolve
    If Not oRecipient.Resolved Then
      Debug.Print "Recipient could not be resolved"
      Exit Sub
    End If
    If oRecipient.AddressEntry.Type <> "EX" Then
      Debug.Print "Recipient is not Exchange type"
      Exit Sub
    End If

This works by using the application Name Space object stored in the global variable "Session", and then using the Create Recipient method to get a new Recipient object for our specified email address (my own).  We can then use the Resolve method to check against the address book, and error out if this fails.  We can then check the Address Entry Type property to check if it's an Exchange account.

Now it's time to check for my availability, using the Free Busy method...

    Dim nInterval As Long
    Dim sFreeBusy As String
    Dim nPosition As Long
    Dim nDuration As Long
    Dim sTest As String
    nInterval = 15
    sFreeBusy = oRecipient.FreeBusy(oAppointment.Start, nInterval, True)
    nPosition = (TimeValue(sStart) * (1440 / nInterval)) + 1
    nDuration = oAppointment.Duration
    sTest = Mid(sFreeBusy, nPosition, (nDuration / nInterval))
    If InStr(1, sTest, "2") Then
      Debug.Print "Meeting conflicts with another appointment"
      Exit Sub
    End If
    If InStr(1, sTest, "3") Then
      Debug.Print "Meeting conflicts with out of office"
      Exit Sub
    End If
    If InStr(1, sTest, "4") Then
      Debug.Print "Meeting conflicts with working elsewhere"
      Exit Sub

    End If

This bit is potentially a bit confusing, so I'll explain what each variable is being set to in turn...

• nInterval - this is the level of detail that I want my calendar in, for me I decided that 15 minutes was the right interval/level of detail, and therefore I set this to 15
• sFreeBusy - we call the Free Busy method, with the day of the appointment (the Appointment Item Start property), the interval and then True to indicate that we want more detail - if this last parameter is set to false then we only get free/busy and not information such as tentative and out of office.  This method returns a string of characters, one for each 15 minute interval, indicating availability.
• nPosition - we need to calculate the start position in the string which matches up with the start time of the appointment, so that we can check availability specifically for this period of the day
• nDuration - we need to know the duration so that we can calculate the end position in the string which matches up with the end time of the appointment
• sTest - this uses VBA Mid function to just take the section of the string which matches up with the appointment, using nPosition, nDuration and nInterval

Now that we've got our string, we can check to see if any of the following characters match with the olBusyStatus values that we're interested in...

• 2 - Busy
• 3 - Out of office
• 4 - Working elsewhere

In any of these cases, I don't want the meeting to be accepted automatically.  However for Free (0) or Tentative (1) then we can go ahead accept the meeting.  So let's do that next...

    Dim oResponse As Outlook.MeetingItem

    Set oResponse = oAppointment.Respond(olMeetingAccepted, True)

    oResponse.Send

    Debug.Print "Meeting accepted"

    oRequest.UnRead = False

    oRequest.Delete

    Debug.Print "Meeting request deleted"

    Exit Sub

There are two sections here; the first creates a new Meeting Item by calling the Appointment Item Respond method (passing in True hides the dialog box) and we then send it using the Send method, and the second deletes the original meeting request by marking it as read (by setting the UnRead property to False - a lovely double negative) and then deleting it (using the Delete method).  If you're still reading this then you probably are something like me, and therefore don't like having any emails marked as unread in your Deleted Items, so marking it as read is very important!

Now obviously there's a lot more that we can do here.  If you click any one of those links into the relevant MSDN page you'll see that there are loads more properties and methods we can use to automate different tasks, but for now, this seems pretty complete to me.

The script is now ready to be compiled, so click on the "Debug" menu and choose the "Compile Project1" menu item.  You're ready to go!

This post has already become massively long, so I'm going to leave you hanging here, and then show you how to use this script in my next post.