views:

771

answers:

6

I have a continually updating SQL database with email addresses from my users. I would like a way to send them newsletters, general information, account information, billing, subscriptions, and other important information.

I want to make a generic HTML/Text template for each bulk mail and have SQL fill out the rest of the information. Is there some API or Software that does this?

I prefer a .NET solution that is free or reasonably priced, if possible. I don't want to write this from scratch unless I have too.

+2  A: 

I've used SQLAnswersMail for things like this, but it's still requiring you to do a bit of work on the SQL side. I primarily used it because it had awesome SMTP email support on SQL Server 2000 (and the PDF, Excel and HTML template support is pretty cool).

We used it for scheduled jobs, operations and triggers in the database which generated mail - some were templatized, some were attachments, etc. SQL Mail in 2000 is unusable for that, because it requires MAPI profiles and doesn't offer good flexibility for setting arbitrary from addresses (each email should come from the department responsible for answering questions about it), etc.

Anyway, you might want to check it out if you are using SQL Server, because effectively everything just happens on the server.

Cade Roux
+1  A: 

I've used HTML templates to do this sort of thing and done simple keyword/value replacement. Make sure that your keywords aren't valid text values. I use things like $USERNAME$. Then load your template into a temporary string and use a Regex to replace all occurrences of each keyword with its corresponding value in the temporary string. Set the mail body to the temporary string and voila!

As a sanity check you can look for patterns left in the temporary string that match unreplaced keywords (\$.*\$) and do some error handling instead sending the message.

tvanfosson
+1  A: 

As mentioned by tvanfosson, is not that hard to roll your own with regexs or just string.Replace, one addition to that is to set up what are sometimes known as "slugs", for when some fields are empty.

eg, if firstName is missing, you may want to insert a default value of "valued customer" etc. Usually the marketing dept tells you what they want there...

seanb
Fixed my name. :-)
tvanfosson
oops - sorry about that - is early and sunday here :)
seanb
+1  A: 

The best home cooked solution I have seen to do customized emails was based on XSLT. Create the email template with an XSLT stylesheet, and generate an XML document in C#, inserting values as required. If your objects are serialization then you can simply input their serialized graphs into the XML document. If not, its easy enough to just add primitive values. Add all the data you would possibly want. Then if you need extra fields down the road, you just change the XSL template. This also works well for localization. You can write an XSL template for each language in which you wish to distribute emails.

Jason Jackson
+2  A: 

I've dealt with this several times at my last couple of jobs. The main problem is that if you have users from AOL, Hotmail, or many of the other big consumer email providers--sending out a mass email yourself from your domain is pretty likely to get blacklisted. Which means you'll have to spend a ton of time on the phone.

At my current position we primarily use Campaign Monitor for any marketing bursts that we send out. They have a SOAP API that you can use to upload all your users.

Then you don't have to worry about implementing any kind of email templating yourself or having to spend hours on the phone with AOL technical support.

Sam Gibson
A: 

Hi, I have found this: www.youmail.50webs.com Have a few free programs.

jack