Retrieving work week (start/end times) from outlook with VBA

[ker_01]

I've searched (object browser, help file, google), but no joy.

I'm pulling certain appointments (based on the contents of the subject line)
and I need to differentiate between any partial or full appointments that are
within the user's workday, and partial or full appointments that are outside
that user's workday (for the partials, I'll have to calculate the number of
hours that are actually within the workday). Different users have different
start and end times set on their calendar.

I haven't been able to find the part of the object model to show the
individual user's workday- can anyone point me in the right directions? Once
I have that I can calculate backwards to get the duration within the workday,
unless there is an easier way to do this somehow.

Thanks!
Keith

 

[Ken Slovak - [MVP - Outlook]]

Those settings are stored in the registry, at
HKCU\Software\Microsoft\Office\11.0\Outlook\Options\Calendar (11.0 is for
Outlook 2003, for Outlook 2007 it's 12.0, etc.).

The values are stored as DWORD's, you will have to reverse engineer what the
meanings are, I'm not sure how they are interpreted by Outlook. Make changes
in the Outlook settings and see which registry values change and in what way
depending on the settings you change.

 

[ker_01]

I think that kills my project.

I have code that pulls any appointment which includes "vacation" in the
title, and uses that to build a department vacation calendar. The problem is
that some folks use recurring appointments (monday, but recur 3 times) or
long appointments (start Mon 7am, ends Thurs 3pm). Right now I'm trying to
set it up so that a person with shared viewing permissions can run the
program for our whole department, so we don't have to bug each person to run
the code on their PC. In my example, the Thurs 3pm could mean 8 hours of
vacation if their worksday ends at 3pm, or it could mean 6 hours vacation, if
their workday ends at 5pm. Eventually we'll have people in another time
zone, and that will make it even tougher to figure out.

I'm surprised, because I would have thought that it would be useful
information to have available to others who are scheduling appointments. If
I'm scheduling an appointment with someone on the other coast of the US, or
someone in Europe, it sure would be nice to see their workday (other times
greyed out as unavailable) so that I could be respectful of their schedule.
Maybe that will be in Office2010...

Thank you,
Keith

 

[Ken Slovak - [MVP - Outlook]]

I don't think it will be in Outlook 2010.

I really don't see the problem. All it takes is a number of changes to the
work week settings, saved from Outlook, with checking the registry each time
to begin to see what patterns there are for the relevant settings. A
registry monitor set to filter on changes to only that registry key will let
you observe the results of every change. Then you would know what the
settings are, what they mean and how to work with them. Maybe a half day's
work at most.

 

[ker_01]

I'm flattered by your estimation of my programming skills ;-)

I'd be willing to learn how the registry setting works- the roadblock I was
referring to is that we wanted to set this up so that one person (who had
view-only permissions to everyone's calendar) could update the data from
everyone's calendars from the one PC. If the workday settings are on each
individual PC/Registry, then that one 'administrator' won't have access to
them.

I think the intermediate solution would be to have each person document
their work hours manually in my output Excel file, and have the code check
each vacation appointment against the work hours for that employee. Then the
one adminstrator can pull the actual calendar contents from there out.

Best,
Keith

 

[ker_01]

I didn't know how to do the registry monitor, but using your directions for
where to look in the registry, it was simple- posting here for future
searches on this topic.

Both the start and end times (Dwords) are hex representations of the number
of minutes into the day. So 7am = 7 hours x 60 minutes = 420 minutes; the hex
representation of 420 is 1a4. 6pm = 18 x 60 = 1080 minutes; the hex
representation is 438. Fortunately Excel has built-in tools to make the hex
conversion.

Of course, now that I'm thinking about it, that's only the final answer for
the local staff (people in the same time zone). I need to do some research on
how time zones will play into this as well...

Best,
Keith