Email users with report

[razchip]

I have an email set up to send reports to users, I haven't been able to simplify the process; currently I have to add a new report and a new line in the code. Can I loop through a table sending the proper report to the user. Example of the table I have is below:

Reports-To EmailAddress ReportName
NameOne xxy@dkdkdkd.com Rpt1
NameTwo wrz@dkdkdkd.com Rpt2
NameThree sfg@dkdkdkd.com Rpt3

Code I'm currently using:
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Title = "" ' Define title.
Help = "" ' Define Help file.
Ctxt = 1000 ' Define topic

'Add each report name as A string
Dim stDocName As String
Dim stDocName2 As String

stDocName = "Rpt1"
stDocName2 = "Rpt2"


DoCmd.SendObject acReport, stDocName, "PdfFormat(*.pdf)", "name One", "", "", "Manager Approval", "", True, """"""

Each User/document has it's own line to send an email and it continues to grow.

I appreciate any guidance anyone can give me.

Thanks for the help.
Greg

 

[trevil620]

Yes, you can use a table to simplify the process. You just need to make sure you have all of the required info in your table, then do something like:

Dim RS As Recordset
Set RS = CurrentDb.OpenRecordset("YourTable")
If Not RS.EOF Then
    DoCmd.SendObject acReport, <RS.yourvalue>, <RS.yourvalue>, <RS.yourvalue>, "", "", <RS.yourvalue>, "", True, """"""
    RS.MoveNext
Else
    MsgBox "No addresses to email reports to...", vbOKOnly, "Missing Info in Email Table"
End If
RS.Close
Set RS = Nothing

The speculative judgment of the quality of an answer is based directly on … what was the question again?

 

[razchip]

Giving it a try, I'm still making an error somewhere. When I run the code below, I get an error message. Cannot find the object '|1' Run-time error '2059'

DoCmd.SendObject acReport, "ReportName", "PdfFormat(*.pdf)", "EmailAddress", "", "", "Manager Approval", "", True, """"""

Anything else you might think of that I can try. Again, thanks for helping.

Thanks for the help.
Greg

 

[trevil620]

Check your parameters - are you sure you have a report object in your database named "ReportName"? I get the same error as you if I use an unknown Report Name. Also, your code generates an email to "EmailAddress" - not a real address.
Are you trying to spin through a table or not? Because the code you just showed does NOT reference any table fields.

The speculative judgment of the quality of an answer is based directly on … what was the question again?

 

[razchip]

Probably my misunderstanding, I used "ReportName" thinking it would pick up the table RS ReportsTo2 report field of Reportname in the first record like MrgA01, and the field emailaddress of xxx@xxxx.com; then move onto the next record. I'm trying to set it up so our user doesn't have to have the code changed each time we add a new member to the file.

Thanks for the help.
Greg

 

[trevil620]

Did you see the code I suggested? Doesn't look like you are using it (or anything similar) because your post at 17 Feb 14 10:28 shows all your parameters inside quotes instead of referencing the values in fields within the table. Please check again, correct, and if you still have a problem, post your code.

The speculative judgment of the quality of an answer is based directly on … what was the question again?

 

[razchip]

Trevil620,

Not trying to be a pain, but I'm just missing the boat. Below is the code that I have.
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("ReportsTo2") 'table

DoCmd.SetWarnings True

rs.MoveFirst

Msg = "Do you want to Email PTO hours used to each Manager for approval?" ' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton2 ' Define buttons.
Response = MsgBox(Msg, Style, Title, Help, Ctxt) ' Response = MsgBox("?", vbYesNo, , , Response)
If Response = vbYes Then ' User chose Yes.

If Not rs.EOF Then
'Tried both methods, top one, method no found. Bottom one, syntax error
DoCmd.SendObject acReport, rs.ReportName, "PdfFormat(*.pdf)", rs.ReportsTo, "", "", "Manager Approval", "", True, """"""
'DoCmd.SendObject acReport, <rs.ReportName>, "PdfFormat(*.pdf)", <rs.ReportsTo>, "", "", "Manager Approval", "", True, """"""

rs.MoveNext
Else
MsgBox "No addresses to email reports to...", vbOKOnly, "Missing Info in Email Table"
End If

rs.Close
Set rs = Nothing

Else
DoCmd.OpenReport stDocName, acPreview
End If

Thanks for the help.
Greg

 

[trevil620]

You are very close.
First, where you reference the field names like 'rs.ReportName', change to 'rs!ReportName'
Second, you have an issue where you reference the NAME of the person (rs!ReportsTo) and not "rs!EmailAddress". But I don't know if you are ok with the email only showing the address or if you want the persons name to appear also. In case you want the name AND Address, replace your "rs.ReportsTo" with
rs!ReportsTo & "<" & rs!emailaddress & ">" In any case you need to use field EmailAddress to get the ....
Finally, you are only sending to one person because you do not loop through the list of records. On the line after "If Not rs.EOF Then"insert a line and add: Do While Not rs.EOF
Finally close the Do Loop by adding "Loop" after line rs.MoveNext.


The speculative judgment of the quality of an answer is based directly on … what was the question again?

 

[razchip]

Excellent, made the last change to your suggestions and it took off fine. Thank you and the person running the program really appreciates it.

Thanks for the help.
Greg