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.

[rpaugh]

@laura2 good news, I was able to add attachment count to the message output by adding a "Include Attachment Counts" field to the tool's configuration.  Note: you can check this box and get the count of attachments without having to select the "Attachments" field and outputting the actual attachments if you don't want to.

Here's a direct link to this version.  If you (or anyone else) wouldn't mind load testing it with larger mailboxes and letting me know if it performs well, I'll update the version on the public gallery page.

[automate]

Hi back again @rpaugh

I'm currently trying to schedule a workflow I have that includes this tool and I keep getting this error.

Has anyone encountered the same problem? And if so, is there a fix to it? I really appreciate the tool and everyone's help so far.

[automate]

I have a possible solution but I'm not sure how to try it out, @rpaugh @automate

I have a network drive at work but when I installed the .yxi file, I had installed it from my local drive. I tried to install the .yxi again (a copy that's located on the network drive) over the old install but it still gives the same error.

Is there a way to clean install the tool and remove the previously installed tool? Thank you very much!

[automate]

Okay so I found out where it's installed but even after deleting the folder and adding it back, it still gives the same error.

If anyone wants to know where to uninstall .yxi files they're located here (found thanks to @patrick_digan:

C:\Users\<<YOUR ID>>\AppData\Roaming\Alteryx\Tools\<<TOOL NAME>>

[rpaugh]

@automate if you're installing this on the server for scheduling then you've probably got an issue with the install directories between Alteryx and the tool.  When you log into the server, and install the Outlook tool with permission for all users to use, does that work?

[automate]

@rpaugh I'm not installing it on a server, I'm only installing it on my local machine. Do I need to install the plugin on my work alteryx server for it to work? We usually don't have to do that but we also usually only use files saved as macros.

[rpaugh]

@automate from your previous screenshot it looks like you're trying to schedule the workflow from your gallery - which I assume is on a server somewhere.  Is that correct?  If so, then you'll have to install the tool on the server.

[automate]

@rpaugh Okay, I understand now! I'm getting with my server people soon to see if they can install it since I'm not an admin. I'll update this thread with what the solution comes out to be, thank you 🙂

[Darrie8468]

How can we get this tool certified by Alteryx?  My company won't put it on our server till that happens.

[rpaugh]

@NeilR can you speak to getting tools certified by Alteryx?

[Thanveer2020]

Hi,

I am new to Alteryx and I have a similar requirement to extract attachments from Outlook.

 I followed the below process but still not able to find the Outlook Input tool in the "Connect" window.

1. Opened "Outlook Tools Install" work flow in Alteryx designer.

2.Ran the Setup.exe file from _external folder.

3.Process completed and restarted the alteryx designer. but not able to find 

the Outlook Input tool in the "Connect" window.

Please help. 

error

[rpaugh]

@Thanveer2020 most likely the tool was not installed in a location Alteryx can see.  For example, if Alteryx is installed for all users on your machine in Program Files and the tool was installed for just your user account (../user/appdata/roaming/Alteryx) or vice versa then the tool will not show up in the ribbon.  Double-check your installation locations and reinstall the tool accordingly.  Just curious, are you using Office365?  If so, I recommend this version of the tool instead as it uses newer technology and Alteryx's built-in installer which prevents this installation issue.

[tb12]

Great tool! +1 for getting this certified with Alteryx.

[NickJ]

@NeilR can you speak to getting tools certified by Alteryx?

Hey there,

Certification is part of the current discussions around the future state of connectors (and other assets) across the wider Alteryx platform. 

Part of the process of certification requires agreement around the following aspects:

Testing (including the development of a testing framework that can be applied to both internal components and partner/community-built components);

Maintenance (including ongoing maintenance where appropriate to ensure that the connector/tool retains its functional and non-functional capabilities). 

Compliance with underlying Software Developer Kit frameworks - as Alteryx continues to evolve the technical backbone of how users create new content, there needs to be assurances that content can follow the latest libraries/modules/etc. in order to ensure smooth interoperability with other features. As SDKs change, so too will the certifiable component have to change in order to stay compliant (think of how iOS apps required a 64-bit upgrade a few years back in iOS 11). 

Certification, of course, brings enormous benefits to 3rd party components including trust, reliability and issue resolution. In order to deliver a certification process that meets these objectives, there is work to do, but the end result means that it's easier to distribute great new connectors or functionality to more of our customers.  

As work continues, we'll be able to report back via this blog on when a certification program can be made available, providing that necessary progress can be made. 

Many thanks,

Nick

[mschaff]

Have been using Outlook Tool for 12+ months with existing configurations, and now tool is getting AutoDiscover errors.

"Autodiscover service couldn't be located"

I can confirm Exchange AutoDiscover service seems to be working, but not with the tool. 

Does anyone have ideas to resolve?

[Thanveer2020]

Thanks @rpaugh. Here we dont have Office 365. Let me try reinstalling it.

[rpaugh]

@mschaff something likely changed with your Exchange Server configuration/security.  You could try using a manual service URL.  Otherwise you may have to contact your IT department to see if they've changed something.

[mschaff]

Hi rpaugh...

As far as our Exchange settings/configurations, our Email and Message Team has suggested nothing known has changed.

To help further, they are asking me about what information is required from exchange by the Tool and how the Tool connects to exchange, (what kind of protocol it uses) 

Can you help me respond back to my Email and Message Team with this intelligence?

[rpaugh]

@mschaff The tool uses the EWS Managed API, which wraps numerous underlying settings.  It uses the AutoDiscover URL to dynamically determine where to connect to your Exchange Server, manages the connection, then provides methods for working with the data.  For more information you can have your IT/Security team review the EWS Architecture.  Unfortunately, I'm not an Exchange expert and rely on the APIs and documentation provided by Microsoft to facilitate communication using the API.  

A few follow-up questions:

• Are you sure there were no software upgrades, security patches, network changes, etc.?
• I noticed you're connecting to a different mailbox.  Is there any chance that mailbox is on a different server than your mailbox?  
• Have you tried using a manual service URL?
• How did you verify that the AutoDiscover service was working, and were you able to verify that using the other mailbox as well?

EDIT: Here are some notes regarding usage of the Autodiscover URL from the API documentation:

If an Autodiscover server returns a redirect HTTP status code, this method will generate an AutodiscoverLocalException with the Message property set to a string such as "Autodiscover blocked a potentially insecure redirection to https://autodiscover.contoso.com/autodiscover/autodiscover.xml. To allow Autodiscover to follow the redirection, use the AutodiscoverUrl(string, AutodiscoverRedirectionUrlValidationCallback) overload." This exception is generated for all redirect responses, regardless of the validity of the URL returned in the redirect response.

To enable redirection, use the AutodiscoverUrl(String, AutodiscoverRedirectionUrlValidationCallback) overload instead.  This method allows Autodiscover to follow redirects when an Autodiscover server responds with a 302 Redirect status.

So it's possible that Autodiscover is redirecting too much or not redirecting to a valid URL.  The first thing I would try is using a manual service URL (https://<your Exchange address>/ews/exchange.asmx).  Typically when Autodiscover presents a problem, the quickest, easiest fix is to jump straight to the URL you're after.

[mschaff]

Hi rpaugh

One more question from my Enterprise Email&Msg Team...

Which version of TLS is the Tool using?  

[mschaff]

When i configure the tool manually, i get this error

"Could not create SSL/TLS secure channel"

[StewartK]

@rpaugh 

I have installed the OnPrem Outlook Input Tool.  I am using a manual service URL.  But I get the following error when I try to run the workflow:

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)

Any help would be appreciated.

Thanks

[rpaugh]

@StewartK the most common issue here is that you need to change your user name to the domain format of your email address rather than your public email address.  For example, "jdoe@apac.acme.com" instead of "john.doe@acme.com". If that still fails then you may need to check with your IT team to see if they've implemented any kind of security measures that prevent logging in to your Exchange Web Services.

[rpaugh]

@mschaff I think it's using 1.0.  I'm looking into how to update the tool to force  TLS 1.2.  I'll let you know if/when I get it fixed.

[mschaff]

@rpaugh 

Thank You for looking into TLS 1.2!

My Email & Messaging IT Team is telling me that Microsoft is no longer supporting 1.0, and that is the likely issue with AutoDiscover and/or TLS error with the Tool when configuring manual connection.

[rpaugh]

@mschaff try this. Make sure .NET 4.5 is installed on the server as well.

[StewartK]

@rpaugh 

Thank you for your help.  Can you tell me how I can determine what this domain format address would be?

[StewartK]

@rpaugh 

Never mind.  Figured it out.  It's working now.

[rjsebold]

@rpaugh 

Thank you so much for developing this tool. It has been a huge asset in automating our workflows.

Like a previous comment, I've been trying to access a shared inbox that I have access to. When I check Use Different Mailbox and enter the associated email address, the tool says I lack permission when I try to access the Inbox. However, if I specify a sub-folder for the same account using the unique folder Id, I'm able to import the data successfully.

I was wondering if you've come across a fix for this problem yet? I'm using the 365 tool on the latest edition of Alteryx.

Thanks!

[JMoore]

Hi Rpaugh, is the latest version on github or only on the google drive? the beta was working well from the google drive for the last 4 months here but the github version lacks the oauth login button etc.

[isubho]

Hi @rpaugh 

In our organization we O365 exchange., by default Legacy Authentication is disabled for everyone. There are exception for few users.

The user with an exception can only login.with the outlook tool.

If possible just wanted to understand how the is tool configured internally to authenticate with exchange servers.

Thanks

Subhojit

[rpaugh]

@rjsebold do you have permission to the whole inbox or was just the sub-folder shared with you?

@JMoore Github has the outdated version, which does not support OAuth - you would have to use the O365 version for that. And since Alteryx is looking to discontinue their C# SDK, and Microsoft their Exchange Web Services, both upon which the tool is based, I have no plans to continue developing on this version.  I have not put the O365 version in a public GitHub yet.

@isubho Are you saying the user with the exception can only login with the older version of the tool and everyone else is using the O365 version?

I apologize for the delayed response, my email decided to start sending Alteryx Community notifications to spam so I missed these updates.

[isubho]

@rpaugh  Actually everyone uses O365 in our organization and by default for everyone it is restricted to use legacy Authentication. 

https://gallery.alteryx.com/#!app/Outlook-Tools-Install/597b35c2f499c716ec34a782

User1 - Have permission to bypass and use legacy authentication.

User2 - Doesn't have the permission to use legacy Authentication.

User1 can use the tool to connect to exchange but User2 is not able to do so.

Thus it seems you use legacy authentication is used to connect with the exchange in the background of the tool, just wanted to confirm that.

Thanks

Subhojit

[rpaugh]

@isubho Yes, that version of the tool uses legacy (basic) authentication. This may not be ideal, but for those who cannot use legacy authentication you could always have them use the O365 version of the tool. Different users would be using different versions of the tool (which do work a bit differently) but at least then they could all take advantage of connecting to Outlook data.

[Ty25]

Thank you @NickJ  for the Alteryx certification response and @rpaugh for creating this tool.
We could really use this tool in our multinational organisation right now but IT have blocked non-certified tools from installation.

[rpaugh]

@Ty25 @Darrie8468 how do your organizations determine whether a tool is certified by Alteryx if there is no current certification program? Do they just only allow installation of tools with Alteryx listed as the developer?

[BrianSz]

 Hi @rpaugh - I am getting an autodiscover error when trying to use the outlook connector tool. Do you have any recommendations? 

@GeneRinas 

[Ty25]

@rpaugh The great news is that the Tool is installed on my organisational laptop now.
I have some early troubleshooting I am looking for help with. I have a 'corporate domain' email address similar to "alteryx.com" but I am unable to find the technical name akin to "alteryx0.onmicrosoft.com".

[rpaugh]

@BrianSz The text in the screenshot is too small for me to read. Would you mind posting the error text? My guess is you either don't have permission to access your Exchange server's autodiscover address, or it's not enabled. You may have to reach out to your IT department to find out which scenario it is. Or select "Use Manual Service URL" and enter the URL to your Exchange services.

@Ty25 You may have to reach out to your IT department to get the domain URL format for your company. Sometimes they're as simple as using your username in your email, like changing "john.doe@acme.com" to "jdoe@acme.com". I've also seen larger companies use regional domains, such as "jdoe@nala.acme.com" or "jdoe@apac.acme.com".

[Darrie8468]

A friend of mine is having trouble getting her email. 

She is...

Going to Outlook online and getting the folder she wants to gather

Going back to the tool and connecting to Office 365

Entering the Folder ID

Selecting all fields

When she launches the tool she gets a 400: Bad Request error.

She does NOT get the error when she removes the folder ID and gathers just from her Inbox.

Any thoughts?

[rpaugh]

@Darrie8468 have her check the Folder Id for bad characters and replace with I believe it's a URL encoded version. I've actually seen this before where something like a slash was in the ID which the API request wasn't correctly converting.

[vaishalilambe17]

where to find this tool? and which Alteryx version?

[Ty25]

Hi @rpaugh and Community

Suppose an IT department does not know what to do when requested to ‘enable auto-discover’, is there another fool proof way to lay out this request to achieve the desired result - being the use of this tool?

How are other users with company outlook email addresses getting their IT department to allow for this tool to work?

Currently, when I enter my email address (confirmed as the principal address) and password using exchange server 2013_sp1 (we use office365), set ‘Inbox’ as my folder to search, select ‘attachments’ and save to ‘my target network folder’, then click RUN, the error I get is:

‘The Auto-discover service could not be located...’. 

When I keep the above inputs constant but tick ‘Manual service URL’ and enter https://outlook.office365.com/owa/, the error I get is:

‘The request failed. The remote server returned an error: (401) Unauthorised..’ 

[rpaugh]

@vaishalilambe17 https://gallery.alteryx.com/#!app/Outlook-Input--Office365--beta/5dc2d7c3826fd30ac477981d. And it should work in all 2018+ versions I believe.

@Ty25 you need the /ews/exchange.asmx address, not the /owa address. Then it should work.

[Ty25]

@rpaugh Thank you. Unfortunately, when I use https://outlook.office365.com/EWS/Exchange.asmx as the Manual Service URL, I get error: "The request failed. The remote server returned an error:(401) Unauthorized.ServiceRequestException at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.....

[rpaugh]

@Ty25 were you able to obtain the correct domain formatted username from your IT department? It looks like the manual service URL worked given that you are now getting a login error, which means you probably just need to make sure you have the correct username and password that your Office365 instance wants.

[Billigans]

Can I use this access a contacts list in Outlook? I am in desperate need. Thanks!

[rpaugh]

@Billigans What exactly is your use case? Are you just wanting to list your contacts, or something else? To answer your question directly, you can access Contacts from the old version of the tool, but not yet from the new version (Office365) - however, I don't think it would be difficult for me to add that.

[Billigans]

@rpaugh That's exactly it. Just a list of contacts associated with the Outlook address. We need this for conflict checks for bankruptcy proceedings and our executives are tired of having to pull this manually. 

[rpaugh]

@Billigans ok, let me see if it's something I can roll out quickly.