In the Outlook object model, the Table object provides a light-weight row-set that allows fast enumeration and filtering of items in a folder. Each row in a Table corresponds to an item, and each column in the Table corresponds to a property of the item. You can call Folder.GetTable to obtain a Table object that represents a set of items in a folder. By default, each item in the returned Table contains only a default subset of its properties, and hence the Table object enumerates and filters faster than the Items object which contains items with all their properties. You can customize a Table to include properties other than the default properties.
As an extension to the visual how to Efficiently Getting and Setting Custom Properties in a Contact Folder in Outlook 2010, the visual how-to, Efficiently Filtering Contact Items in a Contact Folder in Outlook 2010, shows how to use the Table object to filter items in a folder based on built-in properties and custom properties. This visual how-to includes an add-in that does the following:
· When the Sales Opportunity form loads, the add-in uses the Folder.GetTable method to get the default Table object from the Opportunities folder.
· The add-in displays the data in a contact report, which is supported by a DataGridView control. The add-in shows how to manipulate the Row and Column objects corresponding to items and properties of the Table.
· The add-in supports a Filter button that selects contacts that have a specific company name value which is represented by a default, built-in property, and then displays those contacts and their default property values.
· The add-in supports a Customize Columns button that remove a couple of default properties, adds custom properties to the Table, and then displays the data.
· The add-in supports a Filter Custom Column button that selects contacts that have a specific sales representative which is represented by a custom property, and then displays those contacts and values for the customized properties in the Table.
View the video and download the add-in to try out the scenario!
See the following topics for more information about filtering items using the Table object:
Enumerating, Searching, and Filtering Items in a Folder
Adding Columns to a Table Object
Default Properties Displayed in a Table Object
Factors Affecting Property Value Representation in the Table and View Classes
Unsupported Properties in a Table Object or Table Filter
Microsoft Outlook supports properties for items and folders. For example, a MailItem object has built-in properties such as the To, Subject, and Body properties, and a Folder object has built-in properties such as the Name and CurrentView properties. Developers can create custom properties for items and folders as well.
Improving performance has been a major goal in Microsoft Office Outlook 2007 and Microsoft Outlook 2010. Outlook provides the following objects to add, remove, enumerate, get, and set built-in properties and custom properties:
· The ItemProperties and ItemProperty objects
· The UserProperties and UserProperty objects
· The UserDefinedProperties and UserDefinedProperty objects
· The PropertyAccessor object, which was introduced in Outlook 2007.
Follow these best practices when accessing Outlook properties to improve performance on Outlook:
· Use ItemProperties and ItemProperty to enumerate explicit built-in properties and custom properties, and get and set custom properties for items (except for DocumentItem object).
· Use UserProperties and UserProperty to enumerate, get and set custom properties for items (except for the DocumentItem object).
· Use UserDefinedProperties and UserDefinedProperty to add and remove custom properties for folders.
· Use PropertyAccessor to get and set custom properties for the DocumentItem object, built-in item-level properties that are not exposed in the Outlook object model, or properties for the following objects: AddressEntry, AddressList, Attachment, ExchangeDistributionList, ExchangeUser, Folder, Recipient, and Store.
· To get or set multiple custom properties, use the PropertyAccessor object instead of the UserProperties object for better performance.
· To get or set multiple properties, use PropertyAccessor.GetProperties and PropertyAccessor.SetProperties, as opposed to repeated PropertyAccessor.GetProperty and PropertyAccessor.SetProperty, for better performance.
· To set for the first time a property created by the UserProperties.Add method, use the UserProperty.Value property instead of the SetProperties and SetProperty methods of the PropertyAccessor object.
Recently we published a visual how-to Efficiently Getting and Setting Custom Properties in a Contact Folder in Outlook 2010, that shows a very common scenario – an add-in populating a custom form that contains built-in and custom properties, and importing and exporting multiple property values between a file and an Outlook folder. The add-in specifies the following:
· A custom contact message class IPM.Contact.Sales Opportunity.
· A custom form, Sales Opportunity, to display contacts of this custom message class. The custom form defines fields that correspond to and display values of custom properties described in (c) below.
The add-in shows how to do the following tasks:
a) Create a custom folder, Opportunities, for contacts of this custom message class.
b) Set the Sales Opportunity form as the default form for the Opportunities folder.
c) Use UserDefinedProperties.Add to create 5 custom properties for this folder, of various data types including DateTime, double, string, integer, and Boolean.
d) Import data for contacts from a file, create a contact, and use a single call to PropertyAccessor.SetProperties to initialize 5 built-in properties and 5 custom properties for each contact in the Opportunities folder.
e) Export data from the Opportunities folder to a file, by using a single call to PropertyAccessor.GetProperties to get the values of multiple built-in and custom properties for each contact in the folder. The add-in shows how to reference some built-in properties by the MAPI proptag namespace, and custom properties by the MAPI string namespace.
See the following topics for more information about Outlook properties:
Do you sometimes wonder how time slipped by during the work day, and you can’t recall what projects you worked on or how much time you spent on them? I usually record how I spend my time in the Outlook calendar. For example, today, I did the following:
· I spent one hour discussing article titles with my editor
· I spent two hours meeting with a vendor about the next project
· I spent 1.5 hours blogging.
I created three appointments in my Outlook calendar that represent the time I spent on these tasks*. For convenience, I also record any relevant details of the tasks in the appointment items, which often come in handy when I subsequently try to recall them for status reporting purposes.
(*Note: in this article, a “task” is synonymous with an assignment, and is not referring to the Outlook-specific item type “task”.)
Because it matters to me how much time I spend on writing, working with vendor to acquire content, and blogging, I distinguish them by setting up categories to represent each of these high level projects. The following shows the list of categories I have customized for my purposes in Outlook.
When I create an appointment for a task on the calendar, I specify the appropriate category as well. Using the same three tasks as examples, I specified the yellow "My Writing" category with the discussing article titles task, the green "Acquisition" category with the meeting with vendor task, and the acqua "Community" category with the blogging task.
Note each category is associated with a color, so that at a glance on my calendar, I get an idea what projects I have spent time on today. For appointments that are irrelevant for my time reporting, I don’t associate the appointments with any category.
Comes time reporting, I find myself using a calculator to tally time I have spent in the past week on each project. It doesn’t take long for one to wish there is a simple tool to automate the tallying and report on the time spent per project per week. I wrote a macro, FindApptsInTimeFrame, recently using VBA and the Outlook object model to do this. The algorithm is straight-forward enough for any Computer 101 class. There are however a few points of interest and assumptions:
· If you use recurring appointments to track time that is regularly spent on a project, make sure you set the IncludeRecurrences property to True for items in the Calendar folder, and sort the items based on the start of the reporting date range:
'Include all recurring calendar items -
'master appointments as well as recurring appointments.
oItems.IncludeRecurrences = True
oItems.Sort "[Start]"
Doing this would include both the master appointments as well as the individual recurring appointments when you subsequently call the Items.Restrict method.
· The purpose of calling Items.Restrict is to apply the date range filtering to obtain all appointments that fall in or overlap with that period. Notice that I mentioned overlap with the reporting period – sometimes you can have tasks that start before the beginning of the reporting period but end within the period, or start after the beginning of the period but also end after the end of the period. You would want to take these appointments into consideration as well, rather than leaving them out altogether. Specify the filter accordingly, as shown below:
strRestriction = "[Start] <= '" & myEnd _
& "' AND [End] >= '" & myStart & "'"
Note also that in such tasks that overlap with the reporting period but do not fall entirely within the reporting period, you would want to include only the amount of time spent on the task within the reporting period, not the portion outside the reporting period.
· The Categories property of an appointment is a string that is delimited by a separator. You can obtain the separator using the following function, WSHListSep, courtesy of Outlook MVP Sue Mosher:
Function WSHListSep()
Dim objWSHShell
Dim strReg
strReg = "HKCU\Control Panel\International\sList"
Set objWSHShell = CreateObject("WScript.Shell")
WSHListSep = objWSHShell.RegRead(strReg)
Set objWSHShell = Nothing
End Function
Having obtained the separator, you can use the VBA Split function to get the categories into an array, strApptCategories, for subsequent manipulation:
strApptCategories = Split(oAppt.Categories, strListSep)
· For a proof of concept, I wrote this in VBA and simplified the input and output enough to support my testing:
· I assumed the default calendar folder contains the appointments I use to track time spent on projects. Alternatively you can use the Folder.EntryID property and NameSpace.GetFolderFromID method to select a specific calendar folder that contains your time-tracking appointments.
· I allocated arrays of size 21 (20 as the upper bound on the array index, since arrays are 0-based) as the maximum number of categories that a user would specify for an appointment.
· I also hard-coded the dates for the reporting period. To specify different dates, you should do that in two places. One place is the following:
'Hard-code the reporting dates just for simplicity in testing.
myStart = DateValue("09/27/2010")
myEnd = DateValue("10/02/2010")
The other is here:
'Reformat myStart and myEnd to account for minutes.
myStart = #9/27/2010 12:01:00 AM#
myEnd = #10/2/2010 12:01:00 AM#
My plan is to add code to support more versatile input and output: allow the user to specify the range of dates and categories of interest to report on (thus determining the maximum number of categories to support for the reporting). For output, there will be additional reporting on the % of time spent on each project.
Alert: If you have appointments in your date range that start and end before the beginning of work hours you have specified in the Outlook Options dialog box, for the purpose of running Items.Restrict for that date range, you should temporarily change the Start time under Work time in the Outlook Options dialog box, so that all appointments end after that Start time value. This is a temporary work around and you can change the Start time back to the desired value after running the macro.
The following is the VBA macro. If you plan to run the macro in the Visual Basic Editor, copy the macro from the attached text file instead of this blog post. New lines in the code may be changed by display limitations on this blog site and consequently code copied from the display may not compile properly.
Sub FindApptsInTimeFrame()
Dim myStart, myEnd As Date
Dim oCalendar As Outlook.Folder
Dim oItems As Outlook.Items
Dim oResItems As Outlook.Items
Dim oAppt As Outlook.AppointmentItem
Dim strRestriction As String
Dim strApptCategories
' Set 20 as the number of supported categories, should get that number per user's decision.
Dim strAllCategories(0 To 20) As String
Dim iTotalCount As Integer
Dim iDurationPerCategory(0 To 20) As Integer
Dim strListSep As String
Dim i, j, iNumApptCategories
Dim blnExists As Boolean
Dim dtDiff As Long
Set oCalendar = Application.Session.GetDefaultFolder(olFolderCalendar)
Set oItems = oCalendar.Items
'Specify the filter this way to include appointments that overlap
'with the specified date range but do not necessarily fall entirely within
'the date range.
'Date values in filter do not explicitly include minutes.
Debug.Print strRestriction
'Restrict the Items collection.
Set oResItems = oItems.Restrict(strRestriction)
'Sort
oResItems.Sort "[Start]"
iTotalCount = 0
'Get the separator between categories from the Windows registry.
strListSep = WSHListSep()
For Each oAppt In oResItems
Debug.Print oAppt.Start, oAppt.Subject
Debug.Print oAppt.Duration
' Get the list of categories specified for this appointment.
iNumApptCategories = UBound(strApptCategories)
' An appointment that doesn't have a category (with iNumApptCategories being 0) skips this loop.
For i = 0 To iNumApptCategories
' Check if category exists in master array strAllCategories.
blnExists = False
If iTotalCount > 0 Then
' Master array already has some categories, see if there's a match or should add category
For j = 0 To iTotalCount - 1
If Trim(strAllCategories(j)) = Trim(strApptCategories(i)) Then
blnExists = True
Exit For
End If
Next
If blnExists = False Then
' First time this category appears, add category to master array and start tallying time.
If iTotalCount >= 20 Then
MsgBox "The maximum number of categories has been reached."
GoTo Dump
iTotalCount = iTotalCount + 1
strAllCategories(iTotalCount - 1) = Trim(strApptCategories(i))
' Check if the appointment is entirely within the date range.
If oAppt.Start >= myStart Then
If oAppt.End <= myEnd Then
iDurationPerCategory(iTotalCount - 1) = oAppt.Duration
Else
dtDiff = DateDiff("n", myEnd, oAppt.End)
iDurationPerCategory(iTotalCount - 1) = oAppt.Duration - dtDiff
dtDiff = DateDiff("n", oAppt.Start, myStart)
' Category already in master array, just tally the time for the category.
iDurationPerCategory(j) = iDurationPerCategory(j) + oAppt.Duration
iDurationPerCategory(j) = iDurationPerCategory(j) + oAppt.Duration - dtDiff
' First category in master array of categories, start master array and start count of categories.
iTotalCount = 1
strAllCategories(0) = Trim(strApptCategories(i))
iDurationPerCategory(0) = oAppt.Duration
iDurationPerCategory(0) = oAppt.Duration - dtDiff
'List all unique categories and count
Dump:
Debug.Print strAllCategories(j), iDurationPerCategory(j)
End Sub
For testing purposes, I display the following for each appointment that overlaps or falls within the specified reporting period in the Immediate window of the Visual Basic Editor:
· The date and time of the task
· The description of the task
· The duration spent on the task
At the end, I display the total number of minutes spent on each project in the reporting period.
Because this macro may interest Outlook end users as well as developers, if you are not familiar with the Office Visual Basic Editor environment, you can follow these steps to try the macro:
1. Create some appointments that represent time periods that you spent on projects in your calendar.
2. Assign categories to these appointments.
3. Make sure the Developer tab is enabled in the ribbon in Outlook. See How to: Show the Developer Tab on the Ribbon for more information.
4. Click the Developer tab, and click Visual Basic.
5. Double-click the ThisOutlookSession module.
6. Copy the macro from the attached text file to the ThisOutlookSession module in the Visual Basic Editor.
7. Modify the following 2 sets of code statements to specify a date range that includes the appointments you created in step 1, and that you would like to report on:
And:
'Reformat myStart and myEnd to account for minutes. myStart = #9/27/2010 12:01:00 AM# myEnd = #10/2/2010 12:01:00 AM#
8. Click F5 to run the macro.
The primary goal of this macro is to show how to use the Outlook object model to report on appointment times aggregated by category over a specific period of time. I didn’t write this macro to market is as a commercial product. Nonetheless, if you have any suggestions, feel free to leave a comment.
[Note: This is the second in a series of blog posts that highlight some of the new members of the Visio VBA OM.]
In the first post in this series, I talked about the Page.DropConnected method. In this post, I’ll present another new method on the Page object of the Visio 2010 VBA API that you might have overlooked, the Page.LayoutChangeDirection method, which makes it possible to rotate or flip a set of two or more connected shapes on the page as a unit, without having to rotate or flip the individual shapes.
The syntax for this method is as follows:
Page.LayoutChangeDirection(Direction)
As you can see, the method takes a single parameter, Direction, which should be a constant from the VisLayoutDirection enumeration. The four constants in this enum specify rotations of 90 degrees clockwise or counterclockwise, or vertical or horizontal flips.
Here’s an example of how this method might work in the Visio UI. Suppose you have three connected shapes on the page:
If you pass to the LayoutChangeDirection method the visLayoutDirFlipVert constant, your diagram will look like this:
The following VBA code is all it takes:
Public Sub PageLayoutChangeDirection_Example()
ActivePage.LayoutChangeDirection (visLayoutDirFlipVert) End Sub
This code assumes that there are at least two connected shapes on the page. If you have more than one set of connected shapes on the page, the method will work simultaneously on all of them.
If that’s not what you want to happen, you can use another new method on the Selection object, Selection.LayoutChangeDirection, to limit the activity to the shapes you select.
This method has similar syntax to the method on the Page object:
Selection.LayoutChangeDirection(Direction)
It works in much the same way. Select these shapes:
Then run the following code:
Public Sub SelectionLayoutChangeDirection_Example()
Dim vsoSelection As Visio.Selection Set vsoSelection = ActiveWindow.Selection vsoSelection.LayoutChangeDirection (visLayoutDirFlipVert) End Sub
The shapes will then appear like this:
Notice that the shapes themselves aren’t flipped—only their location relative to one another. So, as a result, the pentagon remains point up. This produces an offset of the connector, perhaps not what you intended. Of course, this might not be a problem with other, more symmetrical shapes.
[Note: This is the first in a series of blog posts that will highlight some of the new members of the Visio OM that so far haven’t received a lot of fanfare.]
You might not be aware of the addition of a new method on the Page object to the Visio 2010 VBA API, Page.DropConnected, which makes it possible to add a shape to the drawing page and at the same time connect it to an existing shape on the page. You can specify the object to add to the page, which can be a Visio master, master shortcut, or 2-D shape, or any object that can be represented by an IDataObject object. You can also specify the existing shape on the page to which to connect the new shape, the direction from the existing shape in which to place the new shape, and the type of connector to use. Like the new shape itself, the connector can be a Visio master, master shortcut, or 2-D shape, or any object that can be represented by an IDataObject object.
The syntax for the new method looks like this:
Page.DropConnected(ObjectToDrop, TargetShape, PlacementDir, [Connector])
In the Help topic for this method, the parameters are described as follows:
Note that the Connector parameter is optional. By specifying the PlacementDir parameter, you can place the new shape above, below, to the right, or to the left of the existing shape. The method returns the new shape as a Visio Shape object.
Here’s an example of how this process might look in the Visio UI. Say you have a circle shape on the page:
Let’s say you’d like to add a pentagon below the circle, connected to the circle by the default connector (a 2-D line), so that it would look like this:
To accomplish this, you can run the following VBA code :
Public Sub DropConnected_Example()
Dim vsoShape As Visio.Shape Dim vsoMaster As Visio.Master Dim vsoMasters As Visio.Masters Dim vsoDocument As Visio.Document
Set vsoDocument = Visio.ActiveDocument Set vsoMasters = vsoDocument.Masters Set vsoMaster = Application.Documents.Item("BASIC_U.VSS").Masters.ItemU("Pentagon")
Set vsoShape = ActivePage.DropConnected(vsoMaster, ActivePage.Shapes("Circle"), visAutoConnectDirDown)
This code assumes two things: