set delimiter when using sp_send_dbmail (csv file)

Question

I have a query that brings me back a result set but when I use sp_send_dbmailto send an email to someone containing this result set as a CSV file it opens in excel in the incorrect format! I know that I can correct this format through excel but I don't want the user to do that! I want them to just be able to open the file and everything be visible in the correct format. Below shows how I am creating the CSV file and emailing it out the someone (I am also specifying the seperator but it doesn't work and I can't figure out why):

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='TestProfile',
@recipients='[email protected]',
@subject='Test message',
@body='This is a test.',

@query = 'Select firstName, LastName, Address, Score from TestData.dbo.Student',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_result_separator = ',',
@query_result_width = 25,
@query_attachment_filename = 'Test.csv',
@query_result_no_padding = 1

Once the CSV File is received and opened all the data is represented in the first column, which isn't the desired results!

Screenshot of my list seperator settings

Answer 1

I struggled with this issue for a couple of days , but finally got it to work

@query_result_separator =' ', did the trick, it's TAB as the result separator.

Full code EXEC msdb.dbo.sp_send_dbmail

              @profile_name ='MailProfile',
              @from_address = '[email protected]',
              @recipients = '[email protected]',
              @body = @varBody,
              @body_format = 'HTML',
              @execute_query_database ='MyDB',
              @query = @VarSQL,
              @attach_query_result_as_file = 1,
              @query_result_separator ='    ',
              @exclude_query_output =1,
              @query_result_no_padding=1,
              @query_result_header =1,
              @query_attachment_filename ='MyDB.csv'