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
8 - Asteroid

@rpaugh Thank you! It's people like you who make this community so great.

11 - Bolide

@Billigans I appreciate the kind words. A new version with the ability to query contacts has been updated to the gallery. Let me know if you have any issues with it.

Alteryx Partner

This is such a useful tool. I just connected it with my office365 Outlook and it works like breeze 🙂

 

Thank you @rpaugh 

11 - Bolide

@RameshN Glad to hear it!

8 - Asteroid

@rpaugh wrote:

@Yanoflies whether the old tools continue to work depend on:

  • When Alteryx sunsets the SDK (which they've extended out).
  • When you upgrade Alteryx.  Even if Alteryx sunsets the applicable SDK but you are still using an older version of Alteryx then it should still work.
  • Whether you use an on-premise Exchange server.  If you do, then the tool will work as long as you have it - and have a version of Alteryx that still works with the older SDK.
  • When Microsoft sunsets their Exchange Web Services, upon which the tool relies.  Again, this really applies to cloud (Office365) only.  

In short, you should be good for a little while.  Microsoft announced they will be decommissioning Basic Authentication over the next two years, which is the authentication method the old tool uses.

 

I'll have to look into the mailbox issue.


I left this project on pause for a while and now I am back!

I downloaded the latest fresh copy from the gallery but it looks like I sadly still can't use your new version as I still can't access shared mailboxes -- I get a 403: Forbidden if I try to access a Shared Mailbox that I have access to.

There is also the issue of not being able to work with folder names and traverse down sub-folder structures.

I'll give it another go with the older version that I had forked to map Sent Items to the correct EmailMessageSchema, will report back how I go.


 

11 - Bolide

@Yanoflies well, this is embarrassing. Somehow, I had excluded the "Mail.Read.Shared" permission from the app. I've updated this and a new version is available on the gallery. Let me know if that allows you to read your shared mailboxes.

8 - Asteroid

@jrpaul that did it, it can now access shared mailboxes!

 

I am playing around with the OData query string to see if I can get it to work for me.

Thank you.

11 - Bolide

@Yanoflies that's great news! 

8 - Asteroid

@rpaugh 

 

I need some help with the query strings:

 

I understand the logical syntax e.g. '>=' is 'ge' and '=' is 'eq' but how do I know what fields/terms I can use?

 

For example, how would I build a string that will return emails received within the last X days? Or emails that were sent within the last X days? Which also brings me to, how do I know if an email is a received email or sent email? I used to look at the Inbox versus Sent Items and also at there being a DateTimeReceived versus DateTimeSent.

 

EDIT:

 

I tried

(ReceivedDateTime ge 2020-07-19) and (ReceivedDateTime eq CreatedDateTime)

 
which I thought would be how I'd filter emails received since the 19th (I'd build that date in Alteryx based on today -3 days later on if I want the last 3 days) since I assume emails created at the same time as received means they are emails received.

But that didn't work haha.

'ReceivedDateTime ge 2020-07-19' works though. 'ReceivedDateTime eq 2020-07-19' does not.

 

EDIT 2:

OMG! I got something working:

SentDateTime ge 2020-01-01 and SentDateTime lt 2020-02-01 and from/emailAddress/address eq 'test@test.com'

but it doesn't seem to match received:2020-01-01..2020-02-01 or the sent equivalent when searched in Outlook.

11 - Bolide

@Yanoflies "ReceivedDateTime eq 2020-07-19" looks for exact date/time match, which will never return true since no emails were probably received on that exact date with no time. Unfortunately, Microsoft's new API doesn't include functions for filtering for relative dates such as "today", "yesterday", and "last x days". If you throw in times, you could do something like "ReceivedDateTime ge 2020-07-19 and ReceivedDateTime lt 2020-07-20" to get emails received on that particular date. Another option is to wrap the input tool in a macro and use Alteryx formulas to calculate "last x days" and pass the resulting date calculation as a parameter.

8 - Asteroid

@rpaugh 

 

Thanks for that, it's exactly what I came to conclude.

My issue now is that I cannot separate the received and sent emails.

I am able to use 'SentItems' as the folder name (because it's a known folder name?) but not 'Inbox' -- which doesn't help because I'd need to go through all the sub-folders of 'Inbox' which are not static.

 

Can you suggest anything I could do?

I need to be able to differentiate emails that are inbound versus outbound for my purpose.

11 - Bolide

@Yanoflies Do you need to traverse all sub-folders or are you wanting to query a specific folder? If you're wanting to query a specific one then you can enter the Id in the folder field in the tool configuration. Traversal is a but challenging because the new API structure is quite different and folders work more like Google labels than actual folders you're digging through. I still need to look into some enhanced folder functionality with the new API but I don't have a timeline for that yet. Given the new API, it's better (i.e. more efficient) to narrow your querying as close to the actual message you're trying to receive and filtering from there if necessary than picking a folder and traversing all sub-folders and files within.

8 - Asteroid

@rpaugh 

 

All the sub-folders unfortunately.

 

Not only are there too many mailboxes for me to go through individually, they are constantly changing so it would be impossible to maintain if I had to get the static specific FolderID.

Is there no way to tell if an email is received or sent?

 

I can't figure out how to build such a query string.

 

I don't need to traverse through sub-folders if I am able to differentiate the two types of emails, I will lose accuracy in some things (I won't be able to exclude emails moved to an 'Ignore' folder for example, or deleted emails if they are returned too) but it will be good enough.

11 - Bolide

@Yanoflies I'm a little confused. SentItems should get all of your sent messages and inbox will get all of your received messages. Are you getting cross messages?

8 - Asteroid

@rpaugh 

 

If I leave the Folder ID field empty, it reads the entire mailbox (every folder and sub-folders).


This works well as I can filter out the outbound emails in the SentItems folder, but the problem is when emails are moved from SentItems to a sub-folder either in the Inbox or SentItems folder.

 

I have a test shared mailbox where I did the following

Inbox
        Sub-Folder of Inbox
                Sub-Sub-Folder of Inbox
Sent Items
        Sub-Folder of Sent Items
Deleted Items
Junk Items


Using an empty FolderID gets me emails from every folder.

My approach right now is that I read the Sent Items, Deleted Items, Junk Items separately and subtract them from the big set.

Only issue is when outbound emails are moved hen I lose them as sent emails.

11 - Bolide

@Yanoflies ah I see. I'm guessing you can't use to/from to determine if the email is inbound/outbound? I'm not seeing anything in the API that indicates this aside from using the SentItems folder, which as you said doesn't work if messages are moved to other folders.

8 - Asteroid

@rpaugh 

 

I gave it a try with the to/from but it's not the most accurate either.

I came up with an idea but I think it's a little too much for me to implement (but won't stop me trying):

1. List all of the child folders of Inbox or Sent Items depending on which we are working with (https://docs.microsoft.com/en-us/graph/api/mailfolder-list-childfolders?view=graph-rest-1.0&tabs=htt...) using:

 

 

 

https://graph.microsoft.com/v1.0/me/mailFolders/Inbox/childFolders

or

https://graph.microsoft.com/v1.0/me/mailFolders/SentItems/childFolders

 

 

 

Optional but we can use the childFolderCount to get the children of the children recursively too.

Then when we fetch the emails, we go through the list, optional again but when we build the list we can also keep the folder names there to map to the folderId. 

 

EDIT 2:

Alternatively, if I can somehow access the MAPI properties as an extension, I will be able to differentiate using the PR_RECEIVED_BY property as only received emails should have that.

11 - Bolide

@Yanoflies yeah that's why I haven't implemented more folder functionality in the new tool because I haven't yet found an efficient way to do it. Also, with the changes between how EWS and Graph API work implementing folder filtering and traversing would require substantial rework to the code, since EWS was folder first, and Graph API is item first. 

 

And yes, /childFolders only gets you one level down, so you'd have to do a recursive lookup to traverse the entire tree. You can cheat it a little bit using the $expand option to get the child folder Ids, but even then you'd have to do recursion to reach any levels below that. I'll keep thinking on it and if I have time I'll try playing around with some solutions to see if I can find an efficient one.

7 - Meteor

I need admin rights to install the Outlook Tool on my machine. Is there any way to circumvent this?

 

ChristinaFPE_0-1595535874110.png

 

 

11 - Bolide

@ChristinaFPE my recommendation is to the use Office365 version if possible. If you're using an older on-premise server and absolutely require this version then you'll need someone with admin access to install it. I could send you the DLL and config files for you to put in the necessary folders manually, but depending on which folders you need to place them you may still be prompted for admin rights. Any chance someone in your IT department would be willing to install it for you or give you temporary admin rights? 

5 - Atom

Hey @rpaugh - thank you for making such a useful and well executed plugin. I am trying to automate the plugin to select emails that arrived exclusively on the day the workflow is run. The discussion posts about using the query field and attaching a macro helped me make some progress, but my Designer is crashing when I try to modify the settings for an Action tool while it's connected to the Outlook365 input connector. I have found it doesn't crash if the Outlook connector is default with no account signed in, so I tried setting it up and switching the connection over to another input tool, but then it doesn't seem to update my query string when the workflow runs. Any idea what I might be doing wrong?

11 - Bolide

@brd I encountered the same delay when configuring the action tool, but ultimately the Designer did catch up and let me proceed. I was able to build a macro that took a query string input and apply it correctly to the Outlook Input tool to get the desired output. I don't know how long you waited before determining that your Designer crashed, but try giving a little more time to see if it eventually catches up. In the meantime, I'll see if I can figure out what would cause it to delay during macro configuration.

5 - Atom

@rpaugh After 25 minutes, Designer catches up! Good to know. I have succeeded in getting emails from the current day only automatically by passing a query string with a update val by formula in an action tool like "SentDateTime ge " + ToDate(DateTimeToday()) .

 

Thanks for the help!

11 - Bolide

@brd Glad to hear it. I'll let you know if I figure out what's causing the delay when attaching macro actions.

7 - Meteor

this is such a great tool and exactly what i need to get a lot of reports automated that come daily into my inbox.

Unfortunately, this tool doesnot seem to be working for me.

 

Attached is the error I get. Here is the what i am using in the configuration:

 

1. This is a corporate email, so I use domain\username ( i have also tried my full email address and it does not work)

2. Manual Service URL with ews/exchange.asmx

 

Any help in working this out will be greatly appreciated!

 

outlook error.png

11 - Bolide

@S-9 for corporate email addresses, if the address itself does not work then you'll have to find out what the domain format is from your IT department. For example, instead of "john.doe@acme.com" it might be something like "jdoe@acme.com" (typically just your domain username and your corporate address). If you have regional AD group then you might have something like "jdoe@nala.acme.com" or "jdoe@emea.acme.com" instead of just "@acme.com".

5 - Atom

@rpaugh This tool is amazing and has helped greatly with automating the daily reports I get. Quick question: I get an attachment from the client each day with the same exact attachment name, is there a way to filter on the most recent, and then save it to a location? Or does the save feature happen before any analytics are run? Appreciate the help.

11 - Bolide

@ccleary I assume you're using the original version of the tool, not the Office365 version? If so, the way I had to pull attachments from the messages meant doing it all in the back end, which unfortunately means there's not really a good way to pass through additional filtering. The best workaround for this is to refine your message filters to only pull the current message and grab the attachment from there. This should ensure that you're grabbing only the latest relevant message. If you're worried about  the messages overwriting each other when they're saved to the disk, you can check the box for applying a unique name to each attachment. You can then link the message stream to the attachment stream on message ID to ensure you're pulling the correct file. Does this address your issue?

5 - Atom

@rpaugh that helps a lot thanks. For the workaround, I would just refine the filtering in the query string?

11 - Bolide

@ccleary Yes, exactly. You should be able to do something like "ReceivedDateTime >= Yesterday".

Hi @rpaugh I keep getting these error messages.

 

Info: OutlookInputTool (1): 9223372036854775807
Error: OutlookInputTool (1): The request failed. The request was aborted: Could not create SSL/TLS secure channel.
ServiceRequestException
 or the 

Error: OutlookInputTool (1): The Autodiscover service couldn't be located.
AutodiscoverLocalException
 
error message.
 
what exactly do you mean by use domain name email for username field? I checked in my outlook account settings and I am using the same email as username. also I m using the manual service url from under "access this account on the web" section from outlook/
 
Please suggest
11 - Bolide

@NemesiaKantekar Just curious, what are you using as the manual service url? Feel free to PM me if you do not with to share here - or mask the domain if you like.

6 - Meteoroid

Hello all, hi @rpaugh, great tool, really what I was looking for.

I have the following problem. Running the Outlook Input tool locally, in the Designer, works well, no problem at all. What I haven't managed yet to do is running it on the server. The following error messages pop up each time:

a) The Autodiscover service couldn't be located. AutodiscoverLocalException at...

b) using the service url "https://outlook.office365.com/EWS/Exchange.asmx" the error reads as follows: The request failed. Unable to connect to the remote server ServiceRequestException at...

 

My colleague who is the administrator of our company's Alteryx server however was able to run my workflow with his exchange credentials without any error. Using my credentials resulted in the same error messages as the above. Any ideas on this?

 

Thank you all for your replys, Hanspeter

8 - Asteroid

@rpaugh similar question to Hanspeter, except I still have internal blocks on downloading from Google drive, could up upload the full connector to Alteryx gallery to route the install off of Github? I need a method for my Server admins to be able to install from here to get this into an automated workflow.

11 - Bolide

@Hanspeter Are there any security restrictions on the server that would prevent your user account from reaching the Exchange server from the Alteryx server? Is every other user (except the admin) having this same issue?

 

@JMoore which version of the tool are you using?

6 - Meteoroid

@rpaugh : you are right. I found out that my exchange server is in the cloud (O365) while my colleagues mail account is hosted on premise. That explains the different results with your tool even while working in the same company - do you know if your tool actually works on O365 when the security restrictions were adapted somehow?

Thanks for your reply, 

Hanspeter

11 - Bolide

@Hanspeter depends on which version you're using. The old version uses Basic Authentication, while the new one (entitled O365 beta) utilizes OAuth. Both can be installed and used in parallel if you need them for a true blended environment.

6 - Meteoroid

@rpaugh : you mean that with the old version (basic authentication) this tool should work - and I could ask my IT dep to give me access to the old version? Is that what you mean? (sorry, I have no experience in that area...)

8 - Asteroid

@rpaugh I am using the java Oauth version Beta

11 - Bolide

@Hanspeter The old tool will work for the on-premise server (assuming it still allows basic authentication), while the new tool will work for your Office365 accounts via OAuth.

 

@JMoore So you just need me to post the .yxi file to GitHub for you to pull down?

6 - Meteoroid

@rpaugh : oh, great, I will install the new tool and try to access O365. Now the only question is where to download the old and the new tool - or what exactly do you mean with old and new version of the tool? Is the new one this here https://gallery.alteryx.com/#!app/Outlook-Tools-Install/597b35c2f499c716ec34a782?

8 - Asteroid

@rpaugh that is correct.

11 - Bolide
11 - Bolide

@JMoore Done.

 

Alteryx GitHub Screenshot.png

Alteryx
Alteryx

@rpaugh can I ask you a question? 

 

one of my clients is trying to use the Outlook input tool but it looks like they are using a different Exchange to the options we can select in Alteryx Outlook tool i.e.  Exchange 2013 CU20 not SP1, their mailboxes actually reside in the Office 365 cloud. so their Exchange server merely points to the cloud.

 

Is there any workaround for this? 

 

11 - Bolide

@Hills That's interesting. I would have to know how they are connecting from Exchange to Office365. I assume they're using the on-prem server as a pass-through client, which would mean you should be able to connect directly to Office365 to query the mailboxes. Feel free to PM me if you don't want to share the details here.

5 - Atom

Very Cool.

5 - Atom

@rpaugh - Thanks for creating such an awesome tool !

 

I am pretty new to Alteryx and have a situation where I receive a mail from client on random days (like 3 mails in a single day, 1 mail everyday or 2 mails a week and some days no mail etc.). Everything (like subject name, attachment name etc.) is consistent across all mails except that the attachment has recent data. As such, I want to search my outlook to look for most recent mail from client with given subject name, attachment name etc and extract the latest attachment received so far in the specified folder.

 

I tried using older Outlook tool before but it was overriding all attachments (due to same names) and finally replacing it with the oldest attachment. I could not use any 'query string' (e.g. last week, received:today, date range etc.) there owing to random days mails are sent.

 

I am not able to figure out how to I can modify my workflow to stick to newest/latest mail received and pull attachment from that only (I would run my workflow daily). I installed your latest Outlook tool for office 365 and tried using '$top' in that, but i guess I am nowhere near to what I want to achieve.

 

Perhaps an additional functionality where a user can input the top/bottom emails that he/she wants to retrieve from email may be useful. Request your assistance for same.

 

My second request would be around 'sub-folders' option which was present in previous outlook version, can a similar functionality be added in newer outlook version tool as it was relatively easier to work upon.

 

Thanks in advance!

 

5 - Atom

@rpaugh - Thanks, I could figure that out. 

We would use 'Sort' on variable 'LastModifiedDate Time' in Descending order, then, we would use 'Sample'. Under that :-

Tick on 'First N rows'

Put value of N=1

This would give us the latest attachment so far received.

Then we may add a Blob Converter and proceed from there

 

 

7 - Meteor

This is great @rpaugh