Loading

send email with excel attachment using sql stored procedure

send email with excel attachment using sql stored procedure

Sonali Avhad

5y18.2kLearn iOS Programming

I using sql stored procedure for sending email with attachment.

I want to send email with excel attachment.

I used the following code

DECLARE @Body VARCHAR(8000)
DECLARE @Qry varchar(8000)
SET @Body ='Hi PFA,'
SET @Qry='set nocount on;select * from tblname'
DECLARE
@tab char(1) = CHAR(9)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profilename' ,
@recipients = 'receipentemail',
@body= @Body,
@query=@Qry,
@subject='Send db mailer with attachment',
@attach_query_result_as_file = 1,
@query_attachment_filename='Report.xls'

but in excel is not proper it is disturbed columns and rows.

Please help me for proper excel attachment.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
this is how it should be done
declare @qry varchar(8000)
declare @column1name varchar(50)
-- Create the column name with the instrucation in a variable
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'customerid]'

-- Create the query, concatenating the column name as an alias
select @qry='set nocount on;select customerid ' + @column1name +
             ' ,companyname, contactname, country from customers'

-- Send the e-mail with the query results in attach
exec msdb.dbo.sp_send_dbmail @recipients="Your email",
@query=@qry,
@subject='Client list',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'result.csv',
@query_result_separator=',',@query_result_width =32767,
@query_result_no_padding=1
+2