Using Outlook.Items.Restrict()

Recently, I was working on an Outlook Visual Studio Tools for Office (VSTO) solution and encountered a problem with unexpected results with the Restrict() method. What I was doing was trying to locate all the appointments (meeting and recurrence) that occurred today. The Restrict command was returning items where the Start and End date properties that were from 2000, 2001, 2010, etc. I quickly deduced that it was also returning recurring items that were dated back to the first occurrence. Here is what my code looked like:

///
<summary>
/// EXTENSION METHOD
/// Get all the appointments between the given date times
/// </summary>

/// <param name="PobjItems"></param>
/// <param name="PobjStart"></param>
/// <param name="PobjEnd"></param>
/// <returns></returns>
public static List<Outlook.AppointmentItem> FindAppointments(this Outlook.Items PobjItems,
                                              DateTime PobjStart,
                                              DateTime PobjEnd)
{
   try
   {
      List<Outlook.AppointmentItem> LobjReturn = new List<Outlook.AppointmentItem>();
      string LstrCriteria = "[Start] >= \"" + PobjStart.ToString("g") + "\" AND [End] <= \"" + PobjEnd.ToString("g") + "\"";
      // we want to find the oldest first. This way we do
      // not get stuck on all day appointments that are conflicting
      // with the current value.
      PobjItems.Sort("[End]");
      PobjItems.IncludeRecurrences = true;
      PobjItems = PobjItems.Restrict(LstrCriteria);
      object LobjItem = PobjItems.GetFirst();
      // chcek the appointments or meetings against the
      // the rules and set Skype status
      Outlook.AppointmentItem LobjAppt = null;
      do
      {
         if (LobjItem is Outlook.MeetingItem)
         {
            LobjAppt = ((Outlook.MeetingItem)LobjItem).GetAssociatedAppointment(false);
         }
         else
         {
            LobjAppt = (Outlook.AppointmentItem)LobjItem;
         }
         // as long as we are not null
         if (LobjItem != null)
         {
            LobjReturn.Add(LobjAppt);
            LobjItem = PobjItems.GetNext();
         }
      }
      while (LobjItem != null);
      // done
      return LobjReturn;
   }
   catch (Exception PobjEx)
   {
      PobjEx.Log(false);
      return null;
   }
}

What I created is an extension item off the Outlook.Items object that allows me to easily restrict them to appointments found between two given dates. This is actually a very useful extension method. If it worked the way I expected it to. What I found out is that in order to get recurring items that occur today which have todays date in the Start and End date properties, you MUST sort the results by the [Start] date. You cannot sort by the [End] date as I had done. Almost exactly the same, here is the fixed function:

///
<summary>
/// EXTENSION METHOD
/// Get all the appointments between the given date times
/// </summary>

/// <param name="PobjItems"></param>
/// <param name="PobjStart"></param>
/// <param name="PobjEnd"></param>
/// <returns></returns>
public static List<Outlook.AppointmentItem> FindAppointments(this Outlook.Items PobjItems,
                                              DateTime PobjStart,
                                              DateTime PobjEnd)
{
   try
   {
      List<Outlook.AppointmentItem> LobjReturn = new List<Outlook.AppointmentItem>();
      string LstrCriteria = "[Start] >= \"" + PobjStart.ToString("g") + "\" AND [End] <= \"" + PobjEnd.ToString("g") + "\"";
      // FIX: we must use [Start] in the Sort to get the right recurrence 
      // for todays date...
      PobjItems.Sort("[Start]");
      PobjItems.IncludeRecurrences = true;
      PobjItems = PobjItems.Restrict(LstrCriteria);
      object LobjItem = PobjItems.GetFirst();
      // chcek the appointments or meetings against the
      // the rules and set Skype status
      Outlook.AppointmentItem LobjAppt = null;
      do
      {
         if (LobjItem is Outlook.MeetingItem)
         {
            LobjAppt = ((Outlook.MeetingItem)LobjItem).GetAssociatedAppointment(false);
         }
         else
         {
            LobjAppt = (Outlook.AppointmentItem)LobjItem;
         }
         // as long as we are not null
         if (LobjItem != null)
         {
            LobjReturn.Add(LobjAppt);
            LobjItem = PobjItems.GetNext();
         }
      }
      while (LobjItem != null);
      // done
      return LobjReturn;
   }
   catch (Exception PobjEx)
   {
      PobjEx.Log(false);
      return null;
   }
}

OfficeJS: Create a (VERY) Basic Add-in for Excel

Recently, I was asked what is needed in order to create the most basic add-in you can. The least amount of files, work and effort. Technically, you only need two files:

  1. An HTML file with all your JavaScript inline and all the Office and JQuery libraries being pulled from a CDN.
  2. An XML Manifest file that defines the add-in.

You publish the HTML file to a web server, get the address to where it is published, update the manifest with that information and then install the XML file as an add-in. The XML file will define the add-in name/description and points to the HTML file for the code. When you launch the add-in, the task pane will open and the HTML page will be loaded in it. From there your JavaScript will execute and your add-in will come alive. It is THAT easy. 2 files.

To demonstrate this, I created a simple Excel add-in. Here is the code for the bare minimum manifest XML:

<?xml version="1.0" encoding="UTF-8"?>
<OfficeApp
xmlns="http://schemas.microsoft.com/office/appforoffice/1.1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
xsi:type="TaskPaneApp">
<Id>5226365f-62d0-435f-a4af-cc6a7bd753b2</Id>
<Version>1.0.0.1</Version>
<ProviderName>TheOfficeContext.com</ProviderName>
<DefaultLocale>en-US</DefaultLocale>
<DisplayName DefaultValue="BasicAddin" />
<Description DefaultValue="This is a basic addin."/>
<IconUrl DefaultValue="" />
<Hosts>
<Host Name="Workbook" />
</Hosts>
<DefaultSettings>
<SourceLocation DefaultValue="https://basicaddin.azurewebsites.net/home.html" />
</DefaultSettings>
<Permissions>ReadWriteDocument</Permissions>
</OfficeApp>

Here is the HTML file that is quite basic, I am not even using the Microsoft Fabric for the controls. This is a simple HTML page, period:

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<title>Excel Basic Add-In</title>
<!--using JQuery CDN so we do not need to include-->
<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
    <script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
<script type="text/javascript">
        (function () {
            "use strict";
            // The initialize function must be run each time a new page is loaded.
            Office.initialize = function (reason) {
                $(document).ready(function () {
                    // Add a click event handler for the button.
                    $('#simple-button').click(function () {
                        Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
                        function (result) {
                            if (result.status === Office.AsyncResultStatus.Succeeded) {
                                $("#banner-text").text('The selected text is: "' + result.value + '"');
                                $("#banner").show();
                            } else {
                                $("#banner-text").text('Error: ' + result.error.message);
                                $("#banner").show();
                            }
                        });
                    });
                    $("#banner-close").click(function () { $("#banner").hide(); });
                    $("#banner").hide();
                });
            }
        })();
    </script>
</head>
<body>
<div id="content-main">
<div class="padding">
<h1>Basic Addin</h1>
<div>Select a cell with text and then...</div>
<button id="simple-button">Click Here!</button>
</div>
</div>
<div id="banner" style="position:absolute;bottom: 0;">
<div id="banner-text"></div>
<button id="banner-close"> <i>X</i> </button>
</div>
</body>
</html>

 

What this add-in does is very simple. It opens the task pane with a single button. Enter some text in a cell in Excel, select that cell, and then click the button. It gets the text and displays it in a popup at the bottom of the pane. I have published this add-in manifest here:

https://basicaddin.azurewebsites.net/basicaddin.xml

You can run it by using these steps:

  1. Open Excel Online. I first log into https://outlook.office365.com. Then I click on the menu button in the upper left and select Excel.
  2. I select to create a “New blank workbook.”
  3. On the Insert tab, I click Add-ins.
  4. In the upper right, I select “Upload My Add-in”
  5. I click Browse and in the Filename box, I put https://basicaddin.azurewebsites.net/basicaddin.xml and then click Open. This will download the add-in to the cache on your system and you will get a file name like “basicaddin[1].xml.”
  6. Click Upload.
  7. The add-in will load from my Azure site and you will see the button demonstrated above.

Now, to go ahead and answer a question I know are coming. How did I upload this Azure without Visual Studio publishing tools:

  1. I went to my Azure Protal (portal.azure.com).
  2. I clicked the +New button on the left side.
  3. I selected Web + Mobile, then Web App
  4. I filled in the required information: Name and resource group and clicked Create.
  5. After it was done, I followed these steps to get to the site via FTP: https://blogs.msdn.microsoft.com/kaushal/2014/08/01/microsoft-azure-web-site-connect-to-your-site-via-ftp-and-uploaddownload-files/
  6. I uploaded ONLY the HTML file.
  7. I  got the path to the location from the Azure portal:
    capture
  8. Next, I updated the XML manifest to point to https://basicaddin.azurewebsites.net/home.html
  9. Then I used the steps above to load it into Excel Online.

This is fairly simple from a web developer perspective. Now, if you are not a web developer (but a traditional Office developer), you are probably still on the fence on how/why you would want to go through all of this. Again, the primary thing I am demonstrating here is how you can build an add-in with as few files as possible. But, I want to reiterate how these new add-ins are truly cross-platform. Follow these steps on your iPad and you will know what I mean. It is incredible and this work really is not that difficult to perform. It might just be a tad time consuming in the beginning as you are getting used to the new interfaces.

If you have questions, please feel free to reach out to me.

OneNote: Do Your Tabs Disappear?

Recently, a customer I work with asked me to investigate why the tabs in their shared OneNote notebooks were disappearing. It is especially prevalent when they moved to OneNote 2016. The cause as it turned out was something called Windows Deduplication. At its simplest, it is a way to reduduplicate files and similar streams of data on a disk. It is a form of compression at its simplest which is to say it is a mechanism to allow you to stick more stuff on a computer or server disk. What we found was the deduplication was corrupting the OneNote index (toc) on the server and each time someone went to make changes to the OneNote file on the server. Tabs would start disappearing for other users when multiple users were editing the files. When we enabled the exception for OneNote files in the data deduplication feature and then moved the OneNote files from one folder to another and then back (which is required to de-optimize the files), the problem went away. To resolve this problem you will likely need your administrator involved:

  1. You will need to set an exception for the OneNote file extensions in Windows Server 2012 Data Deduplication.
    • Here are some details on configuring that with PowerShell. See the “Modifying Data Deduplication volume-wide settings” section.
    • You will need to exclude these extensions: *.one, *.onebin, *.onetoc2
  2. Next, have everyone close OneNote on their systems.
  3. Finally, you will need to “deoptimize” (or, and I love this, de-dedup) the files. To do this, you will create a folder somewhere in the shared folder, move all the files in the OneNote folder to that location, then move them back.

NOTE: Nothing needs to be done on the clients, they will just work the next time the users launch OneNote. You might notice the tabs will still initially appear to be missing, but they will re-index and come back. This might take a few minutes depending on how many tabs there are.

Once completed, you should find that your tabs are no longer disappearing.

Office.js: Jump on!

I am asked the following questions all the time by customers interested in making the jump to Office.js. Therefore, I am providing this FAQ as a primer which usually helps open the door to deeper discussions:

  • What is Office.js exactly?

Office.js is a new way to develop customizations (or add-ins) for Office that are cross-platform. It is developed in JavaScript, runs from a webserver and loads into a content pane or task pane in the document or application.

  • Cross-platform?

Yes. You can currently install or use an Office application (Word, Excel, PowerPoint and Outlook), on any of these platforms: Windows, Mac, iOS, Android (soon) or Web. There are some restrictions on what is supported and how much of the toolset is available per platform. See this article: Office Add-in host and platform availability

  • Is Visual Basic for Macros (VBA), Visual Studio Tools for Office (VSTO), COM Add-ins or Macros going away then?

No. Office.js is just another development platform for Office. There are no plans to deprecate any of the other programmability options for Office. Office.js was designed with the Cloud First, Mobile first vision at Microsoft and expands customizations across multiple platforms. It can be thought of as add-ins that you write once, run anywhere.

  • I have an existing COM add-in. Do I need convert it to Office.js?

No. Unless of course you want your add-in to work across multiple platforms. However, it is important to consider what your add-in is doing. The current application programming interfaces (APIs) for Office.js are more limited. While they are increasing in capability every day, they are still not on parity with COM, VBA or VSTO.

  • Can I convert my add-in in Visual Studio?

No. This is because the new Office.js developer platform is completely new. There are no conversion tools. If you want to make the move to Office.js, you will likely need to start from scratch. However, if you have properly separated your backend logic from application interfaces, you might be able to reuse some of your existing code as a Web Service.

  • How do determine if my existing COM add-in can be rewritten in Office.js?

This is harder to answer. The short answer is you try to create it in the new platform and see how much you can get to function. Longer answer, there are a couple of approaches:

  • You rewrite the entire thing in the new platform and where some things do not translate or work the same, you change the requirements to meet the new platform restrictions, such as changing the workflow, number of features or expected results.
  • You write as much as you can in the new platform and keep building on it as the platform continues to take shape. Whatever you cannot accomplish in Office.js you write as a VSTO add-in.
  • You provide a more limited Office.js version for the non-Windows based platforms you support but still use your COM or VSTO add-in on Windows.
  • How do I develop an Office.js add-in?

There are a number of editors you can use. These are web based so technically you can use any platform you want. I suggest using Visual Studio where you have templates the provide you with predefined templates for Word, Excel, PowerPoint and Outlook Web Add-ins. Also, the Publishing process is much simplified in my opinion with the Visual Studio tools.

  • How do Office Web Add-ins work?

Office Web Add-ins are web pages. You publish the web pages to a web server and the entire solution will load and run from there. As part of developing the add-in, you will create an XML file called a manifest. The Manifest in simplest terms will define the name of your add-in, a unique ID for your add-in, its version, a description, any ribbon commands and the URL to the website. In Office you can then add the manifest and this will register the add-in in your Office application and load the ribbon buttons defined. When the user clicks the ribbons the manifest will tell Office what URL to load and then the add-in will appear in a content pane or task pane.

  • What if I have more questions?

Please ask in the comments section and I will work to continue to expand this FAQ.

Office.js: A Face palm moment with Outlook Client in Compose Mode

I have been working with Outlook Web Add-ins  and Compose Message integration for Office Outlook Web Access for a while now. That is, Outlook add-ins that present themselves when the user is composing an email. This scenario is not seemingly as common. But one of my customers has been completely gung-ho about getting all of their COM add-ins converted to the new Office.js model. Some of their solutions will be able to make it there, and some will not. We are finding some roadblocks with mail compose apps.

One area that is currently failing is setting X-headers on an email item. I developed a JavaScript library to assist with this: easyEws. I blogged about it here. The one specific EWS SOAP call my customer wants to use from that library is this one:

updateEwsHeader: Updated the named x-header in the message.

This function works great in two conditions:

  • Outlook Web Access (or Office 365 Outlook Online), and
  • Outlook client – Online mode only

If you use Outlook client (Outlook 2016) in cached mode, this call will seem to work (because it does), but the x-headers will be missing when the user presses “Send.” The problem is that while the currently composed item is saved to the drafts folder and the custom x-headers are written to it via the EWS call (server side), the current Outlook Inspector/session is NOT updated. The cached Drafts folder may take (depending on network latency) anywhere from 1 to 3 minutes to be updated, but regardless the item in the current inspector session will NOT ever be updated. So when the user presses SEND, the item in the drafts folder will be overwritten with one that does not contain the custom x-headers and then sent.

There is no way around this limitation. And I have confirmed this with the Exchange and Office.js teams that this is a limitation of cached mode in Outlook. If you have a need for custom x-headers with an Outlook add-in your only option for now is to create (or keep your existing) COM/VSTO add-in.

If you are running into this limitation, please let me know by commenting below.

Testing Office.JS: The Power of Choice

You know, sometimes the way you want to do things is not necessarily the right way. And even then the right way makes life so hard you want to cry. With Office.JS I recently blogged about “a way” you can test multiple sprints of your add-in in your development environment: Testing a Web Add-in with Multiple Manifest. In this article I explain a method of creating multiple manifests to point to each “version or sprint” of your add-in. Recently, I found that one of my customers threw a wrinkle into that idea. Not that creating multiple manifests was impossible, but they were needing two conflating factors:

  1. Each sprint points to a different folder of HTML/JS.
  2. And they needed to use query string parameters (like this post) to load one of maybe 20 different configuration settings.

With up to 20 configurations per sprint and lets say 4 sprints, you end up with 80 manifest files. So, this is untenable. You need more choices, then enter necessity… wlEmoticon-hotsmile.png

So, lets say you need to test multiple configurations and multiple sprints. Here is how you will set this up:

  • You will publish each sprint of your Web Site Project  into a different folder in IIS. Like this:
folder
Different sprints in separate folders
  • Then, you will create a new HTML page. I called mine Launcher.html. This page will contain a submit button and two drop-down select controls:
    • One for sprints
    • One for configs
  • Next, you will modify your manifest. You can add a new button called “Sprint Testing” for example and have it launch: Launcher.html.
  • In the Launcher.JS, populate those dropdowns. You can populate these hard coded into the HTML, from your client side JS or call your service controller. In my example, I call my service controller.
  • When the user clicks the submit button after choosing the desired settings, you will build the URL to the proper sprint folder and then append the config values to the current query string (that last item is critical so that your sprint Compose/Read page will get the important Office related query string values passed to it). You will see I do this below by replacing my “launcher.html”, replacing it will the new path, then append the config setting:
url = window.location.href.replace("launcher.html", sprint + "/ComposeMessage.html");
url += "&Config=" + config;
  • Once done your code will perform a location.replace() on the url and you will have your proper sprint and configuration loaded. You will only have a single manifest file and you will be able to test/regress test as much as needed with different sprints and configurations.

Here is what the HTML page I created looks like when loaded:

launcherpane

 

Here is the HTML:

<!DOCTYPE html>
<html>
<head>
    <title>Select Which Sprint to Launch</title>
    <meta charset="utf-8" />
    <script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
    <script src="Scripts/jquery-3.1.1.js" type="text/javascript"></script>
    <script src="launcher.js" type="text/javascript"></script>
</head>
<body>
<h3>Please select the sprint you wish to launch:</h3>
<select id="selectSprintList">
</select>
<h3>Please select the configuration:</h3>
<select id="selectConfigList">
</select>
    <button id="launchSprintButton">Submit</button>
</body>
</html>

Here is the code behind for the page:

/// <reference path="Scripts/jquery-3.1.1.js" />
(function () {
    'use strict';

    Office.initialize = function (reason) {
        $(document).ready(function (reason) {
            /// Upon load connect to the server and request
            /// the sprints so that we can fill the select
            /// field on the form.
            makeAjaxCall("GetSprints", null, function (response) {
                /** @type {String} */
                var data = response.Message.toString();
                /** @type {String[]} */
                var results = data.split(",");
                $.each(results, function (index, value) {
                    $("#selectSprintList").append("
<option>" + value + "</option>");
                })
            }, function (error) {
                $(document).append("

" + error + "

");
            });

            makeAjaxCall("GetConfigs", null, function (response) {
                /** @type {String} */
                var data = response.Message.toString();
                /** @type {String[]} */
                var results = data.split(",");
                $.each(results, function (index, value) {
                    $("#selectConfigList").append("
<option>" + value + "</option>");
                })
            });

            $("#launchSprintButton").click(function (event) {
                /// The user clicked the submit button. Build the URL
                /// from the selection in the select control and
                /// change the location
                var sprint = $("#selectSprintList").val();
                var config = $("#selectConfigList").val();
                var url = "";
                if (sprint != null && sprint != "" &&
                    config != null && sprint != "") {
                    // replace the launcher page with the proper sprint folder location,
                    // but be sure to keep all the query string values - to pass on
                    // to our OfficeJS page. However, add our one config setting to the end
                    url = window.location.href.replace("launcher.html", sprint + "/ComposeMessage.html");
                    url += "&Config=" + config;
                    // replace the url
                    location.replace(url);
                }
            });

        });
    }
})();

// Helper function to call the web service controller
makeAjaxCall = function (command, params, callback, error) {
    var dataToPassToService = {
        Command: command,
        Params: params
    };
    $.ajax({
        url: 'api/Default',
        type: 'POST',
        data: JSON.stringify(dataToPassToService),
        contentType: 'application/json;charset=utf-8',
        headers: { 'Access-Control-Allow-Origin': '*' },
        crossDomain: true
    }).done(function (data) {
        callback(data);
    }).fail(function (status) {
        error(status.statusText);
    })
};

And here is the code for the controller, now, I cheated and just returned a string value for each call, but this at least help you get the idea of how to build your own list for sprints and configs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace OutlookLauncherDemoWeb.Controllers
{
    public class DefaultController : ApiController
    {
        ///
<summary>
        /// Service Request - incoming
        /// </summary>

        public class WebServiceRequest
        {
            public string Command { get; set; }
            public string[] Params { get; set; }
        }

        ///
<summary>
        /// Service Response - outgoing
        /// </summary>

        public class WebServiceResponse
        {
            public string Status { get; set; }
            public string Message { get; set; }
        }

        [HttpPost()]
        public WebServiceResponse Values(WebServiceRequest request)
        {
            WebServiceResponse response = null;
            switch (request.Command)
            {
                case "GetSprints":
                    return getSprints();
                case "GetConfigs":
                    return getConfigs();
            }

            response = new WebServiceResponse();
            response.Message = "Unknown command";
            return response;
        }

        private WebServiceResponse getConfigs()
        {
            WebServiceResponse response = new WebServiceResponse();
            response.Message = "Config1,Config2,Config3,Config4,Config5,Config6,Config7,Config8";
            return response;
        }

        private WebServiceResponse getSprints()
        {
            WebServiceResponse response = new WebServiceResponse();
            response.Message = "sprint1,sprint2,sprint3,sprint4";
            return response;
        }
    }
}

The idea here is that this add-in is a baseline for yours. You can build these components into a debug build of your add-in and it will call into each separate folder/sprint where you have posted previous versions.

I have published the full source up on GitHub: https://github.com/davecra/OutlookLauncherDemo