In this article I will explain with an example, how to send query result in body in SQL Server.
This article is applicable to following SQL Server versions i.e. 2008, 2008R2, 2012, 2014, 2016, 2017, 2019 and 2022.
 
 

Unblocking the sp_send_dbmail Stored Procedure in SQL Server

Firstly, you will need to unblock the Stored Procedure sp_send_dbmail used for sending emails using SQL Server.
In order to unblock the Stored Procedure, please refer my article Unblock dbo.sp_send_dbmail in SQL Server.
 
 

Configure SQL Server for sending emails

Then, you will need to configure SQL Server for sending emails from Database.
In order to configure SQL Server, please refer my article Configure SQL Server for sending emails.
 
 

Sending query result in body in SQL Server

Once the SQL Server is configured for sending emails, the SQL Server is now ready to send emails with body format as text.
To send an email, simply copy, paste, edit (according to your settings) and execute the following SQL query.
DECLARE @messageBody NVARCHAR(1000) = 'Hi Mudassar,' + CHAR(13) + CHAR(10)
    + 'Following are the Customer details' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    + 'Thanks' + CHAR(13) + CHAR(10) + 'ASPSnippets Team' + CHAR(13) + CHAR(10)
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mudassar_Email_Profile'
   ,@recipients = 'recipient@gmail.com'
   ,@subject = 'Email from SQL Server'
   ,@body = @messageBody
   ,@importance = 'HIGH'
   ,@query = 'SELECT CustomerId, Name, Country FROM Customers'
   ,@attach_query_result_as_file = 0
GO
 
Once executed, the following messages will appear which confirms, your email has been queued.
Send sp_send_dbmail query results in body in SQL Server
 

Checking Emails Status email sent with query results as attachment

Finally, to find the status of emails whether they are not sent, sent or failed, please refer my article Find email sent status.
 
 

Screenshot

 
Send sp_send_dbmail query results in body in SQL Server
 
 

Downloads

Download Code