Extended MessageBox Library

David E. Craig2 Comments

Several years ago I created this class to assist with making a better, more useful Message Box windows in Add-ins and other projects. You can find it here in my GitHub repository:

Extended MessageBox Library

There were a few times I wanted to show the user a message, but also provide them with a link. Other times I found that I needed to present an error to the user, but wanted to hide more “technical” details from them, unless they clicked a More Info button. I also found cases where I needed to present an error if the user wanted to stop a potentially unattended process, but also wanted the message to dismiss after a period of time so the process could continue. Other times I wanted to present the user with a useful message, but an option to “Do not show this message again.”

After creating custom forms, over and over again, this C# library was born. I recently was writing a VSTO add-in that needed the later option – do not show again – and dusted off this old library, cleaned it up a bit (not not completely) and used it again. I figured, for posterity, I would share this with everyone since I have found it so useful over the years.

For the most part you use this exactly like you would the regular MessageBox object, with some extra goodies included.

For example, this will create a very simple Message box that will look fmailiar:

ExtendedMessageBox.Show(“Hello World”);

Produces this:

simple_msgbox

A more common scenario you might need with a checkbox, looks like this:


ExtendedDialogResult LobjResult = ExtendedMessageBox.Show("Hello World – Are you ok?",
"The Office Context",
MessageBoxButtons.OKCancel,
MessageBoxIcon.Information,
"Do not show this message again.");
if(LobjResult.Result == DialogResult.OK && LobjResult.IsChecked)
{
// do something
}

view raw

ExtendedMessageBoxCheckboxSample.cs

hosted with ❤ by GitHub

checkbox_msgbox

There are a lot more options and capabilities. I will not cover them all here, but they will be detailed in the README in the GitHub repository.

Please let me know if you have any questions or issues.

OfficeJS.dialogs Updated to v1.0.9

David E. Craig1 Comment

Like yesterday with easyEws, it has been a while since I have touched the OfficeJS.Dialogs library. I updated it yesterday with a quick fix and some content updates. I have had questions about using it from a CDN. The primary issue is that the displayDialogAsync() API does not support CDN. It just displays the HTML as raw text in the dialog. There are some ways I can provide this as a workaround, but it involves me support infrastructure. So, the better thing is to just wait for the API to support CDN’s.

If you have any questions about using this library, please let me know.

easyEws Updated to v1.0.11

David E. Craig1 Comment

It has been a while since I have updated this library, however I have had a few requests.

The first one that I am publishing today is adding the ResolveNames operation. The latest is now on GitHub here. And you can install it using NPM like this:

npm install easyews

Also, I have updated the CDN listing. Before I was using RAWGIT but that has been retired. You can now add the CDN like this:


<html>
<head>
<!– DEBUG –>
<script type="text/javascript" src="https://cdn.jsdelivr.net/gh/davecra/easyEws/easyEws.js"></script>
<!– Or, MINIFIED –>
<script type="text/javascript" src="https://cdn.jsdelivr.net/gh/davecra/easyEws/easyEws.min.js"></script>
</head>
</html>

view raw

easyEwsCDN.html

hosted with ❤ by GitHub

To issue a resolve names you will pass the email address or the display name to the function like this:


async function run() {
var msg = Office.context.mailbox.item;
msg.to.getAsync(function(asyncResult) {
if(asyncResult.status == Office.AsyncResultStatus.Failed) {
$("#recipientResult").text(asyncResult.error);
} else {
/** @type {Office.EmailAddressDetails[]} */
var recips = asyncResult.value;
// are there any recipients at all
if(recips == null || recips.length == 0) {
$("#recipientResult").html("NO RECIPIENTS");
} else {
/** @type {string} */
var info = "<br/>DISPLAY NAME: " + recips[0].displayName + "<br/>" +
"EMAIL_ADDRESS: " + recips[0].emailAddress + "<br/>" +
"RECIPIENT_TYPE: " + recips[0].recipientType;
easyEws.resolveRecipient(recips[0].emailAddress, function(result) {
if(result == null || result.length == 0) {
info += "<br/>UNRESOLVED</br>";
} else {
info += "<br/>RESOLVED: " + result[0].MailBoxType;
info += "<br/>RESOLVED EMAIL: " + result[0].EmailAddress;
}
// write tot he form
$("#recipientResult").html(info);
}, function(error) {
$("#recipientResult").text(error);
}, function(debug) {
$("#debugResult").text(debug)
});
}
}
});
}

view raw

resolveNames.js

hosted with ❤ by GitHub

If you have any suggestions for this library, please ping me.

Expanding Personal Contact Lists in Outlook with OfficeJS

David E. CraigLeave a comment

If you are developing an OfficeJS add-in for Outlook or Outlook Online (OWA) and your project requirements have a need to peek inside distribution lists, you might find that there are two kinds:

  • Distribution Lists – these are Exchange groups, or mail enabled Active Directory groups. These are managed by the enterprise IT and contain a list of email addresses and/or other distributions lists.
  • Contact Lists / Private Distribution Lists – these are personal lists created by the user that can contain similar objects.

For an Office Add-in, you must use the makeEwsRequestAsync() command to submit EWS to Exchange. And the specific command you will need to use is ExpandDL to get the members of a DL (or contact list). With regards to the first item in the list above, it is pretty straight forward. For the second item there is this blurb on the page:

Private distribution lists are located in the Contacts folder of a user’s mailbox. Private distribution lists do not have e-mail addresses so their store item identifiers are used in an ExpandDL request. Members of a private distribution list can be any mail-enabled user, contacts or distribution lists from Active Directory, or contacts or private distribution lists from a user’s Contacts folder.

The bold part is key. But where do you get this. When you request a to.getAsync() and receive “Bob’s Favorite People” as a recipient item what do you do with it?

The key is to call the EWS method ResolveNames. When you call this on “Bob’s Favorite People” you will get a return value with PrivateDL and an ItemID (the Exchange Store Item ID in the users Contact folder) that you will then send that ItemId to ExpandDL. Here is what the soap response would look like from ResolveNames:


<?xml version="1.0" encoding="utf-8"?>
<!– Note: EwsEditor has replaced the "utf-16" text in the first line with"utf-8" in order for the XML to render in the response web control. –>
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Header>
<h:ServerVersionInfo MajorVersion="15" MinorVersion="20" MajorBuildNumber="2305" MinorBuildNumber="24" Version="V2018_01_08" xmlns:h="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
</s:Header>
<s:Body>
<m:ResolveNamesResponse xmlns:m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:t="http://schemas.microsoft.com/exchange/services/2006/types">
<m:ResponseMessages>
<m:ResolveNamesResponseMessage ResponseClass="Success">
<m:ResponseCode>NoError</m:ResponseCode>
<m:ResolutionSet TotalItemsInView="1" IncludesLastItemInRange="true">
<t:Resolution>
<t:Mailbox>
<t:Name>Bob's Favorite People</t:Name>
<t:RoutingType>MAPIPDL</t:RoutingType>
<t:MailboxType>PrivateDL</t:MailboxType>
<t:ItemId Id="AAMkADU2NjQzOWIxLWZkMTktNDU2NC04MGYwLTc4OGUxZTQ3ZTQ4OQBGAAAAAABU0Ha8nllhSqfh0CtR+4+uBwAdo3MIorCqSaC1lyH8qlH1AAAAAAEOAAAdo3MIorCqSaC1lyH8qlH1AAQQFxCnAAA=" ChangeKey="EgAAABYAAAAdo3MIorCqSaC1lyH8qlH1AAQQUFsg" />
</t:Mailbox>
</t:Resolution>
</m:ResolutionSet>
</m:ResolveNamesResponseMessage>
</m:ResponseMessages>
</m:ResolveNamesResponse>
</s:Body>
</s:Envelope>

view raw

ResolveNamesResponse.xml

hosted with ❤ by GitHub

You can then make your ExpandDL request like this:


<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<t:RequestServerVersion Version="Exchange2016" />
</soap:Header>
<soap:Body>
<m:ExpandDL>
<m:Mailbox>
<t:ItemId Id="AAMkADU2NjQzOWIxLWZkMTktNDU2NC04MGYwLTc4OGUxZTQ3ZTQ4OQBGAAAAAABU0Ha8nllhSqfh0CtR+4+uBwAdo3MIorCqSaC1lyH8qlH1AAAAAAEOAAAdo3MIorCqSaC1lyH8qlH1AAQQFxCnAAA=" />
</m:Mailbox>
</m:ExpandDL>
</soap:Body>
</soap:Envelope>

view raw

ExpandDLOperation.xml

hosted with ❤ by GitHub

The result will be a list of all the email addresses, like this:


<?xml version="1.0" encoding="utf-8"?>
<!– Note: EwsEditor has replaced the "utf-16" text in the first line with"utf-8" in order for the XML to render in the response web control. –>
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Header>
<h:ServerVersionInfo MajorVersion="15" MinorVersion="20" MajorBuildNumber="2305" MinorBuildNumber="24" Version="V2018_01_08" xmlns:h="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
</s:Header>
<s:Body>
<m:ExpandDLResponse xmlns:m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:t="http://schemas.microsoft.com/exchange/services/2006/types">
<m:ResponseMessages>
<m:ExpandDLResponseMessage ResponseClass="Success">
<m:ResponseCode>NoError</m:ResponseCode>
<m:DLExpansion TotalItemsInView="3" IncludesLastItemInRange="true">
<t:Mailbox>
<t:Name>Bill S. Preston Esquire</t:Name>
<t:EmailAddress>bill@WyldStallyns.ooo</t:EmailAddress>
<t:RoutingType>SMTP</t:RoutingType>
<t:MailboxType>Contact</t:MailboxType>
</t:Mailbox>
<t:Mailbox>
<t:Name>Theodore Logan</t:Name>
<t:EmailAddress>ted@WyldStallyns.ooo</t:EmailAddress>
<t:RoutingType>SMTP</t:RoutingType>
<t:MailboxType>Contact</t:MailboxType>
</t:Mailbox>
<t:Mailbox>
<t:Name>Wyld Stallyns Fans</t:Name>
<t:EmailAddress>fans@WyldStallyns.ooo</t:EmailAddress>
<t:RoutingType>SMTP</t:RoutingType>
<t:MailboxType>Contact</t:MailboxType>
</t:Mailbox>
</m:DLExpansion>
</m:ExpandDLResponseMessage>
</m:ResponseMessages>
</m:ExpandDLResponse>
</s:Body>
</s:Envelope>

view raw

ExpandDLSoapResponse.xml

hosted with ❤ by GitHub

NOTE: For now the ResolveNames operation is not supported in my library easyEws. I am working to add it in the coming weeks.

Please let me know if you have any suggestions or questions.

NOTE: I am not an Exchange Web Services (EWS) expert, but have some experience with it through the OfficeJS makeEwsRequestAsync() command. To get the answer for this blog entry, I had to go to a real EWS expert to figure this one out. Many thanks to Dan Bagley (EwsEditor) for his assistance.

Downloading the OfficeJS Preview API for Offline Testing

David E. CraigLeave a comment

Recently, I had a need to download the preview of the OfficeJS API for testing in an offline environment. The primary (and preferred) way to access the preview beta library is to point to it on the CDN via your HTML, like this:


<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<title>My OfficeJS Add-in</title>
<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.9.1.min.js" type="text/javascript"></script>
<!–<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js&quot; type="text/javascript"></script>–>
<!– PREVIEW API CDN –>
<script src="https://appsforoffice.microsoft.com/lib/preview/hosted/Office.js" type="text/javascript"></script>
</head>

view raw

previewapi.html

hosted with ❤ by GitHub

However, if you need to download this for use offline in testing, you can actually download it using Node Package Manager. All you need is this command:

npm install @microsoft/office-js@beta

I was able to do my testing as if I were online with the CDN, so all was good. Happy coding! wlEmoticon-hotsmile.png

EXCEL: Auto-Run Macros Disabled without Warning

David E. CraigLeave a comment

This entry applies if your security settings in Excel are set to “Disable all macros except digitally signed macros” like this:

disabledmacros

In this scenario, when you open a workbook that is digitally signed, you will get a yellow bar across the top of the screen, that says: “SECURITY WARNING Macros have been disabled”, like this:

macrowarning

But if the workbook is NOT signed and you attempt to open the workbook with macros, you will not see anything, no warning at all. The workbook will appear to open, but all macros will be disabled. If you try to run a macro, you will get the following warning:

macrowarning2

However, the insidious thing is your AutoRun macros will also not execute and you will get no warning. If your code performs some type of data refresh of the spreadsheet, you might not realize this did not happen and what you will be left with is stale data. For many financial institutions this can be a scary prospect.

To workaround this problem, I have created an XLAM that can warn your users that they opened an untrusted workbook. You can access the source and the add-in both on GitHub: VBAProjectNotSignedWarning-Addin

What this add-in does is look for XLSM file to be opened, it then checks to see if the VBAProjectSigned property is false. If the workbook code is not signed, macros are assumed to be in the file, and the user will get the following warning:

certwarningaddin

The SOURCE is provided in the depot because for it to run properly in your environment, you might want to open the XLSM sign the VBA Project with your corporate code certificate, secure the project with a password, and then Save A Copy as an XLAM (Excel Add-in). Once created, you can then place the add-in in the XLSTART folder in Program Files for each user in your organization. Then when they try to open an unsigned macro workbook, they will get a handy warning message.

The code for this is fairly simple. I will not go into each module, just the main function that performs the check of each Workbook that is opened via the Application.Workbook_Open event:


Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If Right(UCase(Wb.Name), 4) = "XLSM" Then
If Wb.VBASigned = False Then
MsgBox "The workbook '" & Wb.Name & "' is not signed. " & _
"If there are auto-run macros in the workbook, " & _
"they will not be run.", vbExclamation, "Signed Workbook Check"
End If
End If
End Sub

view raw

WorkbookOpen.vb

hosted with ❤ by GitHub

Removing Web Add-in Ribbon Customization in Outlook for Windows

David E. Craig3 Comments

If you are following the latest updates the the OfficeJS API’s, you will see that PowerPoint, Excel and Word have this new feature that allows you to specify an equivalent COM add-in in place of the Web Add-in. Here is the link:  https://docs.microsoft.com/en-us/office/dev/add-ins/develop/make-office-add-in-compatible-with-existing-com-add-in

This is useful for when you have created a Web Add-in but it is not as feature rich as your COM add-in. So on the Windows clients, you still want your COM (or VSTO) solution to run there and not the web version of the add-in.

However, this feature has not yet arrived for Outlook. I am told that this is coming, but for now there really are only two workarounds.

If you are using Office 2016, you can set your manifest requirements to 1.5, like this:

    <Requirements>
      <bt:Sets DefaultMinVersion=”1.5″>
        <bt:Set Name=”Mailbox” />
      </bt:Sets>
    </Requirements>

If you are needing to support Outlook 2019 or Outlook for Office 365, then this easy trick will not work. So what you need to do is a bit more complicated. To start you need to collect some information first:

  1. Open Outlook, and verify that your web add-in customization has loaded.
  2. Go to File, Options, Customize Ribbon:options
  3. At the bottom, click Import/Export and select Export all customizations.
  4. Save the “customui” file to the desktop and then open it in Notepad.
  5. You will look for your customization and it will be in a group or a tab and contain an if like this: x1:Group_5febe0ec-e536-4275-bd02-66818bf9e191_msgEditGroup_TabNewMailMessage.
  6. Make note of this value, minus the namespace (x1:) and whether it is a customtab or a customgroup.

If this does not work for you a couple of other options are to add the Group to the Quick Access bar, by right-clicking on the group and selecting “Add to Quick Access.” This should help it appear in the customUI file. However, if this still does not work, you can make an “educated guess” at the name by using these steps:

  1. Open your manifest file in your Web Add-in and locate the ID:

    5febe0ec-e536-4275-bd02-66818bf9e191

  2.  Next, locate what you called the group in your manifest:

    < Group id=”msgEditGroup” >

  3. Finally, you need to determine which “Ribbon” it is on. This is a tad more complex, but since most following the “TabDefault” option, here are two defaults I know about, that could help you:
    • TabNewMailMessage
    • TabReadMailMessage
  4. From there you can build the Group identifier:

    Group_5febe0ec-e536-4275-bd02-66818bf9e191_msgEditGroup_TabNewMailMessage

  5. If you are working with a custom tab, you would look in your manifest and get the name of the custom tab:

    <CustomTab id=”myTab”>

  6. From there you can build your tab identifier:

    Tab_5febe0ec-e536-4275-bd02-66818bf9e191_myTab

The namespace is what makes this whole process a little harder than expected. This namespace is a UID for the Exchange Account to which the add-in was installed. And this ID is unique to every user and each email account in Outlook. What you will need to do is customize the Ribbon XML in your solution to then hide this group. I have created a solution here on github that shows how to do this end-to-end with a new add-in:

https://github.com/davecra/RemoveOfficeWebAddin

What this does is loads a file called RemovedCustomizations.txt from the install directory of the add-in. This file will contain entries like this to remove a tab:

5febe0ec-e536-4275-bd02-66818bf9e191
customtab,Tab_5febe0ec-e536-4275-bd02-66818bf9e191_myTab,

If you want to remove a group on an existing Outlook tab, it looks like this:

5febe0ec-e536-4275-bd02-66818bf9e191
customgroup,TabMail,Group_5febe0ec-e536-4275-bd02-66818bf9e191_msgReadGroup_TabMail

And this is a bit more tricky, but if you need to remove an item from a context ribbon in Outlook, you will add an entry like this:

5febe0ec-e536-4275-bd02-66818bf9e191
customgroup,TabComposeTools\TabMessage,Group_5febe0ec-e536-4275-bd02-66818bf9e191_msgComposeGroup

Important to note, the first entry is the ID of your Web Add-in. This is used to create the namespace entry.

You will use the steps above, to build this list item by item. And it is important to note that your item may appear on multiple tabs. For example, if your manifest is putting your custom group on TabDefault, that could end up in two places for a Compose message:

  • TabMail
  • TabComposeTools\TabMessage

The latter is a context tab and you have to specify the context tab name first, then the tab name. The tab name alone will not help. Finding the context tab is a bit more complex, but you can get those identifiers from here.

So in the end you might end up with a RemoveCustomizations.txt file that looks like this:

5febe0ec-e536-4275-bd02-66818bf9e191
customtab,Tab_5febe0ec-e536-4275-bd02-66818bf9e191_myTab,
customgroup,TabMail,Group_5febe0ec-e536-4275-bd02-66818bf9e191_msgReadGroup_TabMail
customgroup,TabComposeTools\TabMessage,Group_5febe0ec-e536-4275-bd02-66818bf9e191_msgComposeGroup

Overall, this is a tad more complex than I had hoped it would be, so please let me know if you get stuck on any parts and/or if there are areas that you think I can elaborate more.

Docker Hell on Window 10

David E. CraigLeave a comment

I have been working on building out services for the back-end which can be consumed by both my OfficeJS Add-ins and any other host. In researching different options to deploy those services I started to delve into using Docker. I have Visual Studio 2019 installed and I have “Container development tools” added.

container

Next I followed the steps here to this Quick Start. Except when I was trying to publish or even run in Docker for Windows I kept getting errors. When I tried to run Docker locally using Docker for Windows, I got an error that said “Docker for Windows is required” and the details said it could not start/find/access the “MobyLinuxVM.” It turns out, after a LOT of research, this was because it did not exist. It did not exist because I have Hyper-V default folder pointed to a removable drive that was not installed. wlEmoticon-disappointedsmile.png The fix there was one of two things:

  1. Change the Hyper-V default folder to something on the C:\ drive.
  2. Install the removable drive.

So I chose #2, and then I had to uninstall and then reinstall Docker for Windows. Once I did this the “MobyLinuxVM” was there and once I launched Docker for Windows, it also started the VM.

Next, I was getting a very odd error CT1002 about an error with the Docker container service or something. I cannot remember the exact error now but in the details I kept seeing an error where it was attempting to mount folders in my user folder “c:\users\david.” I found this article on MSDN after a lot of digging:

https://blogs.msdn.microsoft.com/stevelasker/2016/06/14/configuring-docker-for-windows-volumes/

This essentially tells you to create a new user called DockerHost, set the password to never change/expire, and add the administrators group. Next you sign in with that account and try to access your personal users folder. For me that was “c:\users\david.” You get the permissions prompt and click “Continue.” Mine took over 5 minutes, but eventually I was able to open the folder and see everything. Then I logged out and this is the step that article forgets, you go into Docker for Windows, Settings, Shared Drives, and Reset Credentials. Then click the C:\ drive again and enter in the DockerHost username and password.

dockersettings

This resolved running in Docker for Windows, locally. Shortly after all this work, I found this page which might also work (adding your account to the docker-users group). I did not try this, but here is the article on that:

https://docs.microsoft.com/en-us/visualstudio/containers/troubleshooting-docker-errors?view=vs-2019

Finally, I am at the point where I want to publish to my Azure Container Registry in the cloud. This I still have not resolved. It gets most of the way done and then I get this error:

docker error

I am working on trying to get this sorted but have run out of time for the day and wanted to add what I did to solve everything else. I am exhausted and a tad frustrated. wlEmoticon-sadsmile.png

What I have learned from this experience is that “out of the box” most of this stuff does not work. Looking for help online I see lots of command line junkies throwing out complex scripts and esoteric answers. It is a really interesting community but it seems to come with a high barrier to entry. With Visual Studio embracing it and trying to get you started “easy” there are still too many speed bumps for most folks trying to delve in.

If anyone knows a better answer on this stuff, please let me know. And if you have any idea what the publish error is I am getting and can give me some pointers, I would GREATLY appreciate it.

Simple Client-Side JavaScript MIME Parser

David E. CraigLeave 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:


/**
* Simple MIME Object for parsing basic MIME results:
* – Headers (parts)
* – HTML (full)
* – Attachments
* – Embedded Image Data
*/
function simpleMIMEClass() {
/** @type {string} */
var fullHeader = "";
/** @type {string} */
var html = "";
/** @type {MIMEImageData[]} */
var images = new Array();
/** @type {string} */
var text = "";
/** @type {string} */
var mimeData = "";
/**
* Internal MIME Image Class
*/
function MIMEImageData(){
this.ID = "";
this.Data = "";
}
/** @type {string} */
this.FullHeader = function() { return fullHeader };
/** @type {string} */
this.HTML = function() { return html; }
/** @type {string} */
this.Text = function() { return text; }
/** @type {string} */
this.GetImageData = function(id) {
for(var i=0;i<images.length;i++) {
if(images[i].ID == id) {
return images[i].Data;
}
}
// if here – nothing
return "";
}
/**
* Parses the MIME data into the object
* @param {@type {string} value} – MIME data from getMailItemMimeContent()
*/
this.Parse = function(value) {
mimeData = value;
text = atob(mimeData);
var parts = text.split("\r\n\r\n");
fullHeader = parts[0];
for(var partIndex=0;partIndex<parts.length;partIndex++) {
if(parts[partIndex].includes("Content-Type: text/html;", 0) > 0) {
html = parts[partIndex+1];
// must remove the =3D which is an incomplete escape code for "="
// which gets into Outlook MIME somehow – now sure
// also removing line breaks which are = at the very end
// followed by carriage return and new line
html = html.replace(/=3D/g,'=').replace(/=\r\n/g,"");
}
if(parts[partIndex].includes("Content-Type: image/", 0) > 0) {
var imgTag = parts[partIndex].split("\r\n");
var imgData = parts[partIndex+1];
var imgID = "";
for(var tagIndex=0;tagIndex<imgTag.length;tagIndex++) {
if(imgTag[tagIndex].includes("Content-ID: ") > 0) {
imgID = "cid:" + imgTag[tagIndex].split(": ")[1].replace("<","").replace(">","");
}
}
var img = new MIMEImageData();
img.Data = imgData;
img.ID = imgID;
images.push(img);
}
}
// done
return this;
};
};

view raw

simpleMIMEClass.js

hosted with ❤ by GitHub

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:


function sampleOfficeEvent(event){
/** @type {Office.Types.ItemCompose} */
var item = Office.cast.item.toItemCompose(Office.context.mailbox.item);
item.saveAsync(function(idResult) {
easyEws.getMailItemMimeContent(idResult.value,
function(mimeResult) {
/** {@type (simpleMIMEClass)} */
var oMime = new simpleMIMEClass();
oMime.Parse(mimeResult);
var doc = $(oMime.HTML()); // load into an HTML document
console.clear();
console.log("MIME: " + oMime.Text()); // get the raw MIME text
console.log("HEADER: " + oMime.FullHeader()); // get the full header info
console.log("HTML: " + oMime.HTML()); // return just the HTML
// here we are looking for the first image in the HTML document
var id = doc.find("img").attr("src");
// we display the SRC – which is a CID
console.log("TAG: " + id);
// Then we display the BASE64 string data for the image
// which can be converted to an image, replaced in the src tag
// to make it inline or store offline
console.log("DATA: " + oMime.GetImageData(id));
// done
event.completed();
});
});

view raw

sampleOfficeEvent.js

hosted with ❤ by GitHub

Using Azure Functions in Excel

David E. Craig3 Comments

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:


#r "Newtonsoft.Json"
using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
public static async Task<IActionResult> Run(HttpRequest req, ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string v1 = req.Query["value1"];
string v2 = req.Query["value2"];
if(v1!=null && v2!=null)
{
int result = int.Parse(v1) + int.Parse(v2);
return (ActionResult)new OkObjectResult($"{result}");
}
else
{
return new BadRequestObjectResult("Invalid input");
}
}

view raw

AddNumbersFunction.cs

hosted with ❤ by GitHub

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.