Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-tos.
Announcement | We'll be doing maintenance during the next 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!
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
Asteroid

@rpaugh sounds good so first error I am getting.

 

When I put a Folder Id in with a folder name I get a "400: Bad Request" without a folder id/name it works fine but very slow.

Bolide

@DmtCoj / @TimothyManning any chance you've tried out the Office365 version of the tool with determining the true sender on a shared mailbox?  I don't have an enterprise setup so I can't test this.

Asteroid

@rpaugh shifting around the workflow to pull in specific pieces using very specific query strings and it is working significantly faster this way. Probably will avoid using a specific folder but should be possible to find a way to limit on that meta data if needed.

Alteryx Partner

@rpaugh I'm unfortunately no longer in the same place I was before when asking the question, and Google drive is blocked at my current place of work so I can't download it. I hope someone else can help test it out

Bolide

@JMoore for the Folder ID field, it needs to be a well-known folder name (such as Inbox or SentItems) or the actual ID (such as AQMkADAwATNiZmYAZC1jMjk3LTFkZAA0LTAwAi0wMAoALgAAA3fCGAQ..................) for custom folders. You should be able to get the custom folder ID from the properties or the URL if accessing it from a web browser.

 

Regarding your next post: what meta data are you talking about limiting?

Meteor

@rpaugh is there a way to get a message itself saved to a network location as opposed to just the attachments (i.e. the .msg file)?

Bolide

@CSlusher not natively.  The API outputs a programmatic object (C# or JSON), not a .msg file.  You could use something like the Alteryx JSON build tool or the reporting tools to compile all of the output fields back into a single JSON object or report and save that off as a file.  It wouldn't be a true .msg file, but at least then you'd have a raw copy of all the data.

Meteor

@rpaugh This tool is great, but I can only get emails from my Inbox.  I have tried a subfolder below the Inbox and I am not sure what to add to the folder ID.  The folder is located here:  \\Username@****.com\Inbox\Outside Emails.  I am getting 400: Bad Request.  Any thoughts?

Bolide

@Darrie8468 Are you able to see the Id if you look at the folder properties?  If not, open your email on the web, click the applicable folder you want, then copy the Id from the URL and paste it into the Folder Id field:

 

Outlook Folder Id Example.png

Meteor

@rpaugh   That did it.  THANKS!!!!  Getting the folder ID from the web interface was the key.

Bolide

@Darrie8468 Excellent!  I have yet to find a better way to do this without adding more processing to the tool, which could seriously affect performance.  If I do find a way, I'll be sure to add it.

@rpaugh thanks man for such excellent tool ! I'm new to alteryx and was looking for such tool in one of my requirement. I have downloaded this tool and followed instruction but m still not able to see the Outlook Input tool in 'Connectors', even after enabling deprecated tools in Connectors. Kinldy help.
Asteroid

Hi @rpaugh 

 

Thanks for creating this tool! I could use some help with custom folders. I'm using the Office365 beta version 5 and it's working fine when I use Inbox, Drafts, etc. for the folder. However when trying to use a custom folder ID I'm getting a 400 bad request error. In the screenshot below I've opened the folder I'd like to download messages from (with a few characters grayed out)...do I use the entire string out to the %3D at the end or some subset of it?

 

2020-01-10_12-22-28.png

 

Thanks,

 

Jonathan

 

Bolide

Hi @jdrummey,  "%3D" is the URL-encoded version of an equals sign.  You will need those as part of your ID, but you'll need the decoded version (e.g. "==").

Asteroid
Hi,

Thanks for the tip…I ran the URL through a decoder and I’m still getting the same 400 error when I switch from one of the default folders like inbox or drafts to the custom folder.

When I log into Outlook the full URL looks something like this (I’ve replaced some repeating strings with ~~~~~):

https://outlook.office.com/mail/AQMkADY3NmZhZTA3L~~~~~YtNDBiOS1hOTVjLWZlNTQwMWE5ZmM3MwAuAAADv%2Fy%2F...

I’ve been assuming that the the string I need to use is based on this section between the /mail/ and /id/:

AQMkADY3NmZhZTA3L~~~~~YtNDBiOS1hOTVjLWZlNTQwMWE5ZmM3MwAuAAADv%2Fy%2Fhfg0SUO7aC0gmzWNQQEAQbRweTh%2Bskax%2FZ08zlnkbgACEPEDpwAAAA%3D%3D

Which decodes to this:

AQMkADY3NmZhZTA3L~~~~~YtNDBiOS1hOTVjLWZlNTQwMWE5ZmM3MwAuAAADv/y/hfg0SUO7aC0gmzWNQQEAQbRweTh+skax/Z08zlnkbgACEPEDpwAAAA==

And I’m still getting the error. I’ve tried parts of the string and those don’t work either. Any suggestions?

Jonathan
Bolide

@jdrummey hm...that should be correct.  Would you mind sending a screenshot of your configuration.  Maybe there's something else going on.  Feel free to PM it if you prefer.

Meteor

@rpaugh 

Something I don't understand, will the old tool stop working? If it is still working for me then I can still use it or should I expect it to stop working in the future?

I've put a lot of work into my previous model (which also included some changes to your code) and I'd really hate to abandon that now.

 

I've been away for a while so sorry if I'm repeating anything, I only had a quick read through.

 

I've installed the Office365 version and this is my initial findings in my environment:

 

1. I cannot access a different mailbox, I receive 403: Forbidden error when I check 'Use Different Mailbox' and enter an email address that I have access to.

 

2. I've tested what I quoted below and I can confirm I can determine the true sender on a shared mailbox.

 

@rpaugh wrote:
any chance you've tried out the Office365 version of the tool with determining the true sender on a shared mailbox?  I don't have an enterprise setup so I can't test this 

3. There's no way to see what the folder is called anymore ('Include Folder Name in Output' option in the previous version) , and also having no way to traverse through all the sub-folders means I can no longer use this tool for what I intend to do.

Bolide

@Yanoflies whether the old tools continue to work depend on:

  • When Alteryx sunsets the SDK (which they've extended out).
  • When you upgrade Alteryx.  Even if Alteryx sunsets the applicable SDK but you are still using an older version of Alteryx then it should still work.
  • Whether you use an on-premise Exchange server.  If you do, then the tool will work as long as you have it - and have a version of Alteryx that still works with the older SDK.
  • When Microsoft sunsets their Exchange Web Services, upon which the tool relies.  Again, this really applies to cloud (Office365) only.  

In short, you should be good for a little while.  Microsoft announced they will be decommissioning Basic Authentication over the next two years, which is the authentication method the old tool uses.

 

I'll have to look into the mailbox issue.  I

Asteroid

@rpaugh - I sent you a PM.

Atom

@rpaugh 

I have an older version of the outlook input macros installed to work with Alteryx designer 2018.3. As this version doesn’t seem to be backward compatible.

 

I’ve tried to limit to search to Today by 

1. Select: checking DateTimeReceived

2. Query String: entering ‘Received:today’

 

However it doesn’t appear to work. Removing the filter on date, the tool downloaded & saved all attachments from emails meeting title name criteria.

 

Please can you kindly advice how to make it work? 

 

 

Atom

@rpaugh 

I think I've figured out why it's not working - it's due to my local time being different from Alteryx default UTC. 

Bolide

@brendali I'm glad you figured it out.  Yes, time zone differences between your local computer and/or Alteryx server and your Exchange location can cause issues with the date filter.  I hope to incorporate time zone selection in a future version to alleviate this problem.

Atom

@rpaugh love that you've put in the work to create this tool

 

I've been trying to get it to work, but can't seem to get it to go.

 

When I use the JS version of the tool I always get a "RecordInfo::Create Record: A record was created with no fields." error.

 

When I use the older version of the tool I get an "AutodiscoverLocalException" error. If I try and use the EWS workaround I get an "Unable to connect to the remote server" error.

 

Do I need to contact my IT department to try and figure out a way to open up some permissions?

Meteor

Hi @rpaugh I am using outlook tool which was provided by you and it is working fine, but i have noticed today onething. I have created wrokflow for extracting the files from outlook by giving condition from (mail and received : today) and the files i received in mail at 4.30AM at 2020-01-30 and received: today is not the giving the files in output.... but when i am giving received: yesterday , i am getting in results files reveived in dates (2020-01-29 and 2020-01-30 )

Bolide

@ahursey do you happen to use Office365?  If so, I recommend using this version instead.  Otherwise it's possible that it's a security issue.  I know several people have had issues with their IT departments locking down EWS security.

 

@gantaanvesh this is probably a time zone issue between your computer and the Exchange server.  Several people have had this issue.  The two best ways to solve it are to 1) expand your filter to capture everything within the desired time period included the time zone shift and use the Alteryx filter tool to narrow the results after the fact, or 2) change the time zone on your computer.  Microsoft's new Graph API (upon which the Office365 version of this tool is based) allows for time zone selection to alleviate this issue, but I just haven't had time to incorporate it into the tool yet.  

Meteor

Hi @rpaugh ,

 

I am selecting folder from where i have to extract (Inbox and sub folder is grofers fill rate ) but when i am executing the workflow and it searching the entire outlook instead if selected folders, can you please suggest how to resolve this.

Also i want to extract files coming from different mails also this is why we have created a folder for each vendor.

 

gantaanvesh_0-1580741157353.png

Bolide

@gantaanvesh if you check "Skip Root Folder Search" then it should search only your sub-folder and not your entire inbox.  And if you separate all of your searches by vendor folders then ideally you would use a separate input tool for each folder and merge them later in the flow (assuming you want to merge them).

Meteor

Any thoughts about this error message?  It comes up when I try to run the workflow.

Darrie8468_0-1580827292155.png

Bolide

@Darrie8468 is that for your company's gallery or the public Alteryx gallery?

Meteor
Bolide

@Darrie8468 ah, yes.  There's nothing to run for that version.  You download it and run it locally to install it.  However, if you're using Office365 I highly recommend the latest version here.  Note, the Office365 one you will run until you get to the last page with the yxi download link.

Meteor

Gotcha.  Using the right download location just might help me get what I am looking for...  HAHAHAHAHAHA

Meteor

Hi @rpaugh ,

 

I am using the outlook tool and extracting the files on a daily basis. files are automatically saving in the provided folder in the configuration. 

In the files we dont have PO Number which is unique code whereas it is coming in the subject in the mail.

After executing the workflow in the results pane anchor (M) getting all the details related to mail (time, subject, ect) but to map this code to those files i need a key column which is only file name.I am not getting the exact file names in the Anchor (M) in any of the columns.

Bolide

@gantaanvesh ParentId from the Attachments stream (A) links back to the Id field from the Message stream (M).  Is this what you're after?

Meteor

@rpaugh   Is there any way you can put this download in the gallery somewhere?  I cannot reach the google drive site at work.

Bolide

@Darrie8468 the gallery doesn't provide direct downloads.  Do you have another file sharing option available to you?

Meteor

@rpaugh   Turns out I just needed to run through the process off my work's network.

 

Thanks!

Meteor

@rpaugh I hope I have not missed a comment that already asked this question.  Regarding attachments.  I see that the info about them is listed off the A in your tool.  How do I access them?  How do I download them?  Are they on my OneDrive?

Bolide

@Darrie8468 with the new API, the attachment stream outputs files as base64 encoded binary data.  So what you need to do is convert the content bytes to a blob then output the blob to a location of your choosing.  Here's an example:

 

Alteryx - Outlook Input - Office365 Attachment Save Example.png

 

Notice I left the extension off the file name in the blob output.  What I've noticed with this is when you select the "Replacing File Name With Field" option and you field name also contains the file extension, that extension is repeated.  For example, if I had used the file name "Test File 1.xlsx" the result of the blob output would be a file named "Test File 1.xlsx.xlsx".

Labels