1. Home
  2. EASendMail SMTP Component
  3. KB
  4. MS SQL Server
  5. Send Email using Hotmail/MSN Live/Oulook/Office365 in SQL Server Stored Procedure

Tutorial Index

Send Email using Hotmail/MSN Live/Oulook/Office365 in SQL Server Stored Procedure

In previous section, I introduced how to send email using Yahoo account. In this section, I will introduce how to use your Hotmail/MSN Live/Outlook.com/Office365 account to send email in SQL stored procedure.

Introduction

Hotmail/MSN Live/Outlook.com SMTP server address is smtp.office365.com. It requires TLS connection to do user authentication, and you should use your Hotmail/MSN Live/Outlook.com email address as the user name for ESMTP authentication. For example: your email is liveid@hotmail.com, and then the user name should be liveidmyid@hotmail.com.

Server Port SSL/TLS
smtp.office365.com 25, 587 TLS

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 Hotmail/MSN Live account over TLS connection]

The following example codes demonstrate how to use EASendMail SMTP component to send email using Hotmail/MSN Live account.

Note

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

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

Hotmail SMTP OAUTH

If your account enabled two-factor authentication, you cannot login your account by normal user authentication, you should use SMTP OAUTH or App Password.

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

Using Microsoft Hotmail SMTP OAUTH

Or you can generate App Passwords and use this app password instead of your user password.

Send Email using Office 365

Office 365 SMTP server uses 587 port and explicit SSL (TLS) connection.

Server Port SSL/TLS
smtp.office365.com 25, 587 (recommended) TLS

App Password and SmtpClientAuthenticationDisabled

If your account enabled two-factor authentication, you cannot login your account by normal user authentication, you should create an App Passwords and use this App Password instead of the user password.

You should also check if authenticated client SMTP submission (SMTP AUTH) is enabled:

Enable or disable authenticated client SMTP submission (SMTP AUTH) in Exchange Online.

[SQL Stored Procedure Example - Send email using Office365 account over TLS connection]

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

Note

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

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

Office365 SMTP/EWS/Ms Graph API OAUTH

If your account enabled two-factor authentication, you cannot login your account by normal user authentication, you should use SMTP/EWS/Ms Graph API OAUTH or App Password.

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

Using Microsoft Office365 EWS OAUTH

Or you can generate App Passwords and use this app password instead of your user password.

Next Section

At next section I will introduce how to send email without specified SMTP server.

Appendix

Comments

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

See Also