Using the msdb.dbo.sp_send_dbmail function

Sometimes, it's helpful to be able to send emails regarding the data in your database, perhaps for things like reporting data problems or sending notifications. SQL Server helpfully provides us with a robust function for sending such emails. Here's a easy overview of the basics.

Stopping emails if you've screwed it up

To begin with, here is how to stop SQL Server from sending emails if you accidentally mess up the end condition on a loop and cause it to be sending infinite emails. (Certainly I've never made a mistake like that....) The process that actually sends the emails is slower than you can generate them from SQL, so emails can easily still be going out even if you've already stopped the job or process that is creating them.

Start by executing the process msdb.dbo.sysmail_stop_sp, which stops the database mail process
End the job or process that is generating the emails. If it was a job that started sending the emails, you can stop it by opening the Job Activity Monitor (right under the Jobs folder in the SQL Server Agent section of your Object Explorer in SSMS), finding the job in the list, and if the status is "Executing" you can right-click and choose "Stop Job"
Go to the msdb.dbo.sysmail_unsentitems table and delete any queued emails that you don't want to be sent out
Once everything generating the emails is stopped and the unwanted ones are deleted from the queue, execute msdb.dbo.sysmail_start_sp to restart the database mail process

Viewing what emails have been sent

You don't need to keep your own logs of sent emails, because the sp_send_dbmail function does it for you in the table msdb.dbo.sysmail_allitems.

Sending a very basic email

//todo: learn enough about database mail profiles to add that info

Ok, now that you know those things, you're ready to send your first email.

To send an email, there are only a few fields you really need - the database mail profile, the recipient(s) email address(es), and a body and subject line for the email. If there are multiple recipients, they should be semicolon separated.

     execute msdb.dbo.sp_send_dbmail
          @profile_name = 'mailprofile',
          @recipients = 'recipient@gmail.com; anotherrecipient@hotmail.com',
          @subject = 'Hello world',
          @body = 'Lorem ipsum dolor sit amet'

Getting a little fancier

You can optionally also set people to be copied and/or blind-copied, and you can specify the "from address" and the "reply to" addresses.

     execute msdb.dbo.sp_send_dbmail
          @profile_name = 'mailprofile',
          @recipients = 'icangetemails@gmail.com',
          @copy_recipients = 'igetacopy@gmail.com',
          @blind_copy_recipients = 'igetablindcopy@gmail.com',
          @from_address = 'igetspoofedasthesendingaddress@gmail.com',
          @reply_to = 'repliestothisemailwilldefaulttome@gmail.com',
          @subject = 'Phenomenal cosmic power',
          @body = 'Itty bitty living space'

A caution regarding code formatting...

Be careful about adding line breaks to your email body. Note the following example:

     execute msdb.dbo.sp_send_dbmail
          @profile_name = 'mailprofile',
          @recipients = 'yougetanemail@gmail.com',
          @subject = 'Line break test',
          @body = 'Here is an email that talks for a long time and because the line was so long
                   the programmer added a line break for code readability'

The resulting email will look something like this:

Here is an email that talks for a long time and because the line was so long
the programmer added a line break for code readability

Adding line breaks and realigning text is intuitive (and good practice) within SQL generally, but in this case by default those will be preserved in the email. If you're sending an email that isn't very short, it's preferable to have more freedom and to be able to use paragraphs or other structure without having to left-justify all your new lines, and it's nice to be able to use styling also. So you'll often want to use HTML formatted emails instead.

Using HTML formatted emails

If you add the @body_format = 'html' parameter, you can use whatever HTML and CSS you want in the body of the email.

     execute msdb.dbo.sp_send_dbmail
          @profile_name = 'mailprofile',
          @recipients = 'recipient@gmail.com',
          @from_address = 'sender@gmail.com',
          @subject = 'Test with formatted body',
          @body = '<head><style>.comic { font-family: Comic Sans MS; }</style></head>
                   <h1>This is a test</h1>
                   <p>Here is a paragraph with some <b>bold text</b></p>
                   <p>Here is a paragraph with <i>italic</i> text</p>
                   <p class = "comic">This is a paragraph with CSS styling</p>'
                   <p>Here is a paragraph that is longer. We can add a line break in the middle of it
                   so that the code is readable, but the break won't be included in the email.</p>,
          @body_format = 'html'

Note that since HTML doesn't include line breaks and indenting in the structure of the parsed text, you can make your @body parameter more readable without messing up the final email.

Sending the results of a query

//todo: add this section