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.