So How Do You do your mass Mailings?

Question

So How Do You do your mass Mailings?

Lowell

SSC Guru

Points: 323518
More actions
March 8, 2013 at 9:59 am

#74468

Once a year I help out a local non profit promote their annual event. This is the third year for doing it, and I just wanted some peer review to see if you'd have recommendations to do it differently.
Right now, the whole thing is a full on DBA process, which requires my well rounded skills. I'd like to change this to a process where any end user could send out an eblast without it being a html developer + DBA required to do the grunt work.
Does anyone use a program for sending stuff out like this? How do YOU do it? homemade app? web page? off the shelf app? I'd like to hear some suggestions
Here's my current process, the gory details are not really needed as far as script examples, i think:
We've got a typical contact database with a few thousand contacts.
We have a meeting where non-technical people say what they want in the email, and i will create a nice looking html email, with images and nice design, blah blah, but with placeholders for find-and-replace for personalizing the emails.
ill stick the html into a table via a basic insert.
Later, when we are ready to actually send it out:
I select the html into a varchar(max) @HTMLBody for the body of the email.
I create a cursor to loop thru the rows of the contacts to be spammed....er asked for donations.
Find and replace placeholders to personalize the message to the contact int eh cursor ie REPLACE'[FirstName],@Firstname) for things like firstname/lastname/email/phone/etc.
each email is sent via sp_send_dbmail to send out individualized messages.
After it's all done,
I'll end up reviewing msdb.dbo.sysmail_allitems and sysmail_faileditems for progress and issues. Contsantly rerunning the same query to see the unsent items dwindle down to zero.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

hb21l6 SSCommitted Points: 1647 More actions · Answer 1

Personally, I write a simple vb programs to loop through the email addresses from DB, A simple threaded sub will allow me to see real time progress.. Then you can add in pause functions etc... simples..

b.fleming SSC-Addicted Points: 459 More actions · Answer 2

Export to .CSV and use Campaign Monitor website. Easy to use, easy to track, pretty cheap ($5 per mailing and then 1 cent per email). Please bear in mind that mass mailings is quite a large part of my job 🙂

hb21l6 SSCommitted Points: 1647 More actions · Answer 3

hb21l6

SSCommitted

Points: 1647

March 28, 2013 at 5:59 am

#1601574

or free if you write your own 😉

Lowell SSC Guru Points: 323518 More actions · Answer 4

Thanks for the insight guys; I'm still hoping for some more suggestions.

My non profit wouldn't even spend the five dollars per mailing, but would love for me to invest hundreds of man hours to make it easier for them.

That's why I was asking; Normally I'd lean towards creating an app to do it, and have the app contain all my logic, and use an existing WYSIWYG editor for the end user to make a "pretty" email.

But this is one of those undefined goals, where no-one knows what they want, so any application i make would have my personal flavor on it, and would then be criticized and caught in feature creep forever. I don't like getting painted into a cornerl with undefined goals.

If I knew of something off the shelf that was peer approved or recommended, i'd throw that into the mix to extract me from doing all the work.

a classic example: I get an email at 8pm at night, telling me in general "we want to say something about X" and ask for volunteers at our upcoming 5 K run.

They want it to go out @7am the next morning, so I'm up all night fiddling with content, and then scripting everything out to do it in sp_send_dbmail.

I need them to have the ability to make the content and essentially test it on a select group, and then to teh full list.

.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hb21l6 SSCommitted Points: 1647 More actions · Answer 5

Well, if you do get stuck for something like that Lowell.. PM me and I'll write you one to your spec. FOC (as its for charity).

I like little projects like that 😀

Dave.

b.fleming SSC-Addicted Points: 459 More actions · Answer 6

hb21l6 (3/28/2013)
Well, if you do get stuck for something like that Lowell.. PM me and I'll write you one to your spec. FOC (as its for charity).
I like little projects like that 😀
Dave.

If you could post the code for that somewhere I'd be interested in having a look at it........

Please??

OCTom SSChampion Points: 11785 More actions · Answer 7

Where I work mass mailings are done using mail merge in MS Word. We set up a data source on the user PC pointing to the SQL table used for the info.

If Word is not available to you Open Office is free and can be used in a similar manner (I've been told this but have not done it).

Tom

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

OCTom (3/28/2013)
Where I work mass mailings are done using mail merge in MS Word. We set up a data source on the user PC pointing to the SQL table used for the info.
If Word is not available to you Open Office is free and can be used in a similar manner (I've been told this but have not done it).
Tom

Youu beat me too it. I haven't done it in quite a while but Word would merge an address file with a documet file and create 1 document per address. If you were to do a little VBA macro to run an "export" proc from Word, you could make it as easy as the push of a button onscreen.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

P Jones SSChampion Points: 12361 More actions · Answer 9

I also use MS Word having extracted the required subset of adressee data into Excel from the database (MySQL in this case)

EdVassie SSC Guru Points: 60476 More actions · Answer 10

I work with some non-profit organisations that use MailChimp for their mailings.

My employer sends out a few million mails per day and uses PowerMTA to process this workload.

The more mails you send out, the more you have to worry about email reputation. Major ISPs will throttle the throughput of emails from a sender with poor or unknown reputation, and allow full speed processing of senders with good reputation.

The main ways to build reputation are a) repeatedly send from the same IP address; b) Do not repeat sending mails to non-existant addresses; c) If your business has the money, work with an established organisation in this field to accellerate building your mail reputation.

The second part part of reputation building means you need to deal with bounced emails. You need to analyse the bounce records and mark bad email addresses so you do not send to them again. Most ISPs will tolerate a small number of repeats to bad addresses, but the more you send the lower your reputation will go.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 11

I've got a lot of experience here. (sorry for being late)

We started as you did, using SQL Server to send mails. This works fine from a capacity standpoint. SQL Server can handle the load. Where you fail is with the compliance and management of mail from there.

We have moved through a few programs (controls and desktop things), to Lyris, to a custom setup.

Personally right now I'd look at some small service that integrates with what features you give people in a database app. I'd spend time building a way for them to create mailings and an interface to send them to a provider of some sort.

Take it from someone that's fought complaints, bounces, blacklists, etc. Use a service.

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Jeff Moden (3/28/2013)
OCTom (3/28/2013)
Where I work mass mailings are done using mail merge in MS Word. We set up a data source on the user PC pointing to the SQL table used for the info.
If Word is not available to you Open Office is free and can be used in a similar manner (I've been told this but have not done it).
Tom
Youu beat me too it. I haven't done it in quite a while but Word would merge an address file with a documet file and create 1 document per address. If you were to do a little VBA macro to run an "export" proc from Word, you could make it as easy as the push of a button onscreen.

Yeah, MS Word 97 did a pretty good job for me at the time.

Newer versions, I believe, are capable of doing the same, pretty much.

Automated scheduling is a bit of a problem, but it's not required in this case.

For automated sends over different time zones all over the world sp_makewebtaks (SQL 2000) was very useful:

create template in an editor, leave placeholders for data and schedule a job to run it when you're asleep.

I believe it's still in operation till these days. 🙂

_____________
Code for TallyGenerator

homebrew01 SSC Guru Points: 55548 More actions · Answer 13

We use a product called StrongMail that queries againt our SQL database. Sorry, but I don't know any details about the process.