Entries:
Comments:
Posts:

Loading User Information from Channel 9

Something went wrong getting user information from Channel 9

Latest Achievement:

Loading User Information from MSDN

Something went wrong getting user information from MSDN

Visual Studio Achievements

Latest Achievement:

Loading Visual Studio Achievements

Something went wrong getting the Visual Studio Achievements

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.
Clarity Blogs

Difficulty: Intermediate
Time Required: 1-3 hours
Cost: Free
Software: Office 2007 Visual Studio Express VSTO 2005 SE Outlook Add-in Templates
Hardware:
Download: Download

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

Tags:

Follow the Discussion

  • Clint RutkasClint I'm a "developer"

    Facebook is a social utility that connects people with friends and others who work, study and live around

  • 10,000 Monkeys - Harnessing the Power of Typing Monkeys10,000 Monkeys - Harnessing the Power of Typing Monkeys

    Writing blog posts just keeps jumping down the list. I think I need to realize that regular blogging

Remove this comment

Remove this thread

close

Comments Closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.