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.

And so it begins

We've been in the house for almost a full week, and the work has already begun. I'll leave it to Chris to do the official recaps, as he has done the lion's share of the renovating work, whereas I've done more in the "unpacking" and "lawn mowing" and "wire fishing assistant" areas instead. But since I'm the one taking most of the pictures and also the one who has them on my computer, I thought I'd give a quick photo recap of what's gone down in the first six days:

We closed on the house at 1:00 last Thursday. By 4:00, we were removing carpet - wall to wall in the living room, dining room and upstairs hall, as well as large (and dusty!) area rugs in all three bedrooms and of course all the really, really nasty padding that was underneath every last, square inch.



Fortunately, we were relieved and delighted to discover that the hardwood floors underneath the carpeting look like this on the main level:



and are only slightly more worn in the bedrooms, where it appears someone once polyurethaned only around the edges of the area rugs instead of, you know, removing the rugs and doing the whole floor.

The next big project was rewiring the house. There was cable in the kitchen and family room, and an internet connection in the office, but that was about it. We wanted cable, internet and phone accessibility in every bedroom, the living room, a different place in the family room and in the office. Chris busted out some mad wire-fishing skills and managed feats like this:



finding teensy-tiny holes and spaces in the basement from which to run wires up to the upper three levels of the house. Well, not all the holes were teensy-tiny...



It's okay - these are all in the closet in the office. We need practice with patching drywall, anyhow.

Once he got the basic wires pulled throughout the house, the next item on the list was wallpaper removal. We have what seems like miles of wallpaper in the house, so Chris tried out the WallWik system we'd seen on one of the DIY shows and discovered that it worked really well. Whew.



The biggest concern is keeping the solution off those hardwood floors we uncovered... down in the office it wasn't that big a deal since the floor is just asbestos tile that is going to be carpeted over, anyhow. For the rest of the house, it will require more careful floor protection.

Tomorrow, I am going to pick up a gallon of "Garden Moss" colored paint - it is a fairly neutral green. We're going to test it out for the family room and office, and if we like it, that's what we'll be putting up on the walls this weekend for the lower level. If we don't like it, we'll be back to the Soft Ecru (now called Soft Linen, apparently) that we used and liked in our old house.

List of Projects

In the rough order in which we plan to tackle them, here is the list of projects we intend to start working on once we move in:

1. Paint over the dark wood panelling in the family room and take down wallpaper and re-paint the office. Once that's done, we'll have wall-to-wall carpeting installed over the vinyl tile.
2. Strip the wallpaper and paint our 6-year-old daughter's room. Getting her feeling comfortable and settled early in the process will help her in the transition.
3. Gut and re-do the kitchen, including taking down a non-load-bearing wall, stripping wallpaper, laying a new porcelain tile floor, installing new cabinets and counters and re-painting. This could take a while.
4. Strip the wallpaper from the dining room, living room and foyer and re-paint.
5. Strip the wallpaper from the master bedroom and re-paint. Possibly install ceiling fan.
6. Replace the vanity and sink in the upstairs bathroom, install new vent fan, strip wallpaper and paint. Possibly retile the floor.
7. Strip the wallpaper from the master bath, replace vanity and sink and repaint. Possibly retile the floor.
8. Strip the wallpaper and repaint the hallway.
9. Strip the wallpaper and repaint the guest room.
10. Strip the wallpaper and repaint the downstairs half bath.
11. Paint over the dark wood panelling in the laundry room.

As part of the interior work, I plan on running new wires to just about every room for cable, internet and telephone. My plan is to have two Cat 5e (one for voice and one for data) and one coaxial cable outlet in every room, all terminating in a central distribution hub in the basement. Most of the rooms will be accesible from the crawl-space attic or from the basement below, so fishing the wire shouldn't be too much trouble.

In addition, there are some exterior projects which will be weather-dependent:

1. Re-paint the trim (going from robin's egg blue to a forest green).
2. Powerwash patio pavers and re-set to slope away from foundation.
3. Powerwash then paint or drylok concrete stairs leading to backyard.

It's an ambitious list, but other than the kitchen, it's not really rocket science. We've got this great wallpaper removal system from Wallwik which is supposed to make that task a piece of cake. And we have lots of painting experience from preparing our house for selling.

What started it all...

We're moving from a house we had built in 2000 to a 1965 split level currently occupied by the original owners.

The house is in fantastic condition, as are all of the original 1965 fixtures and 1965 wallpaper.

We're looking forward to getting in there Thursday and starting the process of updating every room -- from the dark wood-panelled family room, to the Pepto-Bismol pink master bathroom, to the closed-off dark kitchen.

We are planning on doing all the work ourselves. We have watched a lot of HGTV and DIY Network. We might be over confident. We might be in over our heads.

We are almost certainly crazy.

We plan on updating our progress as we move through the various projects. We have learned a lot from other people's project blogs, and we hope someone else as crazy as us might find it helpful.

At the very least, we'll let you know where to get your straitjacket.