Good point by @Jimbo that it depends on how sophisticated you want this to be.
For a start just go with stored procedures and attaching results as email attachment. See this for more details on how to implement it.
This is from MSDN
[ @file_attachments= ] 'file_attachments' Is a semicolon-delimited
list of file names to attach to the e-mail message. Files in the list
must be specified as absolute paths. The attachments list is of type
nvarchar(max). By default, Database Mail limits file attachments to 1
MB per file.
[ @query= ] 'query' Is a query to execute. The results of the query
can be attached as a file, or included in the body of the e-mail
message. The query is of type nvarchar(max), and can contain any valid
Transact-SQL statements. Note that the query is executed in a separate
session, so local variables in the script calling sp_send_dbmail are
not available to the query.
[ @execute_query_database= ] 'execute_query_database' Is the database
context within which the stored procedure runs the query. The
parameter is of type sysname, with a default of the current database.
This parameter is only applicable if @query is specified.
[ @attach_query_result_as_file= ] attach_query_result_as_file
Specifies whether the result set of the query is returned as an
attached file. attach_query_result_as_file is of type bit, with a
default of 0.
When the value is 0, the query results are included in the body of the
e-mail message, after the contents of the @body parameter. When the
value is 1, the results are returned as an attachment. This parameter
is only applicable if @query is specified.
[ @query_attachment_filename= ] query_attachment_filename Specifies
the file name to use for the result set of the query attachment.
query_attachment_filename is of type nvarchar(255), with a default of
NULL. This parameter is ignored when attach_query_result is 0. When
attach_query_result is 1 and this parameter is NULL, Database Mail
creates an arbitrary filename.