SQL Database Mail - Send Emails from SQL Server

Email is so popular now.   Do you also want to send emails from database applications by using T-SQL statements? Do you want to send a result of T-SQL queries directly to some one else?  If you do, Database Mail is the solution to fulfill your needs.  This post is a brief introduction that demonstrates how you can use Database Mail to send a simple email in T-SQL statements. 

First of all, you need to create a profile that contains all necessary information to send emails.  There are two alternative ways to create a profile:  UI or T-SQL.

Use UI to create a profile

1) Open SQL Server Manament Studio (SSMS), connect to SQL Server, and expand Management.  You will see a picture like below

2) Move the cursor over to “Database Mail” and double-click the cursor to start configuration of Database Mail. 

3) Click "Next" to start creating a profile. 

4) Click "Next" to continue.  If it is the first time to use Database Mail, you may see a message as follows.

This is because Database Mail is not enabled yet.  Click Yes to confirm that you want to enable and use Database Mail.  Once it is confirmed, the message will no longer occur. 

5) Name your profile and fill it in the box as follows.

6) Click "Add" to add an email account into your profile

Database Mail sends emails through SMTP server.  The email account is given in the way about SMTP information.  If you don't know the server name for SMTP at your site, ask your system administrator for it.   This is very important.  You must get the server name right.  If the server name is not correct, emails will fail to send out.  The port number is usually 25 you probably don't need to change.

7) Click "OK" and get back to the profile page.

8) Click "Next" and follow the subsequent next steps to finish creation

You have created a profile successfully!  Now let's test if the profile is really a good one.

a) Go back to SQL Server Management Studio and right-click mouse over Database Mail.

b) Select "Send Test E-Mail..." and type your own email address on "To:" line so that you can confirm if the test email is sent to you. 

You should receive this test email after a while though the time may vary depending on SQL and email servers.

Use T-SQL to create a profile

Run the following T-SQL to create a profile as same as demonstrated in UI method above

    -- Start T-SQL
    USE [msdb]
  
    -- Create a profile
    EXEC sysmail_add_profile_sp
      @profile_name = 'MailProfile1',
      @description = 'A profile contains some email account.'
  
    -- Create an email account
    EXEC sysmail_add_account_sp
      @account_name = 'MailAccount1',
      @description = 'The email account contains address, server name, authentication and etc.',
      @email_address = 'someone@microsoft.com',
      @display_name = 'Sample Name',
      @replyto_address = 'someone@microsoft.com',
      @mailserver_name = 'smtp.sample.microsoft.com',
      @port = 25,
      @use_default_credentials = 1
  
    -- Add the email account "MailAccount1" to the profile "MailProfile1'
    EXEC sysmail_add_profileaccount_sp
      @profile_name = 'MailProfile1',
      @account_name = 'MailAccount1',
      @sequence_number = 1
  
    -- End T-SQL
 

Send emails

Now you have created a profile.  You can use it to send emails in T-SQL statements.  Below is an example to send an email to someone.

    -- Start T-SQL
    USE [msdb]
    EXEC sp_send_dbmail
      @profile_name = 'MailProfile1',
      @recipients = 'someone@microsoft.com',
      @subject = 'Database Mail Test by T-SQL',
      @body = 'This is a test e-mail sent from Database Mail on YUHONGLI5\KJINST.'

    -- End T-SQL