Collecting Outlook 2007 Statistics Using VSTO 2005 SE
| |
This article demonstrates how you can create an Outlook add-in using VSTO 2005 SE to listen to outlook events, store data about outlook usage, and produce reports on that usage in a custom form and a form region. |
|
Kevin Marshall, Clarity Consulting, Inc.
Difficulty: Intermediate
Time Required: 1-3 hours
Cost: Free
Hardware:
|
Outlook is used by many people everyday, but few people probably realize the wealth of data that can be learned from how they use Outlook. How long does it take you to respond to emails on average? How many hours do you spend in meetings per week? Using Visual Studio Tools for Office Second Edition (VSTO 2005 SE) and Office 2007, you can easily find the answers to these questions and more by building an add-in for Outlook. This article demonstrates how you can listen to outlook events, store data about outlook usage, and produce reports on that usage in a custom form and new to VSTO 2005 SE, a form region.
What is VSTO 2005 SE?
VSTO 2005 SE is a free add-on to Visual Studio 2005 that enables developers to build applications targeting the 2007 Office system. Developers can create Office-based solutions using the professional development environment of Visual Studio 2005 and the new programming model features of Office 2007 like the ribbon bar and custom form regions.
Creating an Outlook Add-in Project
After installing VSTO 2005 SE and the Outlook Add-in template, you can begin creating an Outlook 2007 add-in. Create a new project, then select Office Outlook 2007 Add-in from the My Templates group and click OK.
The newly created project will have several files. Connect.cs is the main file which contains two methods, InitializeAddin and ShutdownAddin which provide you a starting point for interfacing with Outlook.
Initializing the Add-in
The InitializeAddin method is called whenever your add-in is loaded into memory. For our add-in, we will do three things during start up:
- Create an instance of our EventTracker class. This is the main class that handles all of the logic for processing incoming appointment and mail items.
- Load existing Outlook data via the EventTracker.
- Add a menu item to the Outlook toolbar. The menu item is used to pop up the report window.
Visual C#
private void InitalizeAddin()
{
//Initialize report menu item
this.InitializeMenu();
// Initialize the event tracker object.
_eventTracker = new EventTracker(this.Application);
ListenToEvents(true);
_eventTracker.LoadData();
}
Visual Basic
Private Sub InitalizeAddin()
'//Initialize report menu item
InitializeMenu()
'// Initialize the event tracker object.
_eventTracker = New EventTracker(Me.Application)
ListenToEvents(True)
_eventTracker.LoadData()
End Sub
Top-level menu items can be created the same way in office application. For this add-in, we’ll create a new menu “Reports”, with a single menu item to launch the report popup window.
Visual C#
// Get the Outlook menu bar.
_menuBar = this.Application.ActiveExplorer().CommandBars.ActiveMenuBar;
// Get the index of the Help menu item on the Outlook menu bar.
_helpMenuIndex = _menuBar.Controls[_MENU_BEFORE].Index;
// Add the top-level menu right before the Help menu.
_topMenu =
(Office.CommandBarPopup)_menuBar.Controls.Add(Office.MsoControlType.msoControlPopup,
Type.Missing, Type.Missing, _helpMenuIndex, true);
_topMenu.Caption = "Reports";
_topMenu.Visible = true;
// Add the menu item for loading email reports.
_reports =
(Office.CommandBarButton)_topMenu.Controls.Add(Office.MsoControlType.msoControlButton,
Type.Missing, Type.Missing, Type.Missing, true); Type.Missing, Type.Missing, Type.Missing, true);
_reports.Caption = "Calendar / Email Reports";
_reports.Visible = true;
Visual Basic
'// Get the Outlook menu bar.
_menuBar = Me.Application.ActiveExplorer().CommandBars.ActiveMenuBar
'// Get the index of the Help menu item on the Outlook menu bar.
_helpMenuIndex = _menuBar.Controls(_MENU_BEFORE).Index
'// Add the top-level menu right before the Help menu.
_topMenu =
CType(_menuBar.Controls.Add(Office.MsoControlType.msoControlPopup, _
Type.Missing, Type.Missing, _helpMenuIndex, True), Office.CommandBarPopup) _topMenu.Caption = "Reports"
_topMenu.Visible = True
' Add the menu item for loading email reports.
_reports = _topMenu.Controls.Add(Office.MsoControlType.msoControlButton, _
Type.Missing, Type.Missing, Type.Missing, True)
_reports.Caption = "Calendar / Email Reports"
_reports.Visible = True
Loading Statistics Based on Existing Outlook Data
Data to produce reports on Outlook usage is stored in a SQL Express database. When the add-in first loads, it needs to scan the mail and calendar folders to gather statistics on existing items. The EventTracker class handles the initial data load, as well as hooking events fired when new items are added to those folders.
The schema for the database has tables for storing information about the three types of items we are reporting statistics: inbox items, sent mail items, and appointment items.
Since scanning through the folders can take some time if you are like me and keep every email ever received, it’s best to start the initial data load on a separate thread. Now Outlook can continue responding to user input, while the add-in does its work.
Visual C#
public void LoadData()
{
//create new thread for large inboxes
Thread thread = new Thread(new ThreadStart(CheckInitialLoad));
thread.IsBackground = true;
thread.Start();
}
Visual Basic
Public Sub LoadData()
Dim thread As Thread = New Thread(AddressOf CheckInitialLoad)
thread.IsBackground = True
thread.Start()
End Sub
The initial data load raises an to notify other code, like the report popup form, that the data isn’t ready to produce reports yet. If the database doesn’t contain any exisitng data, then the data load will add records for each exisiting item. Allowing the thread to sleep briefly between iterations prevents the data load from interfering with the responsiveness of the main Outlook thread.
Visual C#
private void CheckInitialLoad()
{
if (!object.Equals(StartDataLoad,null))
{
StartDataLoad(this, null);
}
ReportDBDataSetTableAdapters.SentMailTableAdapter sentItemTA = new ReportDBDataSetTableAdapters.SentMailTableAdapter();
ReportDBDataSetTableAdapters.InboxTableAdapter inboxTA =
new ReportDBDataSetTableAdapters.InboxTableAdapter();
ReportDBDataSetTableAdapters.CalendarTableAdapter calTA =
new ReportDBDataSetTableAdapters.CalendarTableAdapter();
//check to see if calendar stats have been loaded
if (calTA.GetData().Rows.Count == 0)
{
foreach (object item in _calendarItems)
{
if (item is Outlook.AppointmentItem)
{
AddCalendarItem(item as Outlook.AppointmentItem);
}
Thread.Sleep(10);
}
}
if (!object.Equals(StopDataLoad, null))
{
StopDataLoad(this, null);
}
}
Visual Basic
Private Sub CheckInitialLoad()
RaiseEvent StartDataLoad(Me, Nothing)
Dim sentItemTA As ReportDBDataSetTableAdapters.SentMailTableAdapter = _
New ReportDBDataSetTableAdapters.SentMailTableAdapter
Dim inboxTA As ReportDBDataSetTableAdapters.InboxTableAdapter = _
New ReportDBDataSetTableAdapters.InboxTableAdapter
Dim calTA As ReportDBDataSetTableAdapters.CalendarTableAdapter = _
New ReportDBDataSetTableAdapters.CalendarTableAdapter
If calTA.GetData.Rows.Count = 0 Then
For Each item As Object In _calendarItems
If TypeOf item Is Outlook.AppointmentItem Then
AddCalendarItem(CType(item, Outlook.AppointmentItem))
End If
Thread.Sleep(10)
Next
End If
If inboxTA.GetData.Rows.Count = 0 Then
For Each item As Object In _inboxItems
If TypeOf item Is Outlook.MailItem Then
AddInboxItem(CType(item, Outlook.MailItem))
End If
Thread.Sleep(10)
Next
End If
If sentItemTA.GetData.Rows.Count = 0 Then
For Each item As Object In _sentMailItems
If TypeOf item Is Outlook.MailItem Then
AddSentMailItem(CType(item, Outlook.MailItem))
End If
Thread.Sleep(10)
Next
End If
RaiseEvent StopDataLoad(Me, Nothing)
End Sub
By listening to the StartDataLoad and StopDataLoad events, we can display a message to the user if they try to load a report.
Visual C#
if (_isDataLoading)
{
MessageBox.Show("Outlook is currently initializing your folders to enable reporting for the first time. Try again in a few minutes.",
"Loading initial data", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
Visual Basic
If (_isDataLoading) Then
MessageBox.Show("Outlook is currently initializing your folders to enable reporting for the first time. Try again in a few minutes.", "Loading initial data", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Listening for New Items
After the initial data load, the add-in can just listen for incoming items to keep track of Outlook usage. To listen for incoming items, you can use folder events. To hook into folder events you first need to obtain a reference to an Outlook.MAPIFolder object that encapsulates an Outlook folder like Inbox, Sent Mail, or Calendar. In order to get the default Outbox folder call the GetDefaultFolder method of the MAPI namespace object. Then you need to add handlers for the ItemAdd event of the Outlook folders. The following code shows how to listen for incoming mail items in the Inbox folder.
Visual C#
// Obtain references to the folder objects that fire the events we are interested in.
Outlook.MAPIFolder inbox =
app.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox);
// Store references to the item collection objects.
_inboxItems = inbox.Items;
_inboxItems.ItemAdd += new Outlook.ItemsEvents_ItemAddEventHandler(InboxFolderItemAdded)
/// <summary>
/// Handles new mail items added to the inbox
/// </summary>
/// <param name="Item">Reference to the Outlook object added</param>
private void InboxFolderItemAdded(object Item)
{
if (Item is Outlook.MailItem)
{
AddInboxItem(Item as Outlook.MailItem);
}
}
Visual Basic
Dim inbox As Outlook.MAPIFolder = _
app.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
_inboxItems = inbox.Items
AddHandler _inboxItems.ItemAdd, AddressOf InboxFolderItemAdded
Private Sub InboxFolderItemAdded(ByVal Item As Object)
If TypeOf Item Is Outlook.MailItem Then
AddInboxItem(CType(Item, Outlook.MailItem))
End If
End Sub
Appointments and sent mail items can be tracked in a similar manner. Additionally, the Outlook folders support events for removing or changing items.
Items can easily be stored in the database by using table adapters automatically generated from the dataset. Each item has a table adapter which corresponds to a table in the database.
Visual C#
/// <summary>
/// Stores a new message in the database
/// </summary>
/// <param name="mailItem"></param>
private void AddInboxItem(Outlook.MailItem mailItem)
{
ReportDBDataSetTableAdapters.InboxTableAdapter ta = new ReportDBDataSetTableAdapters.InboxTableAdapter();
//determine if mail was sent to an alias or group address
bool isAliased = true;
if (mailItem.To == mailItem.ReceivedOnBehalfOfName || mailItem.CC == mailItem.ReceivedOnBehalfOfName)
{
isAliased = false;
}
try
{
ta.Insert(mailItem.SenderName, mailItem.SenderName, mailItem.SentOn, isAliased, null,
mailItem.Subject, mailItem.EntryID);
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex);
}
}
Visual Basic
Private Sub AddInboxItem(ByVal mailItem As Outlook.MailItem)
Dim ta As ReportDBDataSetTableAdapters.InboxTableAdapter = New ReportDBDataSetTableAdapters.InboxTableAdapter
Dim isAliased As Boolean = True
If mailItem.To = mailItem.ReceivedOnBehalfOfName OrElse mailItem.CC = mailItem.ReceivedOnBehalfOfName Then
isAliased = False
End If
Try
ta.Insert(mailItem.SenderName, mailItem.SenderName, mailItem.SentOn, isAliased, Nothing, mailItem.Subject, mailItem.EntryID)
Catch ex As Exception
System.Diagnostics.Debug.Write(ex)
End Try
End Sub
Producing the Reports
Now that we a have some data points about our Outlook usage, we can produce reports. The reports are displayed in a datagrid on a form that is launched from the Reports menu. The report popup dialog contains a selector for various report types and a selectable date range.
Each report is populated using a method from our table adapters. For example, to get a count of emails sent over a given period, you can use the following code:
Visual C#
this.inboxTableAdapter.CountEmailsRecieved(this.reportDBDataSet.Inbox, fromDate, toDate);
this.dataGridView1.Columns.Add("Emails Recieved", "Emails Received");
this.dataGridView1.Columns["Emails Recieved"].DataPropertyName = "Emails Recieved";
this.reportDBDataSetBindingSource.DataSource = this.reportDBDataSet.Inbox;
Visual Basic
Me.InboxTableAdapter.CountEmailsRecieved(Me.ReportDBDataSet.Inbox, fromDate, toDate)
Me.dataGridView1.Columns.Add("Emails Recieved", "Emails Received")
Me.dataGridView1.Columns("Emails Recieved").DataPropertyName = "Emails Recieved"
Me.reportDBDataSetBindingSource.DataSource = Me.ReportDBDataSet.Inbox
Creating a Form Region
Another way you can display data about Outlook usage is a form region. Form regions can be added to any existing Outlook form. You can also control whether the region is shown in the preview pane, the inspector, or on new items. For this add-in, we’ll add a form region on email messages to show statistics about the recipient / sender.
The first step in creating a new form region is to customize an existing Outlook form. In Outlook, click Tools -> Forms -> Design a Form. Then select Message from the standard forms library. The Outlook form will appear in the design view. On the ribbon bar, select the button for creating a new form region.
On the form, add a new textbox control. You can’t use the regular Outlook textbox though. Right-click the Toolbox window and select Custom Controls. Scroll through the list of controls and select the Microsoft Office Outlook TextBox Control, and then click OK.
Add the textbox to the form to display the Outlook statistics.
Now you can save the form region as an .ofs file and add it to the project as a resource file. In order to connect the form region with our add-in, we need to create a region manifest file and a registry entry. The region manifest is an xml file that defines how Outlook displays the region.
<?xml version="1.0" encoding="utf-8" ?>
<FormRegion xmlns="http://schemas.microsoft.com/office/12/outlook/formregion.xsd">
<name>OutlookStatsRegionCS</name>
<formRegionType>adjoining</formRegionType>
<title>Outlook Statistics</title>
<showInspectorRead>true</showInspectorRead>
<showReadingPane>true</showReadingPane>
<showInspectorCompose>false</showInspectorCompose>
<addin>OutlookStatsCS.Connect</addin>
</FormRegion>
The documentation for the XML Schema for form regions is located here. Since the form for composing new mail item shouldn’t have usage statistics at the bottom, set the showInspectorCompose property to false.
To associate the region manifest with Outlook, you need to create a new registry key under HKEY_CURRENT_USER\Software\Microsoft\Office\Outlook\FormRegions\IPM.Note called OutlookStatsRegionCS. The value of the key should be the path to the region manifest XML file. A sample registry file is included in the project’s \Registry folder in the region_registry.reg file.
Wiring the Region to the Outlook Add-in
Because form regions are based on the Microsoft Forms 2.0 types, you need to add a reference in the add-in project to Microsoft Forms 2.0 Object Library. Now you can add a new class to manage that form region, called OutlookStatsRegion.cs.
The constructor for this class needs a reference to the instance of the form region.
Visual C#
public OutlookStatsRegion(Outlook.FormRegion region)
{
_region = region;
_region.Close += new Outlook.FormRegionEvents_CloseEventHandler(Region_Close);
_form = region.Form as Forms.UserForm;
_txtStats = (Outlook.OlkTextBox)_form.Controls.Item("txtStats");
_txtStats.Text = GetEmailStatistics(region.Item);
}
Visual Basic
Public Sub New(ByVal [region] As Outlook.FormRegion)
_region = [region]
AddHandler _region.Close, AddressOf Region_Close
_form = CType([region].Form, Forms.UserForm)
_txtStats = CType(_form.Controls.Item("txtStats"), Outlook.OlkTextBox)
_txtStats.Text = GetEmailStatistics([region].Item)
End Sub 'New
Using the reference to the region, we can get access to the mail item, and therefore the sender name and email address need to display the statistics.
Visual C#
private string GetEmailStatistics(object Item)
{
string result = string.Empty;
if (Item is Outlook.MailItem)
{
Outlook.MailItem mailItem = Item as Outlook.MailItem;
ReportDBDataSetTableAdapters.InboxTableAdapter inboxTA = new ReportDBDataSetTableAdapters.InboxTableAdapter();
int? countEmailsReceived = inboxTA.CountEmailsReceivedFrom(mailItem.SenderName);
sb.Append("Emails received from ");
sb.Append(mailItem.SenderName);
sb.Append(": ");
sb.Append(countEmailsReceived.Value.ToString());
sb.Append(" [");
sb.Append(percentTotalReceived);
sb.AppendLine("% of total received]");
result = sb.ToString();
}
return result;
}
Visual Basic
Private Function GetEmailStatistics(ByVal Item As Object) As String
Dim result As String = String.Empty
If TypeOf Item Is Outlook.MailItem Then
Dim mailItem As Outlook.MailItem = CType(Item, Outlook.MailItem)
Dim inboxTA As New ReportDBDataSetTableAdapters.InboxTableAdapter()
Dim inbox As New ReportDBDataSet.InboxDataTable()
Dim countEmailsReceived As Nullable(Of Integer) = _ inboxTA.CountEmailsReceivedFrom(mailItem.SenderName)
sb.Append("Emails received from ")
sb.Append(mailItem.SenderName)
sb.Append(": ")
sb.Append(countEmailsReceived.Value.ToString())
sb.Append(" [")
sb.Append(percentTotalReceived)
result = sb.ToString()
End If
Return result
End Function 'GetEmailStatistics
Almost there. Next we have to modify the Connect class to implement the form region interface.
Visual C#
public partial class Connect : Outlook.FormRegionStartup
Visual Basic
Partial Public Class Connect
Implements Outlook.FormRegionStartup
The two methods defined in this interface are called as Outlook loads an item and prepares to show a form. When Outlook starts to load the user interface for a message class that has a form region registered, it first calls the GetFormRegionStorage method to load the layout information for the form region. GetFormRegionStorage should return either a string (which is the path to the .ofs file), an IStorage instance for the contents of the .ofs file, or a byte array that contains the contents of the .ofs file. Before Outlook displays the form region to the user, it calls the BeforeFormRegionShow method, and passes a reference to the form region object.
Visual C#
public object GetFormRegionStorage(string FormRegionName, object Item,
int LCID, Outlook.OlFormRegionMode FormRegionMode,
Outlook.OlFormRegionSize FormRegionSize)
{
if (FormRegionName == "OutlookStatsRegionCS")
{
// Return the storage only when there are headers
return Properties.Resources.OutlookStatsRegionCS;
}
return null;
}
public void BeforeFormRegionShow(Outlook.FormRegion FormRegion)
{
if (FormRegion.InternalName == "OutlookStatsRegionCS")
{
OutlookStatsRegion newRegion = new OutlookStatsRegion(FormRegion);
newRegion.Closed += new EventHandler(Region_Closed);
_openRegions.Add(newRegion);
}
}
Visual Basic
Public Sub BeforeFormRegionShow(ByVal FormRegion As Microsoft.Office.Interop.Outlook.FormRegion) Implements Microsoft.Office.Interop.Outlook._FormRegionStartup.BeforeFormRegionShow
If FormRegion.InternalName = "OutlookStatsRegionVB" Then
Dim newRegion As New OutlookStatsRegion(FormRegion)
AddHandler newRegion.Closed, AddressOf Region_Closed
_openRegions.Add(newRegion)
End If
End Sub
Public Function GetFormRegionStorage(ByVal FormRegionName As String, _ ByVal Item As Object, ByVal LCID As Integer, _
ByVal FormRegionMode As Microsoft.Office.Interop.Outlook.OlFormRegionMode, _
ByVal FormRegionSize As Microsoft.Office.Interop.Outlook.OlFormRegionSize) As Object Implements Microsoft.Office.Interop.Outlook._FormRegionStartup.GetFormRegionStorage
If FormRegionName = "OutlookStatsRegionVB" Then
Return My.Resources.OutlookStatsRegionVB
End If
Return Nothing
End Function
The last step is to register the add-in with Outlook. The add-in template provided with this article automatically generates a .reg file with the necessary settings to register the COM add-in created by the project with Outlook. Double click the .reg in Windows Explorer to register the add-in. Now you can change the debug properties of the project to start Outlook when debugging by setting the Start Action to Start External Program and then browse to the path of Outlook 2007.
Conclusion
As you can see, VSTO 2005 SE allows developers a powerful tool for customizing Office 2007. New features in VSTO 2005 SE such as the Ribbon bar and custom form regions allow for even better integration than previous versions of VSTO. Outlook events made it possible for us to calculate statistics about email and calendar usage. In addition to the reports included in this add-in there is room for future additions. Some possibilities are:
- Adding a custom region on contacts to show amount of time spent in meetings with a particular person
- Tracking the amount of time spent reading / composing emails
- Tracking the amount spent on tasks or the percentage of tasks completed on time
