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
WKESLewis
6 - Meteoroid

@rpaugh! Confirmed, all new updates worked and fixed previous issues I voiced.  Again, great tool and thanks for fielding the feedback.

Nitish_Salve
7 - Meteor

Hi All,

 

I have installed Alteryx designer on windows server and wants to use "OutlookInputTool".
I'm downloading workflow from Gallery on local machine and then moving it to server where Alteryx Designer is installed.
No error while executing this workflow to install tool. But after opening new Alteryx session I'm unable to locate this tool.
I looked in "Connector" pallete; even I did global search in Alteryx but unable to locate.
 
Am I missing any steps?
 
Kindly help me to resolve this.
pennymiller
7 - Meteor

Hi -

I am having the same problem as Nitish (posted above me)

Thanks! Penny

rpaugh
11 - Bolide

@pennymiller, this is the message I sent to Nitish that resolved the issue:

 

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.  

 

Please let me know if any of the above information helps you resolve the issue.

pennymiller
7 - Meteor

Hi -

Yes, that worked.

Thanks!!

pennymiller
7 - Meteor

Hi -
Ok, so I got it all installed.
That being said.
While my company uses Outlook, I just learned we don't have a Microsoft email account - I guess this won't work, then?
Thanks!

Penny Miller

mb1824
9 - Comet

Ignore this i just saw your replies to others with similar issues

JMoore
8 - Asteroid

similar struggle to several people above, the auto discover does not work for me. I try to give the Service URL "https://outlook.office.com/owa/" but that also gives me an error of 401:

 

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

JMoore
8 - Asteroid

The steps I performed were:

1. go in browser to "https://outlook.office365.com/EWS/Exchange.asmx" and login using Office365 credentials

2. Website said that "Service started" or something to that effect

3. put https://outlook.office365.com/EWS/Exchange.asmx into the Service URL and checked the "Use Manual Servie URL" this then allowed me to pull in the data.

 

Thanks Rick for the help on this!

rpaugh
11 - Bolide

Glad to hear that worked for you, @JMoore, and thanks for posting your steps for others to see!

Jay_B
5 - Atom

Hi Rpaugh

 

I was super excited when I saw this feature, however I also have the issue 'Exchange Server doesn't support the requested version'. It seems I am using an older EWS Schema version, please let me know if there is anything that can be done?

 

Kind regards

 

Jay

rpaugh
11 - Bolide

FYI - a new version of the tool is available that allows for the selection of different Exchange server versions for backward compatibility:  https://gallery.alteryx.com/#!app/Outlook-Tools-Install/597b35c2f499c716ec34a782

 

It was built against version 11.7 so if anyone is on 11.5 and can't get it to work let me know.

RSK
7 - Meteor

Hi,

Will this add-on allow me to compose send emails from Alteryx Designer using MS Outlook?

Thx

 

rpaugh
11 - Bolide

@RSK - No, this is just an input tool for reading data from Outlook.

WU1993
7 - Meteor

Hello all,

 

I think this is a fantastic idea and would love to try it out at out institution. Problem is, we use Atleryx 11.3 and don't have permissions to upgrade the Designer version.

 

Is there any way we can make or have a convertible version of this tool? This would be extremely valuable for our team.

 

Thanks,

 

ML

JMoore
8 - Asteroid

Hi Rick or Alteryx,

 

How can we make the fields such as Query String or others use global constants? When I try to put a global constant into it with %type.name% it does not pull in the value.

 

Any insight into what we need to code this would be appreciated.

rpaugh
11 - Bolide

That's a great question, @JMoore.  @jdunkerley79, do you have any input on incorporating global constants into custom tools - or know someone who could provide this info? 

rpaugh
11 - Bolide

New version of the tool is available here.  Hopefully this will solve the issue with backwards compatibility with older version of Alteryx Designer.  I don't have an older version to test so please let me know if it still does not work.

ngqhbinh
5 - Atom

Hi Richard,

 

The tool works great for accessing my inbox and sub folders. Many thanks for that.

Having said that, is there a way to access or identify other mailboxes a user has access to in order to do the same data retrieval?

 

Cheers,

 

B

rpaugh
11 - Bolide

That's a great idea, @ngqhbinh.  I'll look into that and get back to you.

rpaugh
11 - Bolide

@ngqhbinh, the tool has been updated to allow for the querying of a mailbox other than the user's primary.  It still only allows for querying of one mailbox at a time so if you want to query multiple then just use a separate input for each.  If the number of mailboxes you wish to query becomes too unruly for individual inputs then I will look into querying multiple from a single tool:

 

Alteryx - Outlook Tool Different Mailbox.png

 

The updated version is in the gallery.  Let me know if it works for you.

ngqhbinh
5 - Atom

Hi Rick (@rpaugh)

 

It works and it is awesome!!

Querying one mailbox at a time per input is perfect (for me)!

 

Thanks for the connector AND adding extra features to it :)

 

I would have added many more stars if I could.

 

Cheers,

 

Binh

 

 

Pdo
5 - Atom

Sounds like a very useful tool, however I have Alteryx 10.6 and unfortunately I am unable to install.

ngqhbinh
5 - Atom

Hi Pdo,

 

I am in the same situation as you using version 11 vs version 11.7 (Outlook tool).

There is a thread about this adjusting different version of alteryx here: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versio...

 

In this instance (for .yxzp package file), I followed the suggestion, quoted from the last bit of the post:

"What about packages? These are actually just zip files with a .yxzp extension. If you change the extension to .zip you can take a look inside.  It’s just a case of updating the files inside the zip and then saving renaming it back to yxzp."

 

Basically,

change the extension from yxzp to zip

open the zip

open the yxmd file in the zip using notepad

change the version to 10.6 (in your instance)

save it back into the zip

change back the flie extension to yxzp

 

It worked for me.

 

B

 

rpaugh
11 - Bolide

@Pdo, I believe 10.6 used a different version of the .NET platform, which is why the tool wouldn't install on a version that old.  I re-compiled it to use the 10.6 dependencies and published it as a new project in the gallery:  https://gallery.alteryx.com/#!app/Outlook%2BTools%2BInstall%2B(10.6)/5a74751cf499c72598ba0e33.  Let me know if this one works for you.

 

FYI - @harsha384

ritutyagi2104
7 - Meteor

Hi Everyone,

 

I am facing the same issue which Nitish_Salve had. I am installing this tool on windows server, my alteryx designer version is 11.5. When i try opening the tool i get an error message : "this workflow was created with a more recent version of the tool etc etc. So you still want to attempt the workflow?"

Upon clicking yes, I run the workflow. No issues in executing the workflow. But after opening new Alteryx designer I'm unable to locate this tool.

Following is the required path information:

 

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

Kindly help me in resolving this issue.

 

 

rpaugh
11 - Bolide

@ritutyagi2104, some messages back and forth with Nitish_Salve revealed that the Alteryx installation did not match the tool's default installation location.

 

Here was the response:

 

"

I followed your instruction and was able to install "OutlookInputTool".

 

By default after executing OutlookInputTool sdk it is installed in default location “C:\Program Files\Alteryx”, but my Alteryx designer is installed on “C:\Users\Administrator\AppData\Local”.

 

So I change the location to “C:\Users\Administrator\AppData\Local” instead of “C:\Program Files\Alteryx”.

"

 

Verify the installation location of the designer on your server and match the tool installation to that location.  Let me know if that resolves your issue.

ritutyagi2104
7 - Meteor

@rpaugh, My AlteryxGui.exe is placed in "C:\Program Files\Alteryx\bin" & outlook tool is installed in "C:\Program Files\Alteryx".

So this means outlook tool has been installed in right location? Any other thoughts to this?


rpaugh
11 - Bolide

Hm...that looks correct, @ritutyagi2104.  Is the issue that it's not showing up in the tool ribbon in Alteryx?  Can you locate it using the tool search feature?  I ran into this issue with the 10.6 version I just published, so it may just be that it's not placing in the tool list.  I'm looking into why this would happen.

ritutyagi2104
7 - Meteor

I can't locate it using the tool search feature as well.

ritutyagi2104
7 - Meteor

Do you think it has got something to do with version 11.5?

Pdo
5 - Atom
Your are too awesome thanks a million. I will try to install now.
Pdo
5 - Atom
Thanks for the suggestion.
rpaugh
11 - Bolide

@ritutyagi2104, shouldn’t. At least one other user is using it on 11.5. Plus I used it successfully on 11.5 before upgraded to 11.7.

rpaugh
11 - Bolide

@Pdo, If you can't get the workflow installer to work, try using the direct executable here.

ritutyagi2104
7 - Meteor

Hi,

 

You are right that version is not an issue, I installed 11.5 version on my local machine & ran the workflow for outlookInputTool installation & I am able to locate it in my alteryx designer successfully. However on server it doesn't work.

I would like to inform you that the username with which I login to server & username with which "Microsoft outlook" is configured on my server is different. Do you think this could be an issue ? Is this why I am unable to locate outlook tool in alteryx designer?

 

Regards,

Ritu

 

rpaugh
11 - Bolide

If you logged into the server as yourself when you installed Alteryx then it's possible that it installed under your username (e.g. "C:\Users\<username>\AppData\Local"), whereas the Outlook tool installation defaults to "C:\Program Files\Alteryx".  Look in your AppData/Local folder to see if there is an Alteryx folder that matches what is in Program Files\Alteryx.  If so, then you may have to install the tool to this location instead.

Pdo
5 - Atom

@rpaugh  

 

Great work, thanks so much I was able to install.

jlamb
5 - Atom

Thanks, I will try this. One more question, my email has 2 attachments one is .txt and the other is .csv. I only want to pull the .csv file.
Can I do that using the Query String, if so how?
Thanks!



jlamb
5 - Atom

Is it possible to save the email attachment with a different name?

rpaugh
11 - Bolide

@jlamb, no it is not possible to save the attachments with a different name - aside from electing to use unique naming for multiple attachments.  Since there's not necessarily a guarantee on which files you're going to get back from your query (unless you are precise with your query string, I suppose) you run the risk of renaming files you don't want renamed.  For example, if you pulled in multiple Excel files and used a filter after-the-fact to send them to two different data flows based on file name, this would break. Are you not able to pull the data you want from the attachments in your workflow, then output to a new file with the name you want after processing the data?  Or are you trying to do something different?  If you send me some details around what you're trying to accomplish I will see if there's a good way to incorporate that feature into the tool, or possibly suggest alternative solutions to meet your needs.  

jlamb
5 - Atom

Thanks for the quick response!  It would be great to pull the data from the attachment.  I am new to Alteryx and I am not sure how to do this. 

All I need to do is pull the data from the attachment, make some simple formatting changes and then save the file to a specific location.  I can make the changes and save the file, but I not sure how to pull data from the attachment.

Thanks for your help!

rpaugh
11 - Bolide

@jlamb, to pull out attachment data, do the following:

 

  1. Set the destination folder for the attachments you wish to pull out of your email:
    Alteryx Outlook Walkthrough - Step 1.png

  2. Filter for the files you want and append the sheet name from which you wish to extract data (using the Attachment stream from the input tool):
    Alteryx Outlook Walkthrough - Step 2.png
  3. Use a Dynamic Input tool with a template file matching the structure of the attachment from which you wish to query data:
    Alteryx Outlook Walkthrough - Step 3.png

  4. Voila:
    Alteryx Outlook Walkthrough - Step 4.png
rpaugh
11 - Bolide

@jlamb, not directly that I know of, but I can look into it.  Meanwhile you can always filter out attachment types using a standard Alteryx filter after you've pulled out the attachments.  I know that doesn't help the issue with storing files you don't need, but it at least gets you data.

rpaugh
11 - Bolide

@jlamb, if the csv and txt files are named differently then you can filter on attachment name like so:

  • attachment:<name>
jlamb
5 - Atom

3-2-2018 10-58-47 AM.png

 

 

Would you know why I am getting this error message?

My attachment file and the sheet that I want to pull the data from have the same names.

I don't know if that would be a problem or not.

Thank you!

rpaugh
11 - Bolide

@jlamb, you don't need the "|<sheet name>" portion of the attachment path for csv files - that's only for Excel files to define the selected sheet for a subsequent Dynamic input tool.  You should be able to simply send your raw attachment path field directly to a Dynamic Input tool after your ".csv" filter.  Now, if your email contains multiple files and "data01d1importopordrpt25287" is the name of your file then you can simple change your filter tool to the following:

  • Contains([AttachmentPath], "data01d1importopordrpt25287.csv")

Then you can send the [AttachmentPath] field directly to a Dynamic Input and browse the results.

RodLight
8 - Asteroid

FYI...just tried to get to the Help on this tool and got the response...

 

AccessDeniedAccess Denied8F2540B9CCEEC38FnTqSgk1omtP/RKIRpvTGhR1HvzKW/1KUWKwROph+ceAnnJtEHUoOHBkty7UzFGU6JJkJEKYyByM=

 

...in the Help page.

rpaugh
11 - Bolide

@RodLight, unfortunately the help button is tied to the Alteryx designer and is not accessible from the SDK (at least not yet, or that I know of yet), so I can't set it to the proper location.  However, you can access the help page either from the installation workflow from the public gallery or directly at https://sites.google.com/view/paughtech/alteryx-tools/alteryx-outlook-input.

DmtCoj
7 - Meteor

Hi @rpaugh , awesome staff, really helps to automate the process.

Just a quick question, is there are any option to extract data starting from a certain date instead of extracting all the available data for a specific folder and afterwards applying filter tool.  Thanks in advance.