Report with attachements and summary details

[angorwat]

Hi

How to send the email with both attachments and the summary details.  Below is the output I need to send it in email to the customers along with the actual sales figure in the attachments. I am able to send the attachment successfully but I am struggling to the add the summary details in the mail.

Please advise. 

Code I used

/* create summary report */
 
option nocenter;
ods tagsets.excelxp
path= '/Reports/Output'
      file="Salesreport_&rptdate..xls" style= sansprinter
            options(   
                  sheet_interval='none'
                  Orientation = 'landscape'
                  Autofit_Height = 'yes'
                  Embedded_Titles = 'yes'
                  fittopage = 'yes'          );
 
      ods tagsets.excelxp options(
                  Sheet_Name="Sales"
                  sheet_interval='none'
                  Absolute_Column_Width = '8'
                  );
            proc print data=_sales noobs; run;
ods tagsets.excelxp close;
 
 
/* send report thru email*/
 
filename myemail email
                  from=("salessupport@xyz.com")
                to=("salesmanager@xyz.com" )               
                  Subject = ("Salesreport_Monthly")              
                  attach=("/reports/Output/Salesreport_&rptdate..xls")
;
data _null_;
file myemail;
put "Hi All,";
put ' ';
put "Please refer the attached sales report for &rptdate..";
put ' ';
put "Summary of the report";
<< Need summary details here >>>
put "Regards,";
put ' ';
put "Sales support team";
run;

Spoiler

Output required

Hi All,

Please refer the attached sales report for DEC16.

Summary of the report

Region_name   Sales_figure

Texas                    $10000

California             $20500

New York            $23000

Regards

Sales support team

[Kurt_Bremser]

Store this:

Texas                    $10000
California             $20500
New York            $23000

in a dataset

In your email code, do

data _null_;
set summary end=done; /* your summary dataset */
file myemail;
if _n_ = 1
then do;
  put "Hi All,";
  put ' ';
  put "Please refer the attached sales report for &rptdate..";
  put ' ';
  put "Summary of the report";
  put 'Region       Sales';
end;
put
  region_name
  sales_figure
;
if done
then do;
  put " "; /* add an empty line */
  put "Regards,";
  put ' ';
  put "Sales support team";
end;
run;

You will need to adapt the positions in the header line to the lengths of your variables, and probably have to add formats in the put of the variables.

[Kurt_Bremser]

And when using tagsets.excelxp, you should use a .xml filename extension, as Excel usually complains when the extension does not match the content type of a file.