BookmarkSubscribeRSS Feed
Calcite | Level 5

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 emails (areax, emailx, namex, excelrpt, pdfrpt);

options emailsys=smtp
emailhost =
emailport = 25;

option symbolgen mlogic mprint;

filename reports email;

data _null_;
set emails;
file reports
from = ''
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!';

%mend emails;

Tourmaline | Level 20

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?

Calcite | Level 5

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?

Tourmaline | Level 20

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    = ''
                     to      = "&emailx"
                     subject = "WIOA Area: &areax"; %* <= you need double quotes here to resolve;
  put "&emailx";
run cancel;

%mend emails;

option mprint;

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

MPRINT(EMAILS): data _null_;
MPRINT(EMAILS): file reports email from = '' to = "" subject = "WIOA Area: a";
MPRINT(EMAILS): run cancel;




Calcite | Level 5

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.


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.

Calcite | Level 5

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. 

SAS Employee

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 = 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 Smith','C:\temp\rept1.pdf','C:\temp\rept1.xlsx')
    values('South','','Mary Smith','C:\temp\rept2.pdf','C:\temp\rept2.xlsx')
    values('West', '','Rick Smith','C:\temp\rept3.pdf','C:\temp\rept3.xlsx');

/** Send e-mail messages **/

filename reports email from="";

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!';

There are a lot of SAS papers over the years on this topic:

Calcite | Level 5

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.

Lapis Lazuli | Level 10

might also try

to = %tslit(&emailx)

Calcite | Level 5


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 5 in conversation