Send emails to name in report

[doucet1963]

I have this code that works except that it generates an email with everyone's name that are in the table. What I need, is to adapt the code so it would send an email to the names that are in the opened report using an action button.

The code is:

Private Sub Generate_Email_Click()
Dim rst As DAO.Recordset
Dim strEmailAddress

 On Error GoTo ErrorHandler
 
Set rst = CurrentDb.OpenRecordset("Tbl_Organization_Contacts")

Do Until rst.EOF
        If Len(rst("Email") & vbNullString) <> 0 Then strEmailAddress = strEmailAddress & rst("Email") & ","
        rst.MoveNext
    Loop
strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
DoCmd.SendObject acSendReport, "Rep_assigned_Authority", acFormatPDF, strEmailAddress, _
, , "Assignement of Authority", , True

rst.Close

Set rst = Nothing
ErrorHandler:
  Select Case 16388
         Case 16388
         MsgBox ("The email was not sent")
      Case Else
   End Select
   
End Sub

I need to find a solution to replace the statement
Set rst = CurrentDb.OpenRecordset("TableXXX")
so the email addresses would come from the report named "Rep_Assigned_Authority"

Thanks

PS> Sorry if the format is incorrect I am still learning...

 

[isladogs]

Use the record source for the report in the recordset in place of the table

By the way the select case code in the error handler is incorrect

 

[doucet1963]

Ridders,

I am new at this. The case code came from someone else, I can use "1" and I get the same result. Why is 16388 not good?

You mentioned to use the record source for the report, I tried everything and I cannot get it to work.

I tried the SQL = not working,
The Query's name = not working although this is the source;
The report's name = not working...
what else...

I don't know what to type after: Set rst = CurrentDb.OpenRecordset

Do I need to replace the whole code and start fresh.? Is the problem somewhere else?

I want to be able to generate an email that contains only the names from the report... :banghead:

 

[isladogs]

I've no idea whether error 16388 means email wasn't sent..try Googling it.
Anyway the code should start with

 Select Case err.number

Case 16388 'or whatever it should be here

As for the recordset, you need to use the SQL used for the report record source. Not the report name.

To do this do the following

Dim strSql as String
Strsql="....." where the ... are replaced by the report record source code

Then use

 Set rst= Currentdb.OpenRecordset(strsql, dbopendynaset)

Or if the record source is a query called qryName use

 Set rst=Currentdb.OpenRecordset("qryName",dbopendynaset)

NOTE I'm typing this on a phone so there could be typos

 

[doucet1963]

Still one issue with generating email to specific person based on the report.

The report opens based on a query that is filtered by sections. If I remove the filter from the query ([Forms]![Frm_DTAES_4-5_TAA_Assigned_Authority]![Cbo_Section]) then I can generate email, but it will be for the whole database.

If I add the filter ([Forms]![Frm_DTAES_4-5_TAA_Assigned_Authority]![Cbo_Section]) then I cannot generate email at all and I get an error. Unfortunately this is the option that I am looking for!

I added a sample of the database to help you understand what I mean. if you wish to try it, you'll need to access the query and remove the filter on the left to change the configuration.

Many Thanks for your time, I am slowly getting there, one fix at the time...

 

[JHB]

The problem is that you close the form in which you select the section just after the report is open.
Try the attached database, I've add a control in the report and made it invisible and then used the STRSQL in the Openrecordset.

 

[doucet1963]

The problem has been solved many thanks