Emailing a report with multiple addresses / 1 email per address

[hvns]

Hello,

I have a report that is generated based on supplier back orders. For example there is 10 different suppliers that have current back orders in our system. I am needing to send each supplier a report of their current back orders.

So for every supplier there would be a seperate email sent to them with just their data. I am at a loss as where to start with this one.

Thank you in advance for the help

 

[lameid]

Start with a query that will return the DISTINCT suppliers for all the report data.

Use that query to open a recordset. Loop through the recordset sending each supplier an e-mail.

Seeing one of your other posts I got the impression you are comfortable with VBA. Let us know if you need more input.

 

[hvns]

Ok,

Here is the code I hace so far but I am missing something as a copy of the entire report is being sent the all suppliers instead of each supplier only getting their piece:


Private Sub Command50_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_VendorEmailInformation", dbOpenDynaset)
rs.MoveFirst
Do
vVendorNo = rs("VendorNo")
DoCmd.OpenReport "Current_Back_Orders", acViewPreview
DoCmd.SendObject acSendReport, "Current_Back_Orders", "SnapshotFormat(*.snp)", rs("EmailAddress"), "", "", "Current Back Order - Immediate Attention is needed ", "The attached is a listing of items that need your immediate attention. Please contact the Buyer with status, All items that are late are to be expedited at the Supplier's expense.", False
rs.MoveNext
Loop Until rs.EOF
rs.Close
db.Close
End Sub



Thanks in advance for the help...

 

[lameid]

Your report needs criteria... I recommend you make the report's recordsource use a parameter from a form. Then as you loop through the recordset, set the form control to the criteria value from the recordset.

 

[newestAF]

Hey hvns. This is my first time responding to someone so here goes.

Try this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset2
Set db = CurrentDb
Set rst = db.OpenRecordset("Suppliers") 'separate table with suppliers listed
rst.MoveFirst
Set rs = db.OpenRecordset("tbl_VendorEmailInformation", dbOpenDynaset)
rs.MoveFirst
Do Until rst.EOF
vVendorNo = rs("VendorNo")
DoCmd.OpenReport "Current_Back_Orders", acViewPreview, , "[Suppliers] = '" & rst![Suppliers] & "'"
'Make sure the [Suppliers] field and rst![Suppliers] field match

DoCmd.SendObject acSendReport, "Current_Back_Orders", "SnapshotFormat(*.snp)", rs("EmailAddress"), "", "", "Current Back Order - Immediate Attention is needed ", "The attached is a listing of items that need your immediate attention. Please contact the Buyer with status, All items that are late are to be expedited at the Supplier's expense.", False
DoCmd.Close acReport, "Current_Back_Orders", acSaveNo
rs.MoveNext
rst.MoveNext
Loop

DriverExit:
Set rs = Nothing
Set rst = Nothing
Set db = Nothing

Exit Sub

End Sub


Let me know how this works.