Send Mail from SQL Query

Send Mail from SQL Query
We can send mail by different ways, but they are mostly slow in execution. But if we make a table in database with mail attributes like sender, receiver, subject, body etc and run a procedure to send one by one from the table then it wont harm our  project execution time. n joy


--------Enable DatabaseMail Option -------------

use databasename
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure 
go
--------Database Mail option Enabled --------------

--------set gmail account for sending an email -----

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'admin',
    @description = 'Mail account for Database Mail',
    @email_address = 'romeoacharya@gmail.com',
    @display_name = 'ProjectName',
 @username='Email',                           --give your valid gmail id
 @password='PASSWORD',               --give your valid gmail password
    @mailserver_name = 'smtp.gmail.com',
 @port = 587,
 @enable_ssl = 1

------account configured created------

--------------Create Profile --------------
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = ' ProfileName',              --remeber the  ProfileName we will use it every where
       @description = 'Profile used for database mail'
--------------Profile  Created  --------------

------add database mail account to mail profile -----------
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = ' ProfileName ',
    @account_name = 'admin',
    @sequence_number = 1
------add database mail account to mail profile -----------

------  Now grant the Database Mail profile access to the msdb public ...
------ ...database role and to make the profile the default Database Mail profile

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = '  ProfileName ',
    @principal_name = 'public',
    @is_default = 1 ;

----------------------------------------------------------------------------------------

--To send an email use

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'romeoacharya@gmail.com',@body= "This is <b>HTML</b> body", 
@subject ='Email from database ASPNET website', @body_format = 'HTML' ,
@profile_name ='  ProfileName '



select * from msdb.dbo.sysmail_event_log
select * from msdb.dbo.sysmail_mailItems

--------------------------Procedure Example----------------------------------------------

Create proc [dbo].[sendmail_after_user_registration]
(
@profilename varchar(100),
@accountname varchar(100),
@recipentname varchar(200),
@sendername varchar(200),
@bodycontent nvarchar(max),
@sub varchar(max)
)
as
begin
--    exec msdb.dbo.sp_configure 'show advanced options', 1          --The left side green back code 
-- reconfigure                                                                             --Should run only once to enable 
-- exec msdb.dbo.sp_configure 'Database Mail XPs', 1               --Database mail options
-- reconfigure
IF exists
(SELECT * FROM msdb.dbo.sysmail_profileaccount pa JOIN msdb.dbo.sysmail_profile p
ON pa.profile_id = p.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = @profilename AND a.name = @accountname)
     begin
         EXEC msdb.dbo.sp_send_dbmail
         @profile_name = @profilename,
@recipients=@recipentname,
         @subject = @sub,
@body= @bodycontent,
         @body_format = 'HTML'
     end
else
begin
-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @accountname,
@description = 'Mail account for administrative e-mail.',
@email_address = @sendername,
@replyto_address = @sendername,
@display_name = 'ProjectName',
@mailserver_name = 'smtp.gmail.com',
@port = 587,
@username = @sendername,
@password = 'PASSWORD',       --give your valid gmail password
@enable_ssl = 1
-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @profilename,
@description = 'Profile used for administrative mail.'

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profilename,
@account_name = @accountname,
@sequence_number =1

-- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @profilename,
@principal_name = 'public',
@is_default = 1
   --send mail
         EXEC msdb.dbo.sp_send_dbmail
         @profile_name = @profilename,
@recipients=@recipentname,
         @subject = @sub,
@body= @bodycontent,
         @body_format = 'HTML'
end
end

------------------------------------                           N-joy                               -------------------------------------