sp_OACreate
Creates an instance of the COM object on an instance of MS SQL Server

sp_OADestroy
Destroys a created COM object

sp_OAGetProperty
Gets a property value of a COM object.

sp_OASetProperty
Sets a property value of a COM object to a new value.

sp_OAMethod
Calls a method of a COM object.
sp_OAGetErrorInfo
Gets the exception information from OLE automation.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', 'Tester<test@adminsystem.com>'
EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', 'test@adminsystem.com'
EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', '<test@adminsystem.com>'
/* Using AddRecipientEx to add To, Cc and Bcc in SQL
 Multiple addresses are separated with (,)
 The syntax is like this: "test@adminsystem.com, test1@adminsystem.com"*/
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL, 'test1@adminsystem.com, test2@adminsystem.com', 0
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL,
                        'Test1<test@adminsystem.com>, Test2<test2@adminsystem.com>', 0
/* You can also add carbon copy (CC) or blind carbon copy (BCC) in the email.*/
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL, 'CC recipient<cc@adminsystem.com>', 1
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL, 'Bcc recipient<bcc@adminsystem.com>', 2

EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', 'Tester <test@emailarchitect.net>'
/* Set the Reply-To address*/
EXEC @hr = sp_OASetProperty @oSmtp, 'ReplyTo', 'replyto@@emailarchitect.net'
/* Set the email address to receive delivery report */
EXEC @hr = sp_OASetProperty @oSmtp, 'ReturnPath', 'report@emailarchitect.net'
/* Set high priority */
EXEC @hr = sp_OASetProperty @oSmtp, 'Priority', 1

EXEC @hr = sp_OASetProperty @oSmtp, 'BodyFormat', 1

/* Send email by normal TCP/IP without SSL connection */
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 25

/* Send email by SSL connection with STARTTLS command switching */
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 25
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 2

/* Send email by SSL connection with direct SSL */
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 465
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 3

/* Send email by SSL/TLS connection with auto-detect.*/
/* If port is 25 or 587, STARTTLS TLS will be used; otherwise direct SSL will be used. */

EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 465
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 1

EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 25
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 1

/* 4 means if server supports SSL/TLS connection, SSL/TLS is used automatically */
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 25
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 4

EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', 'localhost'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', 587
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 4

DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

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

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.'

/*User and password for ESMTP authentication, if your server doesn't require
User authentication, please set @User and @Password to '' */
DECLARE @User nvarchar(128)
Set @User = 'test@emailarchitect.net'
DECLARE @Password nvarchar(128)
Set @Password = 'testpassword'

/* Set SSL connection*/
DECLARE @SSL int
Set @SSL = 1

/* Set 465 port */
DECLARE @Port int
Set @Port = 465
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

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

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.'

/*User and password for ESMTP authentication, if your server doesn't require
User authentication, please set @User and @Password to '' */
DECLARE @User nvarchar(128)
Set @User = 'test@emailarchitect.net'
DECLARE @Password nvarchar(128)
Set @Password = 'testpassword'

/* Set SSL connection*/
DECLARE @SSL int
Set @SSL = 1

/* Set 25 port */
DECLARE @Port int
Set @Port = 25

exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

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

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

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

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from Gmail account'

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 = 'gmailid@gmail.com'

/* Create app password in Google account*/
/* https://support.google.com/accounts/answer/185833?hl=en */
DECLARE @Password nvarchar(128)
Set @Password = 'your app password'

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

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

PRINT 'start to send email ...'
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

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

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

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

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from SQL server using gmail account'

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 = 'gmailid@gmail.com'

/* Create app password in Google account*/
/* https://support.google.com/accounts/answer/185833?hl=en */
DECLARE @Password nvarchar(128)
Set @Password = 'your app password'

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

/* Use 587 port */
DECLARE @Port int
Set @Port = 587

PRINT 'start to send email ...'
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

/* Yahoo SMTP server address */
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.mail.yahoo.com'

/* Set your Yahoo email address */
DECLARE @From nvarchar(128)
Set @From = 'myid@yahoo.com'

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

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from Yahoo account'

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

/* For example: your email is "myid@yahoo.com", then the user should be "myid@yahoo.com" */
DECLARE @User nvarchar(128)
Set @User = 'myid@yahoo.com'
DECLARE @Password nvarchar(128)
Set @Password = 'yourpassword'

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

/* Because yahoo deploys SMTP server on 465 port with direct SSL connection.
  So we should change the port to 465. */
DECLARE @Port int
Set @Port = 465

PRINT 'start to send email ...'
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

/* Yahoo SMTP server address */
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.mail.yahoo.com'

/* Set your Yahoo email address */
DECLARE @From nvarchar(128)
Set @From = 'myid@yahoo.com'

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

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from Yahoo account'

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

/* For example: your email is "myid@yahoo.com", then the user should be "myid@yahoo.com" */
DECLARE @User nvarchar(128)
Set @User = 'myid@yahoo.com'
DECLARE @Password nvarchar(128)
Set @Password = 'yourpassword'

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

/*set 587 port*/
DECLARE @Port int
Set @Port = 587

PRINT 'start to send email ...'
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

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

/* Set your Hotmail email address */
DECLARE @From nvarchar(128)
Set @From = 'liveid@hotmail.com'

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

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from Hotmail/MSN Live account'

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

/* For example: your email is "liveid@hotmail.com", then the user should be "liveid@hotmail.com" */
DECLARE @User nvarchar(128)
Set @User = 'liveid@hotmail.com'

/* If you got authentication error, try to create an app password instead of your user password.*/
/* https://support.microsoft.com/en-us/account-billing/using-app-passwords-with-apps-that-don-t-support-two-step-verification-5896ed9b-4263-e681-128a-a6f2979a7944 */
DECLARE @Password nvarchar(128)
Set @Password = 'your password or app password'

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

/* Because Live Server deploys SMTP server on 25/587 port with TLS connection.
  So we should change the port to 25 or 587. */
DECLARE @Port int
Set @Port = 587

PRINT 'start to send email ...'
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

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

/* Set your office365 email address */
DECLARE @From nvarchar(128)
Set @From = 'myid@mydomain'

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

DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from Office365 account'

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

/* For example: your email is "myid@mydomain", then the user should be "myid@mydomain" */
DECLARE @User nvarchar(128)
Set @User = 'myid@mydomain'
DECLARE @Password nvarchar(128)

/* If you got authentication error, try to create an app password instead of your user password.*/
/* https://support.microsoft.com/en-us/account-billing/using-app-passwords-with-apps-that-don-t-support-two-step-verification-5896ed9b-4263-e681-128a-a6f2979a7944 */
Set @Password = 'your password or app password'

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

/* Use 587 port */
DECLARE @Port int
Set @Port = 587

PRINT 'start to send email ...'
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

/* Set SMTP server address to '' */
DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = ''

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

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

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 directly, do not reply.'

/* Do not set user authentication */
DECLARE @User nvarchar(128)
Set @User = ''
DECLARE @Password nvarchar(128)
Set @Password = ''

/* Do not set  SSL connection*/
DECLARE @SSL int
Set @SSL = 0

/* Set 25 default SMTP port */
DECLARE @Port int
Set @Port = 25

exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL, @Port

CREATE TABLE [dbo].[rcpts](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [email] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_rcpts] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

DECLARE @email nvarchar(128)
DECLARE rcpt_cursor CURSOR FOR
SELECT email
FROM rcpts
OPEN rcpt_cursor
/* select all records from rcpts table and send it one by one */
FETCH NEXT FROM rcpt_cursor
INTO @email

DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

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

/*User and password for ESMTP authentication, if your server doesn't require
User authentication, please set @User and @Password to '' */
DECLARE @User nvarchar(128)
Set @User = 'test@emailarchitect.net'
DECLARE @Password nvarchar(128)
Set @Password = 'testpassword'

/* If your smtp server requires SSL connection, please set @SSL = 1*/
DECLARE @SSL int
Set @SSL = 0

DECLARE @Port int
Set @Port = 25

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @email
    DECLARE @subject nvarchar(255)
    SELECT @Subject = 'test email for' + @email
    EXEC usp_SendTextEmail @ServerAddr,
            @from, @email, @subject, @BodyText, @User, @Password, @SSL, @Port
    FETCH NEXT FROM rcpt_cursor
    INTO @email
END
CLOSE rcpt_cursor
DEALLOCATE rcpt_cursor

CREATE PROCEDURE [dbo].[usp_SendMailToQueue]  @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

/* Most mordern SMTP servers require SSL/TLS connection now*/
/* 1 means use SSL/TLS based on server port, 4 means if server supports SSL/TLS connection, SSL/TLS is used automatically */
If @SSLConnection > 0
    BEGIN
        EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 1
    END
ELSE
    BEGIN
        EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 4
    END

EXEC @hr = sp_OAMethod @oSmtp, 'SendMailToQueue', @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 @description
    PRINT 'Please make sure you have EASendMail Service installed'
END
ELSE
BEGIN
    PRINT 'Message submitted!'
END
EXEC @hr = sp_OADestroy @oSmtp
Go

DECLARE @email nvarchar(128)
DECLARE rcpt_cursor CURSOR FOR
SELECT email
FROM rcpts
OPEN rcpt_cursor
FETCH NEXT FROM rcpt_cursor
INTO @email

DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

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

/*User and password for ESMTP authentication, if your server doesn't require
User authentication, please set @User and @Password to '' */

DECLARE @User nvarchar(128)
Set @User = 'test@emailarchitect.net'
DECLARE @Password nvarchar(128)
Set @Password = 'testpassword'

/* If your smtp server requires SSL connection, please set @SSL = 1*/
DECLARE @SSL int
Set @SSL = 0

DECLARE @Port int
Set @Port = 25

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @email
    DECLARE @subject nvarchar(255)
    SELECT @Subject = 'test email for ' + @email
    EXEC usp_SendMailToQueue @ServerAddr,
            @from, @email, @subject, @BodyText, @User, @Password, @SSL, @Port
    FETCH NEXT FROM rcpt_cursor
    INTO @email
END
CLOSE rcpt_cursor
DEALLOCATE rcpt_cursor

CREATE TABLE [dbo].[rcpts](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [email] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_rcpts] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[errorlog](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [email] [nvarchar](128) NULL,
    [server] [nvarchar](50) NULL,
    [errorcode] [nvarchar](50) NULL,
    [errordescription] [nvarchar](255) NULL,
 CONSTRAINT [PK_errorlog] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sentlog](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [server] [nvarchar](50) NULL,
    [email] [nvarchar](128) NULL,
 CONSTRAINT [PK_sentlog] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/* change it to your server address */
DECLARE @ServerAddr nvarchar(128)
SET @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
SET @From = 'Tester <test@adminsystem.com>'

/* change it to your sql server address, database, user and password */
/* The server/instance name syntax used in the server option is the same for all SQL Server connection strings.*/
/* e.g.: Server=serveraddress\instancename;*/
/* SQL 2005 */
DECLARE @DataConn nvarchar(255)
SET @DataConn = 'Driver={SQL Native Client};Server=serveraddress;Database=database;Uid=user;Pwd=password;'

/* MS SQL Server 2005 Native Provider */
/*SET @DataConn = 'Provider=SQLNCLI;Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* using SQL 2008 Driver*/
/*SET @DataConn = 'Driver={SQL Server Native Client 10.0};Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* MS SQL Server 2008 Native Provider */
/*SET @DataConn = 'Provider=SQLNCLI10;Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* using SQL 2005/2012 Driver*/
/*SET @DataConn = 'Driver={SQL Server Native Client 11.0};Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* MS SQL Server 2012 Native Provider */
/*SET @DataConn = 'Provider=SQLNCLI11;Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* select all records from this table */
DECLARE @SQLSelect nvarchar(255)
SET @SQLSelect = 'SELECT uid, name, email from rcpts'

/* insert all successful record to this table */
DECLARE @SQLSuccess nvarchar(255)
SET @SQLSuccess = 'INSERT INTO sentlog ( server, email )'
    + ' VALUES( ''{$var_server}'', ''{$var_rcptaddr}'' )'

/* insert all failure record to this table */
DECLARE @SQLError nvarchar(255)
SET @SQLError = 'INSERT INTO errorlog( email, server, errorcode, errordescription )' +
    ' VALUES( ''{$var_rcptaddr}'', ''{$var_server}'', ''{$var_errcode}'',  ''{$var_errdesc}'' )'

DECLARE @Subject nvarchar(256)
DECLARE @Bodytext nvarchar(max)
SET @Bodytext = ''

/*User and password for ESMTP authentication, if your server doesn't require
User authentication, please set @User and @Password to '' */
DECLARE @User nvarchar(128)
Set @User = 'test@emailarchitect.net'
DECLARE @Password nvarchar(128)
Set @Password = 'testpassword'

/* If your smtp server requires SSL connection, please set @SSL = 1*/
DECLARE @SSL int
Set @SSL = 0

DECLARE @Port int
Set @Port = 25

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_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Data-Connection', @DataConn
EXEC @hr = sp_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Sql-Select', @SQLSelect
EXEC @hr = sp_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Sql-OnSentSuccess', @SQLSuccess
EXEC @hr = sp_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Sql-OnSentError', @SQLError
EXEC @hr = sp_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Rcpt-To', '{$var_srecord:email}'
EXEC @hr = sp_OASetProperty @oSmtp, 'DisplayTo', '"{$var_srecord:name}" <{$var_srecord:email}>'

EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', @ServerAddr
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', @Port
EXEC @hr = sp_OASetProperty @oSmtp, 'UserName', @User
EXEC @hr = sp_OASetProperty @oSmtp, 'Password', @Password
EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', @From
SET @Subject = 'test email with data pick'
EXEC @hr = sp_OASetProperty @oSmtp, 'Subject', @Subject
SET @BodyText = 'Test email, do not reply, your id in database is {$var_srecord:uid}.'
EXEC @hr = sp_OASetProperty @oSmtp, 'BodyText', @BodyText

/* Most mordern SMTP servers require SSL/TLS connection now*/
/* 1 means use SSL/TLS based on server port, 4 means if server supports SSL/TLS connection, SSL/TLS is used automatically */
If @SSL > 0
    BEGIN
        EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 1
    END
ELSE
    BEGIN
        EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 4
    END

EXEC @hr = sp_OAMethod @oSmtp, 'SendMailToQueue', @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
    PRINT 'Please make sure you have EASendMail Service installed!'
    EXEC @hr = sp_OAMethod @oSmtp, 'GetLastErrDescription', @description OUT
    PRINT @description
END
ELSE
BEGIN
    PRINT 'Message has been submitted to EASendMail Service!'
END
EXEC @hr = sp_OADestroy @oSmtp
Go