How to send a CSV or XLS file as attachment using SQL Database mail with proper formatting

Today I encountered a weird (may be not) problem…. One of my colleagues was asked to generate a CSV file and email it to someone. He was getting ready to create a SSIS package with data flow task and everything. I thought why to waste time doing this when there is functionality available in SQL Server. Here you go…

Now please remember that the parameter @query_result_separator = ' ' needs to be re-typed to TAB if you are copy pasting the code. When you copy and paste the formatting in SSMS resets it to something else and the formatting in CSV file is all messed up

DECLARE @sub VARCHAR(100)

DECLARE @qry VARCHAR(1000)

DECLARE @msg VARCHAR(250)

DECLARE @query NVARCHAR(1000)

DECLARE @query_attachment_filename NVARCHAR(520)

SELECT @sub = 'TEST XML ATTACHMENT'

SELECT @msg = 'Please refer to the attached spread sheet for the report.'

SELECT @query = ' SET NOCOUNT ON;

            Select top 10 * from master..sysobjects WITH(NOLOCK)  '

SELECT @query_attachment_filename = 'test.csv'

EXEC msdb.dbo.sp_send_dbmail

            @profile_name = 'SQLMAIL',

            @recipients = 'sqldba@sqldba.com',

            @copy_recipients = 'sqldba@sqldba.com',

            @body = @msg,

            @subject = @sub,

            @query = @query,

            @query_attachment_filename = @query_attachment_filename,

            @attach_query_result_as_file = 1,

            @query_result_header = 1,

            @query_result_width = 256 ,

            @query_result_separator = '   ' ,

            @query_result_no_padding =1;