Why not install SSRS and schedule a report? The stored procedure would just be responsible for returning the data.
The converting to CSV and emailing would be done by SSRS.
Once the schedule is set up you can run the report and email subscription on demand if needed by executing the relevant procedure in the ReportingServices database.
(If the recipient email addresses are entirely dynamic and unknown in advance this will require a data driven subscription though, which is Enterprise Edition. If this is not available you'd probably need a different approach.)