How to send a SQL Server Query result in e-mail message?
If you have configured SQL Server Database Mail then you can easily send a query result as an attachment in mail body.
If you do not know how to configure database mail for first time then you can read the post
How to Configure SQL Server database Mail.
Once you have configured Database mail then you need to go through following steps.
First you need to create a SQL Server table , here we create a table named student then insert some dummy records into table.
After that we need to execute SQL Server database mail stored procedure inorder to send student table records as an attachment ( .txt format) into mail.
CREATE TABLE Student
(Sno INT IDENTITY(1,1), Name VARCHAR(50), Age INT, Code INT)

INSERT INTO student VALUES ('Rakesh' ,19,4567)
 INSERT INTO student VALUES ('Suresh kumar' ,18,4599)
 INSERT INTO student VALUES ('Manoj singh' ,21,400)
 INSERT INTO student VALUES ('Harish Tejasvi' ,20,34546)
Now we have a table student in SQL Server database, to fetch the records from student table use following query.
USE SqlTutorials;
GO
SELECT Sno,Name,Age,code FROM Student
GO
As you can see, it returns all the records from student table.
Now our requirement is to send the query result in .txt file as an attachment in mail.
To send query result in mail, we will specify the above query into Database Mail Stored procedure in @query parameter as followings.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMailAlertProfile', @recipients = '********@gmail.com',
 @query = 'USE SqlTutorials;
 GO
SELECT Sno,Name,Age,code FROM Student
GO',
 @subject = 'Student Registrations',
 @attach_query_result_as_file = 1;
Once you execute above database mail stored procedure, an email is sent to user’s mail Id as shown in below screenshot.
When you open attached file in mail, you can see student records which are output of query .
You can also add a message in mail body, passing value to @body parameter in stored procedures as shown below.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMailAlertProfile', 

@recipients = '********@gmail.com', 

@query = 'USE SqlTutorials; 

GO SELECT Sno,Name,Age,code FROM Student GO', 

@subject = 'Student Registrations', 

@body='provide mail body message' @attach_query_result_as_file = 1;
Also Read..
SQL Server Database Mail Configurations
Troubleshooting Database Mail in SQL Server
