Found this thread trolling for answers to the same question. Here's what works for me.
exec msdb.dbo.sp_send_dbmail
@profile_name = 'YOUR_PROFILE',
@Recipients= 'YOUR_EMAIL',
@Subject= 'sample message with attached query',
@Body= 'sample message with attached query',
@query = 'set nocount on select top 10 * from sys.tables',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'sample.csv',
@query_result_separator = ',',
@query_result_header = 0,
@query_result_no_padding = 1,
@exclude_query_output = 1