Send Object Help

  • Thread starter Thread starter snappl3
  • Start date Start date
S

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
 
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.
Click to expand...

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
 
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
 
Back
Top