I am trying to ask SAS to output a small dataset(s) to Excel, copy the contents to the body of an outlook email message, send email and gracefully exit the procedure.
I have a solution that produces a text output in the email body. Please see code below. I would like to improve this by 1) formatting the message body as an excel table 2) using excel to call outlook to send email. I am unsuccessful in using ODS HTML as the body of the email due to exchange security restrictions (see issue described here https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-EMAIL-only-HTML-code-appears-in-the-body-n...). Using SAS to invoke Outlook causes a security pop-up to appear that has to be manually cleared. I have tried the code snippet here (https://support.microsoft.com/en-us/help/816644/how-to-send-a-range-of-cells-in-an-e-mail-message-by...) and it works without invoking a pop-up. I would like to write and invoke this vba sub from SAS.
If folks have a better solution or questions for clarification please feel free to ping me.
Here is my current code:
PROC SQL NOPRINT;
select count(*)
into :N_Crp_Fail
from Fails_corp;
select distinct Ult_Issuer_Name_qc, Total_Exposure
into :UP_Name1 - :UP_Name%left(&N_Crp_Fail), :Exp1 - :Exp%left(&N_Crp_Fail)
from Fails_corp;
select count(*)
into :N_Stf_Fail
from Fails_structured;
quit;
select distinct sm_sec_desc, BHF_Credit_Exposure
into :Sec1 - :Sec%left(&N_Stf_Fail), :SExp1 - :SExp%left(&N_Stf_Fail)
from Fails_structured;
quit;
%macro eia_agl_email;
DATA _NULL;
FILE
MAILBOX EMAIL TO = ('xy@abc.com')
SUBJECT=" &ENV. - Limit Fails as of &As_of_Date1. using 2019 limits structure"
CONTENT_TYPE = "text/html"
ATTACH = ("&Output_CSV_lib.Fails_corp.csv"
"&Output_CSV_lib.Fails_structured.csv"
);
if 0 then set Fails_corp nobs=nFails_Corp;
put "Number of corporate limit fails using 2019 limits structure: " nFails_corp;
put " ";
%DO i = 1 %TO &N_Crp_Fail;
PUT "&&UP_Name&i";
PUT "$&&Exp&i";
PUT " ";
%END;
if 0 then set Fails_structured nobs=nFails_structured;
put "Number of structured finance limit fails using 2019 limits structure: " nFails_structured;
put " ";
%DO i = 1 %TO &N_Stf_Fail;
PUT "&&Sec&i";
PUT "$&&SExp&i";
PUT " ";
%END;
put "Collateral Date available in this run: &Collat_Date1";
put " ";
put "XML Date available in this run: &xml_dt";
stop;
RUN;
%mend;
%eia_agl_email;
Notes: The above code snippet is at the very tail end of a large process. I want to send the contents (formatted as a table) of Fails_corp and Fails_structured (2 tables) in the body of one email to a distribution list specified in "to" field. We are using SAS 9.04 on WX64_WKS and Office 365 ProPlus Excel version 1808.
Original site validation data
Current version: 9.04.01M5P091317
Site name: '--------------'.
Site number: xxxxxx.
Expiration: 30DEC2019.
Grace Period: 45 days (ending 13FEB2020).
Warning Period: 45 days (ending 29MAR2020).
System birthday: 05DEC2018.
Operating System: WX64_WKS.
Product expiration dates:
---Base SAS Software
30DEC2019
---SAS/STAT
30DEC2019
---SAS/GRAPH
30DEC2019
---SAS/ETS
30DEC2019
---SAS/Secure 168-bit
30DEC2019
---SAS/Secure Windows
30DEC2019
---SAS Enterprise Guide
30DEC2019
---SAS/ACCESS Interface to PC Files
30DEC2019
---SAS/ACCESS Interface to ODBC
30DEC2019
---SAS Workspace Server for Local Access
30DEC2019
---High Performance Suite
30DEC2019
