Advanced coding to automails

[mmea]

Hi

I have a question.

If I have an Excel file, which I get from external people, can I make an automail (sending out the file without any manual work) that has rules based on the content of that excel file?

The file that Has to be send out - has a column that says whether test places of COVID has shut down - if so the field will be red:

Place	status
place A	CLOSED
Place B	CLOSED
Place C	OPEN

The automail should then send out in a text out saying: place A and PLACE B are closed

I know how to do the script automatic - but how can I code so i dont manually have to change the email text based on the content of the file - is should do it by it self

Is it possible ?

[SASJedi]

OK - some assumptions:

1. You have SAS/Access to PC File Formats, so we can access the Excel file

2. For this example, the Excel file is c:\temp\COVIDPlaces.xlsx and it contains one tab named Places with your sample data on it.

3. You know the information needed to set up SMTP email from SAS.

If so, here's some code to get you started:

/* These options tell SAS how to send email for you */
options EMAILSYS=SMTP  
        EMAILHOST=("mailhost.example.com" PORT=25 
        USERID="sas.jedi@example.com" PW="This is my password";
/* Access the Excel file */
libname xl XLSX "C:\temp\COVIDClinics.xlsx";

/* Here we set up the recipient email address, subject line & format for the email:*/
filename mymail email "sas.jedi@example.com" 
         subject="Closed Places" 
         lrecl=256 type="TEXT/HTML";

/* Make the email look pretty */
options nocenter ls=124;
ods html5 body=mymail style=journal;
title;

/* Send the email */
data _null_;
   file print;
   set xl.places end=last;
   where upcase(status)='CLOSED';
   if _n_=1 then do;
      put "To whom it may concern:";
      put "Here is a list of the closed places:";
   end;
   put Place; 
   if last then do;
      put / "May the SAS be with you!";
      put "Mark";
   end;
run;

ods html5 close;
filename mymail clear;
libname xl clear;