Outlook Email Extractor

Outlook Addons Programming - Part I

Adding CommandBars to Outlook 2003 using C#

 Outlook Email Extractor

Outlook Mail Content Reading and Exporting it in to Text File

Outlook Automation

MiniCRM

 

 Introduction

Sooner or later, we all have to face the problem of an overloaded number of emails and mail attachments to our Outlook account. Some of them are important to our business, as well, so we really need to save these email messages and attachments. Manually saving messages and attachments is a time-consuming process. So, we need to export these emails and other Outlook items to PST (Personal Storage File) files. The PST file is an archive for all items in an Outlook account. So, after exporting this file to storage media, we can process the PST file to get loose data and messages out of it. In this article, I am going to show you how we can extract this PST file through a .NET application.

Background

Basically, to deal with Outlook in a .NET application, we need an interface named MAPI. MAPI stands for Messaging Application Programming Interface and is provided by Microsoft for Outlook programming. Using MAPI, we can access folders like Inbox, Drafts, Sent Items and so on that reside in a PST file. We can even create sessions and namespace to fetch items from Outlook or, say, from a PST file. Currently, MAPI is the only namespace provided by Microsoft to extract PST files. For further information on MAPI, I suggest newbies visit the relevant MSDN article.

.....................
http://www.addtoany.com/buttons/

Share

Synchronizing a Local Data Store with Microsoft Outlook

Applies to:
   Microsoft Office Outlook 2003
   Microsoft Visual Studio 2005
   Microsoft Visual Studio 2005 Tools for the Microsoft Office System
   Microsoft SQL Server 2005, Express Edition
Summary: Utilize a SQL Express database as a local data cache and the programmability of Microsoft Outlook to integrate enterprise CRM data within the Outlook user interface. (25 printed pages)
Click here to download The CRM Integration for Outlook sample.

Contents

Executive Summary
Introduction
Local Data Store
Data Synchronization Requirements
Implementation Commentary
Conclusion
Companion Resources

Executive Summary

Many organizations have made multi-million dollar investments in customer relationship management (CRM) systems and line of business (LOB) applications over the last decade. Although many of these applications have delivered significant business benefits, some organizations have found that these bespoke systems have not provided the expected returns on investment. One of the argued reasons for this is that specialized enterprise systems, databases, and applications do not integrate well with information worker workflows.
This paper is the second in a series of papers that presents an architectural design guide and sample application that demonstrates an approach for integrating enterprise CRM and other LOB applications with Microsoft Outlook. The guidance in this paper originated from Microsoft's Project Elixir, an internal Microsoft IT initiative to integrate critical customer data with Outlook. The design uses Microsoft Visual Studio 2005 Tools for Office (VSTO), Microsoft SQL Server 2005 Express Edition (SQL Server Express), and Web services.
To support disconnected operation, and to improve performance and keep the size of the user's mailbox to a minimum, the sample solution uses a local data cache in a SQL Server Express database. This paper discusses the structure of the SQL Server Express database and examines how data is synchronized between the data files in Outlook and the local database. For more information about the overall solution architecture, see Extending Enterprise Applications with Microsoft Outlook: Architectural Design Guide. For more information about the other papers in the series and links to the sample code, see the Companion Resources section at the end of this paper.

Introduction

In most industries in which people work with information, Microsoft Office is an integral part of daily work. Over four hundred million people rely on Microsoft Office applications to capture, publish, and process critical business information. It is an essential tool for creating content, making decisions, and communicating with one another. In large organizations, this critical business information often resides in large databases. For example, customer contact information is often maintained in dedicated, centralized CRM applications.
Unfortunately, many of these LOB applications provide a poor user experience, with cumbersome and unintuitive client interfaces. Often the applications do not integrate well with the desktop productivity applications that information workers use constantly, such as Microsoft Office, and Outlook in particular. Users increasingly require application interfaces that are:

• Easy to learn and use.
• Tailored to specific job functions and roles.
• Compatible with daily workflows.
• Integrated with their other applications.

Information workers need to be able to analyze, manipulate, and act on the information in business systems. Information restricted to corporate databases with limited and awkward access is of little benefit. The challenge is to provide a coherent and aggregated view of business data for information workers to enable them to collaborate more effectively with coworkers; to exercise oversight; and to make quicker, more localized decisions to help drive the business.
This paper describes a sample Outlook add-in solution that Microsoft developed to provide access to back-office data held in a fictitious CRM system through the Outlook user interface. The sample add-in provides custom forms and custom processing inside the familiar Outlook user interface (UI).
The solution architecture adopted by the sample supports disconnected operations to enable information workers to continue using the system even without network connectivity. To support this, data from the fictitious CRM system is cached in a local SQL Server Express database. Some of the CRM data in the local cache corresponds to information already cached in the local data file in Outlook. Other cached CRM data, although related to the Outlook data, is not normally kept in this file. This paper describes the operation of the local synchronization of data between Outlook and the SQL Server Express cache.
The sample CRM add-in solution code discussed in this paper is available for download at the Microsoft CRM Integration Sample for Outlook Source Setup download page.

Architecture

The solution architecture consists of a custom Outlook add-in built with Visual Studio Tools for Office, a local SQL Server Express database that serves as a local data cache, and a remote synchronization agent (CRM Sync Agent). The add-in contains a local synchronization engine (Sync Engine) that synchronizes data between Outlook and the local SQL Server Express cache, while the CRM synchronization agent synchronizes data between the local cache and the back-office CRM system. Figure 1 shows the high-level solution architecture for the sample CRM add-in.

Figure 1. Overview of the sample CRM add-in system components (Click on the image for a larger picture)

The sample CRM add-in that accompanies this whitepaper provides source code for all aspects of the described architecture except for the CRM Sync Agent. Due to the complexity and diversity of enterprise back-office CRM systems, it was not possible to provide a simple, generic CRM Sync Agent.

Outlook Add-in

A core component of the sample solution architecture is the Outlook VSTO add-in, which extends the Outlook UI by displaying a custom toolbar and custom forms to display CRM data. The add-in is registered with Outlook when the solution is deployed.
The add-in integrates with Outlook four types of CRM data retrieved from a Web service that provides an interface to the remote CRM System. The data includes CRM accounts, CRM activities, CRM contacts, and CRM opportunities. The data is exposed as a variety of Outlook objects within the folder hierarchy of the Outlook folder list. Each CRM account is associated with a custom folder in the Outlook folder list window. Each account folder contains a set of CRM activities, CRM contacts, and CRM opportunities related to it. At the root of the CRM folder hierarchy is the CRM Today folder, which provides an overall summary of all the CRM information.
When the user starts Outlook, the registered sample add-in is loaded and the initialization code performs the following tasks:

• Determines whether the CRM Today folder exists. If it does not, the code creates the folder.
• Folders and items in Outlook are synchronized with data that has been loaded into the local SQL Server Express database from the remote CRM system.
• Account folders in the local database are synchronized with Outlook data, and for each account folder, items and their properties in the local database are synchronized with the Outlook data.
• A custom CRM toolbar and buttons are added to the standard Outlook UI.
• Additional custom forms display within Outlook to show the CRM data.

The add-in itself handles the data synchronization between the Outlook objects and the local database file. To do this, the internal architecture of the add-in separates the code into the following three main areas, as shown previously in Figure 1.

• Managed Utility Classes. These classes simplify common Outlook development tasks and enhance the ease with which a managed code Outlook add-in can be built. The classes were designed to complement and extend the baseline Outlook extensibility provided by the native Outlook object model.
• Sync Engine. The engine performs synchronization between data rows in the SQL Server Express database and Outlook objects in the Outlook data store.
• Data Access Code. This code handles data access to the local SQL Server Express database.

The AddInLoader component, also shown in figure 1, uses the Visual Studio 2005 Tools for Office (VSTO 2005) run-time engine to provide a standard mechanism for loading both document-level customizations and application-level add-ins into Office applications. For more information about the AddInLoader component, see the paper, Architecture of the Outlook Add-in Support in Visual Studio 2005 Tools for Office.
The Outlook add-in template in VSTO 2005 creates a project that references the VSTO runtime and Office interop assemblies to enable the add-in code to work with the COM Outlook object model. However, the organization of the COM Outlook object model is generally not familiar to .NET programmers. So the sample CRM add-in solution architecture adds a further layer of managed utility classes. This managed utility classes layer was developed with reuse in mind to simplify common Outlook development tasks, and could be useful in other Outlook add-in solutions.
For more information about how the Outlook UI was customized and extended to display CRM data and custom forms for this sample add-in see "Outlook Customization for Integrating with Enterprise Applications" at http://msdn.microsoft.com/architecture/default.aspx?pull=/library/en-us/dnbda/html/OtlkCustInEntApp.asp.

Local Data Store

Many add-in users, such as an organization's mobile sales force, need to be able to use the add-in while not connected to the corporate network. To support disconnected operations, the sample CRM add-in uses a local data store to enable Outlook and the add-in to operate even when the remote CRM system is not available. When operating offline, the add-in saves changes to the local data store. Then, when the network is detected, the CRM synchronization agent synchronizes changes with the back-end CRM system.
Depending on the amount of data changes, the synchronization activity can be time consuming and resource intensive. Using a local data store enables the add-in and synchronization agent for the CRM system to operate independently. This configuration can provide enhanced performance, because the CRM synchronization agent can run when spare local processing and network capacity are available.
You have a number of possible options for local data storage. The following section examines the rational for the sample implementation using a SQL Server Express database, as well as other alternatives available to you.

Choosing a Local Data Store

The following choices represent the main options for a local data store:

• Outlook data file
• Local data file
• Local database

The pros and cons of each approach are discussed below.

Outlook Data File

Using the Outlook data file might be an instinctive choice. For example, if you wanted to synchronize a limited number of contacts from a CRM system with the standard Outlook contacts, using this approach might be appropriate because the Outlook object model gives you the ability to create, modify, and delete Outlook objects.
In many CRM synchronization scenarios, the data fields in the CRM objects to be synchronized (for example, contacts, activities, and so on) have corresponding fields in the native Outlook objects. However, there are some additional fields associated with a CRM object that must be integrated with Outlook, but do not exist in a standard Outlook data file. Fortunately, you can extend Outlook data by using custom properties. For example, you could create an additional property, such as Sales Pipeline Status, to a standard Outlook Task object to accommodate an additional data field from a CRM system. You can access and display these new properties from the add-in custom forms. Similarly, you can program the add-in to connect to the remote CRM system by using Outlook custom fields to track the synchronization of data.
Unfortunately, using the Outlook data file is not always an efficient option. As the quantity of data increases, performance can be impacted. In addition, the Outlook object model is not relational. This results in slow iterations of collections and requires you to develop and maintain large amounts of code.
Another potential issue with using the Outlook file occurs when you use Outlook in conjunction with a Microsoft Exchange server. As the Outlook file grows, the performance of the Exchange replication process decreases. You might, for example, need to change the Exchange setup to increase mailbox size to accommodate the extra data. Such a change might be unpopular within some organizations that want to maintain the stability of their Exchange installation by minimizing adjustments to it.

Local Data File

A local data file external to Outlook can provide advantages over using the Outlook data file itself. The Outlook add-in and agents of the remote CRM system can access an external file without having to run inside the Outlook add-in. This means that the synchronization can take place at different times, resulting in improved performance for users.
The local data file could be a simple Extensible Markup Language (XML) file, or a customized binary format. XML has the advantage of interoperability with other systems, but has the disadvantages of size and inefficient parsing and processing. Also, the .NET Framework provides rich support for XML processing. Although a custom binary file could offer performance advantages over XML, the development complexity increases.

Local Database

Using a local database as a local data store provides an "open" data structure and relational processing capability. Using a local database also means that you can use SQL for data operations. In addition, Visual Studio supports the development of data access code for many database systems that you can use locally.

Choosing to Use SQL Server Express

The sample CRM add-in uses SQL Server Express to implement the data store because SQL Server Express provides several key benefits:

• Extensive support in Visual Studio. This provides a seamless development experience to create and structure the database, and to implement and debug the data access code that the add-in requires.
• Ease of deployment. You can prepare a SQL Server Express data file with the required schema and then deploy it with the add-in using a simple file copy.
• Performance. SQL Server Express runs SQL and performs fast relational processing that is automatically tuned.
• Synchronization support. SQL Server Express can take part in SQL Server database replication, which provides one possible synchronization option for the back-end CRM data.

The openness of SQL Server Express also makes it easier to develop additional agents in the future to synchronize data with additional LOB applications. The database can be extended as required for each remote system.

SQL Server Express Database Schema

You can prepare a SQL Server Express data file with the schema for a custom CRM add-in to use, and then deploy it by using the add-in. This section shows you the structure of the CRM database from the sample solution that you can use as a model. You can use Visual Studio both to create a SQL Server Express database and to add tables and data columns.
For more information about how to use and administer SQL Server Express, see SQL Server Express Books Online.
Figure 2 shows the schema of the sample SQL Server Express database. This is an example of how the client database could be structured for a CRM system.

Figure 2. The local SQL Server Express database schema for the sample CRM add-in (Click on the image for a larger picture)

The sample local database holds the CRM data in a normalized schema with the following tables:

• Accounts. Stores customer account data.
• Activities. Stores CRM activity data; relates to accounts.
• Contacts. Stores customer contact information; relates to accounts.
• Opportunities. Stores CRM opportunity data; relates to accounts.
• ActivityContact. Stores the relationship between a CRM activity and its contacts (for the same account).
• OpportunityActivity. Stores the relationship between a CRM activity and its opportunities (for the same account).
• OpportunityContacts. Stores the relationship between a CRM opportunity and its contacts (for the same account).

Primary and Foreign Keys

Each table in the local database has three keys.

• LocalID. The table primary key. LocalID has been designed as a SQL type unique identifier. This means that each row can have its own globally unique identifier (GUID) generated.
• OutlookID. Holds copies of the keys that Outlook uses. Outlook maintains a key on all its folders and items, and this is represented in the local database in the column OutlookID of type varchar.
• RemoteID. Indicates the position of a key for a remote CRM system.

Details about the keys and synchronization data held in the local database from the remote CRM system are beyond the scope of this paper. The columns RemoteID, RemoteVersion, and SyncStatus indicate the kind of columns that might be used during synchronization with a remote CRM system.

Additional Columns

Some of the other table columns are present in the schema to provide a local store for data that is also kept in the Outlook data file, but this needs to be synchronized with the remote CRM system so that they become available to other users. For example, in the Activities table column, Name, DueDate, and Comments correspond to the Outlook fields Subject, DueDate, and Body.
The sample add-in solution implements a generic local synchronization engine that has been designed to work in a broad array of Outlook data synchronization scenarios. The mapping between data columns and Outlook item properties works declaratively with custom C# attributes that are used to indicate the relationship between data columns and their corresponding Outlook fields. The synchronization engine reads the custom attributes at runtime.
Other columns are present in the schema, but have no equivalent in Outlook. These columns provide local storage for data used in the custom forms that the add-in provides, and they are also synchronized with the remote CRM system. For example, in the Activities table, the columns Type, Category, and Purpose exist in the CRM system, but have no corresponding fields in a standard Outlook installation.

Data Synchronization Requirements

In the sample CRM application, changes made to the data in the Outlook add-in are saved in the Outlook data file and the local database. To keep the enterprise data systems and other CRM users up-to-date, it is assumed that the local changes are then written to the back-end CRM system, but an implementation of this remote synch code is not included in the sample.
Similarly, changes that other user make are loaded into the local database by the agent and are synchronized with the Outlook data when the add-in starts. Through this mechanism, changes to the CRM data are synchronized between users of the add-in and users of the back-end CRM system. The route that the data takes through the local database and the CRM synchronization agent operates independently of any synchronization of data between Outlook and Exchange.
This section describes the general data synchronization patterns for an Outlook add-in using a local SQL Server Express database as a local cache of CRM data, and includes:

• Start-up synchronization for Outlook and local database.
• Standard form data synchronization.
• Custom form data synchronization for the data that the add-in custom forms use.
• The generalized synchronization engine.

You can use the results of the analysis presented in the subsequent sections to help guide your implementation of local data synchronization.

Start-up Synchronization

When planning data flows, there are three primary considerations:

• Data location. You need to determine if the data will be stored in both the Outlook file and the local database, or only stored in the local database.
• Data operations. You need to establish the operations that will be performed on the data (read, write, delete).
• Data modifiers. You need to determine which components will accomplish the data operations (the add-in, Outlook, or both).

Figure 3 shows part of the start-up synchronization process in which the rows of the Activities data table are being synchronized into Task objects in Outlook.

Figure 3. Start-up synchronization of Activities (Click on the image for a larger picture)

In this scenario, the data rows are taken to contain the master copy of the data. The database may contain some rows with an existing OutlookID key; for these rows, the fields in the corresponding Outlook items can be updated to match any new values. The dotted arrows in Figure 3 show the mapping between data columns and Outlook field names.
The database may also contain new rows without OutlookID key values. These need to be added as new Outlook items. When adding a new item to Outlook, a new value of the Outlook key (EntryID) will be generated, and you must store this in the database. Finally, items in Outlook without matching data rows need to be removed.

Standard Form Data Synchronization

The sample add-in uses the standard Outlook contact form to edit and add contacts data, and this keeps the Outlook data file up-to-date. Figure 4 shows that synchronization in the direction from Outlook to the local database is required whenever the standard Contact form is saved.

Figure 4. Standard Outlook Contact form data is synchronized into the local database (Click on the image for a larger picture)

Custom Form Data Synchronization

The sample add-in provides custom forms to allow the user to add and edit CRM data. So in addition to the start-up synchronization, the add-in needs to synchronize data from custom forms with the local database and Outlook.
Figure 5 shows a custom form using data from the local database. Three different uses of data are identified:

1. Read-only data.
2. Data for editing with no corresponding field in Outlook.
3. Data for editing with a corresponding field in Outlook.

Figure 5. Custom form using data from the local database (Click on the image for a larger picture)

Where CRM data is required for display only (A), the add-in requires read-only access to the data in the database. When a custom form enables the user to edit some CRM data, but the data column has no corresponding data field in Outlook (B), then the add-in requires read-write access to the database.
If a custom form enables the user to edit some CRM data that has a corresponding data field in Outlook (C), updates are required both into the database data and the Outlook item.

Generalized Synchronization Engine

Although the proceeding scenarios appear to be different at the outset, a common synchronization pattern does emerge. The custom form synchronization turns out to be a single example of synchronization for a specific type of Outlook item, whereas the start-up synchronizations are performed for every item in a folder, and there are several different Outlook item types. By designing a generalized way to handle an Outlook item, the same synchronization engine can be used for both synchronization scenarios.
In addition, the internal architecture of the sample add-in (see Figure 1) uses a separate utility layer to manage the Outlook objects. This enables the generalized synchronization engine to be separated from the detail of handling different Outlook item types, and allows the creation of a generalized component that can process any of the synchronizations required by the add-in and can be reused in future developments.

Implementation Commentary

In order to create the local database access required by the CRM add-in as quickly as possible, the productivity tools available in Visual Studio were utilized. This section shows how the data access and synchronization requirements identified in the previous section have been implemented in the sample CRM add-in.

Data Access

The synchronization requirements identify data that you can update to enable a filtered selection (in some cases) and to add and delete rows. The System.Data.DataSet type was selected as the basis for the data manipulation performed by the add-in. The .NET Framework data provider for SQL Server (the types in the System.Data.SqlClient namespace) was selected to move data in and out of the database.
An alternative approach for data access would be to use custom business objects instead of data sets. This approach is particularly attractive if custom business objects are already available.

Typed Data Sets

To provide type safety and ease of maintenance for the data manipulation code, the team chose to use typed dataset objects, and chose Visual Studio to generate the code for the properties of the DataTable and DataRow classes. The typed dataset is created in the Visual Studio dataset designer, which makes the data access implementation as easy to write and maintain as possible.

Data for Folder Synchronization

The first task for the add-in when it starts is to synchronize the data for customer account folders in the local database with the folders in Outlook.
To get the data needed for folder synchronization, the sample solution has added data tables to a typed dataset designer for all of the tables in the SQL Server Express database. The data are selected by using SQL, and commands for update, insert, and delete are generated by the designer. Note that the generated classes are split between two source files by using the partial keyword. One file contains the generated code, and the other contains space for you to adapt and extend the generated classes.
For example, Figure 6 shows the data table for the Accounts table and its adapter. The designer has generated the Fill and GetData methods for the table adapter.

Figure 6. The Accounts table adapter

Although all of the Accounts table's records are needed for the folder synchronization with Outlook, when the data for synchronizing activities, contacts, and opportunities is fetched, it must be selected for each of the accounts in turn.
To provide the data for synchronizing activities, contacts, and opportunities, the solution adds additional SQL queries to the table adapters. For example, Figure 7 shows the Activities table adapter under development.

Figure 7. The Activities table adapter

The FillByAccountID and GetDataByAccountID methods use a SQL query with a parameter (in this sample, the parameter is @accountID).

SELECT AccountID, Category, Comments, Description, 
DueDate, LocalID, Name, OutlookID, Purpose, RemoteID, 
RemoteVersion, Status, SyncStatus, Type
FROM Activities
WHERE (AccountID = @accountID)

The method generated from this SQL query can then be called by using a single parameter. This sample code illustrates the ease of coding and maintenance that results from using the dataset designer and its code generation.

CrmDataSetTableAdapters.ActivitiesTableAdapter activitiesAdapter
        = new CrmDataSetTableAdapters.ActivitiesTableAdapter();
CrmDataSet.ActivitiesDataTable activitiesTable
        = activitiesAdapter.GetDataByAccountID(account.LocalID);

Managed Utility Classes

The sample add-in solution includes a set of managed utility classes to help handle the COM interop code that the VSTO 2005 Outlook template adds. These classes help handle the folders and items that are required by the synchronization in a generalized way, simplifying common Outlook development tasks. Table 1 lists the classes that are used in the examples in this paper.

Table 1. Outlook Utility Classes

Class	Description
Folder	Helps return and create Outlook folders. Also helps to navigate the folder tree, for example by returning a root folder.
ItemAdapter	Provide a generic Outlook item type to enable polymorphic access to the baseline functionality provided by all Outlook item types (for example, e-mail, tasks, contacts, and so on). Implements a small subset of methods shared by all Outlook items: Save, Class, and EntryID. Refer to the ItemAdapter section in this paper for further information."

Generalized Synchronization Engine

The sample CRM add-in implementation also includes classes that form a generalized synchronization engine shown in the solution architecture in Figure 1 as the Sync Engine component.
This section describes how the sample CRM add-in code uses the generalized synchronization engine. Table 2 lists the synchronization engine classes.

Table 2. Synchronization Engine Classes

Class	Description
SyncDirection (enum)	Contains ItemToOutlook and OutlookToItem values.
SyncEngine	Contains methods to perform synchronization between folders and row data, and between items and row data.
SyncInfo	Helps by collecting information about an item from the item's attributes.

The SyncEngine class contains methods to perform synchronization between managed utility classes and data rows. Table 3 shows some of the methods used by the sample CRM add-in.

Table 3. Synchronization Engine Methods

Method	Description
SyncFolders	This method ensures that the folders underneath the specified parent folder match the list of folder items in the specified collection. Folders underneath the parent that are not in the collection are deleted. Folders that are in the collection are synchronized by calling the SyncOutlookFolder delegate.
SyncItems	This method ensures that the items in the folder specified and the items in the collection specified are in sync. Items missing from the folder are added. Items existing in the folder are updated as needed. Items in the folder that are not in the database are processed by the calling code through the ProcessOutlookItem delegate. If no delegate is provided, the extra items are deleted.
SyncItem	This method iterates through the properties marked with the OutlookFieldSync attribute and updates the version of each property in Outlook if it differs from the data row. Returns true if changes are made to the Outlook item, false if no changes were made.

The synchronization engine defines two delegates for running functions in the calling program during the synchronization process: ProcessOutlookItem and SyncOutlookFolder.

Interface for Data Synchronization

The synchronization engine defines the interface IOutlookSyncItem to implement on the generated typed data row classes. The interface has a single property of type string to return the Outlook key value. You can implement the interface for each of the data row classes and set and return the OutlookID property.
For example, the implementation for a row in the Activities table is shown here.

partial class ActivitiesRow : Utility.IOutlookSyncItem
{
   string Utility.IOutlookSyncItem.EntryID
   {
      get { return this.OutlookID; }
      set { this.OutlookID = value; }
   }
}

Attributes for Data Mapping

The synchronization engine uses custom attributes, attached to the data rows that it has passed, to retrieve information that maps the row properties onto corresponding properties of Outlook objects. Table 4 table shows the custom attributes.

Table 4. Synchronization Engine Custom Attribute Classes

Attribute	Targets	Properties
OutlookCustomSyncInfo	Class	MethodName. Returns the name of the data row method to call to get the mapping data.
OutlookItemSync	Class	ItemType. Returns the Outlook item type.
OutlookFieldSync	Property	FieldName. Returns the Outlook field name IsUserProp. Returns whether the property is user defined.
OutlookFolderSync	Class	FolderType. Returns the Outlook folder type. This property is defaulted to the OlFolderInbox type. FolderNameProperty. Returns the name of the folder.

The original design used the OutlookItemSync attribute on the class and the OutlookFieldSync attribute on the properties. However, when working with strongly typed datasets, the properties are automatically generated, so you cannot add the OutlookFieldSync attribute. Therefore, the OutlookCustomSyncInfo approach was developed and now each data row class has one OutlookItemSync or OutlookFolderSync attribute and one OutlookCustomSyncInfo attribute. For example, the accounts folder simply states the name of the property used for synchronization: Name. The folder type remains in its default OlFolderInbox type. The following attribute has been added to the accounts data row class.

[Utility.OutlookFolderSync("Name")]
partial class AccountsRow : Utility.IOutlookSyncItem

The activities, contacts, and opportunities data rows have OutlookItemSync and OutlookCustomSyncInfo attributes. For example, the following attributes have been added to the ActivitiesRow class.

[Utility.OutlookItemSync(Outlook.OlItemType.olTaskItem)]
[Utility.OutlookCustomSyncInfo("GetSyncInfo")]
partial class ActivitiesRow : Utility.IOutlookSyncItem

The OutlookCustomSyncInfo attribute names the method GetSyncInfo. This is defined as a static member of the data row class. For example, the following definition of GetSyncInfo has been added to the ActivitiesRow class to describe the mapping "data row to Outlook field."

public static IDictionary GetSyncInfo()
{
   Dictionary syncInfo 
      = new Dictionary();
   syncInfo.Add("Name", 
                 new Utility.OutlookFieldSyncAttribute("Subject"));
   syncInfo.Add("Comments", 
                 new Utility.OutlookFieldSyncAttribute("Body"));
   syncInfo.Add("DateNullCheck", 
                 new Utility.OutlookFieldSyncAttribute("DueDate"));
   return syncInfo;
}

Table 5 shows the mapping table provided by these attributes.

Table 5. Activities Data Mapping

String (key)	OutlookFieldSyncAttribute object
Data row property name	Outlook Field Name
Name	Subject
Comments	Body
DateNullCheck	DueDate

Note that if the alternative business objects design is used instead of datasets, then the business objects' classes need to be modified to implement the IOutlookSyncItem interface and the custom attributes added for data mapping. An advantage of a business objects design is that the OutlookFieldSync attribute can be added to the properties, and there is no need for the OutlookCustomSyncInfo approach. For example, code in a business object representing an activity might look similar to the code shown here.

[Utility.OutlookItemSync(Outlook.OlItemType.olTaskItem)]
public class CRMActivity
{
  [Utility.OutlookFieldSync("Subject")]
  public string Name
  {
    get { 
       ... 
    }
    ...
  }
  ...
}

Running the Synchronization Engine

With the data row class implementing the IOutlookSyncItem interface, and with the data table and data row class and property attributes added, the solution now passes the data table's array of data rows to the synchronization engine for synchronization. The call also passes the Outlook folder containing the items for synchronization and a direction.
You can use the managed utility classes to help retrieve folders and items from Outlook for passing into the synchronization engine. For example, to obtain a reference to the Accounts folder that is under the CRM Today folder, you need to navigate from the root, creating the folders if required, as shown here.

Outlook.MAPIFolder root = Utility.Folder.GetRootFolder(this.Session);
Outlook.MAPIFolder crmFolder = Utility.Folder.CreateFolder(
               root, "CRM Today");
Outlook.MAPIFolder accountsFolder = Utility.Folder.CreateFolder(
                                 crmFolder, "Accounts");

Now you can synchronize the account folders by passing the accounts folder and the rows of the Accounts table into the SyncFolders method, as shown here.

CrmDataSetTableAdapters.AccountsTableAdapter adapter 
   = new CrmDataSetTableAdapters.AccountsTableAdapter();
CrmDataSet.AccountsDataTable table = adapter.GetData();
Utility.SyncEngine.SyncFolders(
                                 accountsFolder, 
                                 table.Rows, SyncAccountFolder);
adapter.Update(table);

To synchronize the contents of a folder with, for example, activities, the activity folder and data rows are passed into the SyncItems method shown here.

Utility.SyncEngine.SyncItems(
                                  activityFolder, 
                                  activitiesTable.Rows, 
                                  Utility.SyncDirection.ItemToOutlook);
activitiesAdapter.Update(activitiesTable);

The SyncDirection.ItemToOutlook enumerated value tells the synchronization engine to move changes from the data rows into the Outlook items before updating the data rows with any missing Outlook keys. The updated data table is then written back to the SQL Server Express Database by using the designer-generated data adapter's Update method.

Read-only Summary Table

The sample add-in includes a CRM Today custom form that displays information in summary tables, as shown in Figure 8. This section shows how the solution binds the custom form to the data that it requires.
The data requirement for a summary table is that the data is retrieved by using SQL, bound to a grid, and then becomes read-only.

Figure 8. The CRM Today summary tables (Click on the image for a larger picture)

Figure 9. The Accounts Summary data table and table adapter

To retrieve the summary data, the solution adds a data table and adapter to the dataset designer. The selection of the data is made by SQL and includes calculated columns for the counts. Because the data only needs to be read, only the select command is needed; commands for update, insert, and delete are omitted.

Figure 10. Data access objects added to Accounts Summary form designer

To complete the data access implementation for the summary table, table adapter objects and a dataset have been added to the Accounts Summary form designer. To enable grid data binding, System.Data.BindingSource objects have been added and connected to the data tables.
To display the data from the local SQL Server Express database when the custom form loads, the data tables are simply filled.

accountsSummaryTableAdapter.Fill(crmDataSet.AccountsSummary);
activitiesSummaryTableAdapter.Fill(crmDataSet.ActivitiesSummary);
opportunitiesSummaryTableAdapter.Fill(crmDataSet.OpportunitiesSummary);   

Read-only Bound Controls

The Account Profile custom form shown in Figure 11 uses data-bound label and link label controls to display information about a single selected account.

Figure 11. The Account Profile form designer

The label and link label controls are bound to a binding source that binds the Accounts data table from the dataset. The solution fills the data table when the form loads by using the Accounts table adapter object.

CRM Contacts Form

The sample CRM add-in uses the standard Outlook Contact form to edit and add contacts data. This keeps the Outlook data file up-to-date, but synchronization in one direction into the local database is required. The sample add-in sets up an event handler for the contact write event, as shown here.

Outlook.MAPIFolder contactFolder = Utility.Folder.CreateFolder(
                      accountFolder,
                      Properties.Resources.ContactsFolderName,
                      Outlook.OlDefaultFolders.olFolderContacts);
_itemManager.RegisterWriteHandler(contactFolder, 
                         new CancelEventHandler(OnContactWrite));

The new event handler calls the SyncItem method by using the SyncDirection.OutlookToItem enumerated value to indicate that the synchronization engine should move changes from the Outlook item into the data row, as shown here.

Utility.SyncEngine.SyncItem(
                 (CrmDataSet.ContactsRow)contactsTable.Rows[0],
                  item,
                  Utility.SyncDirection.OutlookToItem);

CRM Item Custom Forms

The sample CRM add-in uses two custom forms created by using Windows Forms. These have been designed for adding new and editing existing CRM items. There are forms for both CRM activities and CRM opportunities. The custom forms are coded to handle both new scenarios, edits to them, and also the creation of a new CRM item from an existing Outlook item. For example, the new activity form is run from the Create CRM Activity toolbar button.
The same form is used both to add new CRM items and to edit existing items. Figure 12 shows the custom CRM Activity form displaying data from an existing CRM activity item, ready for the user to make changes.

Figure 12. Custom CRM activity form (Click on the image for a larger picture)

Edit Existing Item

When an existing item is edited, some of the data exists in Outlook, and there is an existing Outlook key. The form needs to retrieve any additional data from the local database that belongs to the item that is being edited.
ItemAdapter
To assist handling Outlook item synchronization, the managed utility classes provide a generic Outlook item type to enable polymorphic access to the baseline functionality provided by all Outlook item types in a class called ItemAdapter. ItemAdapter implements a small subset of methods shared by all Outlook items: Save, Class, and EntryID, where EntryID is the Outlook item key.
When a custom form is editing an existing Outlook item, the solution passes an ItemAdapter object to the form constructor. This is stored as a private field called _activity. When the remainder of the data is required, the Activities table adapter's FillByOutlookID method is called and passed the EntryID property. (This gets the Outlook item key.) For example, to fill the Activities table for a certain record.

this.activitiesTableAdapter.FillByOutlookID(
                            this.crmDataSet.Activities, _activity.EntryID);

The form controls are bound to the Activities data table, so when you click the Save and Close button, the solution code saves the changed item to both to the local database and to the Outlook item by using the synchronization engine's SyncItem method. The SyncItem method returns true if the Outlook item was updated, so you can tell whether the Outlook item needs to be saved, as shown here.

if (Utility.SyncEngine.SyncItem(
     row, _activity, Utility.SyncDirection.ItemToOutlook))
{
    _activity.Save();
}

New CRM Item

When a new CRM item is added, some of the data is new in both Outlook and the local database. A new GUID is generated and a new Outlook key created when the item is added to Outlook. As for edited data, a portion of the data is kept in the local database but not in the Outlook data file.
When the form is loaded there is no existing data row, so you need to add a new row and generate a new GUID. For example:

activitiesBindingSource.AddNew();
CurrentRow.LocalID = Guid.NewGuid();

After a new data row has been added, the form code can continue in the same way as the preceding "Edit Existing Item" example. This includes using the SyncItem method that will create a new Outlook item when one does not exist.

New CRM Item from Outlook Item

If a new CRM item is to be created from an existing Outlook item, then you need to create a new data row (as show previously). As before, the form can then continue the same way as the preceding "Edit Existing Item," and call the SyncItem method when you click Save and Close. In this scenario, SyncItem will copy data from the new data row to the existing Outlook item and return true.

CRM Item Associations

CRM custom forms contain a custom control to display related items called associations to an activity. All the data for this control are kept in the local database, so you do not need to make calls to the synchronization engine. When the control is loaded, existing relationship records are loaded into a data table and then displayed in a list.
When the data in the control are ready to be saved, the solution makes a new copy of the data table. The data table rows that correspond to unselected items in the list are deleted, and data rows are created for selected list items without an existing corresponding data row. Finally, the local database is updated by using a data adapter.

Conclusion

This paper has discussed the local synchronization approach utilized in the sample CRM add-in solution to access a CRM system. By examining the sample CRM add-in, the paper has illustrated how and why a local SQL Server Express database is an optimal choice to store additional field data for a custom Outlook add-in, and how synchronization between Outlook data and local database data can be performed.
The internal architecture of the solution contains a layer that helps manipulate Outlook folders and items, and a generalized synchronization engine with methods that perform data synchronization between Outlook folders and items and data rows. Visual Studio provides graphical designers for data objects and high performance database access, and code generated classes that provide type safety and low maintenance code.
These technologies, when used in combination, minimize implementation and code maintenance costs, and deliver a good return on investment.

Companion Resources

Microsoft has developed a sample Outlook add-in solution that demonstrates how the Outlook UI can be extended and customized to create a front-end for a CRM system. For more information about the "CRM Integration Add-in Sample for Microsoft Outlook" see http://download.microsoft.com/download/9/9/C/99CD8598-2A46-48A8-9A5B-7A30D46C0856/CRM Integration Sample for Outlook Source Setup.msi.
For more information about the overall integration scenario and the technical architecture used in the sample, see the Extending Enterprise Applications with Microsoft Outlook: Architectural Design Guide whitepaper.
For a more detailed discussion on how Outlook can be customized to integrate with data from an enterprise application, see the Outlook Customization for Integrating with Enterprise Applications whitepaper.
For more information about the internal Microsoft IT project (code-named Project Elixir) to create an Outlook front-end to Microsoft's internal CRM systems, see https://members.microsoft.com/customerevidence/search/EvidenceDetails.aspx?EvidenceID=13848&LanguageID=1.