Format Excel Attachment using sp_send_dbmail

• jim.schnur

  Old Hand

  Points: 312

  More actions

  March 18, 2010 at 11:02 am

  #216883

  I would like to have the excel file sent via dbmail formatted to be nice an pretty so that the receiver does not have to run a macro against it. Any clues on how to do this would be appreciated. A code snipped is included below.

  EXEC msdb.dbo.sp_send_dbmail @profile_name = @mailprofile

  ,@recipients = @mailrecipient

  ,@subject = @mailsubject

  ,@body_format = 'HTML'

  ,@body = @mailtext

  ,@query = @sqlstatement

  ,@attach_query_result_as_file = 1

  ,@query_result_no_padding = 1

  ,@query_attachment_filename = @mailfilename

  ,@query_result_width = 1000 -- set query result width

  ,@query_result_separator ='' --this is a tab in between the quotes not a space

  ,@append_query_error = 1

• Ray Dai

  SSCommitted

  Points: 1548

  More actions

  March 19, 2010 at 12:28 pm

  #1136968

  You can BCP that to a CSV file and add it to your email as an attachment.

  Or, rewrite it with SSIS and it will do everything you wanted.

• jim.schnur

  Old Hand

  Points: 312

  More actions

  March 22, 2010 at 4:21 am

  #1137478

  Ray,

  Thanks for the information. I will try BCP to obtain what I need.

• Ray Dai

  SSCommitted

  Points: 1548

  More actions

  March 22, 2010 at 8:24 am

  #1137613

  You are welcome. Let me know how it turns out.