Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
rpaugh
11 - Bolide

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 toolOutlook 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 ConfigurationOutlook 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 outlookcopy 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.

 

OutlookInputToolSample.png

 

 

giphy-downsized (2).gifBut 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:

 

Outlook Tools Install instructions.png

 

and a help file

 

Outlook Tools Install help file.png

 

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.
Comments
JPKa
Alteryx Alumni (Retired)

@rpaugh  

 

Great work on this tool.  It does exactly as advertised, and a bunch of us here at Alteryx are already installing and using it for our own Outlook accounts.  

Can't wait to see what else you have to contribute in the future!  

 

 

OliverW
Alteryx Alumni (Retired)

@rpaugh

 

This is brilliant Rick, I just tested it, works great. awesome job!

jdunkerley79
ACE Emeritus
ACE Emeritus

Great job @rpaugh - nice to see other people using the SDK

 

NeilR
Alteryx Alumni (Retired)

 

 

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.

I read it! We're currently hard at work creating good (hopefully) samples and documentation for the upcoming Python and HTML SDKs, after which we certainly want to return our attention to our older C++ and C# SDKs to clean up their samples and documentation. So thank you for the feedback. And great post!

 

 

 

WKESLewis
6 - Meteoroid

First off, game changer on the business side, I was elated to hear from our Alteryx SE that I should review this string when I was telling him about some issues we were trying to overcome.

 

I've been able to successfully load and validate a sample flow to our corporate Gallery, but when I try to execute/run I get an error with locating the 'Save Attachments to:' location.

 

Normally I would just |Options-Advanced Options-Workflow Dependencies-All UNC|, but the identified location for Attachments is not present as a dependency.  Nor can I update the browse to option output to a UNC.

 

Has anyone overcome this already or any ideas how to update the repository location while using the Gallery? 

rpaugh
11 - Bolide

@WKESLewis, are you entering a non-UNC path and wanting it to automatically convert it to a UNC path?  Or are you entering a UNC path that isn't working on the gallery?

WKESLewis
6 - Meteoroid

@rpaugh

 

Entering a non-UNC path and wanting a UNC path (reasoning below)

 

The 'Save Attachments to:' Field has a Browse only option, from there I am able to navigate to the Network Share Drive folder where I can select where I would like to store the files.  The location that appears after selecting is not a UNC path as it leverages an alias (i.e. S:\ rather than \\asb-123-xyz\).  I do not see an option to free form the UNC path or modify in any way.  This leaves me with a non-UNC path dependency in my Gallery, which then fails as expected.  The 'Save Attachments to:' location is also not seen as a Workflow dependency in Designer when I look to update to UNC in Advanced Options.

 

rpaugh
11 - Bolide

@WKESLewis, I updated the tool allowing you to manually edit the attachments path.  Tara will be posting the updated .yxzp file soon.  When you run the installer it needs to update existing DLLs so you'll be prompted to either close Alteryx or reboot later.  Obviously you can't close Alteryx because you're running a workflow, so elect to restart later.  Note: you will not actually have to restart your computer in order to use the new version of the tool.  Let me know if this meets your needs.

JPKa
Alteryx Alumni (Retired)

@rpaugh  

 

Pretty impressive turnaround time Rick!  

 

You may need to get some contributors on the Github repository for this tool as people find more edge cases requiring additional functionality.  

 

Thanks,

 

JP

rpaugh
11 - Bolide

Definitely a good idea, @JPKa.  For now, here's a link to the updated .yxzp file @WKESLewishttps://drive.google.com/open?id=0B11L6XXkpz_YSU9heVN6NUwzblE

 

Let me know if you have any issues with it.

TaraM
Alteryx Alumni (Retired)

I updated the post with your latest fix @rpaugh

WKESLewis
6 - Meteoroid

@rpaugh Update confirmed and tested.  I was able to successfully free form enter a UNC code for the location, post to gallery and run in gallery.

 

rpaugh
11 - Bolide

Thanks for the confirmation, @WKESLewis!

JacobMoncla
8 - Asteroid

This is awesome!

rpaugh
11 - Bolide

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.
ChadM
Alteryx Alumni (Retired)

This is so incredibly good, excellent work @rpaugh!  The original version didn't save attachments correctly, but upon updating to the latest and re-installing, I am now able to automate this.

 

Already have forwarded to several folks. 

rpaugh
11 - Bolide

Thanks for the update, @ChadM.  I'm glad the latest version worked for you.  

Atabarezz
13 - Pulsar

Awesome job, thanks.

Pretty useful for "mailbox analytics..."

TorbenL
5 - Atom

Excellent  work @rpaugh - this tool will definitely come in handy!!!

 

I am trying to narrow down the search to a specific subfolder in my inbox... but without luck so far. I can't find any subfolders in the 'Folder to search' drop down, so I have tried to narrow the search in via the query string: folderpath:(Inbox/Subfolder level 1/Subfolder level 2)

 

Am I handling it the wrong way? I hope I'm not missing something obvious :-)

 

Best,

Torben

rpaugh
11 - Bolide

@TorbenL,

 

The "Folders to search" drop down pulls the list of pre-defined folder names from Exchange, meaning it can not pull a list of custom sub folders.  And the way the tool is coded to allow for searching of all Outlook-based items (calendar, notes, etc.) it's difficult to simply tell it to traverse sub folders in its search.  However, I do have a couple of ideas and believe I can accomplish this.  Let me look into this and I'll keep you apprised of my progress.

TorbenL
5 - Atom

Thank you for your quick reply @rpaugh. I really appreciate your effort :-)

 

Best,

Torben

jamespw
5 - Atom

Hi

 

This looks to be an exciting and fantastic tool and it would allow me to automate a task that relies on a daily emailed attachment. :)

 

Unfortunately I get an error - not sure if it's because our corporate email system is not cloud-based but we have our own server.  Is there still a way to use this tool if this is the case?

 

Thanks! 

James

 

The error is:

 

Error: OutlookInputTool (1): Exchange Server doesn't support the requested version.ServiceVersionException at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.ProcessWebException(WebException webException) at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.GetEwsHttpWebResponse(IEwsHttpWebRequest request) at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.ValidateAndEmitRequest(IEwsHttpWebRequest& request) at Microsoft.Exchange.WebServices.Data.MultiResponseServiceRequest`1.Execute() at Microsoft.Exchange.WebServices.Data.ExchangeService.FindItems(FolderId parentFolderId, String queryString, ViewBase view) at OutlookTools.OutlookEmail.GetMessages(Int64 recordLimit) at OutlookTools.OutlookInputToolEngine.PI_PushAllRecords(Int64 nRecordLimit) at SRC.Alteryx.GenericNetPluginInterface.PI_PushAllRecords(GenericNetPluginInterface* , Int64 nRecordLimit)

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

I get the same error as @jamespw

Also have an on-prem Exchange server.

rpaugh
11 - Bolide

What on-prem version are you using?  It will work for on-prem, but I'm using a feature that may not be available in version 2010.  If that's what either of you are using let me know and I'll update the code.  Otherwise, you might try using a domain-qualified name for your  login rather than your regular email.  For example, instead of "name@email.com", try "username@domain.com".

jamespw
5 - Atom

I am using Outlook 2013 - specifics are:

 

MSO (15.0.4945.1001) 32-bit

 

Thanks

James

rpaugh
11 - Bolide

New version of the Outlook Input Tool is now available in the gallery:  https://gallery.alteryx.com/#!app/Outlook%2BTools%2BInstall/597b35c2f499c716ec34a782

 

New feature: sub-folder searches.  

 

@TorbenL - fyi.

rpaugh
11 - Bolide

Uploaded a new version to the gallery: https://gallery.alteryx.com/#!app/Outlook%2BTools%2BInstall/597b35c2f499c716ec34a782

 

New feature: manual service endpoint URL entry for those struggling with autodiscover.  Hopefully this solves your issues :-).

 

@jamespw @patrick_mcauliffe @prajgiri @sean_bolte_dup_544

danners
7 - Meteor

First off, I have to say this is an amazing tool and is opening up a world of possibilities for us to automate manual workflows.  :)

One thing I'm running into is when you have a report running from a source system that is being e-mailed to you on a set frequency (let's say weekly).  Each week I get a report that is moved into a sub-folder with the e-mails from subsequent weeks.  All the attachments are named the same thing (e.g. 'ReportDetails.xlsx'). 

 

It appears that since the last file being read is the oldest, the tool is overwriting each file and ending on the oldest file.  If it read and saved the files from oldest to newest so that the newest would always be saved last- that would be helpful.  Even better if all of the attachments in the temp directory were suffixed with some id then you should hopefully never encounter this scenario.

Thanks again for all your work on this! 

rpaugh
11 - Bolide

@danners, my initial test version did append timestamp suffixes to the downloaded attachments.  There was a reason I removed it, but now I can't remember why :-).  I'll look into this and if it makes sense to add it back in I will.  In the meantime, you could always add a filter to your input tool to only pull emails sent in the last week so as to prevent pulling older ones.  From there you could always add a section to your workflow that creates a copy of the file with a designated suffix after it's been downloaded/consumed so as not to interfere with future versions in your temp directory.  Let me know if this will work for you.

 

Rick

danners
7 - Meteor

What I'm doing currently what you're suggesting, which is adding a last week filter to the input query but can think of at least one scenario where that would break (manual runs of the report causing more than one e-mail/report to appear when looking back to last week).  That is why I would prefer to just use logic in the workflow to determine the max date and always input the latest.

Thanks for looking into it @rpaugh.  

joshuaburkhow
ACE Emeritus
ACE Emeritus

@rpaugh really great work here! Also went through some of the code up on github. Good stuff. Let me know if you end of needing some help on this. More than  happy to chip in. 

 

Just to give you/everyone reading this a simple use case. My boss needed to go through his Calendar to find instances where he had to go onsite to a client and therefore had to drive. Now putting some simple spatial analytics together with this he can start tracking mileage reimbursement :) Simple but effective. 

TorbenL
5 - Atom

@rpaugh Brilliant work!!! It is great to have the ability to include a specific subfolder name. This will definetly come in handy in allot of use cases. Thx :-)

 

An idea... could it be a future option in the tool, that the search only includes a specific sub-folder, and not the main root inbox as well? 

My primary use case is to get csv attachments from daily mails which Outlook directs to different subfolders. Fx: Inbox/Dashboard Data/Client1/Facebook

 

It could be a good way to limit the output to the mails of interest for the specific workflow. just a thought to improve an allready brilliant tool.

 

 

Best,

Torben

 

 

rpaugh
11 - Bolide

Two new updates to the tool:

  1. Added the ability to search only sub-folders (@TorbenL)
  2. Added the ability to create unique file names for downloaded attachment across workflow runs (@danners)

The new version is available for download from the public gallery.

 

Rick

danners
7 - Meteor

Nice!  I'll be downloading and taking a look today.  Thanks  @rpaugh.

danners
7 - Meteor

I've tried downloading the latest version off public.  I uninstalled the previous version of the tool from add/remove programs and then installed V11.  Due to our server version, I'm currently still on Alteryx Designer 11.0.6.28907.  

 

I get this error when I try to run on my existing workflow.  I also tried with a fresh tool in a brand new canvas and got the same error.  Wondering if it's due to my designer version? 

 

Error: OutlookInputTool (1): There are no fields. Make sure your container element is set properly.
ApplicationException
at OutlookTools.OutlookInputToolEngine.PI_PushAllRecords(Int64 nRecordLimit)
at SRC.Alteryx.GenericNetPluginInterface.PI_PushAllRecords(GenericNetPluginInterface* , Int64 nRecordLimit)

rpaugh
11 - Bolide

@danners, I think I may have not incremented the product version properly in the latest update.  I just fixed that, so try installing again from the gallery (uninstall shouldn't be necessary) and let me know what happens.

danners
7 - Meteor

@rpaugh, same error message as before.

rpaugh
11 - Bolide

I've been informed there was a bug with unique file attachment names not pulling through in the Attachment output stream in the latest version.  This has been fixed and uploaded to the public gallery.

Tradd
5 - Atom

@rpaugh This is awesome! Thanks for creating and sharing!

 

Would it be possible to make a future option that enables the user to name that attachment that is being saved. I use this to save large automated attachments that are sent daily and then clean them up with a separate workflow. The problem is that the attachments all have unique names so in order to select the most recent automatically I have to use the Directory and Dynamic Input tools (which still leaves the previous large files on my server). A naming ability that would overwrite the existing file where it is saved would be extremely helpful.

 

Thanks again, this tool is a life saver!

 

rpaugh
11 - Bolide

@Tradd, thanks for the idea.  I'll look into it.

jbrider
7 - Meteor

Thank you for this! It helped me build a batch macro that can download lots of different email reports.

 

I'm having some trouble with the Querystring field. Your tool doesn't seem to parse out quite the same as the Outlook desktop application.

 

For instance, this query string works fine in Outlook, but not in the Alteryx tool:

     

Subject: 'My Report'; Received: 09/17/2017 5:00AM..09/17/2017 7:00AM

 

This expression does work fine in the Alteryx tool:

Subject: 'My Report'; Received: 09/17/2017..09/18/2017

I'm sure this is user error...but any hints?

Coxta45
11 - Bolide

@jbrider,

 

Wild guess, but maybe because the timestamp request has spaces, the Alteryx tool needs quotes?

 

Subject: "My Report"; Received: "09/17/2017 5:00AM..09/18/2017 7:00AM"

You may also try 24-hour timestamps or even UTC timestamps:

Subject: "My Report"; Received: 09/17/2017 05:00:00..09/18/2017 17:00:00

 

Just a couple of quick guesses...

 

rpaugh
11 - Bolide

@jbrider, did @Coxta45's suggestion work for you?  I reviewed the AQS documentation and it doesn't specifically address date filters with times so I'm actually not entirely sure what format is required.  My assumption would be that using either quotes or UTC timestamps would work.

gradyjofficial
8 - Asteroid

Hi 

 

Anyone had this error before: 

 

OutlookInputTool (1) Exchange Server doesn't support the requested version.¶ServiceVersionException¶ at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.ProcessWebException(WebException webException) ¶ at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.GetEwsHttpWebResponse(IEwsHttpWebRequest request) ¶ at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.ValidateAndEmitRequest(IEwsHttpWebRequest& request) ¶ at Microsoft.Exchange.WebServices.Data.MultiResponseServiceRequest`1.Execute() ¶ at Microsoft.Exchange.WebServices.Data.ExchangeService.FindItems(FolderId parentFolderId, String queryString, ViewBase view) ¶ at OutlookTools.OutlookEmail.GetItemsFromFolder(ExchangeService service, Object folder, Boolean isRoot) ¶ at OutlookTools.OutlookEmail.GetMessages(Int64 recordLimit) ¶ at OutlookTools.OutlookInputToolEngine.PI_PushAllRecords(Int64 nRecordLimit) ¶ at SRC.Alteryx.GenericNetPluginInterface.PI_PushAllRecords(GenericNetPluginInterface* , Int64 nRecordLimit)

 

thanks

rpaugh
11 - Bolide

Hi @gradyjofficial, would you mind sending me a private message with your Exchange version and screenshot of your selected configuration options?

 

Thanks,

 

Rick

SeanAdams
17 - Castor
17 - Castor

cant tell you how excited I am to play with this @rpaugh - thank you for putting this together!

JessieC
Alteryx
Alteryx

@rpaugh - for Calendar, are we able to get meeting date, time, and duration?

rpaugh
11 - Bolide

@JessieC, currently, no.  The current tool was written to be simple and dynamic in order to facilitate the fastest time to delivery with maximum functionality and minimum code.  Now that's not to say it can't be done in a future version.  What you're suggesting is a fantastic idea to greatly improve the analytic potential of the tool, so I will definitely look into updating it to pull more detailed information for other areas such as the calendar.  I'll see what I can put together and reach out to you when I have something more to share.

 

Thanks again for the idea!

rpaugh
11 - Bolide

@JessieC, I've added additional calendar fields and sorted the field list to make it easier to find what you're looking for.  The new version is in the gallery.

 

Here's a sample of a calendar query:

 

2017-10-01_Alteryx Outlook Input Calendar Example.png

JessieC
Alteryx
Alteryx

Thank you @rpaugh! It works great!