Category: Office Development

Simple Client-Side JavaScript MIME Parser

davecraLeave a comment

Today, I have been working on JavaScript for the first time in a while. I have spent a lot of time lately in VSTO and specifically targeting Outlook. It was good to get back to the OfficeJS side of the house.

A customer of mine requested if there was a way to easily parse the MIME content from the easyEws library call: getMailItemMimeContent

I found a number of server-side JavaScript libraries but nothing that would help me with parsing the data on the client-side. Here is the class I created and I am contemplating incorporating it into it’s own library at some point and expanding on it. But for now, here it is:

To use this, I am demonstrating from an On Send event in OfficeJS. I grab the mail item being composed, save it to make sure it is in the Drafts folder, grab the ID of the message, then use that to grab the MIME data. Here is the sample code:

Using Azure Functions in Excel

davecraLeave a comment

I have been delving more and more into Azure recently. In looking for a way to build an Excel User Defined Function (UDF) that did not require Visual Basic for Applications (VBA) or similar, I looked at what I could do with:

And to my surprise it was actually very easy to set this up. Here are the steps:

  1. Log into https://portal.azure.com
  2. Click “Create a Resource” in the upper left
  3. Type “Functions App” then “Create”
  4. Then follow the directions to create your own Functions App called “ExcelFunctions,” for example.
  5. You will want to create an HTTP Trigger function and I called mine “AddNumbers”
  6. You will be asked where you want to edit the function and I chose online in the browser, which is what makes this the coolest thing ever.
  7. Here is what my function looks like:

Once you have created your function, you can also test it right there in Azure. On the Right is a tab called “Test.” You can select it, and then add parameters like this:

azurefunctiontest

You can click “Save and Run” at the top of the page to run the function and verify the result: 42. Now, to get this to work in Excel, you need the URL. To do this click the “</> Get Function URL” button:

geturl

This will give you value like this:

https://xxx.azurewebsites.net/api/AddNumbers?code=123456…==

Now the fun part, getting this to work in Excel.

  1. Open Excel and create a new Workbook
  2. In  A1 type 100, in B1 type -58
  3. Select C1 and add the following function:

=WEBSERVICE("https://xxx.azurewebsites.net/api/AddNumbers?code=123456...==&value1=" & A1 & "&value2=" & B1)

Once you press enter and Excel updates you should see the value of 42 appear.

This is a very simple example of how you can implement an Azure Function in Excel as a User Defined Function. Imagine all the possibilities where you can collect data, analyze results, use Cognitive Services and vast stores of data from a Data Lake and much, much more. The possibilities are endless.

Visual Studio 2017: This is an invalid xsi:type… mailappversionoverrides/1.1:Event

davecra1 Comment

If you have been using Visual Studio 2017 to create an Outlook Web Add-in and are trying to use the new ItemSend event, you will note per the documentation the you need to add the MailAppVersionOverrides 1.1 to your manifest:

<VersionOverrides xmlns="http://schemas.microsoft.com/office/mailappversionoverrides/1.1" xsi:type="VersionOverridesV1_1">

The problem is that when you try to build and run the project, you get this exception:

Severity Code Description
Warning This is an invalid xsi:type ‘http://schemas.microsoft.com/office/mailappversionoverrides/1.1:Events&#8217;.

a

Specifically, Visual Studio 2017 does not like this line:

<ExtensionPoint xsi:type="Events">

If you are like me you have been hoping for an update to Visual Studio 2017 to fix this, but I just got word today that it did not get into the last update to Visual Studio 2017, but will make it into Visual Studio 2019. However, you do not need to run off and install Visual Studio 2019 once it is available. There is a workaround, and here are the steps to follow:

  • If you have an updated Exchange 2016 Server CU9 or later, you can browse to this folder: 
    C:\Program Files\Microsoft\Exchange Server\V15\Bin
  • Locate and copy the file MailAppVersionOverridesV1_1.xsd and copy it to a removable drive or a network share.
  • On your development machine, locate the existing MailAppVersionOverridesV1_1.xsd. It should be located here: 
    C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Xml\Schemas\1033
  • Rename the existing file to MailAppVersionOverridesV1_1.old.
  • Copy the version of the MailAppVersionOverridesV1_1.xsd you got from your Exchange Server here.

At this point you should be good to go.

But wait!?!?! You say you don’t have an Exchange Server? You use Office 365 and your instance of Exchange is up in the cloud? OK… Now this is not the “preferred” method, but it will get the job done. I created a GIST, and you can download it from here. wlEmoticon-hotsmile.png Simply view RAW, copy, open Notepad, paste, Save As: MailAppVersionOverridesV1_1.xsd, and then you have your file.

makeEwsRequestAsync Error: “The requested web method is unavailable to this caller or application”

davecraLeave a comment

If you get the following error when using the Office.context.mailbox.makeEwsRequestAsync() method, it is probably because the supported method is not available to this function:

The requested web method is unavailable to this caller or application

The details of the supported function are outlined in this article on MSDN. If you do not see the method you are trying to call in that table, it is not supported.

However, if you are like me and you do see your function in there, like “ExpandDL” it might be because when you recently updated to CU9 or CU10 (this method was added in CU9), you did not run setup with the “PrepareAD” switch. This last step is required in order to update the policies that allow this function to work. You can also run these two commands from the Exchange PowerShell to update your server:

  • Install-CannedRbacRoles
  • Install-CannedRbacRoleAssignments

NOTE: If these commands are not found, you need to first run this command:
Add-PsSnapin Microsoft*

 

EWS: ExpandDL documentation is wrong

davecraLeave a comment

I am not certain how prevalent this issue is in the wild and/or if anyone else has encountered it or had to workaround it, but there is a documentation bug that had me pulling my hair our for nearly a week.

If you have a need to get the members of an Exchange public distribution group using Exchange Web Services (EWS) ExpandDL operation via SOAP, and you followed this documentation, you will have encountered this super unhelpful error:

The problem is that the documentation is wrong. The namespaces used and the namespace prefixes are wrong. Here is what is on the documentation site:

What I ended up doing was to use the EwsEditor tool and Fiddler (per the guidance of an awesome Exchange guru I work with – thanks Dan!), and I captured the proper soap coming from the Managed API. To get ExpandDL to work properly, you need this SOAP:

Anyway, I will be issuing an update to easyEws very soon to include the correct SOAP.

How to Fix IntelliSense in VSCode for OfficeJS

davecraLeave a comment

I spent the better part of this morning trying to figure out why a new Yeoman generated Outlook add-in was not working with IntelliSense in VSCode. I would type “Office.” and nothing would appear. I would press CTRL+{SPACE} and still nothing.

After a lot of digging and comparing projects that did work to those that did not, I found that the @types were missing. So I then installed @types/office-js:

npm install @types/office-js

Once I did this, viola! IntelliSense started working like a charm. From my experience this has happened to me a few different times, in these given scenarios:

  • Moving an OfficeJS project from VS2017 to VSCode
  • Moving a Script Lab project to VSCode
  • Creating a blank OfficeJS project from scratch with just two files (manifest and webpage).

In my continued research I found that the way VSCode handles types is through NPM and you need to have data type definition files to get it all to work, that is why you need to install both NPM and the OfficeJS @types to get this to work. Per this page:  https://code.visualstudio.com/Docs/languages/javascript

Automatic Type Acquisition (ATA) uses npm, the Node.js package manager, to install and manage Type Declaration (typings) files. To ensure that Automatic Type Acquisition works properly, first ensure that you have npm installed on your machine.

Secondly to that, for OfficeJS development, make sure you have @types/office-js.

easyEws v1.0.10 Released

davecraLeave a comment

In this release I have incorporated two new functions to help with recipients (To/CC/BCC) and groups/distribution lists:

  • getAllRecipientsAsync() – helps you by getting all the unique recipients on the To/CC/BCC lines. It returns two lists in the callback:
    • A list of standard users
    • A list of groups
  • splitGroupsAsync() – accepts an array of groups (or Distribution Groups) and expands them to a unique set of users.

I have also corrected a few small bugs, like one with MailBoxUser (where Address was incorrectly JSDoc’d as Email).

I have also included a minified version of the library. So you can access easyEws.min.js in your code to reduce the download.

You can access it from: