Add-in Express Blog
Pieter van der Westhuizen

Calling Freshbooks web-service from Outlook, part 2

Posted on Tuesday, October 21st, 2014 at 9:57 am by .

In the previous article, we started with the basic concept and layout of our Freshbooks Outlook Add-in, see Integrating Outlook add-in with Freshbooks web-service, part 1.

So far, we’ve connected Outlook to the web-service by creating the Freshbooks specific folders, message classes and solution module, and in this article we’ll continue building our plug-in by doing the following:

Creating custom Outlook views for web-service data

Our first step with today’s article is to create custom views for the Freshbooks web-service folders. For this example, we’ll create a custom view to list the invoices in Freshbooks. Our end goal for the view is to resemble the following screenshot:

Calling the Freshbooks web-service and importing its data into the Outlook add-in.

To start creating the Outlook custom view, open the sample project we’ve created in the previous article, in the CreateFreshbooksFolder method and change the following line:

CreateFolder(freshbooksFolder, "Invoices", 
    Outlook.OlDefaultFolders.olFolderNotes, 
    "IPM.StickyNote.Freshbooks.Invoice", "Invoice");

To:

CreateFolder(freshbooksFolder, "Invoices", 
    Outlook.OlDefaultFolders.olFolderJournal,
    "IPM.Journal.Freshbooks.Invoice", "Invoice");

The folder type needs to change from Note to Journal, this is due to the fact that Outlook does not allow minimum control over the Note items’ inspector object.

Adding web-service specific properties to the Outlook folder

We’ll be using the Outlook Journal item to store any Freshbooks invoice related data. Since the Journal item does not have all the web-service specific properties built-in, we’ll need to add our own. To do this, add a new method called AddInvoiceFields to the AddinModule class.

The code for the AddInvoiceFields method follows below:

private void AddInvoiceFields(Outlook.Folder folder)
{
    AddPropertyToFolder(folder, "fb-clientid", Outlook.OlUserPropertyType.olNumber);
    AddPropertyToFolder(folder, "fb-invoicenumber", Outlook.OlUserPropertyType.olText);
    AddPropertyToFolder(folder, "fb-clientname", Outlook.OlUserPropertyType.olText);
    AddPropertyToFolder(folder, "fb-status", Outlook.OlUserPropertyType.olText);
    AddPropertyToFolder(folder, "fb-date", Outlook.OlUserPropertyType.olDateTime);
    AddPropertyToFolder(folder, "fb-ponumber", Outlook.OlUserPropertyType.olText);
    AddPropertyToFolder(folder, "fb-amount", Outlook.OlUserPropertyType.olCurrency);
}

Since we’ll be adding a lot of properties to the folder I’ve created a method called AddPropertyToFolder to make this a bit easier. The code for the AddPropertyToFolder method follows:

private void AddPropertyToFolder(Outlook.Folder folder, string propertyName, Outlook.OlUserPropertyType propertyType)
{
    Outlook._UserDefinedProperties userProperties = null;
    Outlook._UserDefinedProperty userProperty = null;
 
    try
    {
        userProperties = folder.UserDefinedProperties;
        userProperty = userProperties.Find(propertyName);
        if (userProperty == null)
            userProperty = userProperties.Add(propertyName, propertyType);
    }
    finally
    {
        if (userProperty != null) Marshal.ReleaseComObject(userProperty);
        if (userProperties != null) Marshal.ReleaseComObject(userProperties);
    }
}

With the previous two methods in place we can add the line to the CreateFreshbooksFolders method as suggested below. Add it just below where we created the Invoices folder, for example:

CreateFolder(freshbooksFolder, "Invoices", 
    Outlook.OlDefaultFolders.olFolderJournal, 
    "IPM.Journal.Freshbooks.Invoice", "Invoice");
AddInvoiceFields(GetFolder(freshbooksFolder.FolderPath + "\\Invoices"));

Creating web-service views from the Outlook add-in

We currently have all the required fields for the Freshbooks Invoice in our folder, all we need to do now is to create a new web-service specific view for the Outlook folder to show the invoice data in a tabular format. Create a new method called CreateInvoiceView and add the following code to it:

private void CreateInvoiceView(Outlook.Folder folder)
 {
     Outlook._Views folderViews = null;
     Outlook._TableView tableView = null;
     Outlook._ViewFields viewFields = null;
 
     Outlook._ViewField clientIdField = null;
     Outlook._ColumnFormat clientIdFieldFormat = null;
 
     Outlook._ViewField invoiceNumberField = null;
     Outlook._ColumnFormat invoiceNumberFieldFormat = null;
 
     Outlook._ViewField statusField = null;
     Outlook._ColumnFormat statusFieldFormat = null;
 
     Outlook._ViewField dateField = null;
     Outlook._ColumnFormat dateFieldFormat = null;
 
     Outlook._ViewField poNumberField = null;
     Outlook._ColumnFormat poNumberFieldFormat = null;
 
     Outlook._ViewField termsField = null;
     Outlook._ColumnFormat termsFieldFormat = null;
 
     try
     {
         folderViews = folder.Views;
         tableView = folderViews.Add("Freshbooks Invoices", Outlook.OlViewType.olTableView,
            Outlook.OlViewSaveOption.olViewSaveOptionThisFolderEveryone) as Outlook._TableView;
         tableView.XML = Properties.Settings.Default.EmptyJournalViewXml;
         tableView.ShowNewItemRow = false;
         tableView.AutomaticColumnSizing = true;
         tableView.AllowInCellEditing = false;
 
         viewFields = tableView.ViewFields;
 
         // Client Id
         clientIdField = viewFields.Add("fb-clientid");
         clientIdFieldFormat = clientIdField.ColumnFormat;
         clientIdFieldFormat.Label = "Client Id";
 
         // Invoice Number
         invoiceNumberField = viewFields.Add("fb-invoicenumber");
         invoiceNumberFieldFormat = invoiceNumberField.ColumnFormat;
         invoiceNumberFieldFormat.Label = "Invoice Number";
 
         // Status
         statusField = viewFields.Add("fb-status");
         statusFieldFormat = statusField.ColumnFormat;
         statusFieldFormat.Label = "Status";
 
         // Date
         dateField = viewFields.Add("fb-date");
         dateFieldFormat = dateField.ColumnFormat;
         dateFieldFormat.Label = "Date";
 
         // PO Number
         poNumberField = viewFields.Add("fb-ponumber");
         poNumberFieldFormat = poNumberField.ColumnFormat;
         poNumberFieldFormat.Label = "PO Number";
 
         // Terms
         termsField = viewFields.Add("fb-amount");
         termsFieldFormat = termsField.ColumnFormat;
         termsFieldFormat.Label = "Amount";
 
         //Remove unused view fields
         //  Icon
         viewFields.Remove("https://schemas.microsoft.com/mapi/proptag/0x0fff0102");
 
 
         tableView.Save();
         tableView.Apply();
     }
     finally
     {
         if (termsFieldFormat != null) Marshal.ReleaseComObject(termsFieldFormat);
         if (termsField != null) Marshal.ReleaseComObject(termsField);
         if (poNumberFieldFormat != null) Marshal.ReleaseComObject(poNumberFieldFormat);
         if (poNumberField != null) Marshal.ReleaseComObject(poNumberField);
         if (dateFieldFormat != null) Marshal.ReleaseComObject(dateFieldFormat);
         if (dateField != null) Marshal.ReleaseComObject(dateField);
         if (statusFieldFormat != null) Marshal.ReleaseComObject(statusFieldFormat);
         if (statusField != null) Marshal.ReleaseComObject(statusField);
         if (invoiceNumberFieldFormat != null) Marshal.ReleaseComObject(invoiceNumberFieldFormat);
         if (invoiceNumberField != null) Marshal.ReleaseComObject(invoiceNumberField);
         if (clientIdFieldFormat != null) Marshal.ReleaseComObject(clientIdFieldFormat);
         if (clientIdField != null) Marshal.ReleaseComObject(clientIdField);
         if (viewFields != null) Marshal.ReleaseComObject(viewFields);
         if (tableView != null) Marshal.ReleaseComObject(tableView);
         if (folderViews != null) Marshal.ReleaseComObject(folderViews);
     }
 }

A lot is happening in the code above. First, we get a reference to the passed in folders’ Views collection, next we added a new custom Outlook view called “Freshbooks Invoices” to it and set its XML property to a bare bones version of the view, that contains only the necessary web-service specific fields. The XML we’re using, is as follows:

<?xml version="1.0"?>
<view type="table">
	<viewname>New view 2</viewname>
	<viewstyle>table-layout:fixed;width:100%;font-family:Segoe UI;font-style:normal;font-weight:normal;font-size:8pt;color:Black;font-charset:0</viewstyle>
	<viewtime>217633802</viewtime>
	<linecolor>8421504</linecolor>
	<linestyle>3</linestyle>
	<previewlines>0</previewlines>
	<previewlineschangenum>2</previewlineschangenum>
	<gridlines>1</gridlines>
	<collapsestate/>
	<rowstyle>background-color:White;color:Black</rowstyle>
	<headerstyle>background-color:#D3D3D3</headerstyle>
	<arrangement>
		<autogroup>0</autogroup>
		<enablexfc>0</enablexfc>
		<collapseclient/>
		<collapseconv/>
		<upgradetoconvchangenum>1</upgradetoconvchangenum>
	</arrangement>
	<multiline>
		<gridlines>0</gridlines>
	</multiline>
	<column>
		<name>HREF</name>
		<prop>DAV:href</prop>
		<checkbox>1</checkbox>
	</column>
	<column>
		<heading>Icon</heading>
		<prop>https://schemas.microsoft.com/mapi/proptag/0x0fff0102</prop>
		<bitmap>1</bitmap>
		<width>18</width>
		<style>padding-left:3px;;text-align:center</style>
		<editable>0</editable>
	</column>
	<orderby>
		<order>
			<heading>Start</heading>
			<prop>https://schemas.microsoft.com/mapi/id/{0006200A-0000-0000-C000-000000000046}/87060040</prop>
			<type>datetime</type>
			<sort>desc</sort>
		</order>
	</orderby>
	<groupbydefault>2</groupbydefault>
	<previewpane>
		<markasread>0</markasread>
	</previewpane>
</view>

After setting the XML property we added each custom property we’ve had created in the folder earlier, to the Outlook view, we also removed the Icon fields by calling the Remove method on the ViewFields object. Lastly, we saved and applied our own view to the web-service folder.

The last step in creating and applying this view to the folder is to call the CreateInvoiceView method, by adding it just below the area where we called the AddInvoiceFields method in the CreateFreshbooksFolder method e.g.:

CreateFolder(freshbooksFolder, "Invoices", 
    Outlook.OlDefaultFolders.olFolderJournal, 
    "IPM.Journal.Freshbooks.Invoice", "Invoice");
AddInvoiceFields(GetFolder(freshbooksFolder.FolderPath + "\\Invoices"));
CreateInvoiceView(GetFolder(freshbooksFolder.FolderPath + "\\Invoices"));

Calling the web-service from Outlook and importing Freshbooks data

Next, we need to call the Freshbooks web-service in order to import the invoice data into our Outlook folder. The Freshbooks API is pretty straight forward and can be accessed using HTTP and XML. However, to make things easier we’ll be using a C# helper library by SmartVault Corp.

The helper library provides a single assembly wrapper library around the Freshbooks API and the easiest way to get it into your project is to use the Nuget package. To do this, open the Package Manager Console inside Visual Studio and type the following command:

Adding the Freshbooks library to your project.

This will add the necessary references to your project. Next, we’ll need an Outlook Ribbon button that the user will need to click on, in order to import the Freshbooks data by calling the web-service API.

In your Add-in Express based Outlook add-in project, add a new ADXRibbonTab component to the AddinModule designer surface, by clicking on its toolbar button.

Adding a new Ribbon Tab component to the Addin Module designer surface.

Next, design your custom Outlook ribbon tab to resemble the following image:

Design your custom Outlook ribbon tab.

We’ll be using the “Invoices” button to import the web-service data into the current Outlook folder. Select the “Invoices” button and double-click next to the OnClick property in its property window to generate an empty OnClick event handler.

Generate an empty OnClick event handler.

Add the following code to the event handler:

private void adxImportInvoicesRibbonButton_OnClick(object sender, IRibbonControl control, bool pressed)
{
    Outlook.Explorer currExplorer = null;
    Outlook.Folder currFolder = null;
    Outlook.Items folderItems = null;
    Outlook._PropertyAccessor propertyAccessor = null;
    Outlook.JournalItem invoiceItem = null;
 
    try
    {
        currExplorer = OutlookApp.ActiveExplorer();
        currFolder = currExplorer.CurrentFolder as Outlook.Folder;
        folderItems = currFolder.Items;
        propertyAccessor = currFolder.PropertyAccessor;
        object msgClass = null;
        try
        {
            msgClass = propertyAccessor.GetProperty("https://schemas.microsoft.com/mapi/proptag/0x36E5001F");
        }
        catch { }
 
        if (msgClass != null && msgClass.Equals("IPM.Journal.Freshbooks.Invoice"))
        {
            var api = new FreshbooksApi("Your Account Name", "Your Consumer Key");
            api.UseLegacyToken("Your User Token");
 
            var invoicesRequest = new InvoicesRequest();
            invoicesRequest.PerPage = 100;
            var invoicesResponse = api.Invoice.List(invoicesRequest);
            foreach (var invoice in invoicesResponse.Invoices.InvoiceList)
            {
                invoiceItem = folderItems.Add(Outlook.OlItemType.olJournalItem) as Outlook.JournalItem;
                invoiceItem.Body = invoice.Number;
                invoiceItem.SetProperty("fb-clientid", invoice.Number, Outlook.OlUserPropertyType.olNumber);
                invoiceItem.SetProperty("fb-invoicenumber", invoice.Number, Outlook.OlUserPropertyType.olText);
                invoiceItem.SetProperty("fb-status", invoice.Status, Outlook.OlUserPropertyType.olText);
                invoiceItem.SetProperty("fb-ponumber", invoice.PoNumber, Outlook.OlUserPropertyType.olText);
                invoiceItem.SetProperty("fb-amount", invoice.Amount, Outlook.OlUserPropertyType.olCurrency);
                invoiceItem.SetProperty("fb-date", invoice.Date, Outlook.OlUserPropertyType.olDateTime);
                invoiceItem.Save();
                Marshal.ReleaseComObject(invoiceItem);
            }
        }
 
    }
    finally
    {
        if (propertyAccessor != null) Marshal.ReleaseComObject(propertyAccessor);
        if (folderItems != null) Marshal.ReleaseComObject(folderItems);
        if (currFolder != null) Marshal.ReleaseComObject(currFolder);
        if (currExplorer != null) Marshal.ReleaseComObject(currExplorer);
    }
}

In the code above, we obtained a reference to the current folder and by using the PropertyAccessor object we checked the folders’ message class. If the message class is our custom message class for Freshbooks invoices e.g. IPM.Journal.Freshbooks.Invoice , we’ll use the Freshbooks API to get a list of invoices from Freshbooks and add it to the folder using the Outlook.Items objects’ Add method.

You’ll notice that we’re using a method called SetProperty on the Outlook.JournalItem object to set the user property’s value. This is a non-standard extension method I’ve added to make it easier to set user properties for Outlook Journal items. The code for this extension method should be inside a static class, and follows below:

public static void SetProperty(this Outlook.JournalItem journal, string propertyName, object propertyValue, Outlook.OlUserPropertyType propertyType)
{
    Outlook.ItemProperties itemProperties = null;
    Outlook.ItemProperty itemProperty = null;
 
    try
    {
        itemProperties = journal.ItemProperties;
        itemProperty = itemProperties[propertyName];
        if (itemProperty != null)
        {
            itemProperty.Value = propertyValue;
        }
        else
        {
            itemProperty = itemProperties.Add(propertyName, propertyType, true);
            itemProperty.Value = propertyValue;
        }
    }
    finally
    {
        if (itemProperty != null) Marshal.ReleaseComObject(itemProperty);
        if (itemProperties != null) Marshal.ReleaseComObject(itemProperties);
    }
}

Replacing the Outlook Inspector UI for Freshbooks Invoices

Since we’re using the Outlook JournalItem to store invoice data, when you open an invoice item, you will see the standard Outlook Journal Inspector window. We’ll need to replace it with our own custom UI that will show the Freshbooks Invoices’ data.

To do this, we first need to add a new ADXOlFormsManager to the AddinModule designer surface.

Adding a new ADXOlFormsManager to the Addin Module designer surface.

Next, add a new Outlook Form to your project.

Adding a new Outlook form to your project.

Our Outlook form will contain some basic information about the invoice and its design could resemble the following:

A custom Outlook form containing basic information about the  invoice

Next, create a new event handler for the forms’ ADXAfterFormShow event and add the following code to it:

private void frmInvoice_ADXAfterFormShow()
{
    Outlook.Application outlookApp = null;
    Outlook.Inspector currInspector = null;
    Outlook.JournalItem currItem = null;
 
    try
    {
        outlookApp = this.OutlookAppObj as Outlook.Application;
        currInspector = outlookApp.ActiveInspector() as Outlook.Inspector;
 
        if (currInspector.CurrentItem is Outlook.JournalItem)
        {
            currItem = currInspector.CurrentItem as Outlook.JournalItem;
            txtClientId.Text = currItem.GetPropertyValue("fb-clientid").ToString();
            txtInvoiceNumber.Text = currItem.GetPropertyValue("fb-invoicenumber").ToString();
            txtStatus.Text = currItem.GetPropertyValue("fb-status").ToString();
            txtPONumber.Text = currItem.GetPropertyValue("fb-ponumber").ToString();
            txtAmount.Text = currItem.GetPropertyValue("fb-amount").ToString();
            txtDate.Text = currItem.GetPropertyValue("fb-date").ToString();
        }
 
    }
    finally
    {
        if (currItem != null) Marshal.ReleaseComObject(currItem);
        if (currInspector != null) Marshal.ReleaseComObject(currInspector);
    }
}

The code above will load the values stored in the Journal items’ user properties into the UI controls. You’ll notice we used another extension method called GetPropertyValue to get the user property’s value. The code for this extension method follows below:

public static object GetPropertyValue(this Outlook.JournalItem journal, string propertyName)
{
    Outlook.ItemProperties itemProperties = null;
    Outlook.ItemProperty itemProperty = null;
    object returnValue = null;
 
    try
    {
        itemProperties = journal.ItemProperties;
        itemProperty = itemProperties[propertyName];
        if (itemProperty != null)
        {
            returnValue = itemProperty.Value;
            return returnValue;
        }
        return returnValue;
    }
    finally
    {
        if (itemProperty != null) Marshal.ReleaseComObject(itemProperty);
        if (itemProperties != null) Marshal.ReleaseComObject(itemProperties);
    }
}

Switch back to the AddinModule designer surface and select the ADXOlFormsManager we’ve added earlier. Add a new item to the items collection and set the following properties for it:

  • FormClassName : Freshbooks_Addin.frmInvoice
  • InspectorItemTypes : Journal
  • InspectorLayout : CompleteReplacement
  • InspectorMessageClass : IPM.Journal.Freshbooks.Invoice

Build, register and run your Outlook add-in. When double-clicking to open a Freshbooks invoice item inside Outlook, you should see your custom form displayed with the Freshbooks invoice data:

The Freshbooks web-service invoice data integrated into an Outlook add-in

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample Outlook add-in was developed using Add-in Express for Office and .net:

Freshbooks Outlook add-in (C#)

How to integrate Outlook add-in with the Freshbooks web-service

, ,
Filed under Add-in Express for Office and .net

Post a comment

Have any questions? Ask us right now!

Add-in Express Feedback
If you have any questions or other feedback about the blog posts or our products, please write to us using this form.