Hi:
There are only 2 ways to create an Excel binary workbook with SAS: PROC EXPORT or the Excel LIBNAME engine.
When you use ODS to create markup language files, you are creating ASCII text files that Excel knows how to open and render. Even if you give your ODS file an extension of '.xls', if you look at the output file with Notepad, you will see that it is either a CSV file, an HTML file or a SpreadsheetML file.
Ever since Excel 97, Excel has been able to open and render HTML files, When ODS HTML first came out, many programmers used this "trick" to fool the Windows registry:
[pre]
ods html file="c:\temp\report.xls";
...instead of ...
ods html file="c:\temp\report.html";
[/pre]
That was so that when they double clicked on the file with the .xls extension, the Windows registry would automatically launch Excel and Excel would detect the HTML, but would open the file But, it was really a trick. The "internals" of the file were still HTML -- and, if you did a FILE --> SAVE AS, Excel would show you HTML as the "save as" file type -- so Excel knew that you were trying to fool it, but was showing you that it also knew what the original file type should have been.
There are 3 ways to create a file for Excel using ODS:
1) ODS CSV -- creates a comma separated file that you could open with Excel or Microsoft Access or FoxPro -- or any application that could read CSV files
2) ODS HTML -- creates an HTML file that you can open with Excel or Word -- or any application (including a browser) that can read HTML files
3) ODS TAGSETS.EXCELXP -- creates a Microsoft Spreadsheet Markup Language XML file that you can open with Excel 2002 or higher
In addition, there is a "flavor" of HTML destination, called TAGSETS.TABLEEDITOR, which also allows you to create an HTML file with some embedded JavaScript -- and the JavaScript can launch Excel and display the HTML information.
About your program.... you have ODS HTML at the top and ODS PDF CLOSE at the bottom...this will not work. You should have ODS HTML CLOSE at the bottom. You don't need a DATA _NULL_ to set the path or file name for the report -- you could have set the &REPORT_NAME macro variable in one %LET statement...not sure why you have a %LET inside a DATA step program with a CALL SYMPUT anyway -- sort of apples and oranges.
Also, Scott is correct....your invocation should be:
[pre]
ods html body="&reportname";
...more code...
ods html close;
[/pre]
If you are trying the automatic e-mail with Enterprise Guide, I suspect that even with an XLS file extension, that EG will set the content-type header for the mail as "text/html". If you are using program statements to perform your email, then we'd really need to see the rest of your code, as Scott suggested.
Also, you should be aware of the fact that many sites prohibit the automatic emailing of HTML files through their mail server and force all mail to be plain text. If this is the case with your mail server, you might consider emailing a CSV file, instead.
cynthia