Outlook Input tool Created with the Alteryx SDK

[rpaugh]

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

But 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:

and a help file

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.

[RMojsiak]

Thank you so much @rpaugh

It worked!

[rpaugh]

@chrisrayner ToRecipients in the new tool is a JSON field containing both Name and Email address. If you use the standard Alteryx JSON Parse tool you should be able to get the same information. Be aware however that if there are multiple "To" addresses then this will result in additional rows after the JSON Parse. Let me know if that works for you. As for the ConversationTopic, I'll have to look into that.

Update: it looks like Microsoft has moved ConversationTopic to its own object in their new API. The relationship to this object is via the ConversationId field available in the email message. I will have to research this to determine the best, most efficient way to incorporate this new conversation object information into the email result.

[rpaugh]

@chrisrayner I think I may have figured something out. It's not super slick, but you can pull in the InternetMessageHeaders:

That will pull in a bunch of data as a JSON object. From there you can parse, cross tab, and pull out the message thread topic:

Let me know if that works for you.

[chrisrayner]

@rpaugh great - thank you.  I have now got that part of my workflow back up and running.

However, I have now found another issue.  Is there any way to get 'Task' information out of O365 with this tool?

The old one had the option to extract task data, but I have not been able to find the equivalent on the O365 tool?

Many thanks

Chris

[rpaugh]

@chrisrayner Microsoft has been in transition with tasks. The API they had is now being deprecated next year, and they have a new ToDo API. Let me see if I can update the tool to bring that in. I don't have a timeline for completion yet, but I'll incorporate it as soon as I can and let you know when it's complete.

[chrisrayner]

@rpaugh great.  Thanks very much.

[mfresques]

Hi All,

I've updated my outlook input tool to the Office365 version and it works great from my desktop; however, when I upload the tool to our Alteryx Server I get the below error message...

The job Markit_Prices_Outlook_Input_Tool did not complete successfully and resulted in the following errors:

• Error: Unable to resolve plugin HTML 'Outlook Input (Office365)\Outlook Input (Office365)Engine.html' (Tool Id: 4)

I have installed the new Outlook Input (Office365) to our server machine.  Not sure what this error means.

Can anyone maybe shed some light on what might be causing this error?

Thanks in advance,

Marcus

[rpaugh]

@mfresques Can you verify if the tool was installed to a location on the server that Alteryx can read, and for all users? Are you able to log into the server, open Alteryx, and see the tool in the tool palette?

[mfresques]

@rpaugh So it turned out the tool was not running for all users. 

In order to make this file run correctly we had to make Alteryx lunch and run as an administrator.

We first had to update the AlteryxGui properties to Run this program as an administrator.

Then we were able to install for all users.

Thanks for the Help @rpaugh .  You defeinitely set us on the right path.

Cheers,

Marcus

[rpaugh]

@chrisrayner Are you using planner tasks or Microsoft ToDo tasks?

[chrisrayner]

@rpaugh Honestly, I am not sure what planner tasks are?  Whenever I am looking at my tasks, I look at them through the To Do listing using the icon below

We use a shared mailbox to create the tasks, and then assign them to ourselves, so I view them from the top circle when I am actually actioning my own tasks, but the tool extracts them from the shared mailbox which is the lower circled option below:

Hopefully this helps?

Chris

[chrisrayner]

@rpaugh is there anywhere else you have stored the O365 version?

It seems my company block google drive, so we are not able to download it to install it on our servers.

As an aside, have you had a chance to look at the tasks at all?

Many thanks

Chris

[rpaugh]

@chrisrayner No, only in Google Drive. But if you have another sharing option I'm happy to drop it somewhere else. And yes, I've started working on tasks and think I've just figured out how to get the lists in the tool's config in an efficient way. Now I need to clean up the tool's UI and build the actual task retrieval process in the backend engine. I'll keep you posted :).

[rpaugh]

@JessieC @rjsebold I received the following information from Alteryx support:

"We have good news. We were able to fix the product defect you encountered in case#00496953: Tool Auth Pages Not Loading submitted to Alteryx Customer Support. For your records, this is the Defect number: TCPE-12.

For this fix, you will need to install the latest version of the connector from our Public Gallery AND the latest version of the Designer (2021.3.3.63061)"

[chrisrayner]

@rpaugh Fantastic news.  Thank you very much.  🙂

Would you be able to share it via the public gallery?

Chris

[rpaugh]

@chrisrayner I can share via the public gallery, but only as a .yxi download link....which still relies on Google Drive. Unfortunately, the public gallery doesn't allow direct file downloads, except for workflows themselves. I have the tasks in a working state and ready for testing at https://gallery.alteryx.com/#!app/Outlook-Input--Office365--beta/5dc2d7c3826fd30ac477981d/run. If you still can't download the yxi file let me know if you have any other sharing options. Worst case I'll just email it to you :).

[NeilR]

I can share via the public gallery, but only as a .yxi download link....which still relies on Google Drive.

---

Heads up that Gallery is moving to Community next month (see here for details). Once that happens, you'll be able to upload YXIs without having to rely on Google Drive.

[chrisrayner]

@rpaugh I have downloaded the tool and I now have the option to select Tasks in the 'What to Query' field.  However, I am getting 400: Bad Request and 401: Unauthorised when querying mail, and a 400: Bad Request error when querying Tasks.  Any ideas what I am doing wrong with it to get these errors?

Also, in the pre-365 version there were a couple of extra fields that I used - it is not anything particularly major, but wondered if they could be added in a future iteration?  The additional fields were Categories and StartDate and then there were also two flags - HasAttachements & IsComplete.  I don't think any of this is material to my workflow, but would be handy for the reporting I have off the back of the workflow.

As ever, thanks so much for producing this tool - it is amazing.

Chris

[Fiona99]

Hi, The tool was working great for me for almost a year. Recently I'm getting the following error on the DT & server: 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)

It works fine for another Colleague who also has the ability to use this link in a browser- that I do not. https://outlook.office365.com/EWS/Exchange.asmx

Would anyone have an idea what has changed in my settings?

Thanks 

[lourdes_urena]

Hello @rpaugh , 

When attempting to install the tool and put two browse tools I'm getting the following error:

Outlook Input (Office365) (1) Failed to receive initialization from JavaScript plugin, timeout "20000ms"

I was able to download the package and get this workflow:

Got two files installed on the computer and the "ReportInput.yxdb" dependency appeared in red, but didn't say anything to troubleshoot.  Any help you can provide relating to any missing steps?

OS: Windows 10

Alteryx Version: 2021.3.2

Office 365

[rpaugh]

@chrisrayner Are you testing this against an existing workflow or a new one? If you're trying an existing one, create a new one with a brand new tool on the palette and let me know if you still get the error. Also, can you share your configuration? Maybe there's bad value in there somewhere.

@Fiona99 If you are unable to access https://outlook.office365.com/EWS/Exchange.asmx directly then the issue is with your server/security. You will need to contact your IT team for assistance as this is completely independent of the tool itself.

@lourdes_urena You should not need to download the workflow from the gallery. Run the workflow directly in the public gallery and it will take you to a report page with the link to the Outlook Input tool yxi file. Clicking that link will download that file, and running the file from your computer will install it in Alteryx.

[chrisrayner]

@rpaugh Previously, I had only tried it on an existing workflow.  I have just created a new workflow and pulled the tool onto the palette twice.

I have then configured one with the 'Use Different Mailbox' checkbox (this is the one that I need), and one with this left unchecked.

Both versions of the tool give the 400: Bad Request error message.  What configuration details would be useful here, and how best can I share them?

Many thanks

Chris

[rpaugh]

@chrisrayner I assume you're trying to configure this for Tasks? Does it still work for mail? Does it work with your own inbox instead of another mailbox? Feel free to direct message me and we can troubleshoot further there. For configuration, send me screenshots of the tools configuration window so I can see all of the settings being applied.

[jasonkan]

Would be great if this can be built into Designer

[chrisrayner]

@rpaugh - I have messaged you directly with some further information.  I get the same error with my own inbox as when trying to connect to another mailbox.

Many thanks

Chris

[rpaugh]

@jasonkan That's the dream 😁

[chrisrayner]

Hi Rick,

Is this now available through the public gallery?  Or has the expected change that would allow that been delayed?

Many thanks

Chris

[rpaugh]

@chrisrayner It's available at https://community.alteryx.com/t5/Public-Community-Gallery/Outlook-Input-Tool/ta-p/883455

[chrisrayner]

Thanks very much Rick!!!!!  Thats awesome.

Chris

[AriesV]

when i try to establish oauth connection, its stuck on empty winodws...am i missing some settings? 

[spingili]

Can I please get help with this error?

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Error-Outlook-Input-tool-PI-Init/m-p/8...

[NAnnaldasula]

@rpaugh The tool stopped working on our Alteryx production servers. We are getting the following errors.

Uninstalled outlook input tool --> Installed server updates on P155 and P161 --> Bounced the server --> Reinstalled outlook input tool.
Tested the workflow --> ERROR

Consistent errors on the Alteryx server

Error: OutlookInputTool (1): The request failed. The underlying connection was closed: An unexpected error occurred on a send.
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.FindFolders(FolderId parentFolderId, SearchFilter searchFilter, FolderView view)
   at OutlookTools.OutlookEmail.GetItems(Int64 recordLimit)
   at OutlookTools.OutlookInputToolEngine.PI_PushAllRecords(Int64 nRecordLimit)
   at SRC.Alteryx.GenericNetPluginInterface.PI_PushAllRecords(GenericNetPluginInterface* , Int64 nRecordLimit)

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.InternalGetLegacyUserSettings[TSettings](String emailAddress, List`1 redirectionEmailAddresses, Int32& currentHop)
   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)

[rpaugh]

@AriesV That is a known issue with Alteryx Designer version. Upgrade or downgrade your Designer version and it should fix that.

@spingili Do you have any more information to offer? What configuration settings have you entered? If you've not configured anything yet then you'll get that error the first time.

@NAnnaldasula It looks like you can't access your Exchange server's autodiscover service. Check with your IT team to make sure they didn't disable it, change security, or change any other settings.

[mfresques]

@AriesV 

It appears that with the last two updates (4.2021 and 3.2021) Alteryx broke @rpaugh 's outlook input (office 365) tool (either by bug or design) forcing users toward adopting their own tool (url below).  

Alteryx's Outlook 365 Tool...

https://community.alteryx.com/t5/Public-Community-Gallery/Outlook-365-Tool/ta-p/898140

Alteryx's new Outlook 365 tool requires a Client ID and Tenant ID to avoid having to sign in via the internet browser each day.  Your IT department should be able to get this for you.  Also, I've noticed that there are authentication problems with using this method to access outlook when running this tool from the Gallary.  When uploading Alteryx's version of the outlook 356 tool to our server, the client id and tenent ID get encrypted on our server's designer so it can no longer authenticate.  I've also found problems successfully running Alteryx's outlook 365 tool when I use it with the crew runner macro or workflow event commands.  I had none of these problem with @rpaugh 's Outlook input 365 tool so I hope that Alteryx fixes the bug or enhances their replacement tool to have all the same functionality.

[rpaugh]

@mfresques I feel I should point out that the tool you're referencing is not my tool, so any discussions/support should be addressed to Alteryx for that. @AriesV fyi.

[oliviamarz]

@rpaugh thank you for creating this tool! I've been using it for some months and it has been a real lifesaver.

Hoping you might be able to help me out with a new issue I'm having. Recently, I'm seeing the error pasted below thrown when I run my workflow. The workflow has been running for months just fine, and oddly enough still works - SOMETIMES. I can run the workflow successfully 5 times in a row, and then the 6th run will result in this error. Sometimes the reverse is true, and it errors out more frequently than it runs successfully. Any idea what might be going on here, taking into consideration that nothing within the configuration of the tool or workflow has changed from one run to the next? Thanks!

Error: OutlookInputTool (1): The request failed. The underlying connection was closed: An unexpected error occurred on a send.
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.FindFolders(FolderId parentFolderId, SearchFilter searchFilter, FolderView view)
at OutlookTools.OutlookEmail.GetItems(Int64 recordLimit)
at OutlookTools.OutlookInputToolEngine.PI_PushAllRecords(Int64 nRecordLimit)
at SRC.Alteryx.GenericNetPluginInterface.PI_PushAllRecords(GenericNetPluginInterface* , Int64 nRecordLimit)

[rpaugh]

@oliviamarz There's nothing within the tool itself that I know of that would cause that error. It sounds like it could be either an IT environmental error (e.g. desktop, server, or network) or possibly a setting within the Exchange server itself. I know over the last few years many companies have been adjusting their Exchange server settings to increase security. Maybe they changed something with regard to connection timeout, number of connections, frequency of connections, etc.?

[NatashaSuello]

Hi, I'm having an issue with the outlook input (Office 365) tool authentication Login. 

I'm using my company email to access the mailbox but I get a blank screen after that. See the screenshot below. 

My goal is to read and get a specific email with attachment.  

I'm using the latest version of Alteryx designer x64. Version: 2022.1.1.30961. 

Do I need to downgrade the version? If yes, which version is it compatible with?

Appreciate your help.