Outlook Add-Ins

Improve Your Outlook With Visual Studio Tools For Office

John R. Durant

Code download available at:OutlookAddIns.exe(158 KB)

Contents

The Scenario
Add-In Architecture
Attachment Processing
Creating the Letter
Conclusion

Microsoft Visual Studio Tools for Office 2003, while useful to many developers who wrote tools for Office, supported only Microsoft® Word and Excel®. The new Visual Studio® 2005 Tools for the Microsoft Office System goes farther, adding tools that help you create managed code add-ins for Outlook® 2003.

You can create managed add-ins for Outlook using the Shared Add-in template in Visual Studio 2005 Standard Edition, but you'll miss out on the improvements that have been made in Visual Studio 2005 Tools for the Microsoft Office System that make the process easier and faster.

This article walks you through a sample add-in application for Outlook 2003. Along the way I'll show off some of the things that are possible when creating an Outlook add-in solution using this new version (Visual Studio Tools for Office). The add-in helps a company bring its inventory management data into a more meaningful and immediate sales promotion context.

The Scenario

The business scenario for the sample deserves a little bit of background to help you understand the overall application as well as the technical details. World Wide Importers sells products that other companies and institutions purchase as part of their own marketing campaigns. Items sold include branded key chains, pens, paper, letter openers, and other such office supplies.

In the past, World Wide Importers sales agents have received reports containing data about previous customer orders, but they had to sift through this data manually to find out what was meaningful, slowing the sales process, and causing some missed opportunities. Further, the sales agents felt they spent too much time creating letters and other material that suggested products to their customers. They wanted these materials to be both customer-specific and easy to compile.

My sample application, the Outlook Sales Order Prompt add-in, solved the problem. As seasonal item inventory comes in, the inventory manager runs a batch job from the database. The batch job in turn produces an Excel spreadsheet for each customer and sends the spreadsheet to the customer's sales agent as an attachment. These spreadsheets are not produced in the Excel binary format but rather in the XML spreadsheet format, SpreadsheetML.

The sales agent can review the data in the spreadsheet and, by clicking a button, create a promotional letter containing customer-specific recommended items from the spreadsheet and contact data from Outlook. A resulting promotional letter is saved as a Word document using the Word XML file format, WordProcessingML. An assistant can print these letters and send them to the customers. The entire process, up to the point of sending the physical letter, takes a fraction of a second.

Add-In Architecture

As you can see from the application's data flow, shown in Figure 1, the add-in handles transforming the Excel spreadsheet into two different XML documents. The first is a raw data document, and the second is a fully formatted Word document. While the transformation from spreadsheet to Word document can be done in one operation, there are benefits to having an interim XML document with no Word formatting. For example, what if another system or process needed access to the raw XML data? What if additional data elements were needed in the raw XML data file? It is easier to manage the transformations and various markup demands by isolating the operations.

Figure 1** Data Flow of Outlook Sales Order Prompt Add-In **

Fortunately, users need not worry about any of these details as they interact with the add-in. Its simple menu contains three items, as shown in Figure 2. These menu items simply execute various data transformations supported by the add-in.

Figure 2** Add-In Menu Items **

One key advantage of using Visual Studio Tools for Office to create an Outlook add-in is that the development experience has been greatly simplified. Creating add-ins using the old IDTExtensibility2 model meant that you had five different events to choose from, causing confusion about which event to use for different programming tasks. Now there are just two events, ThisApplication_Startup and ThisApplication_Shutdown. These events are contained in a ThisApplication file that the tools create automatically. In the case of the sample add-in, which was written in Visual Basic, the file is called ThisApplication.vb. The principles discussed here apply to add-ins created in C# as well.

The SalesOrderPrompt add-in runs code at startup to add its menu to the menu bar in Outlook (see Figure 3). The add-in also acquires a reference to a specific Outlook folder because it will need to work with this reference later on. Finally, the startup code gains a pointer to the active Explorer object so that the add-in can disable the menu if the user doesn't have the target folder selected. The only other work that precedes the ThisApplication_Startup method is the class-level variable declarations for the various folders, Explorers, buttons, strings, and other items used during the add-in's lifetime.

Figure 3 Class-Level Declarations and Startup Code

Private WithEvents _Explorers As Outlook.Explorers Private WithEvents _Explorer As Outlook.Explorer Private _taskFolder As Outlook.Explorer Private _SOPFolder As Outlook.MAPIFolder Private _topMenu As Office.CommandBarPopup Private _results As Outlook.Results Private _searchTerm As String Private _contactEntryID As String Private _attachmentPath As String Private WithEvents _createLetter1 As Office.CommandBarButton Private WithEvents _createLetter2 As Office.CommandBarButton Private WithEvents _createLetter3 As Office.CommandBarButton Private _menuBar As Office.CommandBar Dim WithEvents _mailTarget As Outlook.MailItem Private search As Outlook.Search = Nothing Private Sub ThisApplication_Startup( _ ByVal sender As Object, ByVal e As System.EventArgs) _ Handles Me.Startup BuildTopMenu() _SOPFolder = OpenMAPIFolder() _Explorer = Me.ActiveExplorer() End Sub Function OpenMAPIFolder() _ As Microsoft.Office.Interop.Outlook.MAPIFolder Dim SOPFolder As Microsoft.Office.Interop.Outlook.MAPIFolder Dim f As Microsoft.Office.Interop.Outlook.MAPIFolder = Nothing SOPFolder = Me.ActiveExplorer.Session.GetDefaultFolder( _ Outlook.OlDefaultFolders.olFolderInbox) For Each f In SOPFolder.Folders If f.Name = "SalesOrderPrompt" Then Exit For Next Return f End Function

Building a menu in Outlook is easy. The add-in has a separate procedure to build the menu, and it does so by referencing the ActiveMenuBar property of the Outlook ActiveExplorer (see Figure 4). Each Explorer object in Outlook has a CommandBars collection that you can access to add, remove, or modify its collection of menu bars and their controls. The Sales Order Prompt add-in appends a new menu bar to the Outlook standard menu bar and then adds three new buttons to the menu.

Figure 4 Creating the Outlook Menu

Private Sub BuildTopMenu() _menuBar = Me.ActiveExplorer().CommandBars.ActiveMenuBar _topMenu = CType(_menuBar.Controls.Add( _ Office.MsoControlType.msoControlPopup, , True), _ Office.CommandBarPopup) _topMenu.Caption = "SOP Add-in" _topMenu.Visible = True _createLetter1 = CType(_topMenu.Controls.Add( _ Office.MsoControlType.msoControlButton, , , True), _ Office.CommandBarButton) _createLetter1.Caption = "Create Basic Letter" _createLetter1.Visible = True _createLetter1.Enabled = False _createLetter2 = CType(_topMenu.Controls.Add( _ Office.MsoControlType.msoControlButton, , , True), _ Office.CommandBarButton) _createLetter2.Caption = "Create Personal Letter" _createLetter2.Visible = True _createLetter2.Enabled = False _createLetter3 = CType(_topMenu.Controls.Add( _ Office.MsoControlType.msoControlButton, , , True), _ Office.CommandBarButton) _createLetter3.Caption = "Create Premiere Letter" _createLetter3.Visible = True _createLetter3.Enabled = False _topMenu.Enabled = False End Sub

Looking at Figure 4 closely you can see more of the advantages that Visual Studio Tools for Office offers. For example, the add-in provides easy access to the Outlook object model objects by setting up the reference to the primary Outlook application instance. This reference is an instance of the Application object in the Outlook object model. You access this object instance, ThisApplication, using the Me keyword in Visual Basic or the this keyword in C#.

Note that the Application object instance accessed through ThisApplication does not derive from Microsoft.Office.Interop.Outlook. Rather, it derives from Microsoft.Office.Tools.Outlook.Application. This is a custom namespace that accompanies Visual Studio Tools for Office. The key advantage in deriving from this class rather than the normal Primary Interop Assembly (PIA) class is that it allows your add-in code to be forward-compatible by decoupling it from the regular PIA namespace. If future versions of Office change the PIA namespace, your code can remain stable as far as the primary object reference is concerned.

Attachment Processing

The add-in's primary operation runs when the user clicks one of the buttons. The custom add-in menu is only enabled if the user has selected a custom folder associated with the Sales Order Prompt add-in (see Figure 5) and only one e-mail item is selected in the special folder.

Figure 5** Target Outlook Folder **

Recall that the overall scenario specified that the company's database sends an e-mail containing a report for each customer. The customer's sales agent receives the e-mail, reviews the report, and fires off the main code of the add-in by clicking the custom menu button. Restricting the selection of only one e-mail item in the custom folder reduces the code complexity in the add-in. The code to enforce both of these key constraints is in the FolderSwitch and SelectionChange events (see Figure 6) of the Explorer object that the add-in references at startup.

Figure 6 Controlling UI Availability

Private Sub _Explorer_FolderSwitch() Handles _Explorer.FolderSwitch _topMenu.Enabled = _ (_Explorer.CurrentFolder.EntryID = _SOPFolder.EntryID) End Sub Private Sub _Explorer_SelectionChange() _ Handles _Explorer.SelectionChange If _Explorer.CurrentFolder.EntryID = _SOPFolder.EntryID _ And _Explorer.Selection.Count = 1 Then _mailTarget = _Explorer.Selection(1) _createLetter1.Enabled = True _createLetter2.Enabled = True _createLetter3.Enabled = True Else _createLetter1.Enabled = False _createLetter2.Enabled = False _createLetter3.Enabled = False End If End Sub

With these conditions met, the user clicks the button to process the e-mail attachment in order to create a promotional letter. The attachment processing starts by gaining a reference to the selected e-mail's Excel spreadsheet attachment. It finds the name of the customer in the spreadsheet, searches among the user's Outlook contacts to find the contact information for the customer, and merges the contact information with selected data from the Excel spreadsheet report in a raw data XML file. The code converts the raw data XML file into a fully formatted Word document saved in a centrally accessible location. These steps begin in the click event for a button on the add-ins custom menu (see Figure 7).

Figure 7 Processing an E-Mail Attachment

Private Sub _createLetter1_Click( _ ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, _ ByRef CancelDefault As Boolean) Handles _createLetter1.Click Try If _mailTarget.Attachments.Count = 0 Then MessageBox.Show("E-mail message has no attachment to process") Exit Sub End If Dim attachment As Outlook.Attachment attachment = _mailTarget.Attachments.Item(1) Dim excelExport As New XmlDocument() _attachmentPath = Path.GetTempPath() & attachment.FileName attachment.SaveAsFile(_attachmentPath) excelExport.Load(_attachmentPath) Dim namespaceManager As System.Xml.XmlNamespaceManager namespaceManager = New _ System.Xml.XmlNamespaceManager(excelExport.NameTable) namespaceManager.AddNamespace("ss", _ "urn:schemas-microsoft-com:office:spreadsheet") _searchTerm = excelExport. _ SelectSingleNode("/ss:Workbook/ss:Worksheet/" _ & "ss:Table/ss:Row/ss:Cell/ss:NamedCell[@ss:Name=" _ & "'Customer']/../ss:Data/node()", namespaceManager).Value UseAdvancedSearch() Catch exc As System.Exception MessageBox.Show(exc.Message) End Try End Sub

No system is foolproof, so the add-in code checks to see whether the selected e-mail has no attachment and lets the user know if this is the case. With one attachment found, the add-in saves the attachment to a local temporary directory so that its contents can be loaded into an XmlDocument instance. A quick search using an XPath statement lets the add-in find the name of the customer so that it can find contact information.

Saving the file to a local temporary directory is necessary because Outlook exposes the attachment as a binary object with no clearly discernible type information. A potential improvement of the add-in could include the conversion of the attachment's binary stream to its simple text-based XML format.

In order to find the customer's contact information, the add-in leverages the AdvancedSearch object in Outlook, as shown in Figure 8. Using this object, the add-in can look for all contacts whose CompanyName property matches the search term extracted from the spreadsheet attachment.

Figure 8 Using the AdvancedSearch Object

Private Sub UseAdvancedSearch() Dim contacts As Outlook.MAPIFolder = _ Me.ActiveExplorer().Session.GetDefaultFolder( _ Outlook.OlDefaultFolders.olFolderContacts) Try Dim filter As String = String.Format("{0} LIKE '%{1}%'", _ "urn:schemas:contacts:o", _searchTerm) Dim scope As String = String.Format("SCOPE (" _ & "'shallow traversal of ""{0}""')", _ contacts.FullFolderPath) search = Me.AdvancedSearch(scope, filter, True, _searchTerm) Catch ex As System.Exception MessageBox.Show("There was an error looking for a customer:" _ & _searchTerm, "Search") End Try End Sub

The AdvancedSearch object in Outlook works asynchronously so that while the search is underway, users can return to other tasks and not have to wait for the search to complete. Because AdvancedSearch works asynchronously, there is no consistent linear progression from when a user clicks a button on the menu and when the search completes. It is possible for a user to launch the search, and then launch a second search that completes before the first search completes. Thus, the add-in needs a way to distinguish the results of one search from another.

One way to do this is shown in Figure 8 where the search term variable, _searchTerm, is passed as an argument to the AdvancedSearch constructor. This sets the Tag property of the AdvancedSearch object equal to the value of the search term. Then, as the search completes, the add-in can look at this property to determine which search the results belong to. When the search finishes, Outlook fires the AdvancedSearchComplete event and passes as an argument a Search object containing the results of the search (see Figure 9).

Figure 9 Handling AdvancedSearchComplete

Private Sub ThisApplication_AdvancedSearchComplete( _ ByVal SearchObject As Microsoft.Office.Interop.Outlook.Search) _ Handles Me.AdvancedSearchComplete Dim contactResultsForm As ContactResultsForm Dim contactResultArray() As ContactResult = Nothing If SearchObject.Tag = _searchTerm Then _results = SearchObject.Results If _results.Count > 0 Then Dim i As Integer Dim _item As Outlook.ContactItem For i = 1 To _results.Count If _results.Item(i).Class = _ Outlook.OlObjectClass.olContact Then _item = _results.Item(i) ReDim Preserve contactResultArray(i - 1) contactResultArray(i - 1) = New _ ContactResult(_item.FullName, _ _item.CompanyName, _item.EntryID) End If Next If contactResultArray.Length > 1 Then contactResultsForm = New _ ContactResultsForm(contactResultArray) contactResultsForm.ShowDialog() _contactEntryID = contactResultsForm.EntryID ProcessXmlAttachment() Else _contactEntryID = contactResultArray(0).EntryID ProcessXmlAttachment() End If End If End If End Sub

Creating the Letter

In creating the actual letter, the add-in does its primary job of merging data, transforming XML, and producing Word documents to be sent to the customer. The add-in checks to see whether the search results are for the Sales Order Prompt search by looking at the Tag property and seeing if it matches the value of the _searchTerm variable that was set when the search began. As the add-in loops through the results, it verifies that each result's olObjectClass property is set to the contact olContact. The add-in does not process non-contact items returned from the search.

For each contact item found, the add-in creates an instance of a custom ContactResult class and places it into an array. If there is only one contact for a customer, then the add-in can proceed directly with merging the contact data with the spreadsheet data and doing the transformations. If more than one contact exists for a customer, the add-in displays a form that lets the user select one of the contacts.

Once the add-in knows which contact's data to include, it sets a class-level variable, _entryID, to the entryID property of the target contact. This allows the add-in to load the contact from another procedure, ProcessXmlAttachment (see the code download for details).

At an earlier point, the add-in saved the spreadsheet in a temporary directory. Now the add-in loads that XML file into a new XmlDocument instance. It then loads an XSL stylesheet from disk, the location of which is specified in an application-level setting named ExcelExport. With the source document and transformation document specified, the add-in transforms the source document and loads the resulting XML document into memory using the StringWriter object instance.

Next, the add-in merges the data about the sales agent and the company's contact into the new in-memory XML document. This means adding new nodes and appending them to the XML document. To reduce the clutter in this part of the code, the add-in repeatedly calls the AddXmlElement function to add the new node elements:

Private Function AddXmlElement(ByRef ParentDoc As XmlDocument, _ ByVal ElementName As String, ByVal InnerText As String) As XmlNode Dim newNode As XmlNode newNode = ParentDoc.CreateElement(ElementName) newNode.InnerText = InnerText Return newNode End Function

The add-in loads a special XML file in a directory located in the user's special settings: C:\Documents and Settings\[USERNAME]\Local Settings\Application Data\SOP. This directory contains a file called CurrentUser.XML, which contains information about the logged-on sales agent. Data from this file and from the contact returned by the Outlook search is added to the in-memory XML document.

Finally, the add-in loads another XSL stylesheet using a different application-level variable, ProposalLetter. This transformation converts the data-only XML into a fully formatted Word document.

The add-in produces the XML document using an instance of the XmlTextWriter object. It saves the final document in a special directory whose location it keeps in another application-level variable, DocStore. When it saves the file, it includes the name of the company and the current date in the document's name.

Finally, I don't think anyone likes to have menus cluttering up the user interface when they are not really needed. So the add-in responsibly removes the custom menu from the Outlook standard menu bar when the add-in is unloaded. Unloading the add-in can occur when the user quits Outlook or when the user disables it in the list of COM add-ins. In either case, the add-in's ThisApplication_Shutdown event fires:

Private Sub ThisApplication_Shutdown( _ ByVal sender As Object, ByVal e As System.EventArgs) _ Handles Me.Shutdown _topMenu.Delete() End Sub

Another advantage of using Visual Studio Tools for Office to create your Outlook add-ins is that the tools create a setup project for you at project creation. Thus, your solution will contain two projects, the first being your add-in itself, and the second being the setup project for the add-in.

Now you can add special launch conditions and craft your setup package to suit your needs. But the tools have already configured the basic properties for you. For example, the output of your add-in has already been referenced along with dependencies on the Office PIA and the Microsoft .NET Framework 2.0, among others.

Conclusion

Many companies want to make Outlook and the data that it handles a more integral part of their core business processes. Writing solutions in managed code is one obvious way to achieve this. Until now, creating, debugging, and maintaining managed code add-ins for Outlook has been a sizable challenge. Debugging could be a frustrating experience, and there was too wide a gap between the COM world of Office and the managed world of the Microsoft .NET Framework.

Visual Studio Tools for Office narrows that gap considerably and delivers a dependable design time and run-time environment for managed code Outlook add-ins. Rather than spending your time troubleshooting development tools, you can now get right into the core programming tasks and create powerful Outlook add-ins more easily. For more information on developing add-ins for Outlook, see Visual Studio Tools for Office Technical Articles.

John R. Durant is a Program Manager for Visual Studio Tools for Office and is a frequent author and speaker on a wide variety of development topics (blogs.msdn.com/johnrdurant). He coauthored the latest edition of the XML Programming Bible (Wiley & Sons, 2003) and is authoring a new book on Office 12 programmability.