Office 365 API – Querying Exchange

In our last article, we explored how to authenticate using the Office 365 Unified API. Today, we’ll built onto what we’ve learned previously and investigate how to use the Office 365 API to retrieve data such as contacts, calendar entries and e-mails from Exchange.

We’ll create a simple Excel add-in, using our favourite Office development toolset, Add-in Express for Office and .net, which will import Exchange e-mails, contacts and calendar events into the active Excel workbook.

• The Microsoft Office 365 API tools for Visual Studio 2013
• Creating the Excel add-in
• Adding the Nuget packages
• Creating a custom ribbon tab
• Seeing the Office 365 Exchange code in action

The Microsoft Office 365 API tools for Visual Studio 2013

The Office 365 API tools make it easier for developers to integrate with Office 365 by adding a menu item to the Solution Explorer’s context menu. If you’re using Visual Studio 2013, you can download and install the Office 365 API tools from Visual Studio Gallery.

To add a connected service, right-click on your project name inside Visual Studio and select Add > Connected Service.

This will show a dialog window with which you can register your app on Azure AD as well as specify which permission will be required for your app.

All the required Nuget packages and references will be automatically added to your project, which will make it easier to get started using the Office 365 API. Unfortunately, this feature only works with certain project types, so when we try using the Add > Connected Service feature in an Add-in Express project, we’ll see the following error:

Luckily, this is not a major problem, as it simply means we’ll need to manually register our app, as explained in our previous article, in Azure AD and add the required Nuget packages manually.

Creating the Excel add-in

Since we’ll import our Office 365 Exchange data into Excel, we’ll have to create a new ADX COM Add-in project first.

On the next form, select your programming language (C#, VB.NET or C++.NET) as well as the minimum version of Office that your add-in needs to support.

Lastly, select the supported applications, in this example we only need to support Microsoft Excel, and then finish the wizard.

Adding the Nuget packages

Normally, the Add Connected Services feature would add all the required Nuget Packages for you in order to access the Office 365 data. Because this feature is not available with ADX Add-in projects, we’ll need to add the required libraries manually.

We’ll use the Package Manager Console to install the Nuget packages. To open the console, select Package Manager Console, from the Visual Studio TOOLS > Nuget Package Manager menu.

Next, install the Active Directory Authentication Library by entering the following command in the Package Manager Console:

Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.18.206251556

This will install the above mentioned Nuget package as well as any additional libraries it requires. We’ll need to install one more library in order to read our Exchange data from Office 365. To install the .NET Client Library for Office 365 Mail, Calendar and Contacts API enter the following command in the Package Manager Console, and hit the Enter key:

Install-Package Microsoft.Office365.OutlookServices

Creating a custom ribbon tab

With the necessary libraries added to our project, we can go ahead and create a custom Ribbon Tab for Excel, which we’ll use to create the necessary button with which the user can import their Office 365 Exchange data into Excel. Open the AddinModule designer surface and add a new ribbon tab by clicking on the ADXRibbonTab button on the toolbar.

We’ll add three buttons to the newly created OFFICE 365 ribbon tab and the design will resemble the following image:

Select the Get Contacts button and generate an event handler for its OnClick event by double-clicking next to the OnClick event name in the Visual Studio Properties window.

Add the following code to the OnClick event method:

private async void adxRibbonButtonGetContacts_OnClick(object sender,
    IRibbonControl control, bool pressed)
{
    contacts = await GetContacts();
    this.SendMessage(MESSAGE_CONTACTS);
}

In the code above, we called a method named GetContacts. This event asynchronously returns contacts from Office 365 Exchange. You will note, that in order to call this method from our OnClick event, we need to change the event to use the async keyword. Also, because it is an async method we’ll need to use the SendMessage method in order to safely retrieve the results in Excel.

The code for the GetContacts method is as follows:

private async Task<IPagedCollection> GetContacts()
{
    if (string.IsNullOrEmpty(accessToken))
        Authenticate();
 
    if (!string.IsNullOrEmpty(accessToken))
    {
        try
        {
            var client = new OutlookServicesClient(new Uri("https://outlook.office365.com/api/v1.0"), async () => accessToken);
 
            var contactResults = (IPagedCollection)await client.Me.Contacts
                .OrderByDescending(m => m.DateTimeCreated)
                .Take(10)
                .Select(m => new { m.DisplayName, m.DateTimeCreated, m.CompanyName, m.JobTitle })
                .ExecuteAsync();
 
            return contactResults;
        }
        catch { }
    }
    return null;
}

In the GetContacts method, we first need to check if a local variable, called accessToken, contains the access token required to authenticate to Office 365. If it is empty, we need to call the Authenticate method to return the access token. The code for the Authenticate method follows below:

public void Authenticate()
{
    var authContext = new AuthenticationContext(authority);
    var authResult1 = authContext.AcquireToken(resourceId, clientId, new Uri(redirectUri),
        PromptBehavior.Auto);
 
    accessToken = authResult1.AccessToken;
}

As mentioned earlier, to safely process the data we retrieve asynchronously we’ll need to use the OnSendMessage event. To generate an event handler for it, switch back to the AddinModule designer and double-click next to the OnSendMessage event name in the properties window’s event list:

Add the following to the OnSendMessage event handler:

private void AddinModule_OnSendMessage(object sender, ADXSendMessageEventArgs e)
{
    if (e.Message == MESSAGE_CONTACTS)
        HandleContacts();
 
    if (e.Message == MESSAGE_EVENTS)
        HandleEvents();
 
    if (e.Message == MESSAGE_MAILS)
        HandleMails();
}

In the above code, we call three methods to process Contacts, Calendar Events and Mail messages respectively. The code for the methods follows:

private void HandleContacts()
{
    Excel.Worksheet currSheet = null;
    Excel.Range displayNameRange = null;
    Excel.Range jobTitleRange = null;
    Excel.Range companyNameRange = null;
 
    try
    {
        if (contacts != null)
        {
            currSheet = ExcelApp.ActiveSheet as Excel.Worksheet;
            displayNameRange = currSheet.Range["A1"];
            displayNameRange.Value = "Display Name";
 
            jobTitleRange = currSheet.Range["B1"];
            jobTitleRange.Value = "Job Title";
 
            companyNameRange = currSheet.Range["C1"];
            companyNameRange.Value = "Company Name";
 
            int rowCount = 2;
            foreach (dynamic contact in contacts.CurrentPage)
            {
                currSheet.Range["A" + rowCount].Value = contact.DisplayName;
                currSheet.Range["B" + rowCount].Value = contact.JobTitle;
                currSheet.Range["C" + rowCount].Value = contact.CompanyName;
 
                rowCount++;
            }
        }
    }
    finally
    {
        if (companyNameRange != null)
            Marshal.ReleaseComObject(companyNameRange);
        if (jobTitleRange != null)
            Marshal.ReleaseComObject(jobTitleRange);
        if (displayNameRange != null)
            Marshal.ReleaseComObject(displayNameRange);
        if (currSheet != null)
            Marshal.ReleaseComObject(currSheet);
    }
}
private void HandleEvents()
{
    Excel.Worksheet currSheet = null;
    Excel.Range subjectRange = null;
    Excel.Range startRange = null;
    Excel.Range endRange = null;
    Excel.Range typeRange = null;
 
    try
    {
        if (events != null)
        {
            currSheet = ExcelApp.ActiveSheet as Excel.Worksheet;
            subjectRange = currSheet.Range["A1"];
            subjectRange.Value = "Subject";
 
            startRange = currSheet.Range["B1"];
            startRange.Value = "Start";
 
            endRange = currSheet.Range["C1"];
            endRange.Value = "End";
 
            typeRange = currSheet.Range["D1"];
            typeRange.Value = "Type";
 
            int rowCount = 2;
            foreach (dynamic contact in events.CurrentPage)
            {
                currSheet.Range["A" + rowCount].Value = contact.Subject;
                currSheet.Range["B" + rowCount].Value = contact.Start.ToString();
                currSheet.Range["C" + rowCount].Value = contact.End.ToString();
                currSheet.Range["D" + rowCount].Value = contact.Type.ToString();
 
                rowCount++;
            }
        }
    }
    finally
    {
        if (typeRange != null)
            Marshal.ReleaseComObject(typeRange);
        if (endRange != null)
            Marshal.ReleaseComObject(endRange);
        if (startRange != null)
            Marshal.ReleaseComObject(startRange);
        if (subjectRange != null)
            Marshal.ReleaseComObject(subjectRange);
        if (currSheet != null)
            Marshal.ReleaseComObject(currSheet);
    }
}
 
private void HandleMails()
{
    Excel.Worksheet currSheet = null;
    Excel.Range fromRange = null;
    Excel.Range subjectRange = null;
    Excel.Range dateReceivedRange = null;
    Excel.Range hasAttachmentRange = null;
 
    try
    {
        if (mails != null)
        {
            currSheet = ExcelApp.ActiveSheet as Excel.Worksheet;
            fromRange = currSheet.Range["A1"];
            fromRange.Value = "From";
 
            subjectRange = currSheet.Range["B1"];
            subjectRange.Value = "Subject";
 
            dateReceivedRange = currSheet.Range["C1"];
            dateReceivedRange.Value = "Date Received";
 
            hasAttachmentRange = currSheet.Range["D1"];
            hasAttachmentRange.Value = "Has Attachments";
 
            int rowCount = 2;
            foreach (dynamic contact in mails.CurrentPage)
            {
                currSheet.Range["A" + rowCount].Value = contact.Name;
                currSheet.Range["B" + rowCount].Value = contact.Subject;
                currSheet.Range["C" + rowCount].Value = contact.DateTimeReceived.ToString();
                currSheet.Range["D" + rowCount].Value = contact.HasAttachments.ToString();
 
                rowCount++;
            }
        }
    }
    finally
    {
        if (hasAttachmentRange != null)
            Marshal.ReleaseComObject(hasAttachmentRange);
        if (dateReceivedRange != null)
            Marshal.ReleaseComObject(dateReceivedRange);
        if (subjectRange != null)
            Marshal.ReleaseComObject(subjectRange);
        if (fromRange != null)
            Marshal.ReleaseComObject(fromRange);
        if (currSheet != null)
            Marshal.ReleaseComObject(currSheet);
    }
}

We’ll complete the same procedure for the other two buttons. The code for the Get Calendar Events button’s OnClick method is reflected below:

private async void adxRibbonButtonGetEvents_OnClick(object sender,
    IRibbonControl control, bool pressed)
{
    events = await GetEvents();
    this.SendMessage(MESSAGE_EVENTS);
}

The code above invoked the GetEvents method, which in turn returned the calendar data from Exchange. The code for the GetEvents method is as follows:

private async Task<IPagedCollection> GetEvents()
{
    if (string.IsNullOrEmpty(accessToken))
        Authenticate();
 
    if (!string.IsNullOrEmpty(accessToken))
    {
        try
        {
            var client = new OutlookServicesClient(new Uri("https://outlook.office365.com/api/v1.0"), async () => accessToken);
 
            var eventResults = (IPagedCollection)await client.Me.Events
                .OrderByDescending(m => m.DateTimeCreated)
                .Take(10)
                .Select(m => new { m.Subject, m.DateTimeCreated, m.Start, m.End, m.Type })
                .ExecuteAsync();
 
            return eventResults;
        }
        catch { }
    }
    return null;
}

The last button’s code that will fetch the mail messages from Exchange’s OnClick method follows below:

private async void adxRibbonButtonGetMail_OnClick(object sender,
    IRibbonControl control, bool pressed)
{
    mails = await GetMails();
    this.SendMessage(MESSAGE_MAILS);
}

The GetMails method does the actual work of retrieving a mail message from Exchange and its code is as follows:

private async Task<IPagedCollection> GetMails()
{
    if (string.IsNullOrEmpty(accessToken))
        Authenticate();
 
    if (!string.IsNullOrEmpty(accessToken))
    {
        try
        {
            var client = new OutlookServicesClient(new Uri("https://outlook.office365.com/api/v1.0"), async () => accessToken);
 
            var eventResults = (IPagedCollection)await client.Me.Messages
                .OrderByDescending(m => m.DateTimeReceived)
                .Take(10)
                .Select(m => new { m.Subject, m.DateTimeReceived, m.From.EmailAddress.Name, m.HasAttachments })
                .ExecuteAsync();
 
            return eventResults;
        }
        catch { }
    }
    return null;
}

In order for the code we’ve created above to work, we also need to declare the following local variables inside the AddinModule class:

private string clientId = "bb4c578d-60ad-4406-873f-cdb05493100b";
private string authority = "https://login.microsoftonline.com/common";
private string redirectUri = "https://localhost/e62ac7d84f3adff953620a75c0a07592";
private string resourceId = "https://outlook.office365.com/";
private string accessToken = string.Empty;
 
private int MESSAGE_CONTACTS = 0x0400 + 900;
private int MESSAGE_EVENTS = 0x0400 + 901;
private int MESSAGE_MAILS = 0x0400 + 902;
 
private IPagedCollection contacts = null;
private IPagedCollection events = null;
private IPagedCollection mails = null;

Seeing the Office 365 Exchange code in action

With all the necessary code in place, we can build, register and run our Excel add-in. You’ll notice our OFFICE 365 tab inside Excel.

When clicking on any of the three buttons, you should be presented with an Office 365 login form.

After signing in, you’ll be presented with a form indicating what type of permission the Office app will require. In this example, it will be to access Exchange contacts, calendars and emails.

Click Accept and you should see your contacts data inside Microsoft Excel.

I’d like to extend a special thank you to Dmitry for helping out with the SendMessage and Async challenges in this article. Thanks Dmitry!

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:

Office 365 Querying Exchange sample