Direct EMailing of SSRS Reports via SQLCLR

This is something that I was quite surprised that wasn't supported out of the tin with SSRS. When you schedule an email delivery, the report is always sent as an attachment. What would be great, if when you specified the render format as "MHTML", you had the option that the report forms the body of the email. Taking that one step on, how about calling that directly from SQLServer ?

Some of which is to follow has been shamelessly ripped from here

Source Code here
Useage :

exec SSRSMail @retval output,   --1 On Success , 99 on Fail
            @ExceptionString output, -- ErrorMessage
            @SSRSReport,           -- Report name including forward slashes '/Marketing/SalesFigures'
            @Params,              --  Comma Delimited list of parameters to pass to the report
                                  --  Case Sensitive ie 'SalesMonth = 3 , SalesYear = 2009 , SalesManager = John'
            @RecipientList        --  Comma Delimited list of emailRecipients ,
            @SenderEmail,         --  The Sender address. Supports a friendly name ie '<Company Reports>Reports@YourServer.com'
            @Subject   ,          --  Email Subject
            @CCList,              --  Comma Delimited CCList
            @BCCList              --  Comma Delimited BCCList

To use this create a new "SQL Server Project" within visual studio , and add a web reference to your SSRS Server http://yourserver/ReportServer/ReportExecution2005.asmx?wsdl naming it "ReportExecution".

Inside the source code rename "MailServer" to your mailserver. Please note that as i dont logon to that server that it needs SMTP-relaying enabled. The reason i dont use the system.net.mail namespace is that this routine was originally used within SSIS, but within that you cant specify the sender address. If you dissaprove, feel free to change to using that.

So the routine can consume the webservice you need to serialize the dll using the "sgen.exe" utility. More info on that here

We've been running live with this now for a few weeks , sending out a good few hundred emails a day to our clients , so stability seems good :). Ive had no complaints either about incorrect formatting. If you get any problems with that then let me know.