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-no-table/td-p/178526). 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-using-visual-basi) 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
... View more