Loop Through Query, Send Multiple Emails

[tdoolittle]

I've been looking everywhere for help on this, I think the issue is I've read so much that I've now confused myself and trapped myself from seeing the answer.



What I'm dealing with right now is a Query [qry_TodaysTracking] and a Report [rpt_Fedex_Email]. At the end of the day, all of our shipments have been loaded into the database and I have my query [qry_TodaysTracking] show each tracking number that was generated today.

From here I have a report laid out like a FedEx clearance instruction letter with a field that is populated by [qry_TodaysTracking]![TrackingNumber]. What the report is currently/obviously doing is giving 6 pages, each page with one of the unique tracking numbers for today. Currently I'm using the following to at least mail this report out to the required individuals through a button on a form I have:

Private Sub btn_FedExEmail_Click()
    DoCmd.SendObject _
    acSendReport, _
    "rpt_Fedex_Email", _
    acFormatPDF, _
    "test@fedex.com;test@fedex.com", _
    "test@fedex.com", _
    , _
    "Clearance Instructions - " & Date, "Thanks", _
    True _
End Sub

However, going forward I am no longer allowed to submit a multipage PDF to our FedEx reps and they are requiring an individual PDF/email for each tracking number we have. So what I need is code that will relay my first tracking number on the [qry_TodaysTracking], make a PDF, send it, then move the next tracking number and repeat. In my mind and using my limited knowledge this would require a loop. But researching is only confusing me. Could someone assist me in breaking this down?

Thanks guys!

[June7]

Open a recordset object and loop through the records. Within the loop, open report filtered to TrackingNumber from record, send report, close report, move to next record, repeat. Fairly common topic in forum. https://www.accessforums.net/access/...ect-37936.html

[tdoolittle]

Well I tried using those resources. Seems to be the same as most research I've been doing, everyone posts tons of code up but I don't find anything exactly explaining what the code is doing. I'm at this point right now:

Private Sub Command33_Click()
Dim rs As DAO.Recordset
stCaption = "Clearance Instructions - " & Format(Now(), " mm-dd-yyyy")
myPath = "X:\SHIPPING - FEDEX\FedEx Letters\"
stTo = "doolittle@projectorsupercenter.com"
stEmailMessage = "Please see the attached clearance instructions."
stSubject = "Clearance Instructions - " & Date
stReport = "rpt_FedEx_Email"
Set rs = CurrentDb.OpenRecordset("SELECT TrackingNumber FROM qry_NonTRANS_Tracking_Today")
DoCmd.OpenReport "rpt_FedEx_Email", acViewPreview
While Not rs.EOF
DoCmd.SendObject acSendReport, , acFormatPDF, stTo, , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
rs.MoveNext
Wend
DoCmd.Close acReport, stReport, acSaveNo
rs.Close
End Sub

I guess I'm getting somewhere as I have two tracking numbers currently coming up in the query I'm referencing and my on click event is prompting me to send two emails. However each email contains one large PDF, with two pages, each page referencing one of the tracking numbers. Now I just need to figure out how to break it apart so each email contains a PDF with one page referencing its own tracking number. I have a feeling I simply need to move something referencing the RecordSet around? I'm assuming "rs" stands for recordset.

Getting somewhere which is promising though! I'll keep following the rabbit hole of links and see where it takes me

[June7]

Yes, need to open and close the report within the loop and open it filtered to TrackingNumber of the current recordset record.

Is TrackingNumber a text data type? Try:

Private Sub Command33_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT TrackingNumber FROM qry_NonTRANS_Tracking_Today")
stCaption = "Clearance Instructions - " & Format(Now(), " mm-dd-yyyy")
myPath = "X:\SHIPPING - FEDEX\FedEx Letters\"
stTo = "doolittle@projectorsupercenter.com"
stEmailMessage = "Please see the attached clearance instructions."
stSubject = "Clearance Instructions - " & Date
stReport = "rpt_FedEx_Email"
While Not rs.EOF
DoCmd.OpenReport "rpt_FedEx_Email", acViewPreview, , "TrackingNumber='" & rs!TrackingNumber & "'"
DoCmd.SendObject acSendReport, , acFormatPDF, stTo, , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint
DoCmd.Close acReport, stReport, acSaveNo
rs.MoveNext
Wend
rs.Close
End Sub

[tdoolittle]

Thanks for coming back, very much appreciated! I have been playing around with this for a while and tried what you have offered but I always get a PDF report showing a two-paged report each page with its own tracking number but get two separate emails, just both trying to send the same two-paged report. For the life of me I can not get it to send two separate one page reports each with their own tracking number.

Right now the command button is located on a form which contains a subform showing the query which supplies the two tracking numbers. The button click as you know sends the report which is bound to a query. The report has two fields, one which shows today's date and one which pulls directly from the bound query the [TrackingNumber], which correct, is a text data type. The query itself is pulling three columns of data. One is the [TrackingNumber] (text data) one is a [TRANS] (yes/no data) and one is [Date] (date). The [TRANS] has a criteria of "False" set, as I want to only see tracking which is not checked as TRANS and the [Date], for testing reasons as I have no new data in the database yet, has its criteria set to =Date()-60, therefore pulling the two tracking numbers that were loaded within the last 60 days. (Have yet to add all my new data until I get everything working)

I've been playing around with the binding of the report and where the report is grabbing data from, I don't know if this would make a difference? I've tried the way above, I've also tried having the report still bound to the query and having the tracking number field reference the direct subform of where the button resides ([Forms]![frm_Overview]![frm_Overview_NonTRANS_Tracking_Today].Form![TrackingNumber]), this results in a two paged report, each page only referencing the first tracking number so I got myself further away from the goal

Sorry if this is a lot of information, it's mostly me thinking out loud trying to walk myself through this!

[June7]

Maybe my code will work now. I did a minor edit of previous post. Was missing a comma for one of the unused arguments of the OpenReport method.

[tdoolittle]

Oh man, it worked! Ahhhhhhhhhhhhhhhhhhhh joyous days! Thank you very much for your assistance, incredibly appreciated! I'm amazed at what this forum has helped me accomplish after knowing literally nothing about Access or VBA. Thanks so much!