Preparing email list from Access

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

Hi. I don't really understand Access email functionality.
My group mails or emails our newsletter to memebers
depending on their preference. I created a mail label
report. Now I'm trying to figure out how to create an
email list. I'm emailing an adobe file not in Access.

I thought I could create a report with output:
(e-mail address removed); (e-mail address removed); (e-mail address removed)

so that I can copy and paste it into the email "To" field.
However, the report lists the email addresses in a column
so that won't work.
I'm looking for advice on how to fix my report, or find
out how I can get the EmailAddress field to do my bidding.
Thanks for your time! Stephanie
 
Stephanie,

Export your query (with just the email addresses) to a
delimited text file (any kind will do). Open the exported
file in word and do a search and replace (click the more
and the "special") to replace the record delimiters
(paragraph marks - maybe) with semi-colons. Copy the
results into your email address.

If this is goibg to be an ongoing requirement hopefully
some one else has a better way of achieving the desired
result.

HTH,

Terry
 
Stephanie,

You can us a VBA procedure to loop through a recordset based on the
members table and compile a string of email addresses. For example...
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailAddress] FROM Members
WHERE [EmailAddress] Is Not Null")
With rst
Do Until .EOF
strTo = strTo & ![EmailAddress] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo)-1)
Set rst = Nothing
 
Thanks for the reply.
I tried this and couldn't get it to work. I don't know
how to call the code...
I have a Form with Option buttons that call appropriate
Report names. Which means I needed to put your code
attached to a report name.
Function Report_Name() As String
Dim bytOpt As Byte, strAccess As String
bytOpt = Me!frmReport
Select Case bytOpt
Case 7
Report_Name = "Hydrant Email"
End Select
End Function

So I attached your code to the On Open Event of the
HydrantEmail Report but the report produced nothing:

Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null")
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub

Did I do this correctly (never having using code without
the report design wizard's help).

I also tried to call the code via macro, but that didn't
work either.
I'd appreciate any more detailed suggestions. Thanks!
Stephanie
-----Original Message-----
Stephanie,

You can us a VBA procedure to loop through a recordset based on the
members table and compile a string of email addresses. For example...
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailAddress] FROM Members
WHERE [EmailAddress] Is Not Null")
With rst
Do Until .EOF
strTo = strTo & ![EmailAddress] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo)-1)
Set rst = Nothing

--
Steve Schapel, Microsoft Access MVP

Hi. I don't really understand Access email functionality.
My group mails or emails our newsletter to memebers
depending on their preference. I created a mail label
report. Now I'm trying to figure out how to create an
email list. I'm emailing an adobe file not in Access.

I thought I could create a report with output:
(e-mail address removed); (e-mail address removed); (e-mail address removed)

so that I can copy and paste it into the email "To" field.
However, the report lists the email addresses in a column
so that won't work.
I'm looking for advice on how to fix my report, or find
out how I can get the EmailAddress field to do my bidding.
Thanks for your time! Stephanie
Click to expand...
.
Click to expand...
 
Stephanie,

No, the code I gave doesn't have anything to do with a report. Its
purpose is to construct a list of addresses to send the email to, so
ultimately the resulting string needs to end up in the To: field of your
email. This all depends on how you are going about sending the email.
Are you using the DoCmd.SendObject method? Or are you using some
automation code to interact with Outlook? Or what? Since you are
talking about a report, I assume we are talking about sending an email
with the report as an attachment, right? But how are you activating the
emailing of the report... click a button on a form? If so, the code I
suggested would be incorporated into the procedure on the Click event of
the button, and the value of the strTo variable then assigned, for
example, to the To argument of the SendObject method. Anyway, let us
know a bit more detail about what you are trying to achieve.
 
Oh my! What I'd like to do is use code to construct the
email list based on those that want to receive email
(HydrantEmail = Yes) and then have the option to use that
list to send an email telling users that the newsletter is
ready and that the can access it on the following
link.... Can I have 2 choices: 1) send email based on
list along with verbiage (regular email) 2) send email
based on list along with an attachment.

What is DoCmd.SendObject method? I don't at all
understand how email interacts with Access. I don't need a
report. I just didn't know how to "activate" the code.
I'd like to have a form with an option button on it
(the 'ole click here is you want to send to the email
list) to make it easy for users. And then once you have
built the list, how do you associate it with what you want
to send via email?
Sorry to be needy - this is all new to me. Thanks,Stephanie
-----Original Message-----
Stephanie,

No, the code I gave doesn't have anything to do with a report. Its
purpose is to construct a list of addresses to send the email to, so
ultimately the resulting string needs to end up in the To: field of your
email. This all depends on how you are going about sending the email.
Are you using the DoCmd.SendObject method? Or are you using some
automation code to interact with Outlook? Or what? Since you are
talking about a report, I assume we are talking about sending an email
with the report as an attachment, right? But how are you activating the
emailing of the report... click a button on a form? If so, the code I
suggested would be incorporated into the procedure on the Click event of
the button, and the value of the strTo variable then assigned, for
example, to the To argument of the SendObject method. Anyway, let us
know a bit more detail about what you are trying to achieve.

--
Steve Schapel, Microsoft Access MVP

Thanks for the reply.
I tried this and couldn't get it to work. I don't know
how to call the code...
I have a Form with Option buttons that call appropriate
Report names. Which means I needed to put your code
attached to a report name.
Function Report_Name() As String
Dim bytOpt As Byte, strAccess As String
bytOpt = Me!frmReport
Select Case bytOpt
Case 7
Report_Name = "Hydrant Email"
End Select
End Function

So I attached your code to the On Open Event of the
HydrantEmail Report but the report produced nothing:

Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null")
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
End Sub

Did I do this correctly (never having using code without
the report design wizard's help).

I also tried to call the code via macro, but that didn't
work either.
I'd appreciate any more detailed suggestions. Thanks!
Stephanie
Click to expand...
.
Click to expand...
 
Thanks for the links- I'll have to read up.
Currently we are entering Member information in one place,
and creating an email list in Outlook separately. That
means that if someone changes their email, we update it
twice.
We don't email any Access objects- just informational
emails like Volunteer oportunities or a link to our
newsletter. I'd just like Access to do the work for us-
running a fresh list of email addresses every time we're
ready to send out a group email and then populating the,
let's say, BC field of an email. Then we could type the
rest of the email message and know that we are sending it
to all of the members' correct email addresses. We might
include a non-Access-object attachment like an Adobe file.
Thanks for your patience. Stephanie
 
Mail Merge

Ok, I know this is a REALLY old thread, but it shows up 1st when you Google "Access email list," and it does not have a very good answer.

There is a WAY easier way to use an Access data base to send email to a list.

1. In Access create a simple query that pulls information needed. The query should include only those whom you wish to send an email to. Include the email and any other relevant fields (eg name, etc)

2. Draft your email in WORD (if you send this email a lot, save the document. Each time you need to send the email, just change the particulars, such as the date, etc)

3. Click on mailings -> Start Mail Merge -> email message

4. Click on Select recipients and choose your access database. Then choose your query you created in step One.

(5). if you would like, you can use the "insert merge field" button to personalize the email (for example, have each email include the individual's name"). Use the preview results button to see what the email will look like.

6. Click "Finish & Merge" to send the emails. Note that you have to have Outlook set up with an email account for this to work.

This is for Office 2007, but you can do this with other versions of office as well.

The VBA code is not needed--that sendobject command, etc is useful if you need to send an email from a form or something like that, but for simply mailing to a list the merge function is way easier.
 
Back
Top