community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Sending Emails from SMTP server requiring AUTHENTICATION with Python Code tool

Alteryx
Alteryx

Hi everyone,

 

As always - let me go directly to the bottom line.

 

The Email tool within Alteryx Designer currently does not support a setup where you use your SMTP server with authentication.

In other words, you always need to specify the SMTP server with no user authentication in place.

 

To overcome this, at least temporarily anyway, I recently put together a simple macro with Python Code tool which relies on a bunch of Python packages and allows you to send emails including from SMTP servers that require authentication.

 

The simple macro with a bit of Python code is attached underneath this postFeel free to try this out. 

Make sure you follow the comments within the code to change the parameters as per your setup (SMTP server etc.)

 

image.png

 

 

 

 

 

 

 

 

 

 

image.png

 

By all means, this is just a working prototype and not a robust solution. It is enough to be used though and during my testing, it worked nicely with office365 (ie.smtp.office365.com) which requires a TLS connection and also user authentication.

 

By all means feel free to customize/ adjust the solution to make it work for yourself and take it up a notch. 

 

Several assumptions I made designing this macro:

1/ I want to send an email using an SMTP server (in my case office365) which requires a user authentication

2/ I need to be able to specify a secure connection (in my case TLS) and specify my username and PWD

3/ I want to send an email in HTML format, including a PDF attachment (a report that I needed to send) which is stored as a file on the server

4/ The input params for the macro are Receiver (the email address), and FilePath (full path to the file I will attach to the email)

5/ I need to be able to specify my own TO, FROM, BODY, SUBJECT

 

The solution {code} that is used in the Python code tool in my macro workflow is below:

 

 

# List all non-standard packages to be imported by your 
# script here (only missing packages will be installed)
from ayx import Package
from ayx import Alteryx
#Package.installPackages(['pandas','numpy'])

import smtplib
import pandas
import email
import email.mime.application
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from os.path import basename

input_df = Alteryx.read("#1")

# Load the params from the input
file_path = "" #Placeholder for file_path
receiver = "" #Placeholder for receiver

# Turn the input DF into variables
# Can't think of an easier way than itterate throught the DF
for index, row in input_df.iterrows():
    receiver = row[0]
    file_path = row[1].replace("\\","/")

# Print the file path    
print(file_path)

# Specify the sender and recipient
# This needs to be changed to match your SMTP setup
# By default using the office 365 servers
fromaddr = "email@email.com"            # Change this to match your email user
toaddr = receiver
pwd = "PLACE_YOUR_PWD_HERE"             # Change this to match your PWD
server_url = "smtp.office365.com"       # Change this to match your SMTP server url

# Constructing the email message
# Feel free to change these values as needed
msg = MIMEMultipart('alternative')
msg['Subject'] = "Email test Python"     # Subject
msg['From'] = fromaddr                  
msg['To'] = toaddr
html = "This is an html body"            # Body
 
# The MIME type of your email, can be text/html
HTML_Contents = MIMEText(html, 'html')

# Adding an attachement based on the file path supplied by input tool
filename=file_path
fo=open(filename,'rb')
attach = email.mime.application.MIMEApplication(fo.read(),_subtype="pdf")   #Attach PDF document
fo.close()
attach.add_header('Content-Disposition','attachment',filename=filename)

# Attachment the file and HTML to the email message
msg.attach(attach)
msg.attach(HTML_Contents)

# Establish the server connection, send extended HELO, use TLS and login with pwd, then send the email
server = smtplib.SMTP(server_url, 25)  # Change the ports for SMTP as needed
server.connect(server_url,587)         # Change the ports for secure connection as needed
server.ehlo()
server.starttls()
server.ehlo()
server.login(fromaddr, pwd)
text = msg.as_string()
server.sendmail(fromaddr, toaddr, text)
server.quit() 

 

That's it. Give it a try. And let me know if this worked for you. And if you have any great ideas to extend this solution, share them back with us.

 

image.png

 

 

David Matyas
Sales Engineer
Alteryx
Asteroid

For anyone who wants a pre-built solution with a UI, multiple send, PW encryption (I believe... pretty sure its native to masked fields), HTML interpreter, error reporting and regex validation... I built this tool for my company's internal use.

 

Put the .yxmc in your macros folder (find in Options > User Settings > Macros) It shows up as Email (Auth) in the Reporting tab. As with @DavidM's this uses the python tool with the same modules and requires Alteryx 2018.3+

 

EmailMacro.PNG

 

EDIT:

Since posting this here I decided that I should test the tool a bit to make sure I don't frustrate anyone with errors (the few alteryx users in my office can fend for themselves :p ).

 

Issue:  There appears to be an issue when inputting boolean data into CC and Body fields - this occurs when you leave something blank in the text input tool (like CC or Attachment). I have tried rectifying this error with formulas/select in the macro, but it appears that alteryx does an error scan of the macro before executing without applying changes to the metadata when a fieldtype is changed. This means boolean fields in place of string causes regex, replace function, etc to fail -- even if the workflow wouldn't error if it were run.

 

Solution: Put a select tool in front of the mail tool and change your null boolean/int/float/byte-type field to string

Alteryx
Alteryx
Talk going the extra mile to me @WillBowman ;-)
That is a hidden little gem! Love it!
David Matyas
Sales Engineer
Alteryx
Asteroid

Thanks! Building stuff like this is the best of my job! (that's a good thing :p )

Alteryx Partner

Any idea on the error;

 

Error: Email (Auth) (1): Tool #16: ---------------------------------------------------------------------------
SMTPAuthenticationError
Traceback (most recent call last)
<ipython-input-5-d0c14874ff23> in <module>
----> 1 main(mail)
<ipython-input-4-5849cf8eb972> in main(mail)
7 server = smtplib.SMTP(SMTP)
8 server.starttls()
----> 9 server.login(frm,pw)
10
11 for i, e in mail.iterrows():
C:\Program Files\Alteryx\bin\Miniconda3\lib\smtplib.py in login(self, user, password, initial_response_ok)
727
728 # We could not login successfully. Return result of last attempt.
--> 729 raise last_exception
730
731 def starttls(self, keyfile=None, certfile=None, context=None):
C:\Program Files\Alteryx\bin\Miniconda3\lib\smtplib.py in login(self, user, password, initial_response_ok)
718 (code, resp) = self.auth(
719 authmethod, getattr(self, method_name),
--> 720 initial_response_ok=initial_response_ok)
721 # 235 == 'Authentication successful'
722 # 503 == 'Error: already authenticated'
C:\Program Files\Alteryx\bin\Miniconda3\lib\smtplib.py in auth(self, mechanism, authobject, initial_response_ok)
639 if code in (235, 503):
640 return (code, resp)
--> 641 raise SMTPAuthenticationError(code, resp)
642
643 def auth_cram_md5(self, challenge=None):
SMTPAuthenticationError: (535, b'5.7.3 Authentication unsuccessful')

 

Asteroid

It appears your mail server is remembering your authentication from that connection, which is weird.

 

Or it is possible that you don't need authentication, in which case you should use the native Email tool. 

 

 

I have put something in that hopefully will fix, adding this except statement to bypass login method in case of pre-authentication. I will update the attachment to the original post here.

 

try:
    server.login(frm,pw)
except SMTPAuthenticationError:
    print('Authenticated already')]

 

 

EDIT: ok i cant edit that post so it is here

I tried using this tool since I need to send emails via SMTP with TLS. Its a great solution !

I ran into couple of issues - 

1. Attachments are not working for some reason. I gave a full path to the PDF file stored on my desktop, but when i checked the email i received from this tool , attachment is missing.

2. Images in Email body - Any tip on how i could achieve this? I built the field for email body using Report Text tool of alteryx, i got the other content of this text in the email i received from the tool , but images are missing.

 

Also, is it possible to attach files other than PDF type? I currently need to attach HTML files.

 

Thank you.

Rohan.

Alteryx
Alteryx

Hey @rohan_tibarewala,

 

1. Can you please check that your path is written the right way? We use the normal Windows notation for the input param (i.e. "\" rather than the Python style). I would also suggest you try to give your folder a UNC path and then re-run, possibly could have some issues with logical Windows drives.

 

2. I think you would need to really construct the HTML of that email on your own. I would suggest try to Google how to construct HTML body with MIMEText HTML type in Python.

 

html = "This is an html body"            # Body
 
# The MIME type of your email, can be text/html
HTML_Contents = MIMEText(html, 'html')

 

3.. The type of attachment is, I believe, changeable. Try to google out how to work with MIMEApplication for Python - see the SUBTYPE is defined as PDF. I think this should be able to be changed to HTML.

 

attach = email.mime.application.MIMEApplication(fo.read(),_subtype="pdf")   #Attach PDF document

 

David Matyas
Sales Engineer
Alteryx
Bolide

I am using your macro @WillBowman  from the first downloadable version...but if I have an attachment it does not send, otherwise the email works.  what am I missing? 

 

I noticed @rohan_tibarewala had same issue and @DavidM responded but not sure if he was referring to his contribution or your macro.  and I suppose yours is based on his...but I am not familiar with Python and I have not found the issue and I am using full UNC path.

 

thanks!

Highlighted
Asteroid

@rohan_tibarewala @fharper 

 

I fixed the attachment issue. In case you are curious there was an error due to me calling an obj that didn't exist. This is largely due to the fact that I have blanket error handling (unspecified try/except) which is really poor practice. This is because I built this tool in a relatively short period of time.

 

I have also fixed an issue in which validation would fail on null/empty CC.

 

It should work with all attachments, I tested with .xlsx and .twb (tableau work book).

 

As far as html in the body goes, using the report tool won't work. The html in the report tool (I think) taps alteryx data via html api.

 

If you have html from a file you want in the body, load it w/ input and use summarize tool (concat) to get it all in one cell.

 

Good luck!

Labels