Email from a data step with dynamic mailing list

[WIzarDE]

Hi there, this is my first time posting a question online I usually stick to lurking and finding similar queries to my own but alas I am having a world of difficulty setting the email configurations up in SAS EG so that I can get the program up and Running.

I thank you all for all the help you provide the community I've found a ton of info already thanks to you all.

My goal is to have an automated program that is scheduled for a user, upon being started it will conclude a set of predetermined tasks and email the user that made the request the results of the task.

Now I have everything ready but the last piece of the puzzle is the email part.

I cannot use the send to -> Email Recipient as a Step in Project because as far as I can tell I can't give it a dynamic variable for it to use as the email recipient.

proc sql noprint;
	select c_email
		into :email
		from USER_INFO
		;
quit;

I create the email variable with the above code and then wish to use it for sending the the email to the recipient &email.

Now my companies email is a outlook base email from the office 365 schema.

So I though using the MAPI solution would be enough for me and it works fine when I run the task straight from SAS EG.
But when the program is invoked as a scheduled task from a user request it does not run the email program. But it doesn't specify the error either. It just has a cross mark on the program.

options
emailsys="MAPI"
EMAILID="xxx.yyy@company.com"
EMAILAUTHPROTOCOL=none
;

FILENAME Mailbox EMAIL 'xxx.yyy@company.com'
Subject='STARTUP';
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "This is a test message from the DATA step";
RUN;

So I've tested a few solutions SMTP connection with my GMAIL account for testing, MAPI with the companies Outlook client and using the E-mail Recipient step.
The gmail connection through SMTP works fine run directly from the task and invoked as a scheduled request from a user:

options
emailsys="smtp"
EMAILHOST="smtp.gmail.com"
EMAILPORT=25
EMAILID="xxx.yyy@gmail.com"
EMAILAUTHPROTOCOL=plain
EMAILPW="{SAS002}xxxx"
;

options emailhost=
 (
   "smtp.gmail.com" 
   /* alternate: port=487 SSL */
   port=587 STARTTLS 
   auth=plain 
   /* your Gmail address */
   id="xxx.yyy@gmail.com"
   /* optional: encode PW with PROC PWENCODE */
   pw="{SAS002}xxxx" 
 )
;
 
filename myemail EMAIL
  to=("&mail")
  subject="abc - Request from &user"
  attach	=	'C:\Desktop\abc.csv';
 
data _null_;
  file myemail;
  put "Dear User,";
  put "The request information is annexed in the current email";
  put "Best Regards,";
  put "xxx";
run;
 
filename myemail clear;

Now this is where I am stuck right now because from what I can the the E-mail Recipient also use a SMTP connection but I cannot replicate it by script. It also uses Windows authentication.
 

So now how do I replicate the connection that this module is using by script?

I tried the following, but I get an ERROR: Email: The connection was refused.
The code I tried was as follow and I tried to mimic the gmail script:

options
emailsys="smtp"
EMAILHOST="mail.company.com"
EMAILPORT=25
EMAILID="xxx.yyy@company.com"
EMAILAUTHPROTOCOL=plain
EMAILPW="{SAS002}xxxx"
;

options emailhost=
 (
   "mail.company.com" 
   /* alternate: port=487 SSL */
   port=587 STARTTLS 
   auth=plain 
   id="xxx.yyy@company.com"
   /* optional: encode PW with PROC PWENCODE */
   pw="{SAS002}xxxx" 
 )
;

FILENAME Mailbox EMAIL 'xxx.yyy@company.com'
 Subject='STARTUP';
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "This is a test message from the DATA step";
RUN;

with auth = none I get ERROR: Email: 530 5.7.1 Client was not authenticated.
And I know that this probably means that I cannot use an SSL  to TLS request. But I just can't understand how to use the Windows Integrated authentication for the SMTP request the same that the E-mail Recipient does ...


Any help on understanding why using MAPI isn't working as a scheduled taks or why I can use my account with a SMTP request from the E-MAIL Recipient task but can't replicate it by code on a data step would be apreciated.

Some of my reading material can be found here:

https://support.sas.com/resources/papers/proceedings13/023-2013.pdf

http://support.sas.com/resources/papers/proceedings13/340-2013.pdf

https://www.lexjansen.com/pharmasug/2007/tt/TT09.pdf

https://blogs.sas.com/content/sasdummy/2013/07/31/gmail-from-sas-program/

http://support.sas.com/resources/papers/proceedings10/060-2010.pdf

Best Regards,

WIzarDE

[tomrvincent]

here's what I do.

First, I have this macro:

%macro email(email=,cc=,subject=,message=,attach=);

    filename mymail email "&email";
    
    data _null_;
        file mymail
            to=("&email")

    %if %length(&cc)>0 %then %do;
            cc=(&cc)
    %end;

    %if %length(&attach)>0 %then %do;
            attach=(&attach)
    %end;
            subject="&subject.";
            put "&message.";
    run;

%mend;

And I invoke it like this:

%let endtime      = %sysfunc(time(),time8.0)     ;

%let pgmpath = %sysfunc(pathname(output));
%let currprog = &_CLIENTPROJECTNAME;
%email(email=&EMAILADDRESS
    ,subject=&currprog
    ,cc='person1@domain.com' 'person2@domain.com'
    ,message=has finished. started at &starttime and ended at &endtime
    ,attach="&pgmpath./file1.pdf"
             "&pgmpath./file2.html"
             "&pgmpath./file3.rtf"
            );

So if you populated the cc list and put single quotes around each value you should be good to go.

[WIzarDE]

Hi, thanks for sharing. I think I will adapt your methodology I like how you have programmed it.

But this doesn't seem to solve my problem of configuration so I can send emails in the same way the E-mail recipient step does.

[tomrvincent]

You could loop thru your list or just put the dynamic mailing list into the cc or email variable.

[WIzarDE]

Hi,

But my problem is that the Email Recipient step doesn't accept variables. I can't put something like this in the "to" part:
%let email = 'jon.snow@north.com'
to: &email

It doesn't recognise the variable.

And if I write a code similar to yours I get an erro like this E-mail: Permission denied.

My problem here is how do I replicate the SMTP configuration that the email step is using that was configured in the tools > options > administration. And uses Windows Integrated Authentification.

Best regards,
WIzarDE

[tomrvincent]

Okay.  It works for me.  I've been able to email with variables, as my macro shows.  Is anyone at your company able to email from within SAS?  If not, it sounds like a config/system problem.

[ChrisHemedinger · Accepted solution]

I don't have an Office 365 account to test this, but it looks like the configuration is similar to that for Gmail.  Sub in the Office 365 SMTP address as the host.  Here's a Microsoft KB article with the settings.

options emailsys=SMTP;
options emailhost=
 (
   "smtp.office365.com" 
   port=587 STARTTLS 
   auth=login
   /* your Office 365 account/address */
   id="yourO365email@yourcompany.com"
   /* optional: encode PW with PROC PWENCODE */
   pw="your_password" 
 )
;

Note the restrictions from the article: you must be able to supply your user/password -- no provision for a single-signon method, the way you might be accustomed to via a web browser.

Regarding a dynamic TO list.  I do this in my jobs by placing the e-mail addresses in an external file (call it distlist.csv, for example).  It's a simple text file with e-mail addresses stored, one per line.  Then I read it in like this:

filename dl "/u/myaccount/distlist.csv";
data distlist;
  infile dl dsd;
  length email $ 50;
  input email;
run;

proc sql noprint;
select cat('"',trim(email),'"') into :toList separated by ' ' from distlist;
quit;

FILENAME OUTPUT EMAIL
                SUBJECT = "My Subject"
                FROM = "Chris Hemedinger <myemail@mycompany.com>"
                TO = (&toList)
		 type='text/html'
                CT ='text/html';

ods html(id=email)
  file=OUTPUT(title="My email report")
  style=seaside;

ods escapechar="^";
ods html(id=email) text="This automated report blah blah ^{newline 1} More info, and more info ^{newline 1}";

proc print data=mydata;
run;

ods html(id=email) close;

[WIzarDE]

Hello,

Thank you for your reply Chris, the thing is when I use the same configuration as GMAIL I get the following error: ERROR: Email: The connection has timed out.

And what I find odd is that the email recipient block sends SMTP emails without a problem.

Best regards,

WIzarDE

[WIzarDE]

Hi again,

It seems like I replied to early last time.

Thanks to your help I figured out what I was doing wrong.

Basically I was using what I have configured in the tools -> options -> administration for my SMTP server:

Which for some reason is different from the smtp.office365.com server that you indicated.

Now if I use the code as you gave me directly I get this erro: WARNING: Email: 550 5.7.60 SMTP; Client does not have permissions to send as this sender [DB6PR0602MB2901.eurprd06.prod.outlook.com]

But the fix is quite simple, I just needed to add two lines in the options setup and keep the rest the same:

options emailsys=SMTP
EMAILPORT=25
EMAILID="yourO365email@yourcompany.com";
options emailhost=
 (
   "smtp.office365.com" 
   port=587 STARTTLS 
   auth=login
   /* your Office 365 account/address */
   id="yourO365email@yourcompany.com"
   /* optional: encode PW with PROC PWENCODE */
   pw="your_password" 
 )
;

Thank you for your help and for your time.

Best Regards,
Yasin

[WIzarDE]

Hi again,

Sorry to bother you again Chris, but for some reason after weeks of working correctly my email step stopped working with the following error:

ERROR: Email: The connection has timed out.

I haven't changed any options, and my password is still valid.
Do you know what the problem might be?

Best,
W

[NeerajS1104]

Good one .Helpful in my context as well.Thanks