Email Individual Reports separately

[vickster3659]

Thank you, June7. I understand why name will make a poor key field. Before I continue, I am going to consider changing this. This db is not overly complex, so I think I can accomplish this without breaking the entire db. Of course, I will make a backup copy to work with first.....

[vickster3659]

Hi,
I have modified the database to change the key field from a name to a number, RequestorID. I made the suggested change to the code from your last response. I am now getting "Run time error 3265 Item not found in this collection.

I did a little research on this and found a suggestion alluding to RequestorID is not in tables/queries/etc., so pulled the field into the report, and into the query that the report is based on. Still getting this error.

I was going to upload the updated db, however, this will exceed the allowable size. If you can tell me how to remove the first upload, I can send the revised.

Thanks again,
VCoutu

[June7]

Don't have to remove first upload. The limit is per attachment, not total of all.

[vickster3659]

Good morning......Figured it out.....forgot I had to zip the db.

Attached the new one, db2.zip

Thanks!

[June7]

Change the code opening recordset to include the RequestorID field.

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT RequestorID, RequestorEmail FROM qryEmailToRequestors WHERE [DateDue]=Date();")

Need to move the code to open report after

While Not rs.EOF

[vickster3659]

Hi,
Almost there!!! The code has no more error messages popping up, however the WHERE [DateDue]=Date() part is not working. The report shows all transactions, not just transactions for today.

Also, when the report is attached to the email, and I click send, only one person will get their email. After I click send, the Output To box pops up to select format (I am selecting RTF), when I click OK, Outlook is not opening for the next email.

Thank you! Thank you!! Thank you!
VCoutu

[June7]

The date criteria code is only limiting the recordset of email addresses. If you also want the report filtered by date, then include that criteria in opening the report.

DoCmd.OpenReport "rptEmailToRequestors", acViewPreview, , "RequestorID=" & rs!RequestorID & " AND [DateDue]=Date()"

Ooops, also need to move the DoCmd.Close acReport line into the loop.

I tested the code (with edits to send emails to myself and save PDF to my C: drive). All works.

However, only one PDF will be created because the name used is the same for each output. Probably should include the RequestorID in constructing the PDF name.

[vickster3659]

My bad! When I was reworking the db, I must have deleted the AND [DateDue]....

Where exactly do I move the DoCmd.Close acReport line? I moved it to right after DoCmd.Output To, but Outlook did not open again to send the next email (1st person in list will get an email, but no opportunity to send next email)

[June7]

Those edits all worked for me. Outlook opens for each email and the PDF is output. Don't know why it fails for you. Have you step debugged? Refer to link at bottom of my post for debugging guidelines.

Post your revised code.

[vickster3659]

Hi,

For some reason unknown to me, I can't get any of the Debugging tools to work. Is there something in References I need to activate? Our Technologies Services does not support Access, so I'm pretty much on my own (thank goodness for forums!!).

Here is my revised code:

Private Sub cmdEmailToRequestors_Click()
Dim rs As DAO.Recordset
myPath = "\\DFS01\SHARED\CFA\VOL5\TREASURE\CASH_MGR\1- Treasury Operations\4- Database\Wire Tracking\"
stEmailMessage = "Please see the attached report for EFT Payments processed today."
stSubject = "EFT Payments sent" & Format(Now(), " mm-dd-yyyy")
stReport = "rptEmailToRequestors"
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT RequestorID, RequestorEmail FROM qryEmailToRequestors WHERE [DateDue]=Date();")
While Not rs.EOF
DoCmd.OpenReport "rptEmailToRequestors", acViewPreview, , "RequestorID=" & rs!RequestorID & " AND [DateDue]=Date()"
DoCmd.SendObject acSendReport, , acFormatPDF, rs!RequestorEmail, , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & stSubject & ".pdf", False, , acExportQualityPrint
DoCmd.Close acReport, stReport, acSaveNo
rs.MoveNext
Wend

rs.Close
End Sub

[June7]

Except for the PDF naming, all looks good. As I said, code works for me. I cannot explain the failure at your end. You will have to debug.

[vickster3659]

Hi,
No need to look further at the code.....I looked at my data.....I processed many payments today, but not for any of my test people. Therefore, all is working correctly!

I can't thank you enough for your assistance/time/patience!

VCoutu

[vickster3659]

Hello June 7,

Again, thank you for your help previously. I've had to put this project on the back burner for another project, but now I need to get this finished. Your last suggestions made the error messages disappear, and the email process is working. However, my reports are pulling all records for each requestor, where I need only pull the current day records.

To add a twist to this, my organization recently upgraded us from Access 2003 to Access 2010. I am aware that some previously created code may not work after this upgrade, but I don't know if this is my problem.

Thank you,
Vcoutu

[June7]

Need to remove the code from behind the report. This is overriding the form procedure filter criteria.

[vickster3659]

Thank you! This is perfect!!