Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


So How Do You do your mass Mailings?


So How Do You do your mass Mailings?

Author
Message
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38940
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!

hb21l6
hb21l6
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 294
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
b.fleming
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 769
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 Smile
hb21l6
hb21l6
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 294
or free if you write your own Wink
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38940
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
hb21l6
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 294
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 :-D

Dave.
b.fleming
b.fleming
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 769
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 :-D

Dave.


If you could post the code for that somewhere I'd be interested in having a look at it........
Please??
OCTom
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2813 Visits: 4152
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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44996 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
P Jones
P Jones
Say Hey Kid
Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)

Group: General Forum Members
Points: 682 Visits: 1505
I also use MS Word having extracted the required subset of adressee data into Excel from the database (MySQL in this case)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search