Send Object Help

[snappl3]

Below is the code i have to automate pulling an e-mail from a query. However
there seems to be something wrong w/ it. I'm pretty new to the whole
programming thing, so any help would be greatly appreciated. Thanks in
advance.

Sub Checklist()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("qryClientEmails")
With rs
Do Until .EOF
If Len(rs!Email & vbNullString) > 0 Then
DoCmd.SendObject _
To:=rs![Queries]![ClientEmails]![Contact E-mail], _
Subject:="Johnson & Johnson Supplier Checklist", _
MessageText:="Message Text"
End If
..MoveNext
Loop
..Close
End With

Set rs = Nothing

End Sub

 

[Rick A.B.]

Below is the code i have to automate pulling an e-mail from a query.  However
there seems to be something wrong w/ it.  I'm pretty new to the whole
programming thing, so any help would be greatly appreciated.  Thanks in
advance.

I would suggest you create a form and use your query as it's record
sourse.
Add a textbox to your form boundto the Email field of your query. You
can then add a couple of unbound text boxes for your email subject and
your email message and a button to send the email. That way you won't
have to rework your code if your subject or message changes.

Something like the following would work with the code in the onclick
event of your button.

'Check if Subject or Message box is empty
If IsNull(Me.txtSubject) Or IsNull(Me.txtMessage) Then
MsgBox ("You must Enter Subject and message for Email")
Me.Undo
Me.txtSubject.SetFocus
Else

Dim rs As DAO.Recordset

Dim strEmail As String

Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then

rs.MoveFirst
While Not rs.EOF
strEmail = rs!EmailName '[EmailName]

DoCmd.SendObject , , , strEmail, , , txtSubject, txtMessage, False



rs.MoveNext


Wend
rs.Close
Set rs = Nothing

End If

End If

 

[Dale Fye]

I like Rick's method.
But to fix yours, I think you need to make 2 changes:

1. assuming that qryClientEmails has has a field named [Contact E-mail],
change the To line to:

To:= rs("Contact E-mail")

2. Change the order of:

..Close
End With

To:

End With
..Close

HTH
Dale