send mail through SQL

SQL Code Snippets to send emailBelow are snippets which can be used to send emails from SQL Server Note: you need to set the email parameters like the SMTP server e.t.c. wherever mentioned.

--Start SP # 1 Send Mail through SQL

CREATE PROCEDURE usp_SMTPMail
    @SenderName varchar(100),
    @SenderAddress varchar(100),
    @RecipientName varchar(100),
    @RecipientAddress varchar(100),
    @Subject varchar(200),
    @Body varchar(8000),
    @MailServer varchar(100) = '' --Specify the smtpserver with domain here
AS    
SET nocount on
--Set up the procedure and the parameters. 
    declare @oMail int --Object reference
    declare @resultcode int
    EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
--sp_OACreate has an output parameter that returns a reference to the object instance, this is used to assign parameters. The result code is 0 for success. 
    if @resultcode = 0
    BEGIN
        EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost',  @mailserver
        EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
        EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress',  @SenderAddress
--sp_OASetProperty takes the reference to the object (@oMail), takes the property name and sets the value. 
EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName,  @RecipientAddress
--sp_OAMethod calls a method of the object. We pass the object reference, then the method name "AddRecipient". The next parameter is for returning a value from a method. In this case I don't want one so I pass it a null. After that I pass the parameters of the method.
  The "AddRecipient" method has Name and Email Address parameters so I am passing them in. 
        EXEC
  @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
        EXEC @resultcode
  = sp_OASetProperty @oMail, 'BodyText', @Body
        EXEC @resultcode =
  sp_OAMethod @oMail, 'SendMail', NULL
--Similar code here, we set the subject and message body, then call the "SendMail"
  method which sends the email. 
        EXEC sp_OADestroy @oMail
    END    
    SET
  nocount off
--sp_OADestory cleans up and destroys the reference to the object. The object is
  meant to be destroyed when the procedure finishes, but I always like to clean
  up after myself. 


--To use this procedure, call it like this. 
exec usp_SMTPMail @SenderName='me', @SenderAddress='dummyuser@gmail.com', 
@RecipientName = 'dummyuserreceipient@gmail.com', @RecipientAddress = 'dummyuserreceipient@gmail.com', 
@Subject='SQL Test', @body='Hello, this is a test email from SQL Server'

--End SP # 1 Send Mail through SQL