SQL send mail-HTML format

ALTER PROCEDURE dbo.sx_pro_autoemailcontent

As

Begin

DECLARE @Rqty int

DECLARE @n int

DECLARE @m_recipients varchar (1000)

DECLARE @m_copy_recipients varchar (1000)

DECLARE @m_Query varchar (1000)

DECLARE @m_Body varchar (1000)

DECLARE @m_subject varchar (50)

DECLARE @m_AttachAsFile int

DECLARE @m_FileName varchar (50)

DECLARE @m_Frequency varchar (10)

DECLARE @tableHTML NVARCHAR (MAX)

Set @Rqty = (select count (*) from Sx_bas_autoemail_list)

Set @n=1

While @n<[email protected]

Begin

Select

@m_recipients =recipients,

@m_copy_recipients =copy_recipients,

@m_Query =sqlstr,

@m_Body =body,

@m_subject =subject,

@m_AttachAsFile =attachasfile,

@m_FileName =file_name,

@m_Frequency =frequency from sx_bas_autoemail_list WHERE [email protected]

If @m_Frequency = ' Daily ' or (@m_Frequency = ' Monthly ' and Day (getdate ()) =1) or (@m_Frequency = ' Weekly ' and DatePart (DW, GETDATE ()) =1)

Begin

EXEC Msdb.dbo.sp_send_dbmail

@profile_name = ' Db_automail ',

@recipients = @m_recipients,

@copy_recipients [Email protected]_copy_recipients,

@[email Protected]_query,

@body = @m_Body,

@subject [Email Protected]_subject,

@execute_query_database = ' sxquadb ',

@attach_query_result_as_file [Email Protected]_attachasfile,

@[email Protected]_filename;

[Email Protected]_format = ' HTML ';

End

Set @[email protected]+1

End

[Email protected]_name = ' Saeg_db_automail ',

End

SQL send mail-HTML format