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...
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.
No comments:
Post a Comment