• I've been using the send mail functionality for a while now and have found so many uses for it, it's really cool. I send out morning reports using an HTML template or sending out pretty emails to customers, like this...

    The weekly report style

    DECLARE @TableHTML VARCHAR(MAX)

    SET @tableHTML =

    N'<H1>Weekly Report For Week Beginning '+CONVERT(VARCHAR(20),dbo.ConvertTo5amDate(DATEADD(wk, DATEDIFF(wk,0,@Start), 0)))+'</H1>' +

    N'<style type="text/css">' +

    N'table {' +

    N'font-family: Arial, Helvetica, sans-serif;' +

    N'font-size: small;' +

    N'color: #111111;' +

    N'background-color: #EEEEEE;' +

    N'}' +

    N'</style>' +

    N'<table border="1">' +

    N'<tr><th>Col 1</th>' +

    N'<th>Col 2</th>' +

    N'<th>Col 3</th>' +

    N'<th>Col 4</th></tr>' +

    CAST ( (

    SELECT

    td = [Period],''

    ,td = [Total],''

    ,td = [Processed],''

    ,td = [NotProcessed],''

    FROM #Table

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    IF @@ERROR<>0

    BEGIN

    exec msdb.dbo.sp_send_dbmail @recipients = 'errors@here.co.uk'

    ,@body = 'An error occurred in the execution of [WeeklyReport]'

    ,@subject = 'ERROR mail from the database'

    ,@reply_to = 'me@here.co.uk'

    END

    ELSE

    BEGIN

    exec msdb.dbo.sp_send_dbmail @recipients = 'thisdistributionlist@here.co.uk'

    ,@body_format = 'HTML'

    ,@body = @TableHTML

    ,@subject = @SubjectLine

    ,@reply_to = 'me@here.co.uk'

    END

    And the pretty email style

    DECLARE @TableHTML VARCHAR(MAX)

    SET @UserEmail = LTRIM(rtrim(@UserEmail))

    SET @TableHTML ='<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html>

    <head>

    <title>My document title</title>

    </head>

    <body> HI ' + @UserName + ' thanks for whatever you have done on our website, this is a generic confirmation message. Check out these boobies while you wait for something else to happen

    <img src="http://www.destructoid.com/elephant//ul/1929-468x-boobies.jpg">

    </body>

    </html>'

    IF @@ERROR<>0

    BEGIN

    exec msdb.dbo.sp_send_dbmail @recipients = 'errors@here.co.uk;'

    ,@body = 'An error occurred in the execution of the SendSexualEmail'

    ,@subject = 'ERROR mail from the database'

    ,@reply_to = 'me@here.co.uk'

    END

    ELSE

    BEGIN

    exec msdb.dbo.sp_send_dbmail @recipients = @UserEmail

    ,@from_address = 'noreply@here.co.uk'

    ,@body_format = 'HTML'

    ,@body = @TableHTML

    ,@subject = 'Your Sexual Email'

    ,@reply_to = 'me@here.co.uk'

    END

    There's tonnes more stuff you can do and obviously I don't spend all my day sending people images of boobies.