MS Access - Mass Emailing?

Question

I'm using MS Access to create a database with over 5000 contacts. These contacts are separated up into which employee the contact belongs to, and then again into categories for easy searching. What I want to do is create a button that will open up a query in table form (simple), then have check boxes so an employee can select, for example, 100 contacts to send an email to out of the 110 in the table, and then send a mass email such as a newsletter (not so simple!). I've been going nuts trying to work out how to do this as I don't really understand programming (I'm a temp thrown into this job and just doing the best I can) and all I can find on the matter is something about loops (no idea!) and that I need software to do this.

Any solutions for me please? I'd like to avoid buying/installing software if possible and if you do have an answer, please make it as simple as possible...

Thanks in advance!

Kate

Answer 1

I think you're going to need to learn some VBA coding to get this done. This tutorial might be useful.

Heres a way to send email from access.

Answer 2

Here's another resource for sending e-mail through MS Access

Answer 3

I know how to send an email to a single recipient thru Access, but it's the mass emailing I'm having troubles with. The tutorial was useful in understanding how coding works, but it didn't tell me what I need to write to get the right code.

sigh this is ridiculous.

Does anyone know a code I can just copy and paste in?

Answer 4

I have just created the following working example in MS Access 97.

A sample table (I tested the code with valid e-mail addresses):

ID Name Email

1 Rics [email protected]

2 Kate [email protected]

3 X [email protected]

A form with one button. The following code is being performed when the button is clicked:

Private Sub Mail_Click()

    Dim r As Recordset
    Dim email As String
    Set r = CurrentDb.OpenRecordset("select * from Addresses")
    Do While Not r.EOF
        email = r(2)
        DoCmd.SendObject acSendNoObject, Null, Null, email, Null, Null, "Test subject", "Message body of the test letter", False, Null
        r.MoveNext
    Loop
    r.Close

End Sub

I hope you could insert it into your application.

Answer 5

The answer that rics has supplied will send an e-mail to everyone in a recordset, but it sounds like maybe what you are wanting to do is send a single e-mail to a custom distribution list. To do that, tweak rics' code to build up the address string something like this:

Private Sub Mail_Click()

Dim r As Recordset
Dim email As String
Set r = CurrentDb.OpenRecordset("select * from Addresses")
Do While Not r.EOF
    email = email & r(2) & ";"
    r.MoveNext
Loop
r.Close

DoCmd.SendObject acSendNoObject, Null, Null, email, Null, Null, "Test subject", "Message body of the test letter", False, Null

End Sub

Answer 6

Kate,

Sorry to say that there is no specific "magic" code for what you plan to to. You will have to write something. My solution would be:

1. Create a form with 3 controls: 1 text control, 1 listbox control.,1 "send" button
2. The text control contains the text to send
3. The list control displays all my available emails (populated with a recordset)
4. Multiselect will be enabled, so that I can select multiple items in the list

5. By a click on the button, I will

   Concatenate all selected emails to get a "sendTo" string such as sendTo = "[email protected];[email protected]". Call the doCmd.sendObject method using sendTo + the text string as an argument

Options could be to

• Have an extra control for email subject
• Keep a trace of sent mails in a table (subject, text, date, people reached). It could be as basic as "one record per mail" with a memo field to record the sendTo text string (Of course you could build something smarter with multiple tables to stick to the many-to-many relation that can be established between your people table and your mail table, but you might not need it)

Answer 7

Thanks for the code...

...BUT...

I don't understand how to link the code to the macro and therefore to the button?

Told you I'm a complete beginner

Answer 8

Got it working :)

The code was great but it needed some tweaking to work specifically with my data. After a lot of errors popping up this is what I finally came up with:

    Dim r As Recordset
Dim Email As String
Set r = CurrentDb.OpenRecordset("select Email from FranksFinanceBrokers")
Do While Not r.EOF
    Email = Email & r(0) & ";"
    r.MoveNext
Loop
r.Close

DoCmd.SendObject acSendNoObject, Null, Null, "", "", Email, "", "", True, Null

End Sub

Thanks for ur helps guys!

Answer 9

Emailing from Access has a number of pitfalls, not least of which is that doing so can make you look like a spammer and cause problems with your email host. Plus, there are a bunch of security issues. The best source for information on this is Tony Toews's Email FAQ.

Answer 10

No kidding there's pitfalls - just came across one:

Tried adding the code to a different list of contacts, but not all of them have an email address so when i run the code, it comes up with a runtime erro 2295 because some of my recipients are invalid, and i presume those are those empty fields. I just tried to adjust the code so it skips that error but to no avial.

this is ust ridiculous.

so 2 questions: 1. Does anyone know how to adjust my code to fix that prob? 2. Would it be easier to purchase software that just mail merges for me and if so, what should I get?

Thanks!

Answer 11

There are a couple of ways to get around the missing e-mail addresses. The easiest is to adjust your SQL to exclude them:

select Email from FranksFinanceBrokers WHERE (Email IS NOT NULL) AND (Email <> "")

The other approach would be to add an IF statement to the string building code:

IF Not IsNull(r(0)) AND r(0) <> "" THEN Email = Email & r(0) & ";"

I would just filter it at the SQL level -- more efficient and just easier.

Would it be easier to buy software to do this? Yes, but where's the challenge in that? :-) You are already most of the way there, so I'd stick with it.

Answer 12

Ok so I thought I overcame my error problem by adjusting the code to ingnore it, but it turns out it doesn't work! John, I tried both of your ideas and it's like they tried to work, but Access froze and was "not responding" ... I tried a few variations based on that idea too but to no avail :(

Any ideas? Sorry I'm so sucky at this!!

Also, is there anyway to make it so Emails get sent only to contacts who have a tick in their respective checkbox? I don't really understand checkboxes so yeah...

Answer 13

Thanks Ric and Kate, between the both of you. The solutions you provided works.