Category: Microsoft Office

Send to Planner Add-in Published

David E. CraigLeave a comment

Right before the holidays I got my latest solution published to AppSource: “Send to Planner.”

This add-in is based on the “Send to Trello” Add-in and has a very similar look and feel. It was an unexpected side bar in my solutions development. A Send to Trello user contacted me because they also used Planner and were unable to find an effective solution that just did the basic “send an email to Planner” for free. While this has premium features to do much more, like keeping responses to the same email with the original task, the basic functionality will likely meet most casual use.

This was fun to create but also vexing at the same time. I am not sure where I got the idea, that there was an ability to get a front-end access token of the user running the Office add-in (getAccessToken). But turns out this is called an “on behalf of” flow that requires a complex manifest setup and you must send the customer email information to your backend web service to make the call “on behalf of” the user to Graph API. There are several options, but I had to go with the OAUTH flow using displayDialogAsync(). The AppSource (GDRP/Privacy) requirements for a backend data flow was more work and upkeep than I care for (cost and time wise). It is a headache I did not want.

So, I used the MSAL flow to pop an authentication dialog and then call the Graph API from the front-end to write to Planner. I already have users complaining that “it pops up the authentication dialog too much.” This is even though it does not require the entire authentication flow, it just pops up and then goes away.

I try the ssoSilent from the task-pane side and when/if that fails, I pop the dialog and do it there. The issue is because I do not own the frame of the Office task pane my origination domain is not correct. MSAL rejects the Silent SSO attempt. Ergo, it must pop open the dialog every time to get ssoSilent and refresh the token that way. A tad annoying.

I am not sure if I am missing something, but it would be nice to be able to request a front-end token with the proper scopes to do this, or a way to call ssoSilent from a task pane. But for now – this is what it is. Another annoying fact is that the token I get from MSAL, although it is refreshable (with ssoSilent), it lasts only 1 hour before it must be refreshed. So, 99% of the time a user clicks “Send to Planner” they see the dialog flash.

Either way, the add-in is out there and consumable. Please check it out!

Excel Send to Trello

David E. Craig3 Comments

Well, that was fast. My latest add-in is published. Excel Send to Trello.

As per my previous post, the thing I found most interesting was how Excel full client seems to fail if you configure your server .htaccess file to prevent caching. Well, I found out that my Outlook Send to Trello add-in actually had the same problem too. The Outlook client just happened to refresh this morning and my icon disappeared there too. In Office online it seems to work, but in the full client you cannot seem to force the client to NOT cache. I see my files all pulled down locally in the Wef folder and my concern is that when I update the add-in it will not go get the latest every time… I have actually seem and beat my head over this problem a few times. But the solution there for anyone working on a BETA site for example making changes and all of a sudden the full-client stops refreshing your updates, this is a good article to keep handy.

But when Wef strikes in production, I have found customers are not so excited to blow away this folder and find all their add-ins, preferences, stored cached credentials and other goodies for each and every add-in are gone. Ergo why I added the no-cahce to the .htacess. Oh well. 🙁

Also, just to share something else as I am delving more and more into publishing add-ins for real. As an Office Developer, in the traditional sense (aka boomer 😛, VBA/VSTO/COM), there are aspects of living in an HTML web world that I still learning (although this is an old one it comes up now and again because I forget something).

You have to worry about various attack vectors and sanitizing HTML strings. There are LOTS of libraries and solutions out there and Mozilla even has documented a possible standard supported in several browsers, but not all. It is a tricky thing because some sanitizers do too much or not enough, and then you also rely on a dependency which has now burned me more often than just owning things that might be a hundred lines of code for my own common library of goodies.

So, I have created my own based on various library implementations, and found the best option is to escape most of the stuff you find “injected” rather than remove it.

/**
* Sanitizes the string for possible malicious values
* @param {String} string
* @returns {String}
*/
static sanitizeString = (string) => {
try {
string = string.replace(/(javascript:|onerror)/gi, "");
string = string.replace(/undefined/gi, "");
string = string.replace(/<script/gi, "&lt;script");
string = string.replace(/<iframe/gi, "&lt;iframe");
string = string.replace(/<object/gi, "&lt;object");
string = string.replace(/<embed/gi, "&lt;embed");
string = string.replace(/<applet/gi, "&lt;applet");
string = string.replace(/<form/gi, "&lt;form");
string = string.replace(/<meta/gi, "&lt;meta");
string = string.replace(/<link/gi, "&lt;link");
string = string.replace(/<a\s/gi, "&lt;a ");
string = string.replace(/<img\s/gi, "&lt;img ");
string = string.replace(/="/gi, "&#x3D;&#39;");
string = string.replace(/='/gi, "&#x3D;&#39;");
string = string.replace(/=`/gi, "&#x3D;&#x60;");
string = string.replace(/\/>/gi, "&#x2F;&gt;");
return string;
} catch (e) {
return `[[SANITIZED STRING MALFORMED: ${e}]]`;
}
};
view raw sanitizeString.js hosted with ❤ by GitHub

The important thing is that in the web world, anytime you take data from one service to another, or take input in a field, or grab input from some element on a page and insert it back into another element in your code, there is a hack waiting to happen if you do not sanitize.

Outlook Calendar for Trello is Released

David E. CraigLeave a comment

I have really enjoyed writing this bit of code because it stretched from Trello API to Office 365 API, two of my favorite programming interfaces. This Power-Up is similar to the default Trello Calendar Power-up, the key difference being is that it connects to your Outlook Calendar. So, you can see all your Trello Tasks and your Outlook appointments/meetings, side by side in one place, you can link your appointments to Trello cards and vice-versa. With a month view and a weekly view, you can manage your calendar easily by dragging and dropping your Trello Cards on the calendar to create linked appointments for specific tasks all in one place.

Check it out here:

https://trello.com/power-ups/637307154b117e05a423c8a1

New Outlook Add-in: Send to Trello

David E. Craig2 Comments

I have been using Trello for a while now and one of the features I have found most useful is to take an email I received and turn it into a Kanban item on my backlog to address later. This allows me to archive the email but keeps it on my “Trello radar” as I work at my own pace through my personal backlog.

Recently, Trello removed their add-in from the Microsoft Office store. If you have the add-in installed, you will see this error:

Well, since they say necessity is the mother of all invention and I really had to fill the gap as it is part of my routine, I rolled my own. 🤓 To add a degree of difficulty, I wrote this in VS Code in Linux running in Windows Subsystem for Linux (WSL). See my previous post. It was a fun exercise as I am on vacation and using the time tom learn new things, engage in self-improvement and relax (coding is relaxing to me 🤓🤓🤓). In the end, I learned something and created something for everyone to enjoy.

Say hello to the recently published: Send to Trello Outlook Add-in.

Give it a try and let me know what you think.

5entences Add-in Released

David E. CraigLeave a comment

I have been spending some of my free time writing add-ins for Office and Power-Ups for Trello. I just completed two add-ins for Office. One is going through the process with AppSource and I will blog about it soon enough. The other cannot go though AppSource because it uses the Block On Send interface for Outlook. The add-in I just release is the 5entences Add-in for Outlook.

I have been inspired by various books I have read that all cross-pollinate the idea that we use e-mail all wrong or too much. Cal Newport’s “A World without Email” and Tim Ferris’ “4 Hour Workweek” are just two examples. And I believe it was Tim’s book that first touched on brevity in email. And thus, this website was born: five.sentenc.es.

From those books, and website, I had an idea. Why not add “training wheels” to the idea of email brevity? So, I wrote the 5entences Add-in for Outlook as a simple to use add-in that tries to help you keep your email responses brief and to the point.

It has been a fun side project as I was delving deeper into writing React code and using prebuilt React controls, so I wanted to share it with everyone. Maybe we can make email fun again.

What is Office 365, really?

David E. Craig1 Comment

In my day-to-day dealings with people and organizations, there is a common confusion on the term “Office 365.” And that confusion is fed by a few common myths that I have heard repeated by some very smart people in those organizations. So, first, let me dismiss the myths…

  • The first one is that Office 365 is online, in the browser applications only… NO! Office 365 is NOT just Excel Online, Word Online, PowerPoint Online, Outlook Online, and OneNote Online.
  • And the second myth is that the desktop applications are getting deprecated… NO! Microsoft is NOT doing away with the installed desktop applications on Windows and Mac in favor of web based versions.

So let’s get to the question:

If Microsoft is not replacing Office with web versions, then what the heck is all this Office 365 and cloud stuff then?

Primarily, Office 365 is the name of the subscription. You no longer buy the Office applications on disks and install them, then buy the next version, etc. With the subscription you always get the newest version (and much more). As you will read from the link above, there are lots of FAQ’s and other information that explain how this works.

So Excel Online, ET AL. is PART of the Office 365 subscription based platform. Office 365 still includes the traditional Microsoft Office desktop applications on the PC/Windows or the Mac. And they are not going anywhere. When you hear that your organization is moving to Office 365 it does not mean you will ONLY be accessing Office from a web browser.

Office 365 is really Office anywhere, on any device. For example, Office 365 also includes applications on iPhone, iPad, and Android. And it is not just the traditional applications. There are incredible tools such as Forms, Sway, Teams, and for developers the Graph API. It also shifts workloads like Exchange Server for mail and SharePoint server for document management from local IT servers to servers run by Microsoft in the cloud.

So when you hear Office 365, DO NOT THINK GOOGLE DOCS. wlEmoticon-smile.png The online versions of Office are only there to provide an additional avenues to access Office from anywhere, on any device. Per the Office 365 website:

Works across multiple devices

Get the fully installed Office apps on multiple PCs, Macs, tablets, and mobile devices (including Windows, iOS, and Android).

They are not as fully featured and as such you really need to consider when you might use them over using the traditional Microsoft Office desktop applications. Per this site (my emphasis added):

Office for the web (formerly Office Web Apps) opens Word, Excel, OneNote, and PowerPoint documents in your web browser. Office for the web makes it easier to work and share Office files from anywhere with an internet connection, from almost any device. Microsoft Office 365 customers with Word, Excel, OneNote, or PowerPoint can view, create, and edit files on the go. …

The following tables compare Office for the web feature capabilities to feature-rich Microsoft Office desktop apps.

So, if you like your Excel just the way it is, installed on your scientific workstation with a bazillion gigabytes of RAM, you can still run it there if you move to Office 365. What you get with the traditional Office applications is more frequent, seamless and automatic updates from the web. Per the Office 365 website:

Monthly Updates

Get the latest features and capabilities with fully installed and always up-to-date versions of Outlook, Word, Excel, PowerPoint for Windows or Mac, OneNote (features vary), Teams, and Access and Publisher (PC only).

Hopefully, this has helped clear up and dispel the myths. And when you go back to the smart person that has told you otherwise, and continues to insist and tell you otherwise, please send them here. wlEmoticon-hotsmile.png

Goodbye ModNotebooks, Hello OfficeLens

David E. Craig1 Comment

I like taking notes, the old fashioned way (by hand in a notebook, on… paper), but I like finding them the new way (using search on my PC). I love OneNote and how easy it is to keep an online notebook with all sorts of data. For example, due to archival rules some of my email starts to “disappear” from my work account after a couple of years. But some emails I like to keep – little nuggets of wisdom, notices, personal information and such I like to keep around. So, I export them to OneNote, where I can keep them safe.

So, I was living this duplicitous life of daily note taking with pen and paper but also filling my online notebook with all sort of information. I wanted to find a way to bridge these two. A few years back I was at a Microsoft convention and ran into a member of the OneNote team that turned me on to a small startup firm called: ModNotebooks. Their website is now gone, this is all you see:

mod.PNG

What they did was to send you a very high quality notebook, you would fill it up and ship it off to them. In the back of the notebook was a folder with a mail pouch and prepaid postage. They would receive it, scan it in for you and then dump the contents into your OneNote file behind the scenes. I filled 7 notebooks with them. But alas, their business model did not work, I am guessing, and they have gone out of business.

Now, I am left with my seeming duplicity again. wlEmoticon-disappointedsmile.png How do I get back to having my handwritten notes in OneNote again? Enter, OfficeLens.

I have actually been using this app for some time to scan travel receipts to PDF to turn in for expense reimbursement. And while I have always known I can use it for OneNote notes, I never tried it – namely because I had a better thing in ModNotebooks – or so I thought. Being forced to use something is sometimes what it takes. So, I gave OfficeLens a shot.

First, you download it to your phone. It comes on all three platforms (Windows Phone, iOS, and Android). Next, you hook up your Microsoft account (Live/Hotmail/MSN) and then you start scanning. It automatically finds the page and draws a border around it:

20170717_145127000_iOS.png

When you click the button at the bottom, it shows you what it got:

20170717_143239000_iOS.png

In the lower left, you can click the (+1), to add more page (up to 10 at a time – my only complaint – more on that later)* Once complete with your scanning, you click Done at the top right. This will take you to the “Export To” page.

20170717_143248000_iOS.png

From here I select OneNote and it asks me where I want to put it in my Notebook and when I click Save, it begins to upload it to my OneNote notebook:

And once it is in OneNote, it is fully text searchable, based on my handwriting. Yes, I said “fully text searchable” from my handwriting. Here is what it looks like once it gets to the final destination and I perform a search:

OneNote.PNG

Amazing, eh?

So, my only complaint is that the tool only allows you to scan in 10 pages at once. I wish there was a way to override this, for two reasons:

  1. I take a lot of notes and I fill 10 pages quickly. I have to get into the habit of scanning every week at this point to keep up. It is not impossible, but it would be nice to skip a few weeks and then scan them all in – in bulk. Right now, I have to upload it in several different batches of 10.
  2. I recently was working with legal documents and needed to print them out, sign them, scan them in and then send them off. The document had 11 pages. 11. 11! So, I had to scan 10 pages, then scan the last one and then find PDF merge software to merge them all together. That was NOT fun.

With that said, Au Revoir ModNotebooks. You were great while you lasted. I will likely be going back to using Moleskin’s and then using OfficeLens to digitize. We shall see… TUL (the “u” is long, so “tool”) has some nice notebooks too – I love their fine-point pen:

20170717_152827116_iOS

If you have any suggestions, please leave comments below. wlEmoticon-hotsmile.png

Going back to taking notes by hand…

 

Delay Loading Outlook Add-ins

David E. CraigLeave a comment

A customer I work with encountered an issue where a specific add-in was causing Outlook to lose its network connection. Essentially, we were unable to get the “Click here to view more on Microsoft Exchange” (in cached mode) to light up. Here is what we saw:

connect error

Here is what we wanted to see:

connected

It was always grayed out and no mater what we did in Outlook with the connection state, it never same back.

After a lot of troubleshooting we found one particular in-house add-in was causing the problem. Oddly, everything worked great with the same add-in in previous versions of Outlook, but in Outlook 2016, we started seeing this problem. Therefore, we knew it had to be a change made in Outlook 2016. What we found is that Outlook 2016 had been greatly reconfigured in the startup code to optimize network connections as it now connects to the cloud (Office 365). So we started working with the product team on identifying the root cause and in the end we were unable to find a solution (in time). My customers deployment was delayed.

As such, I had to come up with a workaround. We found that if the add-in was not loaded when Outlook started, but was manually enabled after Outlook  launched, the problem would NOT occur. This got me thinking: What if I created an add-in that loaded add-ins AFTER Outlook was done loading all other add-ins?

The Outlook Delayed Loading of Add-ins for the Enterprise was born. By the way, that name is credited to my customer. The catchy acronym stuck: D-LAME Add-in. wlEmoticon-disappointedsmile.png

I have posted the project for it here on GitHub here:

https://github.com/davecra/DLAME

You will need to load it into Visual Studio and compile it and then sign it with a certificate on your own. The code is provided AS IS. The README.md on the page explains the installation, configuration and usage of the a add-in once you have it ready for deployment. Some key points:

  • You will want to make sure it is not disabled by setting the Resilience policy key for the add-in.
  • You will want to move the add-in(s) you wish to delay from HKLM to HKCU registry locations.
  • You can load DLAME as either HKCU or HKLM. The suggestion is HKLM.

So, what can you use this for? Well, it turns out this add-in has a lot of uses and as I have started discussing it with other support folks at Microsoft, several use cases came out:

  1. You have a lot of add-ins that you need to have loaded with Outlook. They keep getting disabled by Outlooks resiliency feature, so you add policy settings to prevent them from being disabled, but now Outlook takes forever to launch. You can now set only DLAME to be resiliency policy blocked and then delay load all your other add-ins.
  2. Because it is a .NET/VSTO add-in, added to the above scenario, you can have all your VSTO add-ins load after Outlook has completed loading all other add-ins.
  3. Because the loading occurs on a background thread, the user will see Outlook fully load and then will start to see the other add-ins load (Ribbons and buttons appearing) after they are able to see their inbox and start reading/selecting items.

Bottom line, this add-in is useful for helping an enterprise manage their add-in without impacting the loading of Outlook or user productivity.

However, there is ONE major caveat. You will need to thoroughly test your add-ins because some add-ins might not like being loaded AFTER the fact. Technically, I have not found any that behave this way, but there could be some that register to certain events (like Application_Load and NewExplorer) that will not get fired if loaded after Outlook is already fully loaded.

OneNote: Do Your Tabs Disappear?

David E. Craig10 Comments

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.

Outlook Export Calendar to Word Add-in

David E. CraigLeave a comment

I have been working with a number of customers over the years that come from the world of Lotus Notes. And one of the areas they often complain about with regards to Outlook is the calendar printing options. There are certain things you just cannot do in Outlook from the printing perspective that leaves them wont for more.

So, I have been working over the years on this add-in. This has actually gone through a few iterations – the current version 1.2.0.9 is the most recent and most fully-featured version.

The full source code is on GitHub, here. It is totally open source and free to use, modify, etc. Here is what it can do:

addin

  • Printing calendars not available in Outlook by default.
  • The ability to create your own custom calendar
  • The ability to combine calendars for multiple people at once:
    • Displaying only overlapping schedules on the same calendar
    • Displaying all meetings including overlapping meeting
  • The ability to export in daily, weekly, by-weekly, tri-weekly or monthly formats.

The exact details on customization, installation and usage are all covered in the user guide, here.