This sp_send_dbmail script works in one of our processes. It attaches an Excel file filled with whatever the query is. It knows to do this because of the extension on the file's name (.xls).

However, it changes a varchar(50) field into a number field, and removes the leading zeroes. This is a known annoyance dealt with in a million ways that won't work for my process.

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = @profileName
   ,@recipients = @emailRecipientList
   ,@subject = @subject
   ,@importance = @importance
   ,@body = @emailMsg
   ,@body_format = 'html'
   ,@query = @QuerySQL
   ,@execute_query_database = @QueryDB
   ,@attach_query_result_as_file = 1
   ,@query_attachment_filename = @QueryExcelFileName
   ,@query_result_header = 1
   ,@query_result_width = @QueryWidth
   ,@query_result_separator = @QuerySep
   ,@query_result_no_padding = 1

Examples of problem below: this simple query changes the StringNumber column from varchar to number in Excel, and removes the zeroes.

SELECT [RowID],[Verbage], StringNumber FROM [dbo].[tblTestStringNumber]

In SQL Server (desired format):

After in Excel (leading zeroes missing):

Now, there might be a way. I only say this because in SQL Server 2016 results pane, if you right click in upper left hand corner, it gives the option of "Open in Excel"

And. . . . drum roll . . . the dataset opens in Excel and the leading zeroes are still there!