Preparing email list from Access

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
 
T

Terry

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
 
S

Steve Schapel

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
 
S

Stephanie

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
.
 
S

Steve Schapel

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.
 
S

Stephanie

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
.
 
S

Steve Schapel

Hi Stephanie,

I suggest you have a look in Help under SendObject. There is also a lot
of good information about emailing at
http://www.granite.ab.ca/access/email.htm

Is the attachment you want to send an output from an object within your
database, or is it another independent file?
 
S

Stephanie

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top