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, myeventbot.com, 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 myeventbot.com .

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:

• Johnny Morano and his Timesheet to Google Calendar Importer
• PoisonBit and his program to Paint System Login Sessions on a Google Calendar

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.

1960's era Speakman-Kent Shower Valves

This is what I was faced with. The hot water valve kept dripping.

I had a bit of trouble getting the right information, but now that I have it, I wanted to share it. If you have Speakman Kent Shower Valves and you want to try replacing them, or replacing a washer (like I had to), you may find this information helpful.

Apparently, almost every shower valve is removed by using a special "bonnet wrench" that turns a six-sided nut type thing (which is actually part of the valve itself) deep inside your shower wall. Well, the Speakman-Kent valves in our house didn't have any any-number-of-sided flat areas inside or outside the wall to attach a wrench to. Everywhere I looked on the internet people were telling me that they were there, I just had to look harder or open the wall a bit more. They were all wrong.

The valve with handle removed. The nut-type thing you see there is the packing nut.

See? No bonnet nut deep in the wall. Just black pipe in there.

Another angle - no bonnet nut. And they wanted me to rip open the walls...

I went to the locally owned hardware store and the nice older guy there told me that the valve just backs out by unscrewing it, as if you were turning the water on and on and infinitely on, once you have removed the packing nut. He suggested putting the handle back on and unscrewing, which I had tried before to no avail. But my previous attempts had been a bit half-hearted, since I wasn't sure it what I was doing was right.

Once I got the "hardware store old guy" seal of approval, I put the handle back on, put on my gloves for extra grip, and lo and behold, the valve kept on turning and came right out. I was able to flip the washer over to re-use it, and then screwed the sucker back in, replaced the packing and packing nut, and I was back in business.

Anyway, in case you're searching for help on removing Speakman-Kent shower valves from the mid-60's (or possibly other eras), I hope you'll find this information useful.