Sending email from sas to list - separate email to each recipient

[Jima]

We have the need to send emails to staff at individual regions. Each recipient receives an unique email with unique attachments. The recipient email addresses are read from an Excel file containing a region ID, recipient name, recipient email address, and location of files for the attachments. I have tried to do this within a data step and via a macro. The current version using a macro is below.

If I hard code a 'to' address, multiple emails are sent (based on the input file) with the correct attachments. If I use a macro variable to identify the email addresses, I get an error/warning: 'WARNING: Email: 554 5.1.1 Invalid recipient address.' 

When I reference 'to = &emailx.', its value is not resolved as it is in the line, but is resolved correctly in the line 'put &emailx.;' A similar situation occurs with '&areax.' in the 'subject' line. The attachments resolve correctly. I have tried us '=' in the macro without luck - %macro email(areax=, ...).

I have searched for solutions of sending emails via sas from a distribution list, but references I have found are sending the same email to each recipient.

Also, I have tried setting the email variable that is passed to the macro with quotes and without quotes placing quotes within the macro.

Any assistance is greatly appreciated...

MACRO CODE...

%macro emails (areax, emailx, namex, excelrpt, pdfrpt);

options emailsys=smtp
emailhost = smtp.vccs.edu
emailport = 25;

option symbolgen mlogic mprint;

filename reports email;

data _null_;
set emails;
file reports
from = 'abc@xyz.edu'
to = &emailx.
subject = 'WIOA PY&year. Q&quarter. Area: &areax.';
put ' ';
put 'Hello ' name;
put ' ';
put "Attached is a test of the email routine. If you have any questions, please let me know.";
put ' ';
put &areax.;
put &emailx.;
put &namex.;
put ' ';
put 'Thanks!';
put '-Jim';
put '!EM_ATTACH!' &pdfrpt.;
put '!EM_ATTACH!' &excelrpt.;
put '!EM_SEND!';
put '!EM_NEWMSG!';
run;

%mend emails;

[ChrisNZ]

We don't have all the details, but

Also, I have tried setting the email variable that is passed to the macro with quotes and without quotes placing quotes within the macro.

As a general rule, don't quote inside the macro values. Also, use double quotes to resolves these values. Single quotes keep them unresolved. So something like to="&emailx"

Does that help?

[Jima]

Thanks. I tried that - it didn't correct the issue.

When the macro variables are used in the 'put' statements, they resolve to the variable values just fine. The same macro variables in the 'file' statement within the 'data _null_;' step seem to resolve to the name of variable that is past to the macro and not the value of the variable. Why are macro variables treated differently between the two statements within the same macro?

[ChrisNZ]

Why are macro variables treated differently between the two statements within the same macro?

There is no reason this should ever happen if you use double quotes.

%macro emails (areax, emailx, namex, excelrpt, pdfrpt);

data _null_;
  file reports email from    = 'abc@xyz.edu'
                     to      = "&emailx"
                     subject = "WIOA Area: &areax"; %* <= you need double quotes here to resolve;
  put "&emailx";
run cancel;

%mend emails;

option mprint;
%emails(a,myname@sas.com,myname)

See how the variables are resolved in both put and file:

MPRINT(EMAILS): data _null_;
MPRINT(EMAILS): file reports email from = 'abc@xyz.edu' to = "myname@sas.com" subject = "WIOA Area: a";
MPRINT(EMAILS): put "myname@sas.com";
MPRINT(EMAILS): run cancel;

[Jima]

ChrisNZ, thanks for the reply, but it doesn't do what I need. I get a bad recipient address using to="&emailx" and using put "&emailx" resolves to the name - not the value - of the passed variable. The macro statement is  %emails (area, email_addressx, name, rpt_xlsx, rpt_pdf); and the result of the put "&emailx"; is 'email_addressx' the variable name not the variable value.

[SASKiwi]

Get your code working without macro to start with - use hard-coded values. That will ensure you know what will work and what won't before converting it into a macro.

[Jima]

SASKiwi, thanks for the reply. This is one of the first things I did to narrow down the issue. When I hard-code an email address in the 'to' statement, the macro works - except for the '&areax.' in the subject. I did this before the original posting. The issue seems to be that the 'file' statement in the 'data _null_;' step resolves the '&emailx.' and the '&areax.' macro variables as the passed variable names and not passed variable values. Statements following the 'file' statement - 'put' and 'put '!EM_ATTACH!' - resolve the macro variables as values. 

[DaveHorne]

Hi @Jima, since your email information is coming from a data file (Excel), you don't really need macro at all in this case.  I've mocked up an example here:

/** Mock up email data **/

%let year=%sysfunc(year(%sysfunc(today())));
%let quarter=%sysfunc(qtr(%sysfunc(today())));
options emailsys=smtp emailhost = smtp.vccs.edu emailport = 25;

proc sql;
  create table work.emails
    (areax char(5), emailx char(40), name char(40), pdfrpt char(40), excelrpt char(40));
  insert into work.emails
    values('North','dave@mailinator.com','Dave Smith','C:\temp\rept1.pdf','C:\temp\rept1.xlsx')
    values('South','mary@mailinator.com','Mary Smith','C:\temp\rept2.pdf','C:\temp\rept2.xlsx')
    values('West', 'rick@mailinator.com','Rick Smith','C:\temp\rept3.pdf','C:\temp\rept3.xlsx');
  quit;
run;

/** Send e-mail messages **/

filename reports email from="abc@xyz.edu";

data _null_;
  set emails;
  file reports;
  put "Hello " name;
  put / "Attached is a test email for " areax;
  put / "Thanks!";
  put "Jim";
  put '!EM_TO!' emailx;
  put "!EM_SUBJECT! WIOA PY&year. Q&quarter. Area: " areax;
  put '!EM_ATTACH!' pdfrpt;
  put '!EM_ATTACH!' excelrpt;
  put '!EM_SEND!';
  put '!EM_NEWMSG!';
  put '!EM_ABORT!';
run;

There are a lot of SAS papers over the years on this topic: https://www.lexjansen.com/search/searchresults.php?q=email%20bulk

[Jima]

Dave, thanks for replying. This email piece a part of a much larger system. There are first a series of programs that read Excel files containing parameters and flat data files. The person who uses the system is not a SAS programmer and it is easier for him to enter the parameter specifications in an Excel spreadsheet. The spreadsheet contains information about year, quarter, locations of input files, locations of output files, and so on. The output is a series of pdf (summary tables) and Excel files (data used in the tables) - two files per area/region.

Once the reports are finalized, copies are emailed to each area. It is the same individual and he will be responsible for maintaining a spreadsheet with the current email addresses for each area. This is why the input for the emails imported from an Excel file. Also, data for the proper year, quarter, and file locations are a SAS program used in building the reports - it is included in the SAS program stream.

So, we don't want build the email address information in a data step. Someone has suggested using 'call execute' to invoke the macro. That solves the problem with the 'file' statement recognizing the macro variable value, but it generates multiple emails for each recipient. I will keep working on it.

[DBailey]

might also try

to = %tslit(&emailx)

[Jima]

DBailey, thanks for the reply. I tried your suggestion and it still resolves to variable name not its value.