Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
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
7 - Meteor

Mind-Blowing @rpaugh 

Like most tool in Alteryx, you can cache the tool so it does not run run everytime you run the workflow.

Is there a way you can add that functionality?

 

Screenshot (3319)_LI.jpg

7 - Meteor

Error: OutlookInputTool (1): Unexpected end of file has occurred. The following elements are not closed: t:Content, t:FileAttachment, m:Attachments, m:GetAttachmentResponseMessage, m:ResponseMessages, m:GetAttachmentResponse, s:Body, s:Envelope. Line 1, position 1350289.
XmlException
at System.Xml.XmlTextReaderImpl.Throw(Exception e)
at System.Xml.XmlTextReaderImpl.ThrowUnclosedElements()
at System.Xml.XmlTextReaderImpl.ParseElementContent()
at System.Xml.XmlCharCheckingReader.Read()
at System.Xml.ReadContentAsBinaryHelper.MoveToNextContentNode(Boolean moveIfOnContentNode)
at System.Xml.ReadContentAsBinaryHelper.ReadContentAsBinary(Byte[] buffer, Int32 index, Int32 count)
at System.Xml.ReadContentAsBinaryHelper.ReadElementContentAsBinary(Byte[] buffer, Int32 index, Int32 count)
at System.Xml.XmlCharCheckingReader.ReadElementContentAsBase64(Byte[] buffer, Int32 index, Int32 count)
at Microsoft.Exchange.WebServices.Data.EwsXmlReader.ReadBase64ElementValue(Stream outputStream)
at Microsoft.Exchange.WebServices.Data.FileAttachment.TryReadElementFromXml(EwsServiceXmlReader reader)
at Microsoft.Exchange.WebServices.Data.ComplexProperty.InternalLoadFromXml(EwsServiceXmlReader reader, XmlNamespace xmlNamespace, String xmlElementName, Func`2 readAction)
at Microsoft.Exchange.WebServices.Data.ComplexProperty.LoadFromXml(EwsServiceXmlReader reader, XmlNamespace xmlNamespace, String xmlElementName)
at Microsoft.Exchange.WebServices.Data.GetAttachmentResponse.ReadElementsFromXml(EwsServiceXmlReader reader)
at Microsoft.Exchange.WebServices.Data.ServiceResponse.LoadFromXml(EwsServiceXmlReader reader, String xmlElementName)
at Microsoft.Exchange.WebServices.Data.MultiResponseServiceRequest`1.ParseResponse(EwsServiceXmlReader reader)
at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.ReadResponse(EwsServiceXmlReader ewsXmlReader)
at Microsoft.Exchange.WebServices.Data.SimpleServiceRequestBase.ReadResponseXml(Stream responseStream)
at Microsoft.Exchange.WebServices.Data.SimpleServiceRequestBase.ReadResponse(IEwsHttpWebResponse response)
at Microsoft.Exchange.WebServices.Data.MultiResponseServiceRequest`1.Execute()
at Microsoft.Exchange.WebServices.Data.FileAttachment.Load(Stream stream)
at OutlookTools.OutlookEmail.GetAttachmentsFromItem(Item item)
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)

 

 

This is the error message I get @rpaugh 

When I connect any tool to the output of the outlook tool it gives that error.

I cannot cache the tool, then I tried writing into an excel file so I can do some data prep and later connect the workflow to the outlook too but that keeps giving this error.

Screenshot (3321).png

11 - Bolide

@DotDigitals for caching, simply throw a Select tool on one or both outputs and cache and run that.

 

Regarding the error, I've not seen that one before. I'll look into it. In the meantime, would you be able to provide further insight into when that error showed up and what email content you were trying to pull?

11 - Bolide

I was looking for just such a tool and downloaded it but get the same error as one of the other posters 

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)

 

The most recent version of outlook in your tool is Exchange2013_SP1 but we are on 2016.  is there an updated version or is my error about autodiscovery unrelated?

11 - Bolide

@fharper The version selection just tells the tool the minimum version to look for so you don't try to access fields that aren't available in previous versions. The autodiscovery error usually means you either don't have permission to use the autodiscover feature or it's turned off by your IT team (I've seen this before). If your IT department hasn't shut off the web services to your Exchange server completely then you can use a manual service URL like "https://<your exchange location>/ews/exchange.asmx".

5 - Atom

Hi @rpaugh

it seems to be a great tool, but I struggle to set it up.

 

I added User Name (my address), password, selected exchange server version, added service URL (exchange address), mailbox (the same as user name), selected Inbox, start/end dates, fields and added simple query - body:state. I tried to run it with other settings, but I the bellow errors. Would it suggest any particular issue?

 

Start: Designer x64: Started running at 10/27/2020 08:27:12
Info: Designer x64: The Designer x64 reported: Allocating requested dedicated sort/join memory would be more than available physical memory. Reverting to 807.5 MB of memory.
Error: Outlook Input (1): PI_Init
Error: Outlook Input (1): c.Fields is not iterable
Error: Outlook Input (1): TypeError: c.Fields is not iterable
at Object.Alteryx.Plugin.PI_PushAllRecords (file:///C:/Users/e661818/AppData/Roaming/Alteryx/Tools/Outlook%20Input/Outlook%20InputEngine.html:134:37)
at Object.<anonymous> (file:///C:/Users/e661818/AppData/Local/Alteryx/bin/RuntimeData/HtmlAssets/Shared/1/lib/compiled/alteryx/engine/main.js:199:28)
at file:///C:/Users/e661818/AppData/Roaming/Alteryx/Tools/Outlook%20Input/Outlook%20InputEngine.html:1:16
End: Designer x64: Finished running in 2.2 seconds with 3 errors

11 - Bolide

@SOleksy It looks like it didn't like your field selection. Would you mind sharing your configuration? Or send it via PM if you don't want something shared here?

5 - Atom

Thanks for your response Rick.


That's the error I got today:
Start: Designer x64: Started running ***\Sebastian\Alteryx\outlook.yxmd at 10/29/2020 11:42:33
Info: Designer x64: The Designer x64 reported: Allocating requested dedicated sort/join memory would be more than available physical memory. Reverting to 708.0 MB of memory.
Error: Outlook Input (1): FindItems Error.
Error: Outlook Input (1): {}
End: Designer x64: Finished running outlook.yxmd in 2.1 seconds with 2 errors

I thought that it's related to the query on the bottom, if I use incorrect username or password I still get the same error.

 

SOleksy_0-1603968934239.png

 

 

Below that I chose dates, selected some fields and added such query: body:thanks


Thanks,

Seb

11 - Bolide

@SOleksy What happens if you remove the query filter? Does it work then? I don't believe you can query body directly so you'll have to focus on things like subject, date received, recipient, etc. to filter your results, then use an Alteryx filter tool to reduce the results down further. Also, some items of note:

  • Service URL only works if you select the "Use Manual Service URL" checkbox. And if you do that you're likely looking for something that ends with ".../ews/exchange.asmx".
  • Mailbox only works if you select the "Use Different Mailbox" checkbox.
  • The date fields you mentioned selecting only work for calendar queries. If you want to filter email messages by date, you'll have to do it in the query string (e.g. DateTimeReceived:yesterday).
5 - Atom

@rpaugh, thanks for your suggestions.

I think that I tried all possible settings.

When I tick Manual Service URL and Different mailbox and remove query I get this error:

 

Error: Outlook Input (3): Cannot read property 'toLowerCase' of undefined
Error: Outlook Input (3): TypeError: Cannot read property 'toLowerCase' of undefined
at new Uri (file:///C:/Users/e661818/AppData/Roaming/Alteryx/Tools/Outlook%20Input/ews-javascript-api/ExchangeWebService.js:3826:39)
at Object.Alteryx.Plugin.PI_PushAllRecords (file:///C:/Users/e661818/AppData/Roaming/Alteryx/Tools/Outlook%20Input/Outlook%20InputEngine.html:175:35)
at Object.<anonymous> (file:///C:/Users/e661818/AppData/Local/Alteryx/bin/RuntimeData/HtmlAssets/Shared/1/lib/compiled/alteryx/engine/main.js:199:28)
at file:///C:/Users/e661818/AppData/Roaming/Alteryx/Tools/Outlook%20Input/Outlook%20InputEngine.html:1:...

 

We use Exchange2010_SP3 and as it's not on the list I select SP2.

6 - Meteoroid

Hi @rpaugh 

 

This tool is working as expected for me in my local machine, but While exporting workflow to the server I am getting error as “Cannot find the plugin”, do we need to install the plugin in server.  if so, will it work as it is in gallery once it is installed in server.

 

To schedule and auto run the workflow which as outlook email tool,  what are the steps that we need to follow, do we need to install Microsoft Exchange outlook in server machine in order for it to work

 

Thanks

Avanti.

11 - Bolide

@SOleksy Are you using an on-premise Exchange server or Office365?

 

@Avanthi you will need to install the tool on the server as well.

5 - Atom

Hi @rpaugh !

 

I've just discovered your tool and I'm having some trouble setting it up.

 

I'm using the old version (not office 365) and I'm having this message after dropping the tool on the canvas :

 

OutlookInput.PNG

 

Is this a nown issue?

 

Thank you for your help.

 

HL

5 - Atom

Hi @rpaugh

 

it will be exchange on-prem server

11 - Bolide

@SOleksy That's interesting. Can you share your service URL? PM me if you like.

11 - Bolide

@HatemL Yes, that is a know issue. Here's how another user said to solve it: "I have solved the problem below by changing the language settings ("Date,time, or number format settings") on the Windows server settings page to English."

5 - Atom

Hi @rpaugh

 

I keep getting an error on the tool, was hoping you could help me decipher it.

 

"The request failed. The remote server returned an error: 401 Unauthorized." 

 

I met with our Exchange IT team and they cannot come up with a reason (firewall or permissions) why I am getting this error since I am locally able to connect to the Exchange Server from Outlook itself.

 

Please help! 🙂

 

 

5 - Atom

Hi @rpaugh !  I love this tool, but I'm having issues getting it to run on our server.  I get the Can't find plugin "OutlookTools.dll" error even when the tool is installed.

11 - Bolide

@ChristineF You're probably using your standard email address as the username instead of the domain format. So if your email is ChristineF@acme.com, but your Windows username is "cf", then your username should be something more like cf@acme.com

 

@Drstaples This commonly happens on server installations when Alteryx is installed under one user context and the Outlook tool under another. You need to match the two. So if Alteryx is installed in program files, then install the Outlook tool there as well. If Alteryx was installed under a specific user context then it will be in an AppData/Local folder, and therefore the Outlook tool should be installed there as well...or at least in a location that Alteryx has permission to read. There are some examples of this earlier in the blog article....found it:

 

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.  

5 - Atom

@rpaugh Thanks for the reply, I actually checked the email address multiple ways, basically all the possible combinations of user name with domain format. Still cannot get it to work. Is this error specific to that scenario?

11 - Bolide

@ChristineF Does your Exchange server allow Basic Authentication? I know some IT teams have disabled this.