Database mail

-- enable Database mail
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
--1.
exec msdb.dbo.sysmail_add_account_sp
    @account_name = N'account',
    @description = N'Mail account for account e-mail.',
    @email_address = N'account@microsoft.com',--your e-mail address
    @display_name = N'account',
    @mailserver_name = N'smtphost.redmond.corp.microsoft.com',-- stmp server
    @username = N'account',
    @password = N'xxxxxx'  
--2.
declare @profileid int = 0 
exec msdb.dbo.sysmail_add_profile_sp
@profile_name = N'profile',
@description = N'Fucheng Zhu',
@profile_id =@profileid output;
--3.
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_id = @profileid,
@account_name = N'account',
@sequence_number = 1 ;

--4.
DECLARE @tableHTML nvarchar(max)
SET @tableHTML =
    N'<H1>Filter Info Report</H1>' +
    N'<table border="1">' +  
    N'<tr><th>Name</th><th>Total Count</th><th>filter Count</th></tr>' +   
    CAST ( (  select td=FileName, '',
          td=TotalCount, '',
          td=filterCount ,''
          from test.dbo.FilterInfo
          FOR XML PATH('tr') ,TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

--5.
exec msdb.dbo.sp_send_dbmail
@profile_name = N'profile',
@recipients = N'recipient1@gmail.com;recipient2@qq.com',
@copy_recipients = N'account@microsoft.com',
@subject = N'Testing  SQL Send e-mail',
@body = @tableHTML,
@body_format = 'HTML',
@importance = N'High'

--6.
exec msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = N'profile',
 @account_name = N'account' ;
 
--7.
exec sysmail_delete_profileaccount_sp
@profile_name = N'profile'

--8.
exec sysmail_delete_account_sp @account_name=N'account'

--9.
exec msdb.dbo.sysmail_delete_log_sp null,null