We help IT Professionals succeed at work.

How can I send a mass email with recipients populated from an Acess DB?  sharepoint, outlook, access, sbs?

942 Views
Last Modified: 2011-06-28
I have a client that has some special needs.  They have an MS Access database with data is kept up to date by another oganization.  The database keeps client information (name, phone number, email, etc).  They would like to be able to use this database to send out mass emails to approx 1,000 clients within this database.  Oh and the database is always being updated, approximately every 30 minutes to an hour a new one is downloaded locally.  That's basically all the functionality they need.

However, I'm working with very non-technical people here.  So I'm wondering what would be the best way to get this working as easily as possible.

I know there are multiple ways of doing this, so I'm just thinking out loud here.  I'm thinking maybe using SharePoint as the "middle man" and have that pull the data from Access and then using Outlook to send the emails.  Or maybe getting Access to send the emails directly from there.  Or maybe there is a way to sync Outlook and Access directly.  Can you tell I don't know where to start.   But I have a gut feeling that this has been done before and I'm not alone on this issue.

Any help, direction, advice, questions, or comments are greatly appreciated.

Thanks!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
what will be the content of the email? just text file or with attachment?

Author

Commented:
With attachments.

good question, I can see how that's vital information.  :)

Author

Commented:
Oh and the emails aren't sent in plain text, it's html.  :)  Just fyi.
CERTIFIED EXPERT
Top Expert 2016

Commented:
see this example from M$, this will need tweaking to get the email from the table

How to use Automation to send a Microsoft Outlook message using Access
http://support.microsoft.com/?kbid=209948

Author

Commented:
Thanks, I read that.  But that doesn't allow adding attachments.

"SendObject method does not give you access to complete mail functionality, such as the ability to attach an external file or set message importance"

am I missing something?

Author

Commented:
Oops, nevermind I see where you have are able to attach documents. :)

Author

Commented:
Sorry for these Access 101 questions, I'm not an Access person.

A few things come to mind:
1)  Will this module get overwritten when the database is updated?
2)  I'd also like to be able to send out the mass email in batches of 200, so 5 batches total. Is this possible?
3)  The end-users have zero experience with Access and they'd much rather use a tool they know and are familiar with. Could this be accomplished via Outlook on the front end?  Or can it only be done via Access on the backend?

Thanks!
Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2016

Commented:
1)  Will this module get overwritten when the database is updated?

depends on how the update will be done.

what do you mean by  "when the database is updated" ?

2)  I'd also like to be able to send out the mass email in batches of 200, so 5 batches total. Is this possible?

the max number of recipient might be reached, not sure.

3. possible, haven't tried yet...
 
Graham MandenoConsultant
CERTIFIED EXPERT

Commented:
Regarding your "Access 101 questions":

1)  Will this module get overwritten when the database is updated?
    I suggest you create a separate database that contains the code, and link the relevant tables in the database that keeps being replaced.  That way, your code will remain intact, but will always reference the latest data.

2)  I'd also like to be able to send out the mass email in batches of 200, so 5 batches total. Is this possible?
    Yes, no problem at all - you just start the session and repeat the part from olkApp.CreateItem(olMailItem) to olkApp.Send as many times as you need.

3)  The end-users have zero experience with Access and they'd much rather use a tool they know and are familiar with. Could this be accomplished via Outlook on the front end?  Or can it only be done via Access on the backend?
    This would be much harder to do from the Outlook end.  Your little front-end app database with the attached tables can easily be set up yo open directly on a user-friendly form with whatever selction controls are required to set up the messages, and a single button to send them.

Author

Commented:
Are they plain text?   No.
Formatted HTML? yes
Do they have attachments?  Yes, mostly pdf.
Is there one message for each recipient, or do you want to send the same message to many recipients in the BCC field?  I want to send the same message to many recipients.

Hmmm, I'm starting to think I might be over my head on this one.  :(

How long do you think it would take an Access guru to complete this?  I think I'd much rather outsource this to someone else.  GrahamMandeno, would this be something you'd be intereted in?

I hope I'm not breaking any EE policies by asking to pay someone to do this for me.  If I did, then I retract my last statement.

Graham MandenoConsultant
CERTIFIED EXPERT

Commented:
Well, I much prefer to "teach a man to fish" rather than "give a man a fish", and I think that's much more in keeping with the philosophy of the community peer-to-peer support groups I work in.  I've not been part of EE for long, but I guess the same applies here.

It's really not all that difficult, and I'd be happy to help you to get it working online, or we can take it offline if you prefer.

The next questions are:
1) Where does the set of recipients come from (presumably a recordset in the database)
2) How are the attachments selected?
3) Is the HTML message body just a standard "cover note" of does it vary from message to message?  If it varies, where does the body message come from?

Author

Commented:
GrahanMadeno, I agree with the philosophy and normally I would learn to fish.  But I feel like this is just a one time fishing trip to Alaska, and not necessarily an ongoing weekend fishing gettaway. :)  So as you could tell from my inquiry, I hadn't made an offer like that before.

I'm not afraid to learn how to do it, so let's proceed. :)  

1) Where does the set of recipients come from (presumably a recordset in the database)
Yes, a recordset within the database.  I haven't dug very deep into the database becuase I've just been looking at different possibilities.
2) How are the attachments selected?
The attachments aren't static. They're always changing each time an email is sent.
3) Is the HTML message body just a standard "cover note" of does it vary from message to message?  If it varies, where does the body message come from?
The HTML message is newly created every time and it varies.  The think body message is currently being created in Word via Outlook.  I'll check with the end-user on this one.  But that's my current understanding.

Think of this situtation like a paid subscription weekly e-newsletter that get's sent out to subscribers.  The newsletter is always changing week after week.  There are sometimes attachments that are included in the newsletter, but not always.  And subscribers are always being added, edited, and deleted all the time.  Does that paint a better picture of what is sort of going on?  :)
Graham MandenoConsultant
CERTIFIED EXPERT

Commented:
OK - more clarification:

1) Does the user select the recipients from the recordset, or does the message go to all the addresses in the recordset?
2) Ditto with the attachments. Does the user select them, or are they specified in a table in the database?
3) Ditto with the message body. Does the user create it? Or is it in a table field in the database? Or is it in a separate file that is located by data in a table? And does the user sending the message have any ability to edit the message body before sending it?

Author

Commented:
1) Does the user select the recipients from the recordset, or does the message go to all the addresses in the recordset?
The user does not manually select the recipients from the recordset.  The message goes to al lthe addresses in the recordset.  I beleive the recordset is based on a query that is run on the tables.
2) Ditto with the attachments. Does the user select them, or are they specified in a table in the database?  The user selects them, the attachments aren't anywhere in the database.
3) Ditto with the message body. Does the user create it? Or is it in a table field in the database? Or is it in a separate file that is located by data in a table? And does the user sending the message have any ability to edit the message body before sending it?
The user creates the message body.  It's not in the database.

I hope I included enough details.  Thanks for your help so far. :)
Graham MandenoConsultant
CERTIFIED EXPERT

Commented:
OK, so the only data that comes from the database is the list of email addresses, correct?

It would be an easy matter to create a message addressed to ALL the recipients, and display it as a new email on Outlook.  From there, the user could type the message body and select any attachments in the familiar UI before clicking Send.

The problem comes with the number of recipients requiring the message to be sent in several batches.

One possibility that occurs to me is that the database could record the MessageID of the first message and then resend it with the next batch of addressees, repeating as often as required. I haven't tried doing that before, but I figure it shouldn't be too difficult.

Would you like to ping me offline at "expex.g.mandeno-at-xoxy.net".  Maybe zip and attach a sample of the database if it's not too large.

Author

Commented:
Yes, that's the only data that comes from the database, the email addresses.

I like the idea of "easily" creating a message addressed to ALL recipients and displaying it on an email in Outook.  I think that is somethig that the end-user would be able to handle.

I wish i could just create one email.  But their email is distributed to approx 1,000 emails.  I can't send this out all at once, for fear of getting their ip blacklisted.

How do I get you the databse file?

Thanks again for all your help.
CERTIFIED EXPERT
Top Expert 2016

Commented:
not for anything, just a reminder
see this
https://www.experts-exchange.com/help.jsp#hi99

you can upload a zip copy of the db here www.ee-stuff.com

i'll leave you two on this q.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.