Sending Email to multiple recipients

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I am wanting to send shipment confirmation emails to customers - usually
will be about 10-15 per day, all at once. I currently have a query that shows
the records of those that will need the email sent to them, and tie it in to
a report that operates off of an SQL line (instead of a saved query) that
shows the correct recipients as well. How would I set up VBA (using
sendObject?) to go through this report and send each customer their own
confirmation? As the report currently breaks per customer, would the email
do the same? Any assistance or code samples would be very helpful.
Thanks!
-gary
 
They way I have done that in the past is to create a recordset in my code
with the account number and email addresses of my report recipients.

2. Next, I add a textbox to my form (give it zero width if you don't want
to see it) and I use some code in the reports query that checks to see
whether that textbox is filled in, something like:

WHERE Forms(myForm).txt_acctno = rs("acctno")
OR Forms(myForm).txt_acctno IS NULL

3. Then I loop through the recordset, populate the textbox (txt_acctno)
with the rs("acctno") value, then send the report using sendObject. Keep in
mind that with the new Office security patches, you will get confirmation
messages for each of these (although I hear there are ways around this).

HTH
Dale
 
Thank you Dale!
This gives me quite a bit to work with. If possible, would you be able to
show me how to set up the recordset and the loop?
I am using a query (Ship_Confirmation_Email) to gather the recipients info
(ShipTo_email and TrackingID) and bring it into the report
(Production_SHIP_CONF)
- which is run from some SQL (joined with the above query). I am hoping to
have this email function ran from a single button click, or possibly a Timer
Event (which would work better) - I know it will ask me to click to send the
email for each as I am currently using SendObject as an Order Received
Confirmation (which works better as it fires once an order is received, but
since we do massive shipping, it would not work on the shipping side)
I think I understand what you are saying, but could you possibly detail it
out for me? I am not the familiar with loops yet.
Thank you so much
 
Gary,

This is the code I use behind a button. The continious form is
populated by a query that lists everyone I've selected to recieve an
email. The form has an email subject box and an email message box and
what ever is put in these text boxes supplies the subject and message
for each email. I can't remember the reason but I had to put an
unbound text box on the form [ThisID] and assign it to the StudentID
of the the record set. I was in a hurry when I did this and never
bothered to figure it all out. The code moves through the recordset
of the form, opens the report "NotComplete" and prints it for our
records, then emails the report as an attachment in RTF format and
finally displays a message for each email sent. Adapt it to you own
fields and needs and look out for word wrap. If I remember correctly,
Salad helped me with it. Thanks again Salad

Dim strEmail As String
Dim rs As DAO.Recordset
Dim strID As Integer
Dim blnOK As Boolean
Dim Msg As String
Dim strName As String
blnOK = True
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then

rs.MoveFirst
While Not rs.EOF
strEmail = rs!EmailName '[EmailName]
strID = rs!StudentID
strName = rs!LastName
[ThisID] = strID
Msg = "You have Emailed report to " & [strName]



DoCmd.OpenReport "NotComplete", acViewNormal, ,
"[StudentID] =" & Forms![frm_qry_22_EmailIncomplete]![ThisID]

DoCmd.SendObject acSendReport, "NotComplete",
acFormatRTF, strEmail, , , txtSubject, txtMessage, False
MsgBox Msg


DoCmd.Close acReport, "NotComplete"


rs.MoveNext


Wend
rs.Close
Set rs = Nothing

End If

End If
 
Back
Top