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
JMoore
8 - 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.

rpaugh
11 - 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.

JMoore
8 - 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.

TimothyManning
8 - Asteroid

@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

rpaugh
11 - 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?

Slushercw
8 - Asteroid

@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)?

rpaugh
11 - Bolide

@Slushercw 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.

Darrie8468
8 - Asteroid

@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?

rpaugh
11 - 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

Darrie8468
8 - Asteroid

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

rpaugh
11 - 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.

Kamlesh_g
5 - Atom
@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.
jdrummey
9 - Comet

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

 

rpaugh
11 - 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. "==").

jdrummey
9 - Comet
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
rpaugh
11 - 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.

Yanoflies
8 - Asteroid

@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.

rpaugh
11 - 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

jdrummey
9 - Comet

@rpaugh - I sent you a PM.

brendali
5 - 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? 

 

 

brendali
5 - 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. 

rpaugh
11 - 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.

ahursey
5 - 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?

gantaanvesh
8 - Asteroid

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 )

rpaugh
11 - 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.  

gantaanvesh
8 - Asteroid

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

rpaugh
11 - 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).

Darrie8468
8 - Asteroid

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

Darrie8468_0-1580827292155.png

rpaugh
11 - Bolide

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

Darrie8468
8 - Asteroid
rpaugh
11 - 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.

Darrie8468
8 - Asteroid

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

gantaanvesh
8 - Asteroid

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.

rpaugh
11 - 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?

Darrie8468
8 - Asteroid

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

rpaugh
11 - Bolide

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

Darrie8468
8 - Asteroid

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

 

Thanks!

Darrie8468
8 - Asteroid

@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?

rpaugh
11 - 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".

automate
6 - Meteoroid

Hi and thank you very much for creating this incredibly helpful tool!

 

I'm using the office365 version and I'm trying to get attachments from email and use them in the workflow. Is there a way I could do that? I've read that you need to parse with JSON or use the blob convert. But when I used the blob convert, it just outputted a file with no extension(supposed to be xlsx). I opened it with excel and it opened fine after an error but is it possible to not have that issue? Thank you!

tgill1
7 - Meteor

@rpaugh 

 

On attempt on installing this, and running it, I get the following error. Do you know what this may be and how to fix it?

 

Thanks! 

 

tgill1_0-1582220479880.png

 

rpaugh
11 - Bolide

@tgill1 at what point during the installation did you get that error and to what directory were you trying to install it?

 

Also, if you happen to be using Office365 I highly recommend this version instead.  It uses Microsoft's new API and Alteryx's native installer - i.e. better on both counts.

gantaanvesh
8 - Asteroid

Hi @rpaugh ,

 

In my schedules i am getting an error as shown below, whereas till morning it was working fine without any errors, but suddenly i can see error like 

(Tool #2 The request failed. The remote server returned an error: (401) Unauthorized.¶ServiceRequestException¶ 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.GetItems(Int64 recordLimit) ¶ at OutlookTools.OutlookInputToolEngine.PI_PushAllRecords(Int64 nRecordLimit) ¶ at SRC.Alteryx.GenericNetPluginInterface.PI_PushAllRecords(GenericNetPluginInterface* , Int64 nRecordLimit))

gantaanvesh_0-1582543077139.png

gantaanvesh_1-1582543164994.png

 

 

rpaugh
11 - Bolide

@gantaanvesh did your IT team update security on your Exchange server?  It looks like either your account no longer has permission to access Exchange Web Services or they changed the security permissions altogether.  I know some companies have shut off the authentication method used by this version of the tool due to security concerns.  I would check with your IT group first to see if they've made any changes that would affect your ability to access the web services.

tgill1
7 - Meteor

Thanks @rpaugh 

 

I use Office 365, and when downloading the package you suggested, I encounter the following error. Do you know why this may be - fear I'm doing something wrong!image.png

 

rpaugh
11 - Bolide

@tgill1 you don't need to download the workflow for the Office365 version.  Just click "Run" until you get to the final report page.  There you will see a direct link to download the yxi file.  Click that to download it, run that, and Alteryx will prompt with its own installer.

tgill1
7 - Meteor

Thanks @rpaugh 

 

Sorry for several silly questions - but how do I find the yxi file? It only gives options to download the file in the following formats after I run it (zip, pdf, docx, xlsx, html).

tgill1_0-1582566791931.png

 

rpaugh
11 - Bolide

@tgill1 

 

Outlook Install Link.png

laura2
6 - Meteoroid

Is there any functionality/options that would allow for the HasAttachment field to tell you how many attachments the e-mail contains, rather than listing only True or False?

rpaugh
11 - Bolide

@laura2 yes and no.  Yes, it is technically possible for me to bring the list of attachments into the message stream and count them.  No, because doing so could drastically affect the performance of the tool, especially if you are only interested in querying messages.  The two streams were split to provide people with the option to utilize one or the other, or both, depending on their particular use case.  You could always link the message and attachments streams together and summarize by message id to get the count of associated attachments.  I realize this may not be ideal given performance concerns, but much of that can be alleviated with strong filters.  

 

Now, all of that said, I can pull in a limited amount of information from the attachment into the message stream (e.g. id only) to reduce the performance hit and output the count as a new field.  I'll look into this and may reach out to you for load testing if you don't mind since my personal mailbox isn't large enough to properly test this.