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
Benjamin_Schalk
7 - Meteor

@rpaugh that's amazing! Will try it out on monday, don't have a working instance at home. Thanks so much for your work and support here!

Benjamin_Schalk
7 - Meteor

@rpaugh sadly I have the same issue with the new tool even after completely un- and reinstalling Alteryx Designer and deleting all the folders the tool installer created. So I can get data with the first run adding my credentials and all. But simply accessing the Configuration Window throws me the error previously mentioned. This means I can't change configs. Running a second time throws me this error which is the same result as running the tool without any specifications: 

image.png

 

Don't know whether it's something in the tool or on my laptop, but I would assume there are no prerequisites for using this tool on Windows 10, right?

Maybe something that prevents the GUI from loading when this/any error is thrown?

rpaugh
11 - Bolide

@Benjamin_Schalk, it looks like you're having an issue persisting your tool's configuration.  

 

Do me a favor, follow the instructions below and let me know what you get:

  1. Create a new workflow.
  2. Add the Outlook tool to the canvas and configure it.
  3. Save the workflow.
  4. Open the saved workflow in an application that can read xml (such as WordPad).  This version of the xml file should have all of your configuration options listed.
  5. Go back to Alteryx and click into your tool as if you're going to update your configuration (this should be where you get the error).
  6. Save the workflow as a new file (so as not to overwrite your other file's xml).
  7. Open the newly saved workflow in an application that can read xml.
  8. Let me know how the two files differ.

My guess is that the first file will show all of your configuration options and the second, none.  Depending on what you come back with will determine how we need to proceed.

 

Thanks.

rpaugh
11 - Bolide

@DavidJohnson, I apologize for the late response.  Yes, pulling in the From field is possible and was actually included in the release that went out last week.

DavidJohnson
7 - Meteor

Thank you @rpaugh I will download the latest release.

Benjamin_Schalk
7 - Meteor

@rpaugh you're guessing correctly, please find the images of both XMLs attached with mail and password obfuscated.

I think it already loses the config during the saving process as that's where the error occurred here, but that would support the issue that the config is not persisted as I "lose" the config window as I press Ctrl+s.

 

What you can also observe is that the <Fields> tag isn't opened in the second file at all, it's just closed

 

Settings1: 

Settings_1.png

 

Settings2:

Settings_2.png

rpaugh
11 - Bolide

@Benjamin_Schalk, the configuration is definitely not persisting.  One more question, are you using admin or non-admin version of Alteryx?  I don't know why, but if you're not using admin then maybe something in your system is preventing configurations from custom tools from saving (and by custom I mean c# custom tools, not macros or html/js tools since those work differently).  You may have to reach out to Alteryx on this one in case this is some kind of bug as I can't seem to reproduce the error and nobody else has reported it.

JOliver251
7 - Meteor

Hi when I run the tool I get this error can anyone help me?

 

Error: OutlookInputTool (3): 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)

rpaugh
11 - Bolide

@JOliver251, that's probably from either incorrect credentials (make sure to use your domain email format "username@company.com") or your server not allowing web service connections.

chrisrayner
8 - Asteroid

Has anyone else had issues with the Autodiscover after installing this tool?  My autodiscover was fine, but having installed the tool, it is no longer working, and as a result, none of the email tools I have in any workflows are working from my Designer any more?

I have tried un-installing the tool, but still can't get the autodiscover back.

Any ideas?

Chris

rpaugh
11 - Bolide

Hi @chrisrayner,

 

Has your IT team performed any changes to your Exchange server recently?  Perhaps this is a coincidence?  I just ran a test today and it worked properly so I'm unable to reproduce the issue.  Do you happen to have an error message (other than "could not find autodiscover service") or other information you can provide that might offer a clue?

 

Thanks,

 

Rick

chrisrayner
8 - Asteroid
Ok. Thanks very much for the update. I will see if I can speak to someone who will know about the exchange setup and see what they can tell me.
Many thanks
Chris


________________________________

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. No one else is authorised to distribute, forward, print, copy or act upon any information contained in this email. If you have received this email in error, please notify the sender.

Hiscox Syndicates Ltd and Hiscox Insurance Company Ltd are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority. Hiscox Underwriting Limited, Hiscox Europe Underwriting Limited and Hiscox ASM Limited are authorised and regulated by the Financial Conduct Authority. Hiscox plc is a company registered in England and Wales under company registration number 2837811 and registered office at 1 Great St Helen's, London EC3A 6HX.

European Privacy Policy

Hiscox is a trading name of a number of Hiscox companies. The specific company acting as a data controller of your personal information will be listed in the documentation we provide to you. If you are unsure you can also contact us at any time by emailing us at dataprotectionofficer@hiscox.com.

We collect and process information about you in order to provide insurance policies and to process claims. Your information is also used for business purposes such as fraud prevention and detection and financial management. This may involve sharing your information with, and obtaining information about you from, our group companies and third parties such as brokers, loss adjusters, credit reference agencies, service providers, professional advisors, our regulators or fraud prevention agencies.

For further information on how your information is used and your rights in relation to your information please see our privacy policy at https://www.hiscoxgroup.com/site-tools/privacy.
chrisrayner
8 - Asteroid

@rpaugh - many thanks for the response.

I have attached a screen print of the error that I actually get (apologies that it is not in a great format for reading it).

I am doing some additional testing on this at the moment as well - as soon as I have any additional information I will let you know, but I am fairly sure that there weren't any changes to Exchange at the same time.

It has however, coincided with us upgrading the version of Alteryx that we are using, so I am doing some investigating around that at the moment.

ChrisMSOutlookAlteryx.png

chrisrayner
8 - Asteroid

@rpaugh - Hi Rick.  It seems that the autodetect has been removed from 2018.3, which is why I am having problems with my email tools, so it was that rather than installing your tool.

Is it possible that the same thing is causing my problems with the tool though?

Regards

Chris

rpaugh
11 - Bolide

Hi @chrisrayner, I'm not sure what autodetect has to do with 2018.3.  What do you mean by "it was removed from 2018.3"?  I just upgraded to 2018.3 so I could reproduce the error but was unable to do so - the tool continues to work correctly with autodiscover.  Are you sure nothing else has changed in your local environment or on the Exchange server in any way?  

chrisrayner
8 - Asteroid

@rpaugh - Hi Rick. Apologies - there are two issues I am having, which I initially believed to be related, and I no longer believe that they are.

We upgraded from version 11.5 to 2018.3 last weekend.  I then installed your Outlook tool on Monday, and during my playing, I realised that my 'send email' tools in any of my workflows no longer worked if I started making changes to the workflow.   I initially assumed that the send email tool issues were related to the fact that I installed the Outlook tool.  I have since discovered that it is because the 'AutoDetect SMTP' button has been removed from 2018.3, and so when you start making changes to a workflow that had previously used that feature, it now generates an error to say that the SMTP cannot be blank.  Having looked on this forum for that issue, it appears that this is by design for security reasons, and that you do have to enter the SMTP details for these tools.

So that issue has now been explained, and I just have the autodiscover issue to overcome.  As yet, I have not been able to get this tool working, so I am just trying to find an initial setup that will work for the tool at my company.

Hope this makes sense, and clarifies my previous somewhat mixed messages (apologies for that).

Chris

rpaugh
11 - Bolide

@chrisrayner, that makes perfect sense, thanks for the update.  The Send Email and Outlook Input tools are different issues and the issue with the Outlook tool is independent of the upgrade to Alteryx version 2018.3 since it's a completely custom tool.  Follow up with your IT team to see if there are any issues with the autodiscover service on your Exchange server (or configuration if you're using Office 365).  Unfortunately, there is little I can do from the client side (i.e. the Outlook Input tool) if something on the server prevents access to its services for any reason.

rpaugh
11 - Bolide

@chrisrayner, if they come back and tell you that they are positive nothing should stop you from accessing the services let me know and we can troubleshoot further.

sgopinath
7 - Meteor

Hi Rick

 

This is a great tool.. thank you.. Strangely, I wasnt able to get it to work until I uninstalled our anti-virus (stumbled upon this issue as we were trying to resolve other issues with Tableau and Alteryx).. but now that it works, my issue is that it takes a very long time to get the results. When i looked for just two fields from my Inbox (with 1531 emails), it took 8 mins and 20 secs to run. Any thoughts on how to improve this speed?

 

Thank you!

 

 

Best Regards.

rpaugh
11 - Bolide

@sgopinath, that is weird.  Glad you got it working.

 

Regarding performance, have you tried using a query string?  For example, "received:today"?  It will still take a little time because Exchange Web Services aren't super performant, but should still speed it up dramatically.

 

Here is some information about using dates in your query string:

 

Alteryx Outlook Tool - Date Filter 9.png

SW7
5 - Atom

Hi @rpaugh , thank you for creating this! I am using Alteryx version 11.5 and cant download your newest version. Do you have an older version that would work for 11.5?

 

Thanks!!

Benjamin_Schalk
7 - Meteor

@rpaugh 

news from my side: It actually seems to be the language setting! 

I just needed to change it to english BEFORE OPENING Alteryx Designer. Changing while it's open won't help as Alteryx seems to preserve this setting. 

 

However, this means that I either need to change my system's date format to UK or US or I can't use the tool. Do you think there is some parsing step in the tool that fails with the german "dd.mm.yyyy" format?

 

EDIT: I could actually create a workflow with the english setting and then switch back to german, that seems to have worked on a first glance. Not super nice though.

rpaugh
11 - Bolide

@Benjamin_Schalk,

 

Wow, nice sleuthing!  That could very well be the issue.  I'll have to dive into the code to see if there's a way to account for that and get back to you.

rickyc
5 - Atom

I receive the following error after configuring the tool (clicking in the canvas to confirm the configuration). Afterwards, I'm unable to enter the tool again to change the configuration.

Version: Alteryx Designer 2018.3

CaptureError.PNG

Benjamin_Schalk
7 - Meteor

@rickyc

I'm having the same, solved it by changing my date format to english (UK) in the control panel BEFORE opening Alteryx Designer. See my comments on page 4 and 5 above...

My PC is in german which has a different date notation than the english one...

rickyc
5 - Atom

@Benjamin_Schalk

Thanks for your reply, this workaround fixed the issue!

Changed the System's date formats as following:

Settings.PNG

Hi,
I receive same error as rickyc above while trying to open outlook tool. Sadly changing format to English (UK) did not help me. Maybe anyone have solved same issue in different way?

sgopinath
7 - Meteor

Quite possible that I am doing something wrong here, but I am unable to convert the Email DateTimeReceived from the emails to a 'datetime' format to run some date calculations. I tried converting using ToDateTime() and also tried to just change the datatype on the join tool when joining with another data source. Had to deconstruct the components of the field and reconstruct in the Alteryx recognized format of YYYY-MM-DD HH:MM:SS

 

Am I missing any trick?

 

Thanks!

DanielG
12 - Quasar

Hi,

 

This tool is pretty cool.  Thanks for all the work you have put into it thus far.  I cant seem to figure out if this is possible, so I am asking here: 

 

Is there any way to connect this to a public folder in Outlook?

 

Thanks

rpaugh
11 - Bolide

Hi @DanielG,

 

You should be able to check the box "Use Different Mailbox, enter the name that contains the desired folder, and search from there.  Let me know if that works for you.

DanielG
12 - Quasar

Hi,

 

Thanks for the quick response.  I copied the folder path from the properties (summary tab) for the desired folder.  I checked "use different mailbox" and pasted the path into the corresponding field.

 

When I hit ran I got a "SMTP address format is invalid" error.

 

I tried using the address xxx@xxx.com as well, and that failed with "the SMTP address has no mailbox associated with it".

 

The tool worked fine when I tested with my own email address, so I am hoping it is just a syntax error on my part.

 

Thanks for any additional assistance that you can provide.  :)

 

DanielG

rpaugh
11 - Bolide

@DanielG, do you know what mailbox is associated with the public folder you're trying to reach?  Feel free to send me a private message so we can troubleshoot further if needed without cluttering up the inboxes of people who have subscribed to this blog.  Thanks.

brianscott
11 - Bolide

@rpaugh - This is an amazing tool.  And I love how responsive you have been to others in this thread.  

 

Is there a way I can access a calendar that has been shared to me?

 

Thanks

brian

 

 

rpaugh
11 - Bolide

Hi @brianscott,

 

Yes, you should be able to select the "Use Different Mailbox" option, enter the name of the account that owns the calendar shared with you, and select "Calendar" in the Folder to Search drop-down.  

brianscott
11 - Bolide

@rpaugh - 

 

 

Hm.  Was getting folder not found exception.  Got some more permissions love from a guy that owns a calendar, and now no longer get an error.  But I get zero records back out.  I can see plenty of events in this persons calendar from within Outlook.  The user gave me full rights on his Calendar, but still get no results back out.  

 

Got any ideas? 

Thanks a lot.  

 

brian

brianscott
11 - Bolide

@rpaugh -  

 

OK.  Actually got it working!  Small possible bug:

 

Attach to a different mailbox.

Set to Calendar. 

Click 'include sub folders'

Click Skip Root Folder Search. 

Unclick 'Include sub folders'. 

'Skip Root Folder Search' remains checked.  

 

Once I undid the skip root folder search, I got the events I expected to get.  This macro is sweet, man.  Great job! 

 

brian

rpaugh
11 - Bolide

Thanks, @brianscott.  I'm glad you got it working.  And thanks for the tip on the bug. I'm actually working on an updated version of the tool so I'll be sure to include that fix.

danners
7 - Meteor

Recently we've started running into a similar error to what @chrisrayner experiences above.  We received a response from IT:

The portion of this error that says "AutodiscoverService.InternalGetLegacyUserSettings" calls out the legacy connection methods (POP, IMAP, etc) that are now turned off.

We were getting great value out of the tool but it seems we won't be able to utilize it going forward unless some changes are made to the authentication options. 

@rpaugh any plans on the roadmap to make this work with modern auth methods? https://docs.microsoft.com/en-us/office365/enterprise/office-365-client-support-modern-authenticatio...

rpaugh
11 - Bolide

@danners, yes I was planning to address other authentication methods if possible in the new version. I’ll be sure to look into this for you and can hopefully come up with a good solution.

danners
7 - Meteor

Thank you!

sgopinath
7 - Meteor

Hi @rpaugh

 

When I try to look up the Contacts folder, the list of available fields to choose from are not related to the Contact entity. Are 'Contacts' not available to extract yet? Thank you!

rpaugh
11 - Bolide

Hi @sgopinath

 

No, additional Contact fields have not been added yet.  I focused on the most common functionality first and you're the first to ask for this one :).  I'll be sure to incorporate this in the new version of my tool and will post here when it's ready.

sgopinath
7 - Meteor

Thanks @rpaugh

 

Very surprised that nobody has asked for Contacts functionality yet :).. ! We are using this to help a few people visualize their contacts.. there are some fun facts that emerge from that as well, which can be cool for people to know.. (how many countries do they have contacts in.. what domains are the contact addresses from.. Gmail.com vs. icloud.com etc.,...what are top first names from their list.. )

 

Separately, I have an additional 'root folder' for contacts that was created on my mailbox. Is there a way to look up custom created root level folders (which are not technically sub folders). Thank you!

 

 

 

rpaugh
11 - Bolide

@sgopinath, are the other root level folders just shared public folders or something else?  If they're public folders then I'm currently investigating how to incorporate those into my updated tool as well.  If they're associated with other mailboxes then you can use multiple input tools and specify the associated mailbox to use to get to those root folders.

sgopinath
7 - Meteor

These are additional root folders associated with the same mailbox. For example - I have two Contact Folders - one is the default Contacts folder and the other is called "CRM - Contacts" at the same level as the primary Contacts folder. 

rpaugh
11 - Bolide

@sgopinath, hmm I'll have to research that and get back to you.

d208290
5 - Atom

Hi all, i was keen to leverage this add in but am unfortunately having no luck. I am currently running version 2018.4.5.55178.

 

The run function appears to work successfully when installing the file however the item is not visible under the connector tools and doesn't return any results when searched.

 

Is anyone able to assist?

rpaugh
11 - Bolide

@d208290, verify that the location in which you're installing the tool matches your Alteryx installation.  This seems to be a common issue when Alteryx is installed for all users, but the user installs the tool only for them (or to a different directory entirely) for example.  If you're able, locate your OutlookInputTool.ini file and verify that the path matches your installation directory:

 

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.

d208290
5 - Atom
I reinstalled the application and input tool to the same location and it's all working. Thank you!

Reinstalling application and input tool helped me as well. Appreciate the help.