Sunday, January 23, 2011

Sending Updates to Google Calendar via Email

Like a lot of people, I use Outlook at work, and I'm not allowed to install software to my work computer. So when it comes to syncing my work calendar on Outlook with my personal calendar on Google Calendars, I was having some difficulty. We're also prevented from logging into Google at work, so exporting/importing wasn't even an option.

It occurred to me that I could write a macro in VBA which would, upon any addition, modification or deletion from my Outlook Calendar, automatically send an email to... somewhere...

In doing some research, I found out that there is no built-in ability to email information directly to your Google Calendar. There's a free website,, which does something kind of similar, where they act as the middle man. But they're limited to only using Google's Quick Add mechanism, which is extremely limited. And there's no way to edit or delete using .

So I spent about 8 hours writing code over the last few days and I have an alpha version of a program that is working really well. Here's how the system works...
  • A VBA program/macro in Outlook sends an email with detailed information of appointments that are added, deleted or modified. It uses a pseudo-XML format I made up, probably incorrectly. 
  • The email is sent to an account I made up just for this purpose. The only e-mail it should receive is the e-mail generated by my Outlook macro.
  • My program runs on my Windows 7 computer at home. It logs into that email account, pulls the email out of there, and extracts the data from the XML code inside.
  • It then logs into the specific Google Calendar I have selected as my "Work" calendar.
  • Depending upon the type of action it needs to take (add, change, delete) it takes that action upon the Google Calendar, and logs any changes it has made in a log file.
  • I set the program up using Windows Task Manager, so it runs every 5 minutes from 7am - 7pm M-F and once an hour every other time.
I still have a fair amount of fine tuning to do, but as it stands right now, it works really great. I had to overcome a number of challenges for this project - questions that you'll find asked on forum after forum with no usable answers.  

This script is written in Perl using Strawberry Perl for Windows 7, but would work just as well on a linux/unix box as a crontab scheduled program. It relies upon Simon Wistow's Net::Google::Calendar module on CPAN, so you'd have to install that if you haven't already.

I got a lot of helpful snippets of code from two other people, so many thanks to:
I still need to do a little better job with documentation and error handling and logging, but the thing works pretty well. Once I'm happy with that, I'll post the code (for the VBA macro and Perl script).

Version two of the program will do the sync in reverse - taking additions/changes/deletions that happen in Google Calendar, sending that info to Outlook and having those actions carried out on the corresponding Outlook appointment item.

While this exactly meets my need to sync calendars in a world where I can't install sync software, the overall usefulness of this type of "email to Google Calendar" seems so obvious to me it's shocking that Google hasn't implemented this functionality itself.

I'll share more details later, along with all of the code, but if you have specific questions in the meantime, leave them in the comments.

No comments:

Post a Comment