As far as the scheduling goes, you could put your code in a SQL Agent Job and set it to run weekly on Monday. How to put the query into Excel is a different story. How you considered using an Excel driver and "insert"ing the data? You could find the driver with a Google search.
First - you cannot send an actual Excel file using sp_send_dbmail. What you can send is a CSV or tab-delimited text file that Excel can open. The CSV file is the better option...and your problem is that you cannot open the file directly from email - you have to save the file and then open it.
To fix that issue - you have to tell Excel this is a CSV file to be opened and you do that by defining the separator in the first column of data. Here is an example:
Declare @sqlCommand nvarchar(max)
, @recipients varchar(max) = 'faiyazu99@gmail.com'
, @cc_recipients varchar(max) = ''
, @bcc_recipients varchar(max) = '';
Declare @body nvarchar(max) = 'Attached is some audit information';
Set @sqlCommand = '
Set Nocount On;
Select c.FirstColumn As [Sep=,' + char(13) + char(10) + 'FirstColumnName]
, {list out all columns to be included}
From dbo.Customer c
Where c.master_customer_id = ''000210232'';'
Execute msdb.dbo.sp_send_dbmail
@profile_name = '{Your Database Mail Profile'
, @reply_to = '{your-reply@address.com}'
, @from_address = '{your-from@address.com}'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @blind_copy_recipients = @bcc_recipients
, @subject = 'Some Audit'
, @body = @body
, @query = @sqlCommand
, @query_result_separator = ','
, @query_result_header = 1
, @query_result_width = 8000
, @attach_query_result_as_file = 1
, @execute_query_database = 'PTEST'
, @query_attachment_filename = 'SomeAudit.csv';
open the file in notepad++ and what do you see for those column names? It really does not have anything to do with SQL server email automation. That is an excel issue
