OBA Part 2 - Building an Outlook Client against LOB Data
In the last couple posts we've been talking about an Office Business Application (OBA) architecture for the new Northwind Traders and how to expose line-of-business (LOB) data, in our case the Northwind SQL database, using ADO.NET Data Services.
Today we're going to talk about how to build an Outlook Add-In that pulls the order history from the database through our data service when a customer email arrives in the inbox of our sales reps. This allows the sales reps to easily communicate current order status and product inventory with the customer without having to open up another program. That's the idea -- we're choosing to build a solution that is instantly familiar to our users since they already live in Outlook.
We're going to display the order history and inventory information in a WPF control in an adjoining form region so that it displays on the email item itself. It will display when the email opens or is displayed in the preview pane. We could have used a Windows Forms control but WPF is going to look a lot better. We want to match the blue color gradients that Outlook uses so that the control looks built in. Using WPF in Office solutions built with Visual Studio is really easy so let's get started.
The Outlook Add-in Project
I'm going to build upon the Visual Studio Solution that we started in the previous post when we built the data service. This solution also contains a database project that I'm using to keep the change scripts organized. If you recall we needed to add an EmailAddress field to Customers and Employees tables in Northwind. We also added some Customer and Employee data that specify the email addresses we are going to use to test the sample.
So just add a new project to the solution and select Office 2007 Outlook Add-In which is available in Visual Studio 2008 Professional and higher:
Next we need to add a service reference to the data service like I showed here when building a simple console app client. Right-click on the project, select Add Service Reference, click the "Discover" button and name the reference NorthwindService. This will generate the proxy code and entity types used by the data service. It will also add a reference to the System.Data.Services.Client assembly.
Building the Outlook Form Region
Now we'll add the Outlook Form region. Add new item to the project and select Outlook Form Region and name it EmailForm. When you click "Add", a dialog will appear that will ask you if you want to design a new region yourself or if you want to import one. Select "Design a new form region" and click Next. Then it asks you how you want the region to appear, whether it should be on it's own tab or adjoining or a complete replacement. We want to display the order and product information below the customer emails so select adjoining.
When we click next we are asked what the Name of the adjoining region should be -- this is displayed in the separator between the email and our control -- so I have specified "Order History". And since we only want to display order history on emails in read mode, uncheck the compose mode checkbox. Finally we are asked which message classes will display the form region. Keep the default selection which specifies all messages. Click finish and the designer will display an empty Windows Forms user control.
Let's test this out real quick, even though we haven't written any code. Set the Outlook client as the Startup Project and hit F5 and you will see Outlook open. Select any message in your inbox and notice that below the email you will see a blank form region labeled "Order History". However we don't want the form region to display for every email we get, only the Northwind customers. So what we'll do is write some code that checks the Northwind database for any email addresses coming into the inbox to see if they match our customers and only display the region in that case. Back on the EmailForm user control right-click and view code, you will see the following template already set up for you:
Imports Microsoft.Office.Tools.Outlook
Public Class EmailForm
#Region "Form Region Factory"
<Microsoft.Office.Tools.Outlook.FormRegionMessageClass(FormRegionMessageClassAttribute.Note)> _
<Microsoft.Office.Tools.Outlook.FormRegionName("NorthwindOutlookClient.EmailForm")> _
Partial Public Class EmailFormFactory
' Occurs before the form region is initialized.
' To prevent the form region from appearing, set e.Cancel to true.
' Use e.OutlookItem to get a reference to the current Outlook item.
Private Sub EmailFormFactory_FormRegionInitializing(ByVal sender As Object, _
ByVal e As FormRegionInitializingEventArgs) _
Handles Me.FormRegionInitializing
End Sub
End Class
#End Region
'Occurs before the form region is displayed.
'Use Me.OutlookItem to get a reference to the current Outlook item.
'Use Me.OutlookFormRegion to get a reference to the form region.
Private Sub EmailForm_FormRegionShowing(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.FormRegionShowing
End Sub
'Occurs when the form region is closed.
'Use Me.OutlookItem to get a reference to the current Outlook item.
'Use Me.OutlookFormRegion to get a reference to the form region.
Private Sub EmailForm_FormRegionClosed(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.FormRegionClosed
End Sub
End Class
The EmailFormFactory nested class is what creates the EmailForm and it gives us a chance to cancel showing the region. Here's where we need to write some code that gets the email address from the mail item and checks the database through the data service. In the FormRegionShowing event handler of the EmailForm we will pull down the Order History based on the customer address along with the Order_Details and related Products. Since we're going to need to get the EmailAddress as well as connect to the same data service in both event handlers, a good place to write global code is in the ThisAddIn class which is automatically created for you when you create the Add-in project.
Double-click on ThisAddIn in the Solution Explorer to open the code. You will see StartUp and ShutDown event handlers for the Add-In automatically generated for you. We don't need to add any code into these handlers. All we need to do is add a public property to the service reference for our data service as well as a function to extract the SMTP email address from the mail item. Note that I added an application setting to specify the location of the data service. During development this is going to be https://localhost:1234:/Northwind.svc but when we deploy the application we'll change it to the location of our production service:
Imports NorthwindOutlookClient.NorthwindService
Public Class ThisAddIn
Private _ctx As NorthwindEntities
Public ReadOnly Property DataServiceContext() As NorthwindEntities
Get
If _ctx Is Nothing Then
'Specify the Service URI in the app.config via project settings.
_ctx = New NorthwindEntities(New Uri(My.Settings.ServiceURI))
End If
Return _ctx
End Get
End Property
''' <summary>
''' Extracts the SMTP email address from the mail item.
''' </summary>
''' <param name="mailItem">the Outlook mail item</param>
''' <returns>The SMTP email address, otherwise empty string</returns>
''' <remarks></remarks>
Public Function GetEmailAddress(ByVal mailItem As Outlook.MailItem) As String
Dim recip As Outlook.Recipient
Dim exUser As Outlook.ExchangeUser
Dim emailAddress As String = ""
Try
If mailItem IsNot Nothing AndAlso mailItem.SenderEmailType IsNot Nothing Then
''get SMTP Address for the sender of this email
If mailItem.SenderEmailType.ToLower = "ex" Then
recip = Globals.ThisAddIn.Application.GetNamespace("MAPI") _
.CreateRecipient(mailItem.SenderEmailAddress)
exUser = recip.AddressEntry.GetExchangeUser()
emailAddress = exUser.PrimarySmtpAddress
Else
emailAddress = mailItem.SenderEmailAddress
End If
End If
Catch ex As Exception
emailAddress = ""
End Try
Return emailAddress
End Function
Private Sub ThisAddIn_Startup(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Startup
End Sub
Private Sub ThisAddIn_Shutdown(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Shutdown
End Sub
End Class
Now back in the EmailFormFactory we can add this code to check if we should even be displaying the form region at all. If we don't want to show it, just set e.Cancel =True:
' Occurs before the form region is initialized.
' To prevent the form region from appearing, set e.Cancel to true.
' Use e.OutlookItem to get a reference to the current Outlook item.
Private Sub EmailFormFactory_FormRegionInitializing(ByVal sender As Object, _
ByVal e As FormRegionInitializingEventArgs) _
Handles Me.FormRegionInitializing
Dim emailAddress As String = ""
Dim ctx = NorthwindOutlookClient.Globals.ThisAddIn.DataServiceContext
Try
Dim mailItem = TryCast(e.OutlookItem, Outlook.MailItem)
emailAddress = NorthwindOutlookClient.Globals.ThisAddIn.GetEmailAddress(mailItem)
If emailAddress <> "" Then
'Make sure the email address is actually in the database
Dim customer = (From c In ctx.Customers _
Where c.EmailAddress = emailAddress).FirstOrDefault()
If customer Is Nothing Then
emailAddress = ""
End If
End If
Catch ex As Exception
'TODO: Error logging
emailAddress = ""
End Try
e.Cancel = (emailAddress = "")
End Sub
Note that a service call could experience unexpected delays depending on where the data service is located. We may want to consider just displaying the form region and writing the calls to the service asynchronously. This is possible using the ADO.NET Data Services client as described in this article. For now, we'll keep this OBA sample simple and assume that the data service is located somewhere on our intranet.
Now that we have this code in place we can write the code that will specify a LINQ query to retrieve the order history in the EmailForm's FormRegionShowing handler. Notice I'm using the ".Expand" syntax in the query to pull in the related entities on the Order. Then I'm pulling the Product entity down for each of the Order_Detail entities so I can display inventory information. (We may want to consider only pulling orders within a date range for better scalability but here I'm pulling all of them down for the specified customer for simplicity.)
'Occurs before the form region is displayed.
'Use Me.OutlookItem to get a reference to the current Outlook item.
'Use Me.OutlookFormRegion to get a reference to the form region.
Private Sub EmailForm_FormRegionShowing(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.FormRegionShowing
Dim emailAddress As String = ""
Dim ctx = NorthwindOutlookClient.Globals.ThisAddIn.DataServiceContext
Try
Dim mailItem = TryCast(Me.OutlookItem, Outlook.MailItem)
emailAddress = NorthwindOutlookClient.Globals.ThisAddIn.GetEmailAddress(mailItem)
If emailAddress <> "" Then
'pull the order history from the database
Dim customerOrders = From o In ctx.Orders.Expand("Order_Details").Expand("Customer") _
Where o.Customer.EmailAddress = emailAddress _
Order By o.OrderDate Descending
For Each order In customerOrders
For Each detail In order.Order_Details
ctx.LoadProperty(detail, "Product")
Next
Next
Dim ordersList As New List(Of Order)(customerOrders)
'TODO: Set this ordersList as the DataContext of our WPF user control
End If
Catch ex As Exception
'Could not connect to the service
'TODO: Error logging
End Try
End Sub
You also should notice that we still need to pass this list of Order entities that were returned from the data service into our WPF control -- but first we need to create it!
Building the WPF User Control
So let's add a new WPF User control via Add New Item (listed under WPF) -- I named it OrderHistory. This will show the WPF designer. The WPF User Control simply specifies just the UI in XAML of what the control should look like and sets up the data binding to the properties on our entities.
Here we've set up two ListViews that display Order and Order_Details and below that the Product inventory information along with an Image Control showing off the new Northwind logo. This control is really easy to data bind because of how the entity associations are specified in our data model. Order has Order_Details collection and Order_Detail has a Product reference. When the WPF User Control's DataContext property is set to the list of Orders, the data binding will take care of displaying the related data properly. Here's how we set up the data bound controls (styles omitted for clarity):
<UserControl x:Class="OrderHistory"
xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml"
Height="300" Width="541" Name="OrderList" Grid.IsSharedSizeScope="True">
<UserControl.Resources>
<Style ...
</UserControl.Resources>
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="120" />
<RowDefinition Height="160*" />
</Grid.RowDefinitions>
<ListView IsSynchronizedWithCurrentItem="True"
ItemsSource="{Binding}" Name="ListViewOrder" Margin="0,0,0,6"
Style="{StaticResource ListViewStyle}">
<ListView.View>
<GridView ColumnHeaderContainerStyle="{StaticResource ColumnHeaderStyle}">
<GridViewColumn DisplayMemberBinding="{Binding Path =ShipName} "
Width="200">Ship to</GridViewColumn>
<GridViewColumn DisplayMemberBinding="{Binding Path=OrderDate, StringFormat ='d'} "
Width="100">Order Date</GridViewColumn>
<GridViewColumn DisplayMemberBinding="{Binding Path=ShippedDate, StringFormat ='d'} "
Width="100">Ship Date</GridViewColumn>
<GridViewColumn DisplayMemberBinding="{Binding Path=OrderTotal, StringFormat ='c2'} "
Width="100">Order Total</GridViewColumn>
</GridView>
</ListView.View>
</ListView>
<Grid Grid.Row="1" Margin="0" Name="DetailsGrid"
DataContext="{Binding Path =Order_Details}" >
<ListView ItemsSource="{Binding }" Style="{StaticResource ListViewStyle}"
IsSynchronizedWithCurrentItem="True" Name="ListViewDetails" Margin="0,0,0,81">
<ListView.View>
<GridView ColumnHeaderContainerStyle="{StaticResource ColumnHeaderStyle}">
<GridViewColumn Header="Product" Width="300"
DisplayMemberBinding="{Binding Path =Product.ProductName}" />
<GridViewColumn Header="Quantity" Width="100"
DisplayMemberBinding="{Binding Path=Quantity, StringFormat ='n0'} " />
<GridViewColumn Header="Unit Price" Width="100"
DisplayMemberBinding="{Binding Path=UnitPrice, StringFormat ='c2'} " />
</GridView>
</ListView.View>
</ListView>
<Image Height="75" HorizontalAlignment="Right" Name="Image1"
Stretch="None" VerticalAlignment="Bottom" Width="75"
Source="northwindlogo75.gif" SnapsToDevicePixels="True"
StretchDirection="DownOnly" MinHeight="75" MinWidth="75"
MaxHeight="75" MaxWidth="75" ClipToBounds="True" />
<StackPanel Height="75" Name="StackPanel1" VerticalAlignment="Bottom" Margin="0,0,81,0">
<TextBlock Height="25" Name="txtProductName" Width="Auto"
Text="{Binding Path =Product.ProductName, StringFormat=Inventory information for \{0\
}}" />
<TextBlock Height="25" Name="txtUnitsInStock" Width="Auto"
Text="{Binding Path =Product.UnitsInStock, StringFormat=Units in stock: \{0:n0\
}}" />
<TextBlock Height="25" Name="txtUnitsOnOrder" Width="Auto"
Text="{Binding Path =Product.UnitsOnOrder, StringFormat=Units on order: \{0:n0\
}} "/>
</StackPanel>
</Grid>
</Grid>
</UserControl>
Notice that there is a field I'm binding to called OrderTotal on the Order entity however if you go back to the data model (Northwind.edmx in the NorthwindService project) you won't see this field coming from the database. That's because it's a calculated property on the Order entity on the client. You can extend the entities on the client using Partial Classes. All you have to do is right click on the project and select Add-->Class, call it Order, and then make sure you specify the proper namespace of the service reference:
Imports NorthwindOutlookClient.NorthwindService
Namespace NorthwindService
Partial Public Class Order
Private _total As Decimal = 0
Public ReadOnly Property OrderTotal() As Decimal
Get
If _total = 0 Then
_total = Aggregate detail In Me.Order_Details _
Into Sum(detail.Quantity * detail.UnitPrice)
End If
Return _total
End Get
End Property
End Class
End Namespace
Now that we have our WPF control all designed we are ready to add it to our form region. EmailForm is a Windows Forms user control so in order to add the WPF user control we need to first add an ElementHost control. This is found in the WPF Interoperability Tab on your toolbox. When you drop this control onto the designer you can immediately select the WPF user control to display, in our case it shows the OrderHistory control we just designed (if you don't see it, build the project first).
I also set it to dock in the parent container so it takes up the entire region and set the font of the EmailForm region to Calibri 11. Lastly we can add the line of code back in our EmailForm_FormRegionShowing handler to set the list of Orders we returned from our data service to the DataContext of the WPF user control:
Dim ordersList As New List(Of Order)(customerOrders)
'Set this ordersList as the DataContext of our WPF user control
Me.OrderHistory1.DataContext = ordersList
That should do it! To test this you need to make sure the email address you'll be using is in the database so you'll have to add some test data. When we run it we should see the adjoining form region at the bottom of our customer emails (click to enlarge):
One tip to remove the Outlook Add-in when you're done debugging is to select Build --> Clean Solution menu item in Visual Studio to unregister the Add-In.
I've uploaded the code that we've talked about so far in this series to Code Gallery so have a look. I'll continue adding releases to this as we build the other pieces of the OBA. Next post I'll show how we can create a purchase order in Word 2007 and use that to automatically feed Order data into the Northwind database.
Enjoy!
Comments
Anonymous
February 07, 2009
PingBack from http://blog.a-foton.ru/index.php/2009/02/08/oba-part-2-building-an-outlook-client-against-lob-data/Anonymous
February 07, 2009
Thank you for submitting this cool story - Trackback from DotNetShoutoutAnonymous
February 08, 2009
The comment has been removedAnonymous
February 12, 2009
In my last few posts we've been building a simple Office Business Application (OBA) for the new NorthwindAnonymous
February 19, 2009
Beth Massi has been busy creating a number of great posts describing how to build add-ins to Excel, Outlook,Anonymous
March 08, 2009
In my last few app building posts we've been building a simple Office Business Application (OBA) forAnonymous
March 08, 2009
In my last few app building posts we've been building a simple Office Business Application (OBA)Anonymous
April 21, 2009
In my last few app building posts we've been building a simple Office Business Application (OBA) forAnonymous
April 22, 2009
Hi, am new to web development I just started couple of months ago. I would appreciate if you can let me know if it is possible to build a windows service(or something better) to automatically pull emails from Outlook and store them into the database automatically. Thanks