Sending Multiple E-Mails From Access

I have created a form in Access, which has fields like name, lastname, job, email, city, country....

And i created a "send email" button and added this command:

--------------------------------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String
Dim Subject As String
Dim Message As String

If IsNull([E-Mail]) Or ([E-Mail]) = "" Then
MsgBox "This Person Has No E-Mail Address"
Exit Sub
Else
stLinkCriteria = Me![E-Mail]
stSubject = ""
DoCmd.SendObject acSendNoObject, , , stLinkCriteria, , , stSubject
End If
--------------------------------------------------------------------------

But this sends individual emails to selected person on form.

How can i edit this script to send emails to all persons which are located in my "e-mail" field?

And is there a way to filter with another field like "city" or "job" ?

Thanks in advance :)

Welcome Foreigner.

If you need to send an email to a group of person, you will have to loop the email addresses and stock them in String variable with a semi-colon between each.

This will result in something like
ToList = "address1;address2;address3"

You then have to use the "TO" parameter of the SendObject function to put this variable.

For the filter, I presume an SQL query with a WHERE clause would do it (?)

Hope it helps a bit...

Thanks alot :)

Since new contacts added to my database everyday, will a loop like this work ?

ToList = "E-Mail1;E-Mail1+1"

In this case how can i indicate when last record reached.

Regards :)

If you use a Recordset, you just have to create a loop and stock each address.
Something like:


For i = 0 To Rs.RecordCount - 1 'Rs being the name of the Recordset
ToList = ToList & Rs!E-Mail & ";"
Next

with your help... i made this;

Private Sub Send_Email__Click()
Dim X
X = fHandleFile("mailto:" & strMailTo, WIN_NORMAL)
End Sub

Private Function strMailTo() As String
Dim rs As New ADODB.Recordset
Dim str As String
Dim X As Integer
rs.Open "emails", CurrentProject.Connection, adOpenDynamic, adLockReadOnly

If rs.EOF = True Then
MsgBox "No e-mail address."
Else
rs.MoveFirst
Do While Not rs.EOF

str = str & rs(0) & ";"
rs.MoveNext

Loop
End If

strMailTo = str
Set rs = Nothing
End Function

"emails" is the query which i collect emails from database.

i also added mdlShellExecute module

but when i run this command, i get this error:

compile error.. user-defined type not defined

and shows "Dim rs As New ADODB.Recordset" line as the error line.

what am i doing wrong :(

when i run this command it runs and sends a single email. but i want all email addresses which is stored in the query to be added to email;

Private Sub cmdEmail_Click()
Dim X
X = fHandleFile("mailto:" & Me!email, WIN_NORMAL)
End Sub

For the error, I think you just did not create a reference to ADO ?
Is it possible ?

depending on your warning, i edited like this;

Private Sub Mail_Click()

Dim X
X = fHandleFile("mailto:" & strMailTo, WIN_NORMAL)

End Sub

Private Function Mail_Click() As String
Dim rs As New ADODB.Recordset
Dim str As String
Dim X As Integer

With rs
.Source = "emails"
.Open CurrentProject.Connection, adOpenDynamic, adLockReadOnly

End With

If rs.EOF = True Then
MsgBox "No E-Mail Addresses."
Else
rs.MoveFirst
Do While Not rs.EOF

str = str & rs(0) & ";"
rs.MoveNext

Loop
End If

strMailTo = str
Set rs = Nothing
End Function

---------------

now it gives this error;

ambigous name detected: Mail_Click()
This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired.

----------------

oh well.. i guess this is "pro" for me :) i couldnt make it.. but thanks for all your time and your patience :)

with my best regards