here is my script for Dbmail.
--DECLARE @S VARCHAR(max)
--SET @S = 'Data for '+CONVERT(VARCHAR(12),GETDATE(),107)
DECLARE
@S VARCHAR(max),
@query_result_separator CHAR(09),
@query_result_width SMALLINT,
@Qry2 VARCHAR(2048)
SET @S = 'XXXXXX_'+CONVERT(VARCHAR(12),GETDATE(),107)
SET @query_result_separator = CHAR(9)
SET @query_result_width = 32767
SET @Qry2 = ' SET NOCOUNT ON;
exec DbName.dbo.USP_SP_NAME'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'xxx',
@recipients = 'xxxx'
, @subject = 'xxxxxx '
, @subject =@s
, @body = 'xxxxxx'
, @query_attachment_filename = 'report.csv'
, @attach_query_result_as_file = 1
, @query_result_header = 1
, @query_result_width = @query_result_width
, @append_query_error = 1
, @query_result_no_padding = 1
, @query_result_separator = @query_result_separator
, @query = @Qry2