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.
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