check this
sp_send_dbmail (Transact-SQL)
[
^]
sample code
EXEC sp_send_dbmail @profile_name='default',
@recipients='dev@null.com',
@subject=@SUB,
@body=@BODY,
@query= 'SELECT [MID],[HID],[MeC],[LC],[RowCDate]
FROM [JBC].[dbo].[Table1] WHERE RowCDate >= GETDATE()
',
@attach_query_result_as_file=1
@query_attachment_filename = 'Results.csv',
@query_result_separator = ','