Email Alert with an Excel attachment

GO

IF OBJECT_ID (N'dbo.accountCRN', N'U') IS NOT NULL

DROP TABLE dbo.accountCRN;

GO

create table accountCRN

(

Name varchar(10),

Value varchar(max)

)

Insert into accountCRN

Exec [P_CustomerExcludeAccount]

DECLARE

      

       @File varchar(20),

       @Subject varchar(20),

       @Separator NCHAR(1) = CHAR(9), -- ASCII Tab character

       @SqlStatement NVARCHAR(MAX) = '

              SET NOCOUNT ON

             

                     SELECT * from accountCRN '

                    

     

SET @File = 'File' + RIGHT('0' + RTRIM(MONTH(GETDATE())), 2) +

                               CONVERT(CHAR(2),GETDATE(),103) +

                               SUBSTRING(CONVERT(VARCHAR(4),DATEPART(YY, GETDATE())),3,2) + '.csv'

SET @Subject = 'CUSTCRN_EXCLUDE_ACTT' + RIGHT('0' + RTRIM(MONTH(GETDATE())), 2) + '-' +

                               CONVERT(CHAR(2),GETDATE(),103) + '-' +

                               SUBSTRING(CONVERT(VARCHAR(4),DATEPART(YY, GETDATE())),3,2)

      

EXEC msdb.dbo.sp_send_dbmail

       @profile_name='ETL-Package',

       @recipients=’emailname’,

       @subject=@Subject,

       @query=@SqlStatement,

       @execute_query_database='dbname',

       @query_result_separator=@Separator,

       @query_result_header=0,

       @query_result_no_padding=1,

       @attach_query_result_as_file=1,

       @query_attachment_filename=@File

To send the query attachment in to the excel

DECLARE @separator VARCHAR(1)

SET @separator = CHAR(9)

EXEC MSDB..sp_send_dbmail

@recipients = 'xxxx'

,@subject = 'test'

,@body = 'Please refer the attached text'

,@body_format = 'TEXT'

,@query = 'SET NOCOUNT ON;SELECT TOP 10 * FROM sometable'

,@query_attachment_filename = 'sometable.xls'

,@attach_query_result_as_file = 1

,@query_result_separator = @separator

,@query_result_no_padding = 1

,@query_result_width = 65535