I need a Macro that will generate multiple reports and e-mail each report to a specific person.

[craig1988]

Hi June7

Thank you for the ongoing advice. Yes my naming conventions were a big mistake, which I did not realise at the time!!



I am having an problem with the Set rsData line. could you advise please?

Public Function SendTestEMail()
Dim rsData As Recordset
Dim rsEmails As Recordset
Dim strBody As String
Set rsEmails = CurrentDb.OpenRecordset("SELECT eMail FROM TStaffList;")
While Not rsEmails.EOF
    Set rsData = CurrentDb.OpenRecordset("SELECT * FROM TStaffList WHERE TStaffList=" & rsEmails!TStaffList & ";")
    While Not rsData.EOF
        'code to build email body using rsData record
        strBody = strBody & rsData!field1 & ", " & rsData!Field2 & ", " & rsData!field3 & vbCrLf
        rsData.MoveNext
    Wend
    'code to send email
    rsData.Close
    rsEmails.MoveNext
Wend
End Function

[June7]

rsData needs to reference the table that has the data you want to send in the email body. What data do you want to send in this email? You want each email to have only the data that is relevant to each recipient? Both tables must have some value to filter the records for the specific recipient, like a StaffID.

("SELECT eMail, StaffID FROM TStaffList;")

("SELECT * FROM [the data table name] WHERE StaffID=" & rsEmails!StaffID)

Then construct the body of the email with the fields from rsData. Use your field names instead Field1, Field2, Field3.

You don't show the code that actually constructs and sends email. Refer back to post 2 for that.

An alternative to opening the rsData recordset and building email is the OutputTo method which will attach a PDF of a report object to an email.

[craig1988]

Can you advise me on the code needed to use the OutputTo method to loop through the e-mail address's and attach a PDF with the relevant data and send to that e-mail address.

Thanks for your continued help... I know, I am very frustrating...

[June7]

Sorry, I meant SendObject.

Like:

Public Sub SendTestEMail()
Dim rsEmails As Recordset
Set rsEmails = CurrentDb.OpenRecordset("SELECT eMail, StaffID FROM TStaffList;")
While Not rsEmails.EOF
    DoCmd.OpenReport "report name", , , "StaffID=" & rs!StaffID
    DoCmd.SendObject acSendReport, , acFormatPDF, rs!eMail, , , "subject", "message", False
    DoCmd.Close acRecport, "report name", acSaveNo
    rsEmails.MoveNext
Wend
End Sub

[craig1988]

Hi June7, I have spent some time looking at the Do.Cmd items below and familiarised myself with them. I am still having some issues.

The

 DoCmd.OpenReport "RTestReport", , , "StaffID=" & rs!StaffID

does not run...

Is this because I have not set a rsStaffID?

Public Function SendTestEmail()

Dim rsEmails As Recordset
Set rsEmails = CurrentDb.OpenRecordset("SELECT eMail, StaffID FROM TStaffList;")
While Not rsEmails.EOF
    DoCmd.OpenReport "RTestReport", , , "StaffID=" & rs!StaffID
    DoCmd.SendObject acSendReport, , acFormatHTML, rs!Email, , , "Test Report", "message", False
    DoCmd.Close acRecport, "RTestReport", acSaveNo
    rsEmails.MoveNext
Wend
End Function

[June7]

The recordset is declared and set.

I don't know your data structure. I don't know your table and field names. Substitute into the code as appropriate for your db. Do you have a field named StaffID in TStaffList?

My suggested procedure is a Sub, not a Function.

The Sub could be a button Click event.

[craig1988]

I have 2 tables. One table is TStaffList that includes the fields; StaffID, ForeName, SurName, eMail. The other table is the TCorrectionLog which has fields: StaffID Date, Time, Error. The two StaffID fields are related and the TStaffList acts as a look up for TCorrectionLog.

I need to loop through the StaffId's in the StaffID field in TStaffList and use each StaffID as a parameter in a query to extract only the records with same StaffID from the TCorrectionLog.

This is more than likely how you imagined it but just to give you info on the structure of the DB

[June7]

Then the code looks appropriate. But why is it a Function? What event are you calling it from? Why not just put the code in a button Click event?

Refer to link at bottom of my post for debugging guidelines.

[craig1988]

Hi June7

I have changed from a function to a sub as advised.

My current code still not working

Private Sub Command0_Click()
Dim rsEmails As Recordset
Set rsEmails = CurrentDb.OpenRecordset("SELECT eMail, StaffID FROM TStaffList;")
While Not rsEmails.EOF
    DoCmd.OpenReport "testReport", , , "StaffID=" & rs!StaffID
    DoCmd.SendObject acSendReport, , acFormatHTML, rs!Email, , , "subject", "message", False
    DoCmd.Close acRecport, "testReport", acSaveNo
    rsEmails.MoveNext
Wend
End Sub

I tried to understand the debugging procedures with not much luck.

The line

 DoCmd.OpenReport "testReport", , , "StaffID=" & rs!StaffID

is the one showing as yellow.

Can you advise me on how to fix the code to have it run effectively.

Thanks again!!

[June7]

What is the exact error message?

Is StaffID a number or text field?

If it is text the parameter must have apostrophe delimiters.

DoCmd.OpenReport "testReport", , , "StaffID='" & rs!StaffID & "'"

Date data type would use # delimiter.

[craig1988]

StaffID is a text field so I have added the delimiter as advised.

The same line is still yellow though

 DoCmd.OpenReport "RMaster", , , "StaffID'=" & rs!StaffID & "'"

When I click the button the Dialog box pops up with "Run time error '424': Object required"

When I click debug and hover over rs!StaffID a popover appears saying "rs!StaffID = <Object required>

[June7]

You put the first delimiter in the wrong place. It goes after the = sign. See my example.