Access Email Addresses

[srctr]

I want to take the email addresses I have in Access, Run a Query that will
pull the records from the table that say Yes to Email Newsletter. Then take
this query and open an Outlook email with the BCC filled in with these email
addresses. Fill in the Subject with This month's Newsletter, and the message
box would say something like "Here is this month's newsletter..." Then allow
me to attach a PDF file and manually send.

How can I do this?

 

[Arvin Meyer [MVP]]

This code will send them 1 at a time:

http://www.datastrat.com/Code/MultipleEmail.txt

You could also stick them all in a BCC field like:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strBCC As String
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("qryYourQuery", dbOpenSnapshot)

With rst
If .RecordCount > 0 Then
.MoveFirst
For i = 1 To .RecordCount
If Len(!Email) > 0 Then
strBCC = strBCC & !Email & ";"
End If
.MoveNext
Next i
End If
End With
strBCC = Left$(strBCC, Len(strBCC) - 1)

Then modify the code at:

http://www.datastrat.com/Code/OutlookEmail.txt

to send the email.

 

[Armen Stein]

You could also stick them all in a BCC field like:

Watch out though, because many ISPs limit the number of BCC addresses
you can use in an email. Dozens should work, but maybe not hundreds.

Also: An often overlooked feature of Word is the ability to mail
merge a document and send it via email. You might want to check that
out too.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Arvin Meyer [MVP]]

That's very true. Our local computer society creates a quarterly mailing
that goes to several hundred. We break it up into 5 groups to get the mail
out. Similarly, my own ISP limits to 250 a day per email account,
fortunately I own my own domain and can use multiple accounts, if necessary.

 

[srctr]

Okay, but I am a novice here. Where do I stick this code? A Form, report,
in a query and where?

 

[Arvin Meyer [MVP]]

Put it in a module and call it from a command button in a form. The BCC code
below needs to be changed to fit the query you draw your email addresses
from "qryYourQuery" and put in a sub in a form or module. If this isn't a
regular thing, I wouldn't bother with coding the BCC string into a variable,
just put:

Debug. Print

after the last line like:

strBCC = Left$(strBCC, Len(strBCC) - 1)
Debug. Print strBCC

then just copy the output from the Immediate Window, and paste it into the
BCC field in your email. If you are really a novice, you'll need to spend
some more time familiarizing yourself with Access and code. You can't just
jump in and figure it will happen totally automatically. Start learning and
ask questions instead of trying to swallow everything at once.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com