Generating multiple encoded HTML/Text emails T-SQL

Question

For my first question, I have your typical web e-com scenario where the user can order various products and then receive confirmation emails. I've been doing a lot of searching on the best way to send emails for this application, and I am hoping someone can point me in the right directions.

I can send plain text confirm emails from either the app or the database just fine, but now I want to change things up by creating a series of HTML template to use when sending emails. There will be multiple HTML templates, and multiple email contents. When the user makes an order, the order is recorded in the database, which then determines, via stored proc, the type of product/order, and selects the appropriate template file name, email content and replaces all the key text with user/order information.

At the same time, I'd like the email encoded with the text version as well, so I can send out one email containing both types, and be done.

Is it possible to do this in SQL Server 2008 R2, or should I send the content information back to the app and then send the email? The problem I am worried about with the latter is, it may cause some delay on the site as it communicates back and forth with the database.

Further, I'd like to switch to Amazon SES, which is easily used with .Net, but I have not yet been able to set up with SQL 2008 R2, so I'm not sure if this is a mistake on my side, or a compatibility issue.

The basics: Application: .Net 4.0 Database: SQL 2008 R2

Answer 1

The key requirement here is sending the HTML and text versions of the message in the same email. SQL Server database mail doesn't support sending multi-part MIME messages - if you want to do this, you will need to send the mail from your application.

Command reference for sp_send_dbmail - note that the @body_format parameter can be either HTML or text - not both.