Email people found in a query??

E

e_Clark

New member
Local time
Today, 15:05
Joined
Jan 13, 2010
Messages
9
Right, well I have created a query that finds all of my employees that have made a certain selection (so tick box is ticked). The query contains their name and email address.

What code would I then use and attach to a button that says "email employees with box ticked" to put all of those employee's email addresses into an email?

I'm guessing this must be fairly simple, but I'm just not even sure where to start!

Many thanks.
 
Alot depends on what you are emailing them, and how?
Do the emails include attachments?
Are you using Outlook?

More info needed.

David
 
DCrake said:
Alot depends on what you are emailing them, and how?
Do the emails include attachments?
Are you using Outlook?

More info needed.

David
Click to expand...

Sorry!

I just want it to be a blank email, as it will be a different message each time, so I literally just want the email addresses of those selected people to be put into an email from outlook, ready for the user to type the address and subject line etc.

So far I have (with Business Ethics being the name of the query):

Private Sub Command7_Click()

Set db = CurrentDb
Set rs = db.OpenRecordset("[Business_Ethics]", dbOpenSnapshot)
Do Until rs.EOF
tovar = tovar & rs![EmailAddress] & ";"
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , tovar

End Sub

At the moment the code is stopping at the [Business Ethics] bit saying:

Run-Time error '3078':
The Microsfot Office Access database engine cannot find the unput table or query '[Business_Ethics]'. Make sure it exists and that its name is spelled correctly.

That is the name of the query though! I don't know if the rest of it works as I don't know why this bit doesn't...
 
If you want to send the email through Outlook then use this on the buttons on click event:-

Code: 
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("[COLOR=red]YourQueryNameHere[/COLOR]", dbOpenSnapshot)
 
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
            If IsNull(.Fields([COLOR=red]0[/COLOR])) = False Then
                sToName = .Fields([COLOR=red]0[/COLOR])
                sSubject = "[COLOR=red]YourSubjectHere[/COLOR]"
                sMessageBody = "[COLOR=red]YourMessagebodyHere[/COLOR]"
                
                DoCmd.SendObject acSendNoObject, , , _
                    sToName, , , sSubject, sMessageBody, False, False
            End If
            .MoveNext
        Loop
End With
 
Set MyDb = Nothing
Set rsEmail = Nothing
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 3021 Then
     MsgBox "There are no records to send"
     End If

Simple change the items highlighted in red. The "0" should be replaced with the column number that the email address field is held in in your query.
 
dbDamo said:
If you want to send the email through Outlook then use this on the buttons on click event:-

Code: 
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("[COLOR=red]YourQueryNameHere[/COLOR]", dbOpenSnapshot)
 
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
            If IsNull(.Fields([COLOR=red]0[/COLOR])) = False Then
                sToName = .Fields([COLOR=red]0[/COLOR])
                sSubject = "[COLOR=red]YourSubjectHere[/COLOR]"
                sMessageBody = "[COLOR=red]YourMessagebodyHere[/COLOR]"
 
                DoCmd.SendObject acSendNoObject, , , _
                    sToName, , , sSubject, sMessageBody, False, False
            End If
            .MoveNext
        Loop
End With
 
Set MyDb = Nothing
Set rsEmail = Nothing
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 3021 Then
     MsgBox "There are no records to send"
     End If

Simple change the items highlighted in red. The "0" should be replaced with the column number that the email address field is held in in your query.
Click to expand...


Thanks, I've just tried that, and when I run it it highlights an error with:
If IsNull(.Fields(0)) = False Then

saying:
runtime error 3265, item not found in this collection.

What does this mean/how do I sort it?
 
Try changing it to rsEmail("NameOfField")
 
I am guessing that you are using Access & Outlook 2007?

This error message is probably the result of tighter security for Outlook 2003 onwards(?)

In A2000 you can get around this by installing CDO and sending directly from your SMTP server bypassing Outlook completely, this might also work for your version. I use this method and here is the code I use:-


Code: 
Dim mydb As DAO.Database
Dim rs As DAO.Recordset
Set mydb = CurrentDb()
Set rs = mydb.OpenRecordset("[COLOR=red]YourQueryNameHere[/COLOR]", dbOpenSnapshot)
With rs
.MoveFirst
Do Until rs.EOF
If IsNull(rs.Fields([COLOR=red]0[/COLOR])) = False Then
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "[COLOR=red]YourSubjectHere[/COLOR]"
objMessage.From = "[COLOR=red]SenderNameHe[/COLOR][COLOR=red]re[/COLOR] <[COLOR=red]SenderEmailAddressHere[/COLOR][COLOR=black]>[/COLOR]"
objMessage.Sender = "[COLOR=red]SenderNameHe[/COLOR][COLOR=red]re[/COLOR] <[COLOR=red]SenderEmailAddressHere[/COLOR][COLOR=black]>[/COLOR]"
objMessage.To = rs.Fields([COLOR=red]0[/COLOR])
objMessage.TextBody = "[COLOR=red]YourMessageBodyHere[/COLOR]" 
 
objMessage.Configuration.Fields.Item _
("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2
 
objMessage.Configuration.Fields.Item _
("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = "[COLOR=red]YourSMTPServerAddressHere[/COLOR]"
 
objMessage.Configuration.Fields.Item _
("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25
objMessage.Configuration.Fields.Update
 
objMessage.Send
End If
.MoveNext
Loop
End With
rs.Close
Set mydb = Nothing
Set rs = Nothing
 
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 3021 Then
        MsgBox "There are no emails to send"
     End If

Might be worth giving this a go. Please ask if you do not know how to install CDO.
 
You must log in or register to reply here.

Similar threads

N
Solved I want to be able to send a few emails in access 2016 using gmail but am having challenges
Replies
7
Views
411
nector
N
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
641
CJ_London
C
N
Solved How to remove this error "the execution this software stopped due to run time" error in MS Access VBA
Replies
11
Views
648
Minty
Minty
D
Solved Query showing all records instead of just the ones that meet the criteria
Replies
3
Views
163
The_Doc_Man
The_Doc_Man
E
Emmanuel Katto : How to Set Outlook to Open in HTML Format from Access Email
Replies
2
Views
189
Trevor G
Trevor G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom