Send Email using Gmail in SQL Server Stored Procedure

In previous section, I introduced how to send email over SSL connection. In this section, I will introduce how to use your Gmail account to send email in SQL stored procedure.

Introduction

Gmail SMTP server address is smtp.gmail.com. It requires SSL or TLS connection, and you should use your Gmail email address for ESMTP authentication. For example: your email is gmailid@gmail.com, and then the user name should be gmailid@gmail.com.

Server Port SSL/TLS
smtp.gmail.com 25, 587 TLS
smtp.gmail.com 465 SSL

Important

If you got authentication error, you need to enable Allowing less secure apps or Sign in using App Passwords.

Another way is Gmail OAUH, please see Gmail SMTP OAUTH section.

Note

Remarks: All of samples in this section are based on first section: Send email in a simple SQL stored procedure. To run the following example codes successfully, please click here to learn how to create the test enivronment and use EASendMail in your project.

[SQL Stored Procedure Example - Send email using Gmail account over SSL connection on 465 port]

The following example codes demonstrate how to use EASendMail SMTP component to send email using Gmail account.

Note

To get the full sample projects, please refer to Samples section.

/* 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'
DECLARE @Password nvarchar(128)
Set @Password = 'yourpassword'

/* 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

[SQL Stored Procedure Example - Send email using Gmail account over TLS connection on 587 port]

The following example codes demonstrate how to use EASendMail SMTP component to send email using Gmail account.

Note

To get the full sample projects, please refer to Samples section.

/* 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'
DECLARE @Password nvarchar(128)
Set @Password = 'yourpassword'

/* 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

Gmail SMTP OAUTH

The Gmail IMAP and SMTP servers have been extended to support authorization via the industry-standard OAuth 2.0 protocol. Using OAUTH protocol, user can do authentication by Gmail Web OAuth instead of inputting user and password directly in application. This way is more secure, but a little bit complex.

Using Gmail SMTP OAUTH

Next Section

At next section I will introduce how to send email with Yahoo account.

Appendix

Comments

If you have any comments or questions about above example codes, please click here to add your comments.