How To Send Mail Using SQL Server

Create procedure as.....

ALTER PROCEDURE [dbo].[usp_SendTextEmail]  @ServerAddr nvarchar(128),
@From nvarchar(128),
@To nvarchar(1024),
@Subject nvarchar(256),
@Bodytext nvarchar(max) = 'This is a test text email from MS SQL server, do not reply.',
@User nvarchar(128) = '',
@Password nvarchar(128) = '',
@SSLConnection int = 0,
@ServerPort int = 25

AS

DECLARE @hr int
DECLARE @oSmtp int
DECLARE @result int
DECLARE @description nvarchar(255)

EXEC @hr = sp_OACreate 'EASendMailObj.Mail',@oSmtp OUT 
If @hr <> 0 
BEGIN
    PRINT 'Please make sure you have EASendMail Component installed!'
    EXEC @hr = sp_OAGetErrorInfo @oSmtp, NULL, @description OUT
    IF @hr = 0
    BEGIN
        PRINT @description
    END
    RETURN
End

EXEC @hr = sp_OASetProperty @oSmtp, 'LicenseCode', 'TryIt'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', @ServerAddr
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', @ServerPort

EXEC @hr = sp_OASetProperty @oSmtp, 'UserName', @User
EXEC @hr = sp_OASetProperty @oSmtp, 'Password', @Password

EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', @From

EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL,  @To, 0

EXEC @hr = sp_OASetProperty @oSmtp, 'Subject', @Subject 
EXEC @hr = sp_OASetProperty @oSmtp, 'BodyText', @BodyText 


If @SSLConnection > 0 
BEGIN
    EXEC @hr = sp_OAMethod @oSmtp, 'SSL_init', NULL
END

/* you can also add an attachment like this */
/*EXEC @hr = sp_OAMethod @oSmtp, 'AddAttachment', @result OUT, 'd:\test.jpg'*/
/*If @result <> 0 */
/*BEGIN*/
/*   EXEC @hr = sp_OAMethod @oSmtp, 'GetLastErrDescription', @description OUT*/
/*    PRINT 'failed to add attachment with the following error:'*/
/*    PRINT @description*/
/*END*/

PRINT 'Start to send email ...' 

EXEC @hr = sp_OAMethod @oSmtp, 'SendMail', @result OUT  

If @hr <> 0 
BEGIN
    EXEC @hr = sp_OAGetErrorInfo @oSmtp, NULL, @description OUT
    IF @hr = 0
    BEGIN
        PRINT @description
    END
    RETURN
End

If @result <> 0 
BEGIN
    EXEC @hr = sp_OAMethod @oSmtp, 'GetLastErrDescription', @description OUT
    PRINT 'failed to send email with the following error:'
    PRINT @description
END
ELSE 
BEGIN
    PRINT 'Email was sent successfully!'
END

EXEC @hr = sp_OADestroy @oSmtp
============================================================

Call above procedure as .......


/* Gmail SMTP server address */
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.gmail.com'

/* Set your Gmail email address */
DECLARE @From nvarchar(128)
Set @From = 'xyz@gmail.com'

DECLARE @To nvarchar(1024)
/*You can input multiple recipients and use comma (,) to separate multiple addresses */
Set @To = 'abc@gmail.com'

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from SQL SERVER'

DECLARE @Bodytext nvarchar(512)
Set @BodyText = 'This is a test text email from MS SQL server, do not reply.'

/* Gmail user authentication should use your 
 Gmail email address as the user name. 
For example: your email is "gmailid@gmail.com", then the user should be "gmailid@gmail.com" */
DECLARE @User nvarchar(128)
Set @User = 'xyz@gmail.com'

DECLARE @Password nvarchar(128)
Set @Password = 'pwd#123'

/* Enable SSL/TLS */
DECLARE @SSLConnection int
Set @SSLConnection = 1

/* If you want to use TLS, please set it to 25 or 587 */
DECLARE @ServerPort int
Set @ServerPort = 587

PRINT 'start to send email ...'


exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSLConnection, @ServerPort