Emailing .xls attachments with html body

[Tiffany]

Hello,

I am having some trouble emailing an xls attachment. I am sending an email with the body in html with data output, but I also want to attach an xls file in case people want to manipulate the data. The xls file is being created fine, but when I attach it it gets appended
as a text file with a .xls extension and strange text in the file.
Any suggestions?

thank you!
Tiffany

my code:

%MACRO REPORTS;

%if &records %then %do;
ods listing close;

title "Weekly Report";
ODS TAGSETS.EXCELXP
file='/home/mydocs/coverage.xls' options(width_fudge='0.75')
STYLE=Printer
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100'
embedded_titles = 'yes');

proc print data=coverage noobs;
run;

ods tagsets.excelxp close;
ods listing;

filename outfile email
to =...@something.com'
replyto=...@something.com'
subject= "Weekly Report"
attach= ("/home/mydocs/coverage.xls")
type="text/html";

ods html body = outfile style=sasweb2;

proc print data = coverage;
run;

ods html close;

data _null_;
file temp;
run;

%end;
%mend reports;
%reports;

* example of text in attached file:


xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"...

Message was edited by: Tiffany Message was edited by: Tiffany

[Cynthia_sas]

Hi:
As you have discovered, TAGSETS.EXCELXP is the ODS destination which creates Spreadsheet Markup Language XML. This type of markup language is indeed an ASCII text file, which conforms to the Office 2003 specification for XML which describes a spreadsheet.

TAGSETS.EXCELXP does not create a true, binary Excel file. When you create a file with this destination and you use a file extension of ".xls", you are merely "fooling" the Windows registry into launching Excel when you double click on the file. Using the .XLS extension does not automatically make the file a true, binary Excel file. The snippet of XML that you posted is the XML processing instruction and the beginning <Workbook> tag that is part of every TAGSETS.EXCELXP file.

There are many previous forum postings on emailing files and emailing attachments. If the people you are sending the file to have Office 2003 or higher, then Excel should open the XML file correctly. If they only have Office 2000 or Office 97, then Excel will not open and render the XML correctly. I'm not sure, exactly, what's happening...it sounds like your mail server recognizes that the attachment is an XML file. Some mail servers will not send XML or HTML files, so perhaps something like this is involved.

You might want to search on support.sas.com for more help with emailing or open a track with Tech Support for more help. When I run this code on my Windows system, the files (HTML and XML) are correctly sent using Microsoft Outlook. When I receive them, I can save them both to disk.

Also, did you know that HTML files created with ODS can be opened in a browser and can be opened with Excel?? Ever since Office 97, Microsoft Office has been able to open and render HTML files. I know that TAGSETS.EXCELXP gives you some suboptions that control things like FITTOPAGE, and ORIENTATION, etc, however, you are essentially sending two "markup language" copies of the same file -- one HTML Markup Language and the other an Office 2003 Spreadsheet Markup Language file.

cynthia
[pre]

ods tagsets.excelxp file='c:\temp\table_xp.xls' style=printer;
ods html file='c:\temp\table_ht.html' style=sasweb;

proc print data=sashelp.class;
run;

ods _all_ close;

filename doemail email to='username@something.com'
from='other.user@something.com'
subject='Testing attach of multiple files'
attach=('c:\temp\table_xp.xls' 'c:\temp\table_ht.html');

data _null_;
file doemail;
put 'this is a test with several attachments.';
put 'one file is an HTML version of SASHELP.CLASS';
put 'the other file is an XML version of SASHELP.CLASS that you should';
put 'be able to open with Excel 2003 or higher.';
run;
[/pre]

[Tiffany]

Thank you for your suggestions. Unfortunately I still can't open the xls files, but at least I have a better understanding of what is going on. Secondly, I now know that html files can be opened with Excel. I love this. Now they can look nice and be functional - this may be my temporary solution. Thanks again!

[Cynthia_sas]

Remember that the ".xls" files are REALLY .xml files and you will NEVER be able to open them on the mainframe or UNIX. You will ONLY be able to open them with Excel 2003 or higher.

This means that the person who receives your EMAIL might have to explicitly SAVE the file before they can open it.

Did you run my code (instead of your macro program)? If you take my simple program and it does not work on your system or with your mail server then you have 2 choices:
1) check with Tech Support to see whether there are any other SAS configuration settings that need to be set by your system administrators and/or
2) check with your e-mail administrators (on your system) to verify that you are allowed to email HTML and XML files using your mail server.

cynthia

[BobD]

I think the problem may be that you've specified the TYPE option on your FILENAME statement. Try changing it to specify that the attachment is plain text rather than HTML, such as:

filename outfile email
to =...@something.com'
replyto=...@something.com'
subject= "Weekly Report"
attach= ("/home/mydocs/coverage.xls" content_type="text/plain")
type="text/html";