Outlook Input tool Created with the Alteryx SDK

[rpaugh]

How does one automate that which cannot be automated?

We are in an information age.  An age where someone can monitor the security of their home and unlock their care from a mobile device; start streaming a movie at home, move to an iPad, and finish on a plane; and even blend, cleanse, and predict data with little or no data science knowledge.  And yet many, if not all of us, at some point have tried to gain access to data within our own organizations only to be met with the solution of “we can email you an Excel file”.  Really?!?  I can put money into my Starbucks account via my phone, and have Starbucks scan and recognize the payment, deduct the cost from my account and give me my bonus stars for the transaction, but the only solution you have to give me data is to email it to me?  Now is a good time to tell you something important about myself – I’m lazy.  I loathe manual, tedious, repetitive tasks.  I often joke with my coworkers that I’m going to automate everything I do so I can sit and watch YouTube all day.  In fact, were I to successfully automate myself out of a job I would consider that the single greatest accomplishment of my career.  As you can probably imagine, I was not willing to manually download and process Excel files each week.  Fortunately, I come from a software development background and was able to write code to extract attachments from Outlook emails and baked that into an SSIS package that ran weekly. 

Outlook Input toolFast forward a couple of years to Inspire 2017.  I was having breakfast before the opening keynote and I heard an Alteryx employee talking about using Alteryx to blend data from a file she receives from another department.  I had a hunch and asked her how she gets those files.  I stifled a literal “LOL” moment when she said she receives them via email.  I told her I had some code that might help her get to a fully automated solution.  Later in the conference I attended a session on using the Alteryx API and SDK.  I paid particularly close attention to the SDK portion because at the time I had no idea creating custom tools was even an option.  I know this is going to sound lame and cliché, but at that moment I was truly inspired to jump into code and build my own custom input tool to pull attachments out of Outlook emails.  I know, I know, too corny.  Moving on.  As a fantastic conference was coming to a close I just happened to spot one of the engineers, @JPKa,  who hosted the session on using the API and SDK at the closing reception.  I told him my idea and he pointed me in the right direction to get started.  Two weeks of personal time later I had  a solid version 1  of the tool and I shared it with my friends at Alteryx.

 

 

Working with the SDK

Outlook Input Tool ConfigurationEverything I needed to get started was already installed with Alteryx Designer. I used a combination of reading through and interpreting the sample and the getting started documentation. Honestly, my first impression was confusion.  The sample project involves using an XML file containing Alteryx tool XML config information as an input to set the tool XML config information.  When it came to interpreting the code to understand how it was linking the xml config information to the input data I was often confused as to whether the xml components in question were part of the Alteryx configuration or the input tool that was providing the configuration.  Simply put, it was setting configuration from configuration. If anyone from Alteryx Product Management is reading this,  a better sample might be to pull dummy customer data from a .csv file and send it to an output stream.

My biggest obstacle was interpreting the sample code - figuring out how to separate the xml config as input from the xml config that was reading the input.  It makes perfect sense now, but when I was trying to understand the custom tool architecture and input file interpretation simultaneously for the first time I had some trouble.

Once I had a working tool I went back and forth with the folks at Alteryx and there were some limitations there with getting them the install and sharing attachments. I discovered I could wrap the install into a yxzp file and include instructions and a help file. JP was very helpful with identifying some of the nuances of working with the SDK and offered some suggestions on improving the tool. I needed to incorporate the "UpdateOnly" parameter because the C# code was executing every time a new tool was added to the canvas. I didn't know that the Alteryx Engine had a special feature where it runs the workflow (with no actual records other than metadata) every time a new tool is added.  

He also suggested I add some date filtering capability to the tool, allowing people to avoid downloading their entire inbox if they select “Inbox”.  For this I added a configuration element for the user to input a query string and included a link to the documentation to build the query.

Using the Outlook Input Tool

You may be tempted to point this sucker to your Inbox right out of the gate, and I don't know what your Inbox looks like, but if it looks anything like some of our testers, I'd advise against that. Play with your drafts folder or deleted items first.

You may find copying the search syntax from Outlook into the Query String helpful:

copy the query string from outlook

As for me, this is how I've been using the tool. In this example I filter out a specific Excel file and send it through a Dynamic Input tool to see the results. I'll leave it up to you to discover new and interesting ways to consume the files that you no longer have to manually fish out of your inboxes.

But wait, there’s more!  Act now and as a bonus you get the added message stream allowing you to perform any number of analytics on your email messages.  And if that isn’t enough, join the two streams together on Message ID for maximum analytical potential!!

 

 

 

What's included in the package

You'll find an Alteryx *.yxzp file attached to this post. When you open it in Alteryx Designer, you will see installation instructions:

and a help file

The workflow is a single RunCommand tool that will install the Outlook Input tool. You can find my source code on Git Hub.

I hope you find the Outlook Input tool useful. This has been a fun and exciting project and I can't wait to see what creative ways the community finds to use this tool. Use the comments below to let me know your experience working with the tool.

UPDATE 8/1/2017:

A new version is available on the gallery: https://gallery.alteryx.com/#!app/Outlook-Tools-Install/597b35c2f499c716ec34a782.

Changes:

• Implemented paging to prevent timeouts through the Exchange Web Services when working with large inboxes and complex filters.  Note: you still may need to adjust throttling settings on your server: https://msdn.microsoft.com/en-us/library/office/jj945066(v=exchg.150).aspx.
• Updated the installer to remove previously installed versions of the tools so it's not cluttering up your programs list.

[nataliamiteva]

Hi @rpaugh, can the tool be installed on any Alteryx version? I am using 2019.3 and can't seem to be able to install it successfully. Thanks in advance. 

[rpaugh]

@nataliamiteva It should work on anything 11.5 and up.  What issue(s) are you running into?  Errors?  Missing tool?  Something else?  Feel free to PM me if you do not wish to share any details in this public thread.

[DmtCoj]

Hi, Starting from Friday I am receiving the "The Autodiscover service couldn't be located" error. Therefore, I wonder if anyone else is facing that problem (till Friday everything was perfect). Thank you.

[rpaugh]

@DmtCoj did something change on your Exchange server?  I haven't changed anything with the tool recently, so that leads me to believe something happened with the autodiscover service.  Any chance IT turned that off?  I know some companies who have done that for security reasons. 

[JMoore]

So good ol' microsoft is changing around the Exchange service, it has completely taken offline the app now for me: https://techcommunity.microsoft.com/t5/Exchange-Team-Blog/Upcoming-changes-to-Exchange-Web-Services-... IT is investigating it but not sure what will work here likely need to re-build using something else.

[rpaugh]

@JMoore I didn't realize Microsoft was actually shutting down EWS services yet.  That said, I am actually currently in the process of rebuilding the tool to work with both the new Graph API and OAuth.  I've got the email and calendar functions working and am adding in some outstanding features from the current tool and polishing what's left.  I hope to post a beta version soon for everyone to start testing out. 

[BPrior]

@JMoore @rpaugh Looks like we're suffering a similar issue, we'd actually recently switched to your workaround of logging into the EWS on browser but even that is completely down now. Appreciate updates on a beta version as we have a massive reliance on it for our reporting and business KPIs.

[rpaugh]

Hello to everyone having issues with EWS and/or authentication.  I've completed what I believe to be a solid first version of the tool using OAuth security with Microsoft's new Graph API for Office365 accounts and am opening it up for public beta.  Check it out here for more information and the download link and please send me any feedback as I do not have a commercial/enterprise account with which to deeply test this tool.  On that note, if you'd like to support further development of the tool and a more robust Microsoft license with which I can test it, please feel free to donate.  I hope to have a better system in place soon for accepting support requests and will post that as well.

@BPrior @JMoore @DmtCoj @Darrie8468 @huyly0325 tagging you all here since you all reported some form of server access and/or authentication error with other version of the tool.  Hopefully this new version will be more secure and reliable for you.  If you have time please test and send me any feedback.

[Darrie8468]

Ok...  You have my undivided attention.  Small problem...

[rpaugh]

@Darrie8468 whoops, forgot to add it to the public gallery.  Fixed 🙂

[rpaugh]

An updated version of the Office365 w/ OAuth beta has been posted to the public gallery.  You can again query on specific folders to improve performance.  Due to the heavy API load required to iterate all child folders and the fact that the new API allows direct message querying across all folders (rendering folder iteration largely unnecessary), I have deliberately excluded the "Include Sub-Folders" option from the tool's configuration.  For those of you wanting to traverse child folders looking for messages, I recommend using multiple tools for each sub-folder you specifically want to search and/or using the query string configuration item to help narrow down your results to the exact messages you're after.  

[BPrior]

@rpaugh Appreciate this, still waiting on some further feedback from IT before I can test this fully. If I'm reading the above correctly, you're recommending to have a separate input tool for each sub-folder or are we still able to just grab the parent folder and everything inside it?

We are currently using 6 workflows (3 for received 3 for sent) to pull data from 60+ mailboxes and having to split that further into sub-folders will quickly get into 100+ inputs. Apologies if I have read the above incorrectly and I imagine this tool probably wasn't intended to be used on such a wide level.

[rpaugh]

@BPrior the idea is that if you know what messages you're looking for, there is no longer a  need to traverse all sub-folders to find them.  Or if you know in which sub-folder the messages reside then you can go straight to it.  The previous API required you to know which folder you were searching in whereas the new one allows you to cross all folders and pull out specific messages directly.  What kind of queries are you using to pull messages out of sub-folders and would there be any issues with searching without specifying the folder first?  Feel free to PM me if you do not wish to share this information publicly.

[jessecolucci]

Hi! I get the following error when trying to login. Is the app looking for an outlook admin account?

Br,
Jesse Colucci

[rpaugh]

@jessecolucci I removed a couple of permissions that said they needed admin access.  The tool wasn't using them anyway.  Go back to the gallery and download and re-install the tool.  Hopefully that should fix it.

[JMoore]

@rpaugh I am running on 2019.1 but I can't get the Office365 version to open as it says it was created with a newer version, is there a way around this? I am getting my IT to give me Graph permissions here still.

[rpaugh]

@JMoore that's strange.  I believe tools should work across versions as long as you don't use any version-specific code - which I don't believe I did.  Are you having trouble during the installation or after the installation when you drag the tool onto the canvas and try to run it?

[JMoore]

@rpaugh yes it was not allowing it to run still, unzipped the file changed the version from .2 to .1 saved it now trying to figure out the correct zip to yxzp format to try and install

[JMoore]

oh this is pulling from google drive for the package, can you publish the full thing to Alteryx?

[rpaugh]

Oh, were you downloading the workflow from the gallery?  If so, you don't need to do that.  Run the workflow in the public gallery to get to the web page describing the tool.  At the bottom is a link to the yxi file that you download and run.

[JMoore]

ah ok, so the yxi is on google drive which is blocked here for me, I got around this though. which Mailbox should I enter? I am still working to get email admin privileges established but this is looking promising.

[rpaugh]

@JMoore  You shouldn't have to enter a mailbox.  When you click OAuth Login, use your company email to sign in.  That should direct you to the appropriate corporate login form if different than the standard Office365 form.  Once that login pop-up closes it will save the associated refresh token to the configuration (as a hidden password).

  

[Darrie8468]

@rpaugh ...  I love this tool!  It totally works!  And it is so simple to use.  Just  few clicks and I get my mailbox and calendar (both rather large) in under 3 minutes!  Great job!

[rpaugh]

@Darrie8468 Excellent!  I'm so glad it worked for you.

[JMoore]

@rpaugh sounds good still trying to get admin rights on outlook365 here so I can authenticate.

[rpaugh]

@JMoore you shouldn't need admin rights to use the tool.  Or did they lock down API access?  Just curious.

[jessecolucci]

I still get the admin error too. LEGO probably locked me out 😞

[rpaugh]

@jessecolucci let me know if there's anything I can do to help.  Hopefully your IT team can just approve whatever access you need to the API and the tool will magically start working.

[Slushercw]

I just came across this tool yesterday.  After an hour of going through the comments, I see I should have started closer to the end haha.  Using the original tool I was getting a message saying "this account is locked" and redirecting me to a website to unlock it.  Then I realized there was a newer tool here, so I downloaded that.  After downloading I was also having issues with the "401 forbidden error". 

I was able to get the issue figured out by doing the following:

1. Use manual service URL and entered https://outlook.office365.com/EWS/Exchange.asmx

2. Visited that site and entered credentials to start a "service" per @JMoore instructions

3. Making sure there is something in the query field relative to (at least one of) the fields selected (i.e. if subject is selected, you would want "Subject:xxx" in the query field)

The main issue that I hadn't seen mentioned is that when entering credentials on that site *and* in Alteryx, I didn't need to use my actual login password for my network.  The "password" I needed to use was the app password that was setup when Office was installed on my machine (that I luckily had saved for future use). 

Once I did this, the workflow started working properly. 

I have downloaded the new tool here, which uses my network password instead of the app password, but I either haven't gotten the new query syntax down or haven't configured it quite properly yet because I keep getting a "400: Bad Request" error.  Below are the configurations for each tool in case it's helpful for anyone.

On-prem/Office 365 tool

Office365 (OWA) tool

My specific use is to pull text/data from an e-mail body and manipulate it, but I think I will need to do some kind of JSON parse based on what I have read previously.  Either way, I just wanted to share my issue/resolution in case anyone else finds it helpful.  Big shout out to @rpaugh for the continued development/support on this tool because it's pretty amazing!

Alteryx Version - 2019.2.5

[rpaugh]

@Slushercw items like "contains" and "startswith" work a little differently.  For those, you have to do the following:

• contains(subject, 'example')
• startswith(subject, 'example')

[Slushercw]

@rpaugh thanks!  That worked.  I've looked in the help documentation and can't find much info about actually extracting the data from the e-mails into usable forms.  I'm trying to use it for the following purposes:

1) Pulling body data into a workflow (to manipulate as if it was an excel file)

2) Pulling attachments and saving them to a network location (.xlsx files)

Re: #1 - I saw a mention of using a JSON parse tool, but when I did that I just got null values for everything.  I'm still a beginner in Alteryx and don't have much of a coding background so I have no idea how to use that tool.  I also tried to use the JSON build tool to no avail (obv I have no idea what I am doing).  I'm only pulling out the body and subject lines for the e-mails, but I'm not sure how to get the "body" data in a format that's usable and don't see many options to play with when configuring the JSON parse tool.

Re: #2 - I thought I had configured my workflow properly to get the attachments to save, but it's just saving a "file" as opposed to an Excel file.  Not sure where I went wrong tbh.  I think I remember reading something about a blob file but couldn't find much after going back a couple pages in the comments.

Are there tutorials for either of these issues available (preferably for noobs)?

Thanks for all your help!

[rpaugh]

@Slushercw The email body object contains 2 components: contentType and content.  Throwing a JSON Parse tool on the email Body field will spit out two rows for each email:

The resulting JSON_ValueString field for all records where the JSON_Name equals "content" will be the actual raw content of the email.  From there you'll have to do parsing depending on what you're after - e.g. JSON, text to columns/rows, regex, etc.  There are many resources in the community to help with specifics around parsing data.

For the attachment, you'll probably need to sent the contentBytes field out to a Blob Output tool.

[Slushercw]

@rpaugh 

Getting close on the attachment issue.  I was able to get an excel file to show up on my network, but then when I tried to open it, gave me this error message:

For the JSON parse thing, I'm not getting the same results as you.  Could it be something with the way the e-mail is formatted?

[rpaugh]

@Slushercw I've noticed that issue with attachments too and am looking into it.  It's no different than the previous version of the tool so I'm not sure why it's giving us grief.

For the email body, what are you getting out of the message stream prior to the JSON parse object?  That will help me troubleshoot what's happening.

[Slushercw]

@rpaugh 

This is what the body string looks like before the JSON parse tool (let me know if you need something different):

Also, re: the attachment issue - one thing I noticed is that when using the blob output tool, the file size is only showing up as 5 bytes, when it should be 5 KB.  Could this be something Base64 related?  The output from the outlook input tool is showing 5 bytes as well.  I'm not sure where in the process the file size should revert to the original size, but it should happen at some point before being saved to the folder, right?

Thanks again for all your help on this!

[rpaugh]

@Slushercw oh you're using the js beta version of the tool.  Ok.  For that version, no JSON parse is necessary for the body.  The new version (for Office365 and OAuth) outputs the type of content in addition to the actual content which is why JSON parse is required.  

You may be on to something with the attachment content.  I'll look into that.

[Slushercw]

@rpaugh 

Re: js Beta tool

When I output the body data into a file, it looks like the data from the e-mail body isn't all coming into the tool.  I'm guessing this is something with the format of the e-mail?  Don't know if it would be easier to look at this via DM - if so let me know.

Re: OAuth/365 tool:

Just wanted to point out that I think the "help" button for the OAuth/Office365 tool is still pointing to the js beta help page with the different query syntax.

I'm trying to only pull attachments from "yesterday" (or "past week", etc).  Does that kind of language work with the new syntax?  I didn't see anything in the OData links about that.  Right now I am just pulling in all e-mails based on my subject query and then using a filter tool to pulls the date that I want, but it took a full minute to run the workflow which seemed unusual.  Would putting these limiters in the query field help to speed up the workflow?

I tried using the query below but got a 400:Bad Request error.  Also tried it without the comma and had the same result.

contains(subject, 'example'), ReceivedDateTime eq date:today

Thanks!

[rpaugh]

I've published a new version of the Office365 beta tool.  It now allows you to log out so you can sign in under different accounts for different tools in the same Atleryx instance.  I've also changed the contentBytes encoding on the Attachment output.  Now you can simply use a blob convert tool to convert the contentBytes field to a blob using Base64 encoding, then use blob output to send it to a physical file.  I tested it with a .xlsx file and it worked perfectly. 

@Slushercw I'll into the date filter.  The syntax for multiple filter criteria is "and".  For example, "contains(subject, 'example') and receiveddatetime ge <some date>".

[Slushercw]

@rpaugh thank you!

For the receiveddatetime ge <some date> does it have to be an actual date or could it be receiveddatetime ge <yesterday>?

[rpaugh]

@Slushercw so it doesn't seem to have a way to do <yesterday> or <today>, which seems strange.  I'm still looking into that to see if there's some kind of workaround.  Worst case, you could always wrap the tool in a macro and use Alteryx to calculate the current date and send that into the macro to filter your data. Not ideal, but that would work.

[Slushercw]

@rpaugh thanks again for all your help!  I have my workflow configured to get the end results I'm looking for, it's just a little less efficient because it pulls in all the attachments based on my query, then just filters based on current date.  I'm sure I could speed up the workflow as needed by cleaning my inbox up a little bit, so it's not a big enough deal to worry about.  I've already taken up too much of your time!

In case anyone comes by this later and happens to have a similar question/issue, here's my workflow (Filename Generation step is combining [network folder] + [date] + " Report.xlsx" into the Filename field):

Blob Convert config

Blob Output config

[JMoore]

Hi @rpaugh,

Still having issues here, I am successfully logged into Graph Explorer now, but my Outlook365 admin's need to permission "Alteryx Outlook Input" do you know what is needed to be able to setup that permissioning?

 

 

[rpaugh]

@JMoore Nothing in the app explicitly asks for any admin-required permissions, so I wonder if your IT department has locked something down to prevent unauthorized OAuth app requests.

[JMoore]

@rpaugh that is correct every app has to be individually allowed.

[jyotijyoti_dup_475]

Hi Folks,

This version does not work with 12.1 version.

Please advise.

[JMoore]

@rpaugh do you have the unique ID's etc for the app so I can have them input for authorizing?

[rpaugh]

@JMoore Here's the application ID:  423da247-57c3-48bc-b35b-a2d0eace3bc7

Let me know if that works.

[JMoore]

@rpaugh login now seems successful, did 2 login pages then had an admin log into their account which then uploaded the app into the office365 universe. testing on additional users now.

[JMoore]

@rpaugh are you uploading this version to Github?

[rpaugh]

@JMoore I'm glad you were able to get it to work.  Sounds like your IT might be a little more strict with the email security than some other companies.  

I may put it on GitHub, but probably not until I feel I've got a solid non-beta version ready.