How does Database Mail work?
The SQL Server Database Engine may send emails using Database Mail, an enterprise email solution. Your database applications can send users emails by using Database Mail. The messages may include files from any resource on your network in addition to query results.

Step 1: In order to send mail from SQL Server, we must first install Microsoft SQL Server Management Studio (SSMS) on our PC or laptop. Thus, download and install SSMS first if you haven't already on your PC.

Step 2
Now, Open SSMS and connect it to your SQL Server instance.
Expand Management option, there you can find the Database Mail option. As you can see in the following image.

Now, Right click on Database Mail and select the Configure Database Mail option.
This will open a Database Mail Configuration wizard. Select next to continue.
Now choose the first option, Set up Database Mail, by performing the following tasks, and click next.

Now, give your database mail Profile Name and its description as whatever you want. Here, I am giving "Test Profile" as the profile name. In the SMTP account section, select the Add account option to create a new SMTP account as in the following image.

Now, enter the information shown in the following image in the New Database Mail Account box. I'm sending emails from this account using Gmail. Thus, I set the server name to smtp.gmail.com and the port number to 587.

Use the email address and password from which you wish to send emails when logging into SMTP Authentication and Outgoing Mail Server. For security reasons, confirm that the "The server requires a secure connection" check box is checked as well. When you are finished, click OK. To move on to the next screen, click next.

Step 3. The next screen is about making a Database mail profile, either public or private. You can leave this option and move to the next screen. Again, select next to finish the creation process for the Database Mail profile.

Now you are all set up to send e-mail. Now go to Management, right-click on Database Mail, and select Send Test E-mail... option to send test mail as in the following image.

Choose Test Profile as Database Mail Profile from Dropdown and enter To email address, Subject, and Body, and click Send Test E-Mail. Now your e-mail has been sent successfully from SQL Server.

Step 4. If you want to send mail using SQL query, then you can use the system database 'msdb' and its stored procedure 'sp_send_dbmail' as follows.
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Test Profile',
    @recipients = '[email protected]',
    @subject = 'Test Mail',
    @body = 'This is the test mail.',
    @body_format = 'HTML';

To check the status of the e-mail you sent, you can execute the following query in SQL Server.
USE msdb;
GO

SELECT *
FROM sysmail_allitems;

In this query result, you can find all the details about the mail sent from the SQL Server. You can check the sent_status column to check the status of mail.
So, this is all about using the database mail in the SQL Server to send e-mails. I hope you understood my explanation and liked this article. Thank you!

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.