community
cancel
Showing results for 
Search instead for 
Did you mean: 

Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-to's.
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. 

 

OutlookInputTool.pngOutlook 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

 

OutlookToolConfig.pngOutlook 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 querystring.pngcopy 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
Bolide

@DmtCoj, if you are referring to filtering only emails within a certain date range you can use the following (should also work with the received property):

 

Alteryx Outlook Tool - Filter Example.png

 

You can combine this with other filters to narrow down your results using query logic operators:

 

Alteryx Outlook Tool - Filter Logic Example.png

 

If you are referring to filter the data out of an attachment prior to pulling it into the workflow then no, that can't be done.  The reasoning is that the tool is file agnostic - it offers you the ability to pull in any data file that Alteryx is capable of reading without having to know anything about that file.  For example, filtering an Excel file is very different than filtering a JSON file or a blob file, so to code for every possible scenario would far exceed the scope of the tool.  

 

I hope this answers your question.

 

Rick

 

 

 

 

Asteroid

@rpaugh Hello, I am trying to do something similar to what @jlamb was doing.

 

Instead I'm looking to download an excel attachment from an email that gets sent to an Outlook subfolder once a week

 

I have followed the steps you posted above to find the excel attachment and open the specific sheet. However i am having trouble connecting to my work Outlook and get the rather large error message I've posted below:

 

I have checked my Autodiscover and it is on/exists.

 

Any help on how to connect to a subfolder in my outlook would be greatly appreciated!

 

I have attached a picture of my current workflow/outlook input tool fields:

Capture.PNGWorkflow

 

 

 

Error message

 

Info: OutlookInputTool (1): 9223372036854775807
Error: OutlookInputTool (1): The Autodiscover service couldn't be located.
AutodiscoverLocalException
at Microsoft.Exchange.WebServices.Autodiscover.AutodiscoverService.InternalGetLegacyUserSettings[TSettings](String emailAddress, List`1 redirectionEmailAddresses, Int32& currentHop)
at Microsoft.Exchange.WebServices.Autodiscover.AutodiscoverService.GetLegacyUserSettings[TSettings](String emailAddress)
at Microsoft.Exchange.WebServices.Autodiscover.AutodiscoverService.InternalGetLegacyUserSettings(String emailAddress, List`1 requestedSettings)
at Microsoft.Exchange.WebServices.Autodiscover.AutodiscoverService.GetUserSettings(String userSmtpAddress, UserSettingName[] userSettingNames)
at Microsoft.Exchange.WebServices.Data.ExchangeService.GetAutodiscoverUrl(String emailAddress, ExchangeVersion requestedServerVersion, AutodiscoverRedirectionUrlValidationCallback validateRedirectionUrlCallback)
at Microsoft.Exchange.WebServices.Data.ExchangeService.AutodiscoverUrl(String emailAddress, AutodiscoverRedirectionUrlValidationCallback validateRedirectionUrlCallback)
at OutlookTools.OutlookEmail.GetItems(Int64 recordLimit)
at OutlookTools.OutlookInputToolEngine.PI_PushAllRecords(Int64 nRecordLimit)
at SRC.Alteryx.GenericNetPluginInterface.PI_PushAllRecords(GenericNetPluginInterface* , Int64 nRecordLimit)

Bolide

@Izbiz, what format are you using for your username?  If you're using your company email address then you may have to switch it to the domain format.  For example, bob.smith@acme.com may have to be something like bsmith@na.acme.com.  Feel free to message me directly if you don't want to broadcast your information to the whole community.

Asteroid

@rpaugh I was working on a problem with Alteryx support the other day and in looking at the connector the password is not encrypted in xml/html body they recommend adding that to it, I was going to take a quick look at it on Github this weekend but we shall see if I get to it.

Bolide

Ah, good catch.  I didn't really think much about it considering the configuration files are likely stored on machines that are already protected from other users, but I guess it wouldn't hurt to throw in an extra layer of security.  There are definitely several ways to accomplish this.  If I find a good one, I'll run it by you.

Meteoroid

Hi @rpaugh, thanks for developing this great tool.

 

I am attempting to install on a system with Alteryx 10.6, unfortunately I can't use the executable. Attempting to access the gallery at this link I receive the following error:

 

Access Denied

Sorry, you don't have access to this workflow.
!

This workflow is not shared with you via a collection.

 

Are you able to assist?

 

Best

Plight

Bolide

@Plight, Hm...strange, it should be in the public gallery.  You may have to create a gallery account if you've not done so already.  Or you can download the executable directly here.

Meteor

Hi, hope you are doing well. 
I tried to work with your Outlook Macro Tool and have and issue. I believe that this tool has a specific timezone for the data extraction (for example, I want to extract data for 15th of March and as an end result it shows me data starting from 2am 15th till 2am 16th), my outlook settings have a time zone UTC+02:00. Do you have any suggestion how to resolve such issue without extracting data for two day and than applying a formula tool. Thanks for your awesome contributions 🙂 

Meteoroid

@rpaugh I had to update my Alteryx to 11.5 and when I pulled the newest version from the Gallery and after successful running of the flow I see the Tool loading in my programs and I see the outlook folder in my Alteryx folder, but within Alteryx I do not see the tool in Connectors or via the search function.  Is there a newer version that works with 11.5 or did I miss something with the install of the flow from the Gallery?

Bolide

@WBender, did you close and re-open Alteryx after the install?  Also check to make sure Alteryx and the Outlook tool were installed to the same location.  I've seen some users run into the issue of one or the other being installed in a folder within their user's AppData folder rather than the computer's Program Files folder.  This will still work, but you may need to locate the .ini file for the Outlook tool and update the path in it to the actual Alteryx Designer installation folder.

Meteoroid

@rpaugh unfortunately yes, I uninstalled just to verify, re-installed to C:\Program Files\Alteryx , closed Alteryx, opened Alteryx and still no Outlook Tool. The Settings\AdditionalPlugins\OutlookInputPlugin is even pointing to the correct location too.

 

 

 

Update - I didn't notice that our team had added a (User) shortcut to my taskbar, which was incorrect.  So as usual your tool works perfectly 🙂

Alteryx Partner

Thanks for all you work - the tool is great.

 

I think i encountered a bug - in my setup if a single email has multiple attachments, only the first one gets downloaded to the selected folder. I'm using  Alteryx 11.7.4 , Exchange version is 14.3.352 and I have the tool configured to Exchange2010_SP2. I attempted with the unique filename box ticked as well as not ticked. The attachments are .docx files with distinct names of similar length

Meteoroid

Our team is looking to use Microsoft Exchange Servers 2016. Is there an enhancement available or a way to alter the code, so this can be accomplished?

Hey- I've downloaded the Outlook tool but can't find it in my "Connectors" pane. I'm running on Alteryx 11.7.

Meteor

Hey everyone,
Has someone managed to figure out how to identify the actual sender from the shared mailbox(Outlook) when using that tool? 
@rpaugh thanks again for such an awesome job!)

Bolide

@patrickbrown,

 

Sometimes servers are set up with different drive and installation settings so it's possible that the DLLs installed to the correct location, but the .ini file that tells Alteryx where to look for those DLLs may not have the right path.  Locate your OutlookInputTool.ini file on the server, open it in notepad, and make sure the path in it points to the location you selected during installation.  

 

For example, in my case the DLLs installed to:

  • C:\Program Files\Alteryx\Outlook

...my OutlookInputTool.ini installed to:

  • C:\Program Files\Alteryx\Settings\AdditionalPlugins

...and the path in the .ini file is:

  • C:\Program Files\Alteryx\Outlook

Another thing to keep in mind is that the directory in which you installed the tool needs to be accessible by Alteryx, which is why the default location is the same as the Alteryx Designer's default directory.  

 

If you're dealing with a desktop installation, verify that either the tool or Alteryx itself was not installed for a particular user (i.e. in the user's AppData folder).  If it is, then simply locate the .ini file mentioned above and change the x64 path in the file to match the Alteryx installation.

Bolide

@bhattaa, this tool should work as-is for Exchange 2016.  The only issue will be that any new fields in Exchange 2016 may not be accessible in the tool yet - but it should not crash or anything.  Once a new version of the API I'm using is released for Exchange 2016, I'll get the tool updated so you can take advantage of any new fields.

Bolide

@VVV, I have not encountered an issue with only pulling one attachment from an email - in fact, I typically run into the opposite in which the tool pulls every attachment and embedded image as an attachment.  Is it just the one email (or type of email) giving you the trouble or are you able to reproduce the issue for every single email you're trying to pull attachments from?

Meteoroid

Error Message.jpg@rpaugh

 

Please advise. We selected Exchange Server 2013, but the Alteryx did not pull the attachment. We got the following error: please see attached file.

 

 

Bolide

@bhattaa, would you mind reposting your error message?  I'm having trouble reading it from your image.

 

Thanks.

Meteoroid

Hey @rpaugh, thanks for your quick response!

 

Error Message.jpg

Bolide

@bhattaa, it looks like the auto-discover service may not be enabled for your server.  There are two ways to approach this:

 

  1. Contact your IT department and have them enable auto-discover on your Exchange server.
  2. Use a manual service URL:
    Alteryx Outlook Tool - Manual Service Endpoint.png

@rpaugh,

 

Thanks, I've been troubleshooting with IT. We have the desktop/user version installed.

 

We Pointed the outlook settings.ini file to your users\local\alteryx folders and no luck. Also pointed to the users\local\alteryx\bin folder where I saw a bunch of .dll’s.

 

We still cant see the tool in the "Connectors" pane. Any other ideas on how to remedy?

Bolide

@patrickbrown,

 

Would you mind sharing the following information:

  • Alteryx installation path.
  • Outlook tool installation path.
  • Text in the OutlookInputPlugin.ini file.

Also, what profiles are on the computer, and if there are more than one is the Alteryx installation available to all of them?  

 

Feel free to send me a personal message if you do not wish to share this information in this blog.

Meteoroid

@rpaugh is the current download compatible with the newest version of Alteryx (11.7...) ? I have kept a version of 11.0... so I could still leverage the Outlook Tool, but on 11.7... version or on other user's machines they are having the same issue where they cannot find the Tool with the later versions.  Is this just a mapping like you are noting above or user error on my part?

Bolide

@WBender, yes, the tool works in 11.7.  If you're not seeing it in the Connectors panel then it's probably because either the Outlook tool was not installed in the correct location where Alteryx can read it or the OutlookInputPlugin.ini file is pointing to a location where Alteryx is not installed.  Check both and let me know if you still can't get it resolved.  You may also try looking further up in this blog post for more information on how these files/locations should match up.

Hi! I'm unable to download the workflow as I get an message noting it was created on a newer version, I currently have version 11.5 and no newer version is available for me through work at this time. Is there a later version that I can download and use? Thanks so much!

Bolide

@Caitlin-Patterson, The tool works with all versions, it's just an issue with the installer workflow in the gallery.  You can download the executable directly here.

@rpaugh - Hey love this tool.  I've been using this to send out maintenance schedules and have come across issues with reoccurring maintenance.  Is there an available field tool pull the retention date besides the field labeled retention date as this one comes back null.

 

Thanks for putting this here!

Alteryx Certified Partner

This is really cool @rpaugh!

 

Do you know if this would work with a shared inbox?

Thanks

Bolide

Thanks, @TimothyManning!

 

Yes, you should be able to log in with your normal credentials then enter the shared mailbox into the following section in the tool's configuration window:

 

Alteryx Outlook Tool - Shared Mailbox.png

 

Or if you have credentials for the shared mailbox you could try logging into it directly.

Asteroid

This is Amazing!  

 

Is there anyway to publish this to a workflow in my company's gallery?   I'd like to use it with scheduler? 

 

I'm getting an error message

Can't find plugin "OutlookTools.dll" (Tool Id: 1)

 

Thanks.

Bolide

@BPurcell, you'll have to install the tool on the server that's powering your company's gallery.  That will give it the OutlookTools.dll that it's looking for.

Bolide

@pkuttner12, I apologize for the delayed response to your question.  I did some research and from what I could tell the provided retention date field is the correct one.  It looks like there may be some extra information on the recurrence object that I might look into incorporating into the tool, but I didn't see a separate retention date field.  What exactly are you trying to do with this field?  Maybe there's something else on the recurrence object that could meet your needs..

@rpaugh.  We are using your tool to automate a report coordinating the maintenances.  Engineers create a meeting with the details of their maintenance and the expected start time and end time of their maintenance.  The report is sent out to avoid having overlapping maintenances which in broadcast could cause an outage. Sometimes the maintenances have multi day windows.  The engineer generally uses the end by date field in reoccurrence.  In the tool IsRecurring(Boolean) and Reoccurance which gives the pattern (daily/weekly/monthly) come through fine.  There is a field labeled RetentionDate which I figured correlated to retention comes through null.  Without this retention date the meeting disappears after the first instance.  However, I was able to work around this by requesting engineers to copy and paste meetings for successive days.

 

Again thank you for making this available to the community.

Bolide

@pkuttner12, ok it looks like that information is located on the recurrence object in the code.  I'll have to look at the code to figure out how to add recurrence attributes to the tool's output.  This likely won't be a quick solution, but I'll hopefully get something out as soon as I can.

Alteryx Partner

Thanks! This tool helps me out a bunch! Quick question though, would it be possible to read shared calendars as well? As in the calendar of a colleague that he or she shared with me (given limited or full permissions for viewing), but that I'm not a (co-)owner of? Use different mailbox is apparently not an option in this case.

Bolide

@mjeckmans, what happens when you attempt to use a different mailbox?  Do you get an error or just no data?  I just ran a test with two accounts (both ways) and was able to pull shared events.  Highlighted below is the event from the john.doe calendar:

 

Alteryx Outlook Tool - Shared Calendar Example.png

Bolide

@pkuttner12, a new version of the tool with the ability to pull recurring events has been published to the public gallery:

 

Alteryx Outlook Tool - Recurring Event Example.png

 

Note the new configuration options:

  • Include Recurring Events
  • Start Date
  • End Date

If you don't check "Include Recurring Events" you will still get the first instance of a recurring event in your regular results, just nothing afterwards.  

 

Note #2: I couldn't locate the DLLs associated with version 10.6 so I had to pull what I had for 11.7.  For that reason, if you're using an older version of Alteryx you may or may not be able to run the latest version of this tool.  Again, I don't have an older version with which to test this so I can't say for sure.  If it doesn't work for you then uninstall it, go back to the public gallery, click on the gallery app's version number, select version 25 or below, and reinstall that.  At least that way you won't lose the ability to run what you are running now.

 

Let me know if you have any questions or encounter any errors.

 

Rick

Alteryx Partner

@rpaugh

 

Using your exact same settings I get this error:

 

Error: OutlookInputTool (1): The specified folder could not be found in the store.
ServiceResponseException
at Microsoft.Exchange.WebServices.Data.ServiceResponse.InternalThrowIfNecessary()
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)

 

I can see their calendar details fully when opening in Outlook though. Again this is not a shared account, it's the calendar of someone else's account of which they have allowed me to see the details. If that matters 🙂

 

Thanks for the prompt reply by the way!

Bolide

@mjeckmans, contact your IT department and have them verify that the setting "InheritanceType" is set to "All" for the user's mailbox/calendar.  Let me know what happens.

Alteryx Partner

@rpaugh Thanks! Got it to work! Magnificent tool 🙂

 

One thing that's not working (yet) for me is trying to use the Start: and End: query string parameters. They work in Outlook itself, and the sent: and received: ranges work perfectly fine, just start and end would be highly beneficial when dealing with calendar data.

 

Is that something that needs to be implemented in the tool or does this lie elsewhere?

Bolide

@mjeckmans, calendar querying works slightly differently than the standard inbox querying, so the query string field only applies to top level items and not the recurring items.  I'll look into what additional calendar filter options I can apply.  In the meantime, a workaround would be to pull everything and use the standard Alteryx Filter tool to pare down your results.  If that still doesn't work for you let me know and I'll see if I can throw together an in-between solution relatively quickly.

Alteryx Partner

@rpaugh That's how I've currently got it set up.

 

Basically what I'm building is an app for internal use to match people to assignments, based on calendar availability amongst others... But the data pull is enormous (or rather extremely slow), and I'm looking to restrict that somehow to only pull future events, rather than all history. Using some smart sent/received business rules I can narrow it down slightly, but it's nowhere near as robust as I'd like.

 

So yeah filter within Alteryx is what I'm currently using, but it'd be extremely useful if the data wouldn't have to be pulled at all. It's the difference between a midnight refresh cycle or maybe an hourly cycle. The latter being much more useful to us. Using start: and end: does work when using it in outlook as a search option, so I'm holding out hope 😉

Bolide

@mjeckmans, haha, I hear ya.  I'll see what I can do. When you say it's getting everything right now, what does "everything" mean?  This quarter?  This year?  Since the beginning of time?

 

That sounds like an awesome use case btw!!  I absolutely love seeing how people use the tool in different ways that I'd never even considered.

Alteryx Partner

@rpaugh By everything I mean I'm pulling all events in someones calendar, the entire history. Again, I could restrict this by using the sent and received date filters, but I'd like the app to be robust when dealing with year changes and long-standing recurring events. Hence I'd much rather pull all events with an end date sometime in the future, which reduces the number of rows pulled. Right now on average a pull takes over 4 minutes per person, which can be reduced to probably 10-15 seconds when dealing with just future events.

Bolide

@mjeckmans, while recurring events cannot be filtered the same way as other items (or the master events) via the Exchange Web Services, I did find a bug in the date filter for recurring events.  I fixed that and published a new version of the tool installer to the public gallery.  Please let me know if this version gets you at least most of the way to where you want to be.  Also, please note that the master event record also appears as an occurrence in the recurring event results (if that master is within the date range you're pulling that is) so you'll need to account for that to prevent duplicate results.

 

Here's an example pulling all master events, but only recurring events from now until the end of June:

 

Alteryx Outlook Tool - Recurring Event Example 2.png

 

 

 Dear Community, 

 

Do you have the link to be able to download this tool in the 11.5 version? We have the 11.5 version for desktop/server in our company and want to schedule a workflow that includes this tool but the macro should be able to work on the wanted version. 

 

Thanks!

 

-JfP

Bolide

@Jpariente1, to clarify: are you having trouble getting the tool to run on 11.5 or are you having trouble running the installer app from the gallery in 11.5?  If it's the latter then open the downloaded app in a text editor, change the version number in the text to 11.5, save, re-open the installer workflow and you should be good to go.

Meteor
This is such a cool tool! Thanks for developing it and the support when it wasn't working correctly for me. Is there a way to have the alteryx workflow move the emails that were read via the tool to a specified folder? It would be great if the workflow could filter some emails, extract the data from those emails and then move them to a new folder so they are out of the inbox.
Labels