Adding many records of e-mail addresses to an e-mail

lryckman · October 16th, 2009, 02:22 PM

UGH! I am having a time with this one. Any help would be much appreciated.

I am working on a Supplier Address book that will contain 300+ contacts and be utilized for mass mailings.

End Goal: Have a button that when clicked, it asks for criteria (an organizational role ie: Plant Manager, Planner, Engineer, etc.) and then creates a blank e-mail and adds every e-mail address that has a matching organizational role.

I can get the e-mail addresses (that the e-mail is suppose to be sent to) in a multi-record query without a problem. Once I get all the e-mail address on one line though, it goes beyond 255 characters and I don't capture all the e-mail addresses required. I believe I am going to have to involve more code to get all the matching record e-mail addresses into the e-mail.

Any ideas on the best way to accomplish this?

I started working on using a recordset, but it doesn't like my line of code:

Code:

 
rst1.Open "Select * From fltRoleChosen", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

and gives me an error that it is missing one or more required parameters.

Once I get the recordset to work, I don't really have a clear path beyond that either though so I am open to any suggestions.

Thanks!

SerranoG · October 26th, 2009, 03:55 PM

Hmmm... I don't think you're going to get away with more that 255 characters for the recipient list.

What I would do is every time you tack on another e-mail address (comma or semi-colon delimited) to the end of the recipient string, check for the string's size. If the string is greater than 255 characters, put that person "on hold" (so to speak) and then send the e-mail to the ones you have so far.

Then have Access start fresh with that person you put on hold and start growing your string again. Once THAT string gets over 255, do it again, until finally you send two or three identical e-mails that catch everyone.

Because large recipient lists should be blind carbon copied (BCC), send the e-mail to yourself and BCC each e-mail. If you do that then

1) people won't get an e-mail with an annoyingly large header,
2) you don't divulge private addresses to all the recipients, and
3) they can't tell that the e-mail went to a subset of people and that you sent out more than one e-mail.