Loop email. Attaches wrong report

[rnickels]

This is one of those probelms that I really thought I could solve myself.
I have to admit that I am stumped.

I have a button on my main form called "TestForm" with a record source from a query named "MonthlyPaymentDue".
On my form is a button that when pressed executes a loop.
I want the loop to send an email to each recipient with an attached report specific to them.
The report has a text box named "PlayerName" that references the text box "TextID" on my main form called "TestForm".
The loop seems to be working in that the loop does send an email to each recipient but the report that is attached is not specific to them.
The report that is emailed to everyone is the very first recipients report.


I have tried to use the DoCmd.GoToRecord , , acNext. to go to the next record on my main form but this isn't working.
Here is my code.

Private Sub Click_Click()

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("MonthlyPaymentDue", dbOpenSnapshot)
    
        Do Until rsEmail.EOF
                sToName = rsEmail!PlayerEmail
                sSubject = "Invoice." & " " & rsEmail!PlayerName
                sMessageBody = "Hello" & " " & rsEmail!PlayerName
                DoCmd.SendObject acSendReport, "StatementOnePlayerAuto", acFormatPDF, sToName, , , sSubject, sMessageBody, False, False
                                
                DoCmd.GoToRecord , , acNext
                rsEmail.MoveNext
                Loop
      
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

If anyone can help this would save me many hours of future invoicing.

Thank you.

 

[pbaldy]

I wouldn't try to keep the form in sync with the recordset. Try using an unbound textbox on the form (it can be hidden) for the report to use in the criteria, and change the GoToRecord line to:

Me.NewTextBoxname = rsEmail!PlayerName

or the appropriate field name if that's not it.

 

[rnickels]

pbaldy,
That you for taking the time to look at my problem.

I have added a new unbound text box as you suggested, but it seems to me the text box isn't being updated with the rsEmail!PlayerName data,
So the loop doesn't run because the report then has no Player name to link it to.
I hope that makes sense.

 

[pbaldy]

I have used that technique to send individual reports to customers, so I believe it should work. Is that the field the report will be looking for? What's the full code now? Can you post a sample db? Now that I look back, you would need that line to be above the SendObject line so it's available to the report.

 

[rnickels]

Many thanks to PBaldy for the solution.
Here is the working code now.

Private Sub Click_Click()

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("MonthlyPaymentDue", dbOpenSnapshot)
    
        Do Until rsEmail.EOF
        Me.UnboundPlayerName = rsEmail!PlayerName
                sToName = rsEmail!PlayerEmail
                sSubject = "Invoice." & " " & rsEmail!PlayerName
                sMessageBody = "Hello" & " " & rsEmail!PlayerName
                DoCmd.SendObject acSendReport, "StatementOnePlayerAuto", acFormatPDF, sToName, , , sSubject, sMessageBody, False, False
                                
                
                rsEmail.MoveNext
                Loop
      
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

 

[GettingDone]

I am attempting to learn more about Automatically generating e-mails from a report.
I used the above script as a base line but am still encountering a similar error to that "rnickels" fixed. My loop is working correctly in the sense that it is looping through and sending the report via e-mail to each person in the dataset. However, each person is recieving all the reports rather than just one report. I believe the error lies in my report which was created off the logic from the Microsoft Support site titled "ACC2000: How to Create Reports to Mail Merge Microsoft Access Data" (I can't post links). Any suggestions?


Thank You

Details:
I have a form [Test_form] with a command button that pressed, executes the loop (script below). The record source is a query entitled Q_info. The report is [Report_Test]. There are three records in the query.

Dim MyDb As DAO.Database
[FONT=Verdana]Dim rsEmail As DAO.Recordset[/FONT]
[FONT=Verdana]Dim sToName As String[/FONT]
[FONT=Verdana]Dim sSubject As String[/FONT]
[FONT=Verdana]Dim sMessageBody As String[/FONT]
 
[FONT=Verdana]Set MyDb = CurrentDb()[/FONT]
[FONT=Verdana]Set rsEmail = MyDb.OpenRecordset("Q_Info", dbOpenSnapshot)[/FONT]
 
[FONT=Verdana]Do Until rsEmail.EOF[/FONT]
[FONT=Verdana]Me.UnboundCode = rsEmail!Code[/FONT]
[FONT=Verdana]sToName = rsEmail!Email[/FONT]
[FONT=Verdana]sSubject = "TEST3!! Announcement for" & " " & rsEmail!Code & "."[/FONT]
[FONT=Verdana]sMessageBody = "Hello" & " " & rsEmail!Title & " " & rsEmail!First_Name & " " & rsEmail!Last_Name & "."[/FONT]
[FONT=Verdana]DoCmd.SendObject acSendReport, "Report_test", acFormatPDF, sToName, , , sSubject, sMessageBody, False, False[/FONT]
 
 
[FONT=Verdana]rsEmail.MoveNext[/FONT]
[FONT=Verdana]Loop[/FONT]
 
[FONT=Verdana]Set MyDb = Nothing[/FONT]
[FONT=Verdana]Set rsEmail = Nothing[/FONT]
[FONT=Verdana][FONT=Verdana]End Sub[/FONT][/FONT]

 

[pbaldy]

Not sure how that method is supposed to filter the report. I'd have a criteria in the query that pointed to the control you're putting the value into: UnboundCode.

 

[GettingDone]

Thanks for the response!

When putting the criteria in to my query it returns an error. This makes sense because no values are entered in the the unbound text box: [UnboundCode]

My report right now is 3 pages, for the 3 records in the query. To make this process loop correctly and only send one report, should my report only be one-page? My report has each textbox =[aColumnInTheQuery], should my report be set up differently?

 

[pbaldy]

This line would appear to be putting the current record's value into the textbox, which the query should be able to use:

Me.UnboundCode = rsEmail!Code

Can you post the db?

 

[GettingDone]

View attachment Report_Generate.mdb

I think it errored because it was looping, the query cant pull from the box it is sourcing in the code.

 

[pbaldy]

For starters, there is no textbox with that name (it's UnboundDealerCode). That will cause errors. I would also base the recordset on a different source than the report. When you add a form criteria to the query the recordset won't like it. There are ways around that, but probably simplest to have separate queries. You can also leave the criteria out of the query and use this method to filter the report:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[GettingDone]

That was something I changed to run some more tests, it was [UnboundCode]. That is a good thought, I will experiment with the changing the recordset and adding another query, possible one that pulls just "Code" and "Email" from the orginal data table. I will post any issues that arise

Your adivce is great I really appreciate it.

 

[pbaldy]

No problem; post back if you get stuck.

 

[GettingDone]

Pbaldy,

Everything works perfect now with two different queries. I was also hoping to get around the outlook security warnings (when sending from access). I have 500 recipients getting customized reports and it would be painful to have to click "yes" for each email. I tried a very complicated process of coding a macro within outlook and then calling for it within access, but to no success... any suggestions?

 

[GettingDone]

One more issue:

I have multiple email boxes set up in outlook. I want to select which box the end-recipients recieve the file from. I added these following lines to the code, but it still sends from my default mailbox....

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
[COLOR=red]Dim sMailFrom As String[/COLOR]
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("Q_Email", dbOpenSnapshot)
 
 
 
 
        Do Until rsEmail.EOF
        Me.UnboundCode = rsEmail!Code
                [COLOR=red]sMailFrom = "ManagerBox" [/COLOR][COLOR=seagreen]'this is the name of the Box in my outlook, does this need to be a link?[/COLOR]
                sToName = rsEmail!Email
                sSubject = "TEST8!! Announcement for" & " " & rsEmail!Code & "."
                sMessageBody = "Hello, Attached is an for " & " " & rsEmail!Code & "."
                DoCmd.SendObject acSendReport, "Rep_test", acFormatSNP, sToName, , , sSubject, sMessageBody, False, False
 
 
                rsEmail.MoveNext
                Loop
 
Set MyDb = Nothing
Set rsEmail = Nothing

 

[pbaldy]

Sorry, lost track of this thread. Did you get these sorted out? I've used this to send out from another mailbox:

MyMail.SentOnBehalfOfName = "User@Domain.com"

That works in our Exchange Server environment, and using automation instead of SendObject. There are several ways around the warnings; I've used ClickYes, a free utility, and I've used CDO. There's also Outlook Redemption and others.

 

[GettingDone]

Pbaldy,

No worries, I got around the sent email box by changing my user profile in outlook to only show one email box.

Never did get around the outlook security warning without errors in the whole process.

If i need help in the future should I begin a new thread?

 

[pbaldy]

Yes, it keeps things cleaner if new topics are in their own threads. I most commonly use CDO to send email without the Outlook warnings. Here's a start:

http://www.granite.ab.ca/access/email/cdonts.htm