Creating an Appointment in Outlook 2010

I got a new job about a year ago and I went from using Google’s calendar to Outlook. I’ve added some code to Outlook to handle emails a little more like GMail does, but one thing I’ve missed is the ability to add something to the calendar easily. According to Google, you can enter multiple properties of the appointment in one string like “7pm Dinner at Pancho’s” and it’s awesome.

It’s not perfect, though, so I didn’t want to just replicate the function, I wanted to improve it. For one, Google doesn’t deal with fractions of hours very well. Now I can type a narrative in a textbox and create an appointment.

And that opens a pre-filled appointment like this

I started trying to parse the text with a lot of Split() functions, but it quickly became cumbersome. Not impossible, just not very elegant. To be more fancy, I ignored this advice:

Some people, when confronted with a problem, think
“I know, I’ll use regular expressions.” Now they have two problems.

To be perfectly honest, nobody ever confused me with someone who could write regular expressions beyond the incredibly simple ones. But I gave it a go and eventually got some help from stackoverflow and from Rick Measham.

The rules go like this:

  • Start with a time. Can be 5, 5:00, 5pm, 5:00pm, 5 pm, 5 pm CST, 5pmPST and a bunch of other stuff
  • Then the subject or title of the appointment. Stop capturing when you get to " for " or " at " because those are keywords indicating other information.
  • If you get to " at ", everything after that is the location. Stop capturing when you get to " for ".
  • If you get to " for ", everything after that is the duration in hours.

The regex looks like this:

^((?:1[0-2]|0?[1-9])(?::[0-5]\d)?)\s*([ap]m)?\s*([ECMP][DS]T)?\s*(.*?(?=\s+for\s+|\s+at\s+|$))(?:\s+at\s+(.*?(?=\s+for\s+|$)))?(?:\s+for\s+(\d*(?:\.\d+)?)\s*hour)?

Simple, huh? I’ll wrap up this post with a discussion of the entry point procedure. Tomorrow, I’ll discuss the code behind the form.

Public Sub MakeGoogleAppointment()
   
    Dim dtStart As Date
    Dim dtDay As Date
    Dim ufGoogle As UGoogle
    Dim ai As AppointmentItem
   
    'if the user is on a calendar, get the date and/or time
    On Error Resume Next
        dtDay = Int(Application.ActiveExplorer.CurrentView.SelectedStartTime)
        dtStart = Application.ActiveExplorer.CurrentView.SelectedStartTime - dtDay
    On Error GoTo 0
   
    'if their not on a calendar, assume today
    If dtDay = 0 Then
        dtDay = Date
    End If
   
    'Get the rest of the string via a form
    Set ufGoogle = New UGoogle
    ufGoogle.Day = dtDay
    ufGoogle.When = dtStart
    ufGoogle.Initialize
    ufGoogle.Show
   
    'create the new appointment
    If Not ufGoogle.UserCancel Then
        Set ai = Application.CreateItem(olAppointmentItem)
        ai.Start = ufGoogle.When
        ai.Duration = ufGoogle.Duration * 60
        ai.Subject = ufGoogle.What
        ai.Location = ufGoogle.Location
        ai.Display
    End If
   
End Sub

The first section attempts to get whatever is selected if the user is looking at a calendar. The SelectedStartTime property returns a Date. It’s only the date portion if the user is on Month view (pretty much the only view I use). It includes both the date and time if the user is on a view that has times. The Int() function gets only the date by lopping off the time if it exists.

If the date is zero, then the user isn’t on a calendar view and I set the date to today.

Next, I instantiate a new userform, pass in some data via Property Let procedures, and run some setup code in an Initialize method.

Finally, if the user doesn’t click Cancel, a new AppointmentItem is created. The duration in my narrative is in hours, but Outlook’s Duration is in minutes, so I multiply by 60 to convert it. The new AppointmentItem is displayed for the user to add more information of change things.

One Comment

  1. snb says:

    Why don’t you introduce a ‘appointment’ separator ?
    If the user types

    “13:00_2:30_Department Meeting_Conference room B”

    with sn=split( ,”_) you would simply get start, duration, subject and location

    with createobject("outlook.application").createitem(1)
      .start=timevalue(sn(0))
      .duration=DateDiff("N", 0, TimeValue(sn(1)))
      .subject=sn(2)
      .location=sn(3)
    end with

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: