Email from Access--HELP

[CatahoulaHound]

Sorry if this question seems basic. Can somebody explain how I can send an email to everyone in my database? All of the email addresses are in the database. Does the email text have to be created in Access or can I create it in Word etc?

Thanks for any and all help.

Pat

 

[Remou]

Hi CatahoulaHound
You will find quite a bit on emailing from Access in various fora (both posts and FAQs), such as:
Microsoft: Access Forms
Microsoft: Access Modules (VBA Coding)

You can also attach your table to Microsoft Word as a datasource for emailing, either manually or programatically.

I hope I have not missed your point.

 

[SeeThru]

I use an outside program called 'Group Mail'. You can export a list of email addesses from access to a text file and import them to the program.

 

[Phil4tektips]

Create a form, have a button to call some code...in the properties of the button under the 'Other' tab name the button "Email". Under the Event tab type [Event Procedure] in the On-Click event.

This code will email all the email addresses stored in a column of a table. Hope this gets you started...

Paste the following code into the VB window of the form:

Private Sub Email_Click()
On Error GoTo Err_Email_Click

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strEmail As String
Dim strDate As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Table Name")

strDate = Date

With rst
'writes all the email addresses to a string
While Not .EOF
strEmail = strEmail & ![Column Name] & ";"
.MoveNext
Wend

DoCmd.SendObject , , , strEmail, , , "This is a TEST for Catahoulahound on the" & " " & "" & strDate & "", "This is an automated email directly sent from my database. If I set the end property to FALSE this email will send without viewing it before sending - Thankyou.", TRUE

.Close
End With
Set rst = Nothing: Set dbs = Nothing

Exit_Email_Click:
Exit Sub

Err_Email_Click:
MsgBox Err.Description
Resume Exit_Email_Click

End Sub

~Phil4tektips~
Grant us peace in our days work!

 

[Phil4tektips]

Any news on how you have got on??

~Phil4tektips~
Grant us peace in our days work!

 

[CatahoulaHound]

Phil4tektips:
Thanks for your replies. I did attempt to get this to work, but I had to get the emails out so I resorted to the laborious cut and paste solution. However, I am still trying to learn how to do this. I’ve read the FAQ that was suggested by Remou in the first post to no avail.

When I saw your post I did this:
1-Created a form based on query that had all the names and email addresses in it
2-added a command button, named it and added the code that you suggested.

When I click on the button I get an error box that says

Complie Error:
User-defined Type not defined.

With-- “dbs As DAO.Database,” highlighted.

Not sure where to go from here. What did I do wrong?

All help is appreciated,

Pat

 

[Remou]

Hi
Have you a reference to Microsoft DAO 3.x Object Library?

 

[CatahoulaHound]

OK--I checked the references table and selected Microsoft DAO 3.6 Object Library. So now when I click the button I get an error msg. that says:
"Unknown message recipient(s); the message was not sent"

My table is named Members and the fields are:
MemberID
LastName
FirstName
DOB
Guardian
email – Father
email – Mother

I want to send emails to both mother and father.

Thanks So Much for your help.

Pat

 

[Phil4tektips]

Remou is right there.

What you need to do is simple. In your database, press ALT+F11 to bring up the VB code. When in the VB window click Tools then References.

Select (tickbox) Microsoft DAO 3.x Object Library and Click OK.

The error isnt to do with anything you've done wrong its just not recognising what you are talking about because you havent set one of the properties.

This used to be a setting that was pre selected in earlier versions of MS Access, but recent versions have their default set as unchecked.

Get back as to how you get on! Phil.

~Phil4tektips~
Grant us peace in our days work!

 

[Phil4tektips]

Can you post the code you've got so far?

~Phil4tektips~
Grant us peace in our days work!

 

[CatahoulaHound]

Sure:

Private Sub email2_Click()
On Error GoTo Err_Email_Click

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strEmail As String
Dim strDate As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Members")

strDate = Date

With rst
'writes all the email addresses to a string
While Not .EOF
strEmail = strEmail & ! & ";"
.MoveNext
Wend

DoCmd.SendObject , , , strEmail, , , "This is a TEST for Catahoulahound on the" & " " & "" & strDate & "", "This is an automated email directly sent from my database. If I set the end property to FALSE this email will send without viewing it before sending - Thankyou.", True

.Close
End With
Set rst = Nothing: Set dbs = Nothing

Exit_Email_Click:
Exit Sub

Err_Email_Click:
MsgBox Err.Description
Resume Exit_Email_Click
End Sub

Thanks
Pat

 

[Phil4tektips]

Private Sub email2_Click()
On Error GoTo Err_email2_Click

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strEmail As String
Dim strDate As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Members")

strDate = Date

With rst
'writes all the email addresses to a string
While Not .EOF
strEmail = strEmail & ! & ";"
.MoveNext
Wend

DoCmd.SendObject , , , strEmail, , , "This is a TEST for Catahoulahound on the" & " " & "" & strDate & "", "This is an automated email directly sent from my database. If I set the end property to FALSE this email will send without viewing it before sending - Thankyou.", True

.Close
End With
Set rst = Nothing: Set dbs = Nothing

Exit_email2_Click:
Exit Sub

Err_email2_Click:
MsgBox Err.Description
Resume Exit_email2_Click
End Sub


I've made some slight changes above, nothing to get rid of teh error message your getting though. I take it your button is called "email2"?

Does your table have a column/field called "email" or are there two fields called "email - Father" and "email - Mother"?

~Phil4tektips~
Grant us peace in our days work!

 

[CatahoulaHound]

Actually, not knowing what was going to happen when I clicked the button, I created a new field called email and placed only my email address in it. All of the email addresses that I must use are loacated in "email-father" and "email-mother" The button is named "Email2"
I pasted the new code in and, as you susspected, I get the same error. Seems that it is looking for a "reipient(s)" to send to??

Thanks for taking the time to help me with this.

Pat

 

[Phil4tektips]

try changing:

strEmail = strEmail & ! & ";"

to:

strEmail = "xxxx@zzzz.co.uk"


Put your email address in for the "xxxx@zzzz.co.uk"....test to see if that works first. I think its a problem with the string. Have you only got one email address in that field at the moment? If so trying adding another email to that field/column (your email again if your testing). Use the original code. You should see your email appear twice in the To: section of the email?

Is all that section of code is trying to do is pull out all the email addresses in the specified column (email) of the specified table (Members), and write them all to one string so that the emails To: section becomes populated with all the email addresses.


~Phil4tektips~
Grant us peace in our days work!

 

[Remou]

Hi CatahoulaHound
I have tried Phil4tektips code and it works fine for me. Have you tried a debug.print on strEmail, or a step through to see what strEmail contains? I ask, because the only way I can get this to fail in the manner you mention, is with a blank "To" string. Even DoCmd.OutputTo by itself does not fail for me.

 

[Phil4tektips]

Ok ..... I kind of just realised.....

If you have any blank fields the string will become corrupted.

You need to make sure that every cell has an email address in.

So if you populated "email" with your email address throughout all the records this strEmail would work.

~Phil4tektips~
Grant us peace in our days work!

 

[Phil4tektips]

Because if you leave a blank the string becomes something like:

zzz@xxx.co.uk; ccc@hhj.co.uk; ;fff@ggg.co.uk;

Its the double semi-colon in the middle of the string that is causing the error failure I believe. I got the same error by deleting an email addy from one of my lists. So just make sure every record in the 'email' field has an email addy!

~Phil4tektips~
Grant us peace in our days work!

 

[Remou]

I often use something like:

While Not .EOF
If ![email] & "" <> "" Then
    strEmail = strEmail & ![email] & ";"
else
   'Deal with blank email
End If
.MoveNext

Wend

 

[CatahoulaHound]

OK
I've replaced the line that you suggested Phil and added my email address. when I hit the button Outlook pops up the email, subject line and body intact, ready for me to hit send.

 

[Phil4tektips]

Remou has solved it there....

Use this code then and it should deal with blanks!:

Private Sub email2_Click()
On Error GoTo Err_email2_Click

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strEmail As String
Dim strDate As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Members")

strDate = Date

With rst
'writes all the email addresses to a string
If ! & "" <> "" Then
strEmail = strEmail & ![email] & ";"
else
'Deal with blank email
End If
.MoveNext
Wend

DoCmd.SendObject , , , strEmail, , , "This is a TEST for Catahoulahound on the" & " " & "" & strDate & "", "This is an automated email directly sent from my database. If I set the end property to FALSE this email will send without viewing it before sending - Thankyou.", True

.Close
End With
Set rst = Nothing: Set dbs = Nothing

Exit_email2_Click:
Exit Sub

Err_email2_Click:
MsgBox Err.Description
Resume Exit_email2_Click
End Sub


~Phil4tektips~
Grant us peace in our days work!