The Case
It seems like the use cases for SharePoint are a little wind blown. Maybe it's because SharePoint Foundation came free with every Windows Server license since 2003 or maybe it's because SharePoint (ahem Enterprise) comes free with every Office 365 tenant. Well anyway, one really good one is for a work support, or work ticket system. You might say CRM is the way to go, except that it's really expensive, and in many cases way overblown, IMHO.So, let's go with the work ticket system. You want, proactive notification, you want status updates as the ticket is worked, you want work history, and probably want some sort of effort tracking. What else do you say?.. You want to be able to send an email to a generic email box, that will kick off work to be done?
Well, all of this was built in in SharePoint on-premises versions, but with a move to Office 365, not only do you loose out on custom server side code, and the entire on-premises BI stack (Business Intelligence to keep you in the know), you also loose out on incoming email/List integration. Not to mention you cant customize the outgoing email From or Reply-To for your site collections, web application, or farm. You can't even specify them at a tenant level. I'm not really sure why Microsoft choose not to do this, but there are some obscure references to mail being blocked in spam filters or something. I'm not sure who really wanted email addresses spoofed, a good admin reply-to for support is usually what you want anyway.
Hey, we really wanted that functionality, and dang-it we want it in SharePoint Online, because ... CLOUD IS AWESOME NO MORE TOUCHY THE SERVIE NETZ! As an aside, why does it seem that many arguments for cloud services are the same arguments to get all of the "wage slave IT riffraff" out of site and into the basement? Well that's a whole other article
The Soln
Man, I like it when I use that weird abbreviation that comes from technical publications, makes me feel smart or whatever, go a way, it's my feeling don't be a buzz kill :)OK, now that I got that out of my system, the solution that is both CHEEP, and INCLUDED in your O365 subscription... Microsoft Flow!!!
Flow is pretty convenient, but what it lacks for in documentation it makes up in obscurity. Right of the bat, it has a nice Web GUI, with all of those nice connecting lines and drag and drop type abilties, you don't even have to know anything about the PowerApps Workflow Definition Language (ok, that one's a slight stretch). But really, the big ticket item is that it has built-in support for lots and lots of Cloud tech.
For instance you could send an email to a GMail then have the attachments saved to a Drop Box folder. Or maybe when a new video is added to a Vimeo channel, you want to schedule an event on your calendar to remind you to watch it. I bet you have always wanted to fetch a row from Informix whenever a issue has been assigned to you in GitHub.
Well, I never wanted those either, but I did want create an item in a SharePoint list when an email was sent to a service email box. Well there's triggers and actions for that (check out the Services list, it reads like an SSAS who's-who)!
To the right is a screen capture of the Flow I built for a help-desk ticketing solution (sorry for the small size, click on it to zoom in, but you already knew that. Sorry, sorry, I think I'm turning Canadian). We wanted two critical features. First, when an email arrives, create a new SPO List Item. Second, if a user replies to an email correctly, add any comments from the email to an appending text field on the same list item. Here's how it works.- Look for new emails in the email box. We used rules in our Outlook 365 account to move them to a folder based on the alias used, thus we can support multiple lists with a single O365 account.
- When an email arrives, fetch the email received time stamp, and then do some edits on the html message body (more on that later).
- Decide if the email we found conforms to a "Reply-To" format or if it's something else.
- If it's something else:
- Create a new SPO List Item
- Build a path to store attachments based on the Item's ID and email timestamp. Note: Flow doesn't support adding attachments to list items yet. That's coming, but as of yesterday (June 6th, 2017) that feature is Started.
- Save each of the attachments to a Document Library referenced in the path in the previous step
- Else when the email conforms to the correct Reply-To subject string
- Retrieve the SPO List Item ID from the email's Subject field
- Update the SPO List Item using the ID and the email's message body. We specifically updated our "Appending Text" field, that you get when you use versioned list items
- Calculate a path to store attachments like above.
- Save each of the attachments to a Document Library
Check out this beauty. A screen shot of the outcome. Yes, that is me eating cake. It was my birthday, OK! I deserved it, so boo to your comments! :)Some things to know:
- The attachments created an interesting problem. We can't yet add them to a List Item, and in-line pictures show up as broken empty image boxes. JavaScript (jQuery) to the rescue... That comment above about editing the email's HTML was some prep to insert the timestamp into the <IMG> tags. Then jQuery on the forms to fix it up (more on this in another post).
- I created an email box specifically for this List integration. I'm not sure what would happen if the email was marked read before Flow tried to process it.
- I run the Flow process as the account that owns the email box. This ensures that it would have access to Outlook 365.
- I granted the account read access to the entire site, and contribute access to the SharePoint List. With out read access to the site, the UI doesn't work quite right as it provides a drop down to select the list to interact with.
- I could have created the flow as another account, but I would have had to create connections Outlook and SharePoint user id's in them. It is much easier to keep it all under one account, IMHO.
- Getting help for Microsoft Flow from the internet is crazy hard, unless you include PowerApps or Power Apps in your Google/Bing/Yahoo/DuckDuckGo query. I'm new to Flow, so I suspect it was recently rebranded. It just showed up in my Charms in February, but there's references to it on the PowerApps PowerUsers site from a couple of years ago
