How do I use SAS Datastep to write output to Excel and Email content as message body in Outlook

[indra1975]

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

[tomrvincent]

Use proc export to create the excel file. Then you can simply attach it like you're doing for the CSV.