OK,

I was able to make this work with SQL Server 2014 and Excel 2013.

First, here's the sp_send_dbmail call:

exec msdb.dbo.sp_send_dbmail

@profile_name = 'dbmail'

, @recipients = N'XYZ@abc.com'

, @subject = N'T-SQL Query Result'

, @body = N'The result is attached'

, @query = N'select top 10 * from sys.objects;'

, @attach_query_result_as_file = 1

, @query_attachment_filename= 'Test.xls'

, @query_result_width = 32767

;

With extension ".xls", when I try to open the file with Excel 2013, I get this message:

[highlight="#DDDDDD"][font="System"]The file format and extension of 'Test (003).xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?

[Yes] [No] [Help][/font][/highlight]

I click [Yes], and the file opens.

With extension ".xlsx", when I try to open the file with Excel 2013, I get this message:

[highlight="#DDDDDD"][font="System"]Excel cannot open the file 'Test.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension maches the format of the file.

[OK][/font][/highlight]

I can only click [OK], and Excel does not open the file.

I added "@query_result_width = 32767", because at first I was getting extra line breaks that were causing each query result row to be split across multiple Excel rows. With "@query_result_width = 32767" each one query result row is one Excel row.

Now I can open the file properly in Excel. Then I select column A and use "DATA > Data Tools > Text to Columns > Fixed Width..." to convert the text lines to real Excel cells. Then I format the datetime columns with Custom: yyyy-mm-dd HH:mm:ss.000 and delete row 2 with the hyphen underlines for the column headers.