For the uninitiated (like me), Microsoft SQL Server has T-SQL (think PL/SQL for Oracle). Of course being an MS product, it has to have some VB scripting integration, which is cool. Part of this integration lets you get your hands on a component called SQLMail and SQLAgentMail. These things plug into the MAPI mail system on the SQL server and send emails through it.
The downside of this is that in shared hosting environments, you may not have the luxury of setting up a mail profile on a PC and therefore MAPI may be lacking. There are a few alternates though, like specifying your own DLL and using it to send mail or some other means.
Either way, its good to know that it is possible to get your scheduled reports to send mails out when they are finished.
The links below cover a variety of means you can achieve this.