Generate custom calendar in Word from Outlook 2003 data

My boss has a strong preference for having a hard copy of his six-month calendar that he carries around and on which he and his wife make updates and notations of personal appointments and events.  

His assistants maintain his official calendar on Outlook 2003.  We tried printing out the six-month calendar from within Outlook (using the normal print function) but he is very picky about the design of his six-month calendar and says that it can't have those boxes Outlook insists on printing around the name of each day.  He also dislikes having extraneous details included in the six-month calendar (we use the details section to remind ourselves of things like the contact person for the event or the need to get the speech writer to draft speeches) and goes ballistic when he sees "12:30 p.m. - 1:30 p.m. Lunch" on his calendar as the only entry for a day.  Final fussy detail:  he wants to see an entry for every single day, even if that day has no appointments, in case he wants to add a personal appointment on the weekend or on an otherwise dull weekday.

His first assistant (who resigned to get away from him) had one of the junior staff laboriously type every entry from Outlook into Word, following the Boss's preferred format and applying the rules on what to include and what to omit by hand.  Not surprisingly, this has not proven to be an ideal solution because it's labor-intensive AND the junior staff didn't always remember to update the Word version when the Outlook version changed, so Boss was sometimes working from very old information.

The second assistant (who also resigned for much the same reason) was the one who tried to sell him on the Outlook-generated calendar but he wouldn't buy it (plus we had the problem with no entries for "empty" dates and too much information included in details).

I recently got roped into trying to figure out a way to generate the hard copy calendar from the Outlook data, meeting all his requirements but avoiding the hand-typing method.  It CAN be done but it uses almost every program in MSOffice and I'm concerned it's too complex a process to hand over to the junior staff.

My solution thus far has been:

Use Import/Export Wizard to export Outlook data to an Excel file.

Inside Excel, convert the date and time information from Outlook's text format to a date format.  I've been using DATEVALUE to copy the Outlook date column to a new column.  I then copy "values" from the new column into a third column and delete the original Outlook date and the formula-filled column.  Ditto the two times (StartTime and EndTime).

I then create "empty dates" by autofilling dates-only at the end of the table.  These end up being the entries for the "empty dates" the Boss wants to have included.

I sort the columns a number of times and a number of ways to get rid of the pesky "Lunches" and to clear out the "12:00 AM" starting times Outlook attaches to "all day events."

With one last sort, I go through and delete the "empty dates" for those days which DO have at least one appointment.

Next, I use "get external data" in Access to pull the Excel spreadsheet data into an Access table.  The Access table is the recordsource for an Access report that meets the Boss's demands for grouping by day and grouping by month.

From within the "preview report" pane, I use Share Office Tools to create a Word Rich Text Format file.

I save the .rtf file as a .doc file and use Word for final editing and formatting.

Does anyone have any suggestions of a more efficient (and more idiot-proof) way to get to the final Word file?

For the record, the fields I end up dealing with are:
StartDate (date/time)
StartTime (date/time)
Subject (text; the "main title" of the event)
Location (text)
EndTime (date/time)
Description (text; the info included in the details section of the Outlook appointment)

To help you frame your answers, I have a lot of experience programming VBA in Access but have never tried to use VBA in any other MS Office application.  I do know what a macro is and can usually be trusted to copy code correctly, but I might need to be coached through how to add code to non-Access Office programs (never tried it and nervous about giving it a go).

I have loads o' Experts Exchange points available and am willing to be generous with awarding them if we can get this down to a junior-staff exercise that's close to turn-key in its simplicity.

Feel free to give input on just a segment of the problem.  I noticed, for example, an EE answer to the "Outlook exports dates and times to Excel as text" problem that uses a macro to clean up the data (instead of having to create, copy, and delete columns).  A rewrite of that solution tailored to my problem would be greatly appreciated.  I'll award 500 additional points via other questions for any segment that works for me. (I'm sure we can work around the point-awarding problem with some patience and creativity.)

Thanks in advance for your help.  I just as soon not be the third assistant to solve this problem by running away from it.

Who is Participating?
Jeroen RosinkConnect With a Mentor Software testing consultantCommented:
Hello Kitty,

perhaps this is a way of working for you as you mentioned to replicate the macro's in access. an option is to call excel from access and use excel function in that macro.

perhaps following link might help you:
Jeroen RosinkSoftware testing consultantCommented:
Thanks Kitty,

But to me it is OK if you ask for refund and deletion of this question with the comments that you asked this issue in excel board and it is solved.

I would suggest that he got a PDA/Blackberry or something similar. Most PDA's come with synchronisation abilities. This means that every day he/she can put their PDA into it's cradle and the computer and the PDA with copy across all changes to Calendar and Contacts (as well with some My documents/photos etc) that have happened either on the PDA or computer.

He can then carry around a small PDA, make all changes/additions on that and save a few trees.

A good PDA is the iPaq, which costs very little but is quite good - my bosses use them and never bother with paper diaries now.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

prettykittyqAuthor Commented:
Waiting to hear from Dave before I split the points.

MalicUK,  I fully agree that it would be preferable for The Boss to use a PDA and save us all a lot of grief.  (All of his staffers have Blackberrys and we use them constantly.)  Unfortunately, The Boss is 72 years old and has never logged into his personal computer or used the Blackberry the agency allocated to him.  He makes his assistant dial his cell phone for him (when he's in the car) and usually calls the Chief of Staff into his office if he wants to use his television set.  Go figure.

We can't teach an Old Boss new tricks, so the hard copy calendar is a given.  With your collective help, I've got the process down under an hour and I'm pretty sure the instructions are goof-proof, so...

From the bottom of our hearts, we thank you.

Points on their way....

MalicUKConnect With a Mentor Commented:
HAHAHA! that's quite good. All I can say is I feel for you :)

Well, in that case to make life easier how about doing a mail merge of the data once it's in excel and all the changes have been done to it? Then you could set up the formatting in the mail merge and just run it off when you wanted.
prettykittyqAuthor Commented:
I did try using mail merge in Word using Excel as my data source but ran into two problems.

First, the times show up as "10:37:00 AM" and I haven't been able to get rid of the seconds formatting no matter what I do.

Second, I haven't been able to figure out how to group on days and months so that each month begins on a new page and each day gets a header with the appointments listed neatly beneath it.  

Since I'm much better at messing about with Access than I am with Excel and I know how to do the grouping stuff in an Access report, I just went ahead and exported the nice cleaned-up data from Excel (thank you again) into Access and set up my report there.

Why then (you may ask) didn't I just export the data from Outlook directly into Access and eliminate the Excel middleman?  

I tried "export to an Access database" at first but found that Outlook creates its own database rather than letting me export to an existing database.  I also tried saving the Outlook data as an Excel file and then just importing that to my existing Access  database but found that Outlook exports the date and time data as text strings so Access gave me a "data type mismatch" error.  Hence, I do the data clean up in Excel (brilliantly now that I have a bunch o' macros to use) and just run it through Access to create my report (which then gets exported to Word for final clean up).

You have to keep in mind that the people who are (hopefully) going to be producing this calendar are secretaries, not programmers, so I need to be able to to tell them to just "run this macro" and "click this button." With your help, it's now pretty much automated which means I should be able to hand over some very detailed instructions and get back to my normal work of deciding whether or not U.S. corporations should be allowed to give out stock options to their bright young programming whiz kids :-P

(I must say that now that y'all have given me the code to do what I want, I might decide to replicate the macros in Access and do the data modification that way.  But not any time soon.)

Thanks again.


prettykittyqAuthor Commented:
Question closed, problem sorted, Kitty got a $500 bonus from the office for her plagiarized brilliance, The Boss is happy, all is right with the world.


Another Satisfied Customer
Jeroen RosinkSoftware testing consultantCommented:
Thanks kitty for your Bonus to us.
Normally I would wish I had a boss like you have but looking back at the stories above Im not sure the $500 will cover the pain:)
prettykittyqAuthor Commented:
The Great Calendar Project Update:

Last Saturday (Oct 9) when I was struggling with all these data clean-up things, it took me about 45 minutes to generate the Word document (and that was fast and full of potential user errors).

Today, having adopted all the lovely macros you guys wrote for me, it takes under three minutes and is goof-proof.

Ain't life grand.

Thanks again for all your help.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.