Sending an email through Access

[Guest]

OK - I thought I was a semi-great Access user until I found that I could not
solve a simple problem. I have email addresses on all of my contacts within
my database. Each month I need to send them an email - I have yet to find
the easy way to do this - I know there has got to be a way,....... HELP !!!

 

[Ron Hinds]

Are you using Windows 2000 or XP? Add a reference to the Microsoft CDO for
Windows 2000 Library (CDOSYS.DLL). Open a Recordset on the table that has
the eMail addresses, then loop through it calling the CDO methods like so:

Dim oMail As New CDO.Message
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT eMail FROM Contacts")

Do Until rs.EOF

oMail.From = "(e-mail address removed)"
oMail.To = rs(0)
oMail.Subject = "Your subject"
oMail.TextBody = "Whatever message you want sent."

oMail.Send

Set oMail = Nothing

rs.MoveNext

Loop

OK - I thought I was a semi-great Access user until I found that I could not
solve a simple problem. I have email addresses on all of my contacts within
my database. Each month I need to send them an email - I have yet to find
the easy way to do this - I know there has got to be a way,....... HELP

!!!

 

[Guest]

OK - so I learned Access through the wizards and such - and I am terrible at
SQL and coding - so your response is Greek to me. I am using Windows 2000 -
is there no easy way to do it???

 

[Ron Hinds]

If by "easy way" you mean a Wizard that will write the code for you then the
answer is unfortunately no. But it doesn't get a whole lot easier than this.
Create a Command button on the form you want to send the eMails from - click
Cancel when the Wizard comes up. In the Command Button properties dialog,
select the Events tab. In the box next to On Click, select [Event
Procedure] - a button will appear next to it with an ellipsis (three dots) -
click it and you will be in the code window. Cut and paste the code I gave
you below, substituting eMail and Contacts with the name of your field and
table respectively. Change the Subject and TextBody to be what you want.
Then, with the code window still open, select Tools | References from the
menu. Find Microsoft CDO for Windows 2000 Library in the list and place a
check mark next to it. That's it! Run your form, click the new Command
button and away they go!

 

[Guest]

So I did all that - but I get the following message:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Email Address'.

This is a copy of the code:

Option Compare Database

Private Sub Command0_Click()
Dim oMail As New CDO.Message
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Email Address FROM qry_Email
Addresses")
Do Until rs.EOF
oMail.From = "(e-mail address removed)"
oMail.To = rs(0)
oMail.Subject = "CCC Information : Monthly AAP Reminder"
oMail.TextBody = "Whatever message you want sent."
oMail.Send
Set oMail = Nothing
rs.MoveNext
Loop

End Sub


What am I doing wrong?????

 

[Greg G.]

bumblebug787 said:

So I did all that - but I get the following message:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Email Address'.

Set rs = CurrentDb.OpenRecordset("SELECT Email Address FROM qry_Email

What am I doing wrong?????

Just an off-the-cuff guess, but if you plan on using spaces in field
names, adopt the habit of bracketing them when referencing.

= CurrentDb.OpenRecordset("SELECT [Email Address] FROM qry_Email

FWIW,


Greg G.

 

[Ron Hinds]

You need to put square brackets [] around Email Address since there is a
space in it like so:

Set rs = CurrentDb.OpenRecordset("SELECT [Email Address] FROM
qry_EmailAddresses")

I can't tell from reading it if there is a space in qry_Email Addresses - if
there is, then you will probably want to bracket it, also.

 

[Guest]

OK - now it reads:

Option Compare Database

Private Sub Command0_Click()
Dim oMail As New CDO.Message
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [Email Address] FROM [tbl_Volunteer
Contact Information]")
Do Until rs.EOF
oMail.From = "(e-mail address removed)"
oMail.To = rs(0)
oMail.Subject = "CCC Information : Monthly AAP Reminder"
oMail.TextBody = "Whatever message you want sent."
oMail.Send
Set oMail = Nothing
rs.MoveNext
Loop

End Sub


but I get the message:

Runtime Error '13'
Type Mismatch

 

[Ron Hinds]

I'm sorry - I should have instructed you to use the Microsoft CDO for NTS
1.2 Library (it's a lot less complicated to use - we were missing some
things in the CDO 2000 code). With the code window open, go to Tools |
References and uncheck Microsoft CDO for Windows 2000. Then scroll down and
find the Microsoft CDO for NTS 1.2 Library and check it. Next, change the
code to be like this (I've marked the lines you need to change with ******
above and below):

Option Compare Database

Private Sub Command0_Click()
'**************
Dim oMail As New CDONTS.NewMail
'**************
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [Email Address] FROM [tbl_Volunteer
Contact Information]")
Do Until rs.EOF
'**************
If Len(rs(0)) > 0 Then
'**************
oMail.From = "(e-mail address removed)"
oMail.To = rs(0)
oMail.Subject = "CCC Information : Monthly AAP Reminder"
'**************
oMail.Body = "Whatever message you want sent."
'**************
oMail.Send
Set oMail = Nothing
'**************
End If
'**************
rs.MoveNext
Loop

End Sub

The "If...End If" will ensure that there actually is *something* in the To:
field.

 

[Guest]

Dear Ron:

My auto e-mail function is executed when the "save" button of the form is
clicked.
1. How can I send all fields data and its field name to user???
2. In this form I have subform to keep multiple e-mail address. Link by ID.
Where should I add the "Where" condition?

Thanks.

Vivi

I'm sorry - I should have instructed you to use the Microsoft CDO for NTS
1.2 Library (it's a lot less complicated to use - we were missing some
things in the CDO 2000 code). With the code window open, go to Tools |
References and uncheck Microsoft CDO for Windows 2000. Then scroll down and
find the Microsoft CDO for NTS 1.2 Library and check it. Next, change the
code to be like this (I've marked the lines you need to change with ******
above and below):

Option Compare Database

Private Sub Command0_Click()
'**************
Dim oMail As New CDONTS.NewMail
'**************
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [Email Address] FROM [tbl_Volunteer
Contact Information]")
Do Until rs.EOF
'**************
If Len(rs(0)) > 0 Then
'**************
oMail.From = "(e-mail address removed)"
oMail.To = rs(0)
oMail.Subject = "CCC Information : Monthly AAP Reminder"
'**************
oMail.Body = "Whatever message you want sent."
'**************
oMail.Send
Set oMail = Nothing
'**************
End If
'**************
rs.MoveNext
Loop

End Sub

The "If...End If" will ensure that there actually is *something* in the To:
field.

OK - now it reads:

Option Compare Database

Private Sub Command0_Click()
Dim oMail As New CDO.Message
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [Email Address] FROM [tbl_Volunteer
Contact Information]")
Do Until rs.EOF
oMail.From = "(e-mail address removed)"
oMail.To = rs(0)
oMail.Subject = "CCC Information : Monthly AAP Reminder"
oMail.TextBody = "Whatever message you want sent."
oMail.Send
Set oMail = Nothing
rs.MoveNext
Loop

End Sub


but I get the message:

Runtime Error '13'
Type Mismatch