Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

So How Do You do your mass Mailings? Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 9:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 12,910, Visits: 32,016
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1428671
Posted Wednesday, March 27, 2013 6:45 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 19, 2014 9:19 AM
Points: 81, Visits: 285
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..
Post #1435867
Posted Thursday, March 28, 2013 5:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:31 AM
Points: 323, Visits: 613
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 :)
Post #1436414
Posted Thursday, March 28, 2013 5:59 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 19, 2014 9:19 AM
Points: 81, Visits: 285
or free if you write your own ;)
Post #1436416
Posted Thursday, March 28, 2013 6:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 12,910, Visits: 32,016
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1436423
Posted Thursday, March 28, 2013 6:18 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 19, 2014 9:19 AM
Points: 81, Visits: 285
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.
Post #1436430
Posted Thursday, March 28, 2013 6:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:31 AM
Points: 323, Visits: 613
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??
Post #1436437
Posted Thursday, March 28, 2013 6:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:22 PM
Points: 2,555, Visits: 3,809
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
Post #1436447
Posted Thursday, March 28, 2013 8:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1436807
Posted Tuesday, April 2, 2013 1:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 561, Visits: 1,169
I also use MS Word having extracted the required subset of adressee data into Excel from the database (MySQL in this case)
Post #1437731
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse