how to send emails to recipients extracted from the dataset

[tparvaiz]

Hi,

I am using following code to generate email. 

----------------------------------------------------------------CODE-------------------------

%macro email;
FILENAME Mailbox EMAIL 'xyz@hotmail.com'
Subject='Report - Ran okay' ;
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Please find the Report as an attachment";
PUT "Thank you";
PUT " ";
RUN;

%mend email;
run;

%macro email_error;
FILENAME Mailbox EMAIL 'xyz@hotmail.com'
Subject='Report - Error' ;
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Please be advised that Report was generated with potential errors";
PUT "Thank you";
PUT " ";
RUN;

%mend email_error;
run;

data Table2a_;
set Table2(where = (
location_ID = 14
) );
if 180000 <= price <= 300000 then call execute ('%email');else call execute ('%email_error');
run;

------------------------------------------------------

may I know how to use email addresses from a dataset rather than defining it in the code. for example, let's say I have a dataset TBL_EMAIL that looks something like this

Report_ID  email_address

1                 abc@hotmail.com

1                 xyz@hotmail.com

2                 abc@hotmail.com

so I want to send the report to all the email addresses where report id = 1

please advise

Thanks

[Jagadishkatam]

this is an untested code, hopefully will work

%macro email(emailid);
FILENAME Mailbox EMAIL "&emailid"
Subject='Report - Ran okay' ; 
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Please find the Report as an attachment";
PUT "Thank you";
PUT " ";
RUN;
%mend email;

%macro email_error(emailid);
FILENAME Mailbox EMAIL "&emailid"
Subject='Report - Error' ;
DATA _NULL_;
FILE Mailbox;
PUT "Hello";
PUT "Please be advised that Report was generated with potential errors";
PUT "Thank you";
PUT " ";
RUN;
%mend email_error;

data Table2a_;
set Table2(where = (
location_ID = 14 
) );
if 180000 <= price <= 300000 then call execute ('%email('||email_address||')');else call execute ('%email_error('||email_address||')');
run;

[tparvaiz]

How is it calling TBL_EMAIL.EMAIL_ADDRESS

can you please advise

[Jagadishkatam]

We need to use the dataset which has the email id

data Table2a_;
set Table2(where = (
location_ID = 14 
) );
if 180000 <= price <= 300000 then call execute ('data _null_; set TBL_EMAIL; where report_id=1; %email('||email_address||'); run;');else call execute ('data _null_; set TBL_EMAIL; where report_id^=1;%email_error('||email_address||');run;');
run;

[tparvaiz]

Here is the code that failed and generated an error message

---------------------CODE that I used - --------------------------------------------------

data Table2a_;
set Table2(where = (
location_ID = 14
) );
if 180000 <= price <= 300000
then call execute ('data _null_; set TBL_EMAIL; where report_id=1; %email('||email_address||'); run;');
else call execute ('%email_error');
run;

----------------Error Message that I got ------------------------

NOTE: The file MAILBOX is:
E-Mail Access Device

ERROR: Invalid E-mail recipient.

-----------------------What worked ------------------------------

Following code worked (suggesting that there is something wrong in a way I am calling the email macro)

data Table2a_;
set Table2(where = (
location_ID = 14 
) );
if 180000 <= price <= 300000 
then call execute ('%email(myemail@Domain.com)');
else call execute ('%email_error');
run;

Thanks

[tparvaiz]

here is how I am creating a test table

data TBL_EMAIL;
input

nuid $ 1-7
email_address $ 9-28
report_id $ 32-33
objectname $ 34-47
objecttype $ 49-64;
cards;
A111111 myemail@Domain.com 1 test report report
A111112 myemail@Domain.com 2 test report report
;
run;