You need a tab delimited file for excel to recognise it and the file extension needs to be .csv (.xls will work but will display an error first).

eg.

DECLARE @query_result_separator CHAR(1) = char(9);

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'test@test.com'

,@body='Hi Team,

Please find attached the status log for today.

Please check the status .

Regards,

Reporting team

'

,@subject ='Status Log for Today'

--,@profile_name ='Status HDS A'

,@query ='SELECT ''dddddddddd'' as columnheader, ''aaaaa'' as col2, ''ffffff'' as col3

UNION ALL

SELECT ''a'',''b'',''c'''

,@attach_query_result_as_file = 1

,@query_attachment_filename ='Status_Log.csv'

,@query_result_separator= @query_result_separator

,@exclude_query_output = 1