BookmarkSubscribeRSS Feed
Jima
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 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;

10 REPLIES 10
ChrisNZ
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?

Jima
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?

ChrisNZ
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    = '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
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.

SASKiwi
PROC Star

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
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. 

DaveHorne
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 = 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
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.

DBailey
Lapis Lazuli | Level 10

might also try

to = %tslit(&emailx)

Jima
Calcite | Level 5

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 3862 views
  • 0 likes
  • 5 in conversation