Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 917
  • Last Modified:

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

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!
0
vITComputing
Asked:
vITComputing
  • 9
  • 5
  • 4
1 Solution
 
Rey Obrero (Capricorn1)Commented:
what will be the content of the email? just text file or with attachment?
0
 
vITComputingAuthor Commented:
With attachments.

good question, I can see how that's vital information.  :)
0
 
vITComputingAuthor Commented:
Oh and the emails aren't sent in plain text, it's html.  :)  Just fyi.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Rey Obrero (Capricorn1)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
0
 
vITComputingAuthor 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?
0
 
vITComputingAuthor Commented:
Oops, nevermind I see where you have are able to attach documents. :)
0
 
vITComputingAuthor 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!
0
 
GrahamMandenoCommented:
How you go about this depends on the nature of the messages you want to send.  Are they plain text? Formatted HTML? Do they have attachments? Is there one message for each recipient, or do you want to send the same message to many recipients in the BCC field?

Access provided a *very simple* mechanism for sending email through the SendObject method.  However, for anything serious you should consider Outlook automation, or sending messages directly to an SMTP server.

The following code shows you how to automate sending a message in Outlook:

Dim olkApp As Outlook.Application
Dim olkNS As Outlook.NameSpace
Dim olkMsg As Outlook.MailItem
Dim olkRcp as Outlook.Recipient
Dim olkAtt as Outlook.Attachment

' do these three lines only once for each "session"
Set olkApp = CreateObject("Outlook.Application")
Set olkNS = olkApp.GetNamespace("MAPI")
olkNS.Logon

' do this for each message
Set olkMsg = olkApp.CreateItem(olMailItem)

' repeat this as often as required to add one or more recipients:
' <address string> can be in one of the three following forms:
'      "john@doe.com" (standard email address)
'      "John Doe" (name of contact in Outlook address book
'      "John Doe <john@doe.com>" (displays name but sends to address)
' <recipient type> can be olTo, olCC, or olBCC
  Set olkRcp = olkMsg.Recipients.Add(<address string>)
  With olkRcp
    .Type = <recipient type>
    .Resolve
  End With

' repeat this as often as required to add zero or more attachments:
' <File name> is the full path to the file you want to attach
' <display name> is an optional name to display in the message instead of the filename
  Set olkAtt = olkMsg.Attachments.Add(<File Name>, , , <Display Name>)

With olkMsg
  .Subject = "This is a test email"
  If <you want to send HTML> Then
    .HTMLBody = "<font face='Verdana' size=2 color='#FF0000'><p>" _
      & "This is a test message in HTML format</p></font>"
  Else
    .Body = "This is a text message in plain text"
  End If
  If <you want to preview and edit the message before sending> Then
    .Display
  Else
    .Send
  End If
End With

You will need to set a reference to the "Microsoft Outlook <YourVersionHere> Object Library".

Note that later versions of Outlook throw up an annoying message saying "another program is trying to send email... yada yada".  To avoid this you can use a product called "Outlook Redemption" from http://www.dimastr.com/redemption.  I recommend this, especially for large quantity emailing.

Finally, there are SMTP code samples available, if you want to Google them.

Good luck :-)

Graham Mandeno
Access MVP since 1996
0
 
Rey Obrero (Capricorn1)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...
 
0
 
GrahamMandenoCommented:
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.
0
 
vITComputingAuthor 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.

0
 
GrahamMandenoCommented:
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?
0
 
vITComputingAuthor 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?  :)
0
 
GrahamMandenoCommented:
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?
0
 
vITComputingAuthor 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. :)
0
 
GrahamMandenoCommented:
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.
0
 
vITComputingAuthor 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.
0
 
Rey Obrero (Capricorn1)Commented:
not for anything, just a reminder
see this
http://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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now