Correction It should be going to the email address FROM the T_Inspectors, however only for the names that are in the query ran from the R_WeeklyDispatch_Today report. Hope this is clearer
I am not convinced your .SendObject is doing anything. I do understand it is looking at the rs recordset and retrieving an Email address. It is placing this email address into the TO
.SendObject does not have an object name. You could give it a name like the report name. Not sure it is going to respect looping through the rs recordset.
I was thinking it would be a good idea to test the earlier .openReport
If that is working then just below you could add a line to select object "Report name"
then with the report name included in the .SendObject you could give it another go.
I would just like to be sure it is opening the correct report. Still a little confused if this is happening.
Code:DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Last Name]='" & rs![Last Name] & "'" DoCmd.SelectObject acReport, "R_WeeklyDispatch_Today", False DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True DoCmd.Close acReport, "R_WeeklyDispatch_Today"
Ok I use this below. Its still doing the same thing. Sending a blank report even though this report has a query of 4 records Date(). Not sure where its pulling from.
Also, it keeps sending it to the first email in the T_Inspectors table. Has nothing to do with the query setup. SO confuse...
Private Sub Command5_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [Last Name], Email FROM T_Inspectors;")
While Not rs.EOF
DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Employee]='" & rs![Last Name] & "'"
DoCmd.SelectObject acReport, "R_WeeklyDispatch_Today", False
DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
DoCmd.Close acReport, "R_WeeklyDispatch_Today"
rs.MoveNext
Wend
End Sub
Right, it is looking at the table rs is the table.
That is what your SELECT is doing. it is looking at the table. It will go to the first record and move down until it finds a match with EMPLOYEE.
When EMPLOYEE (within the query) matches the Last Name in the table it will grab that record. It will open the report where it finds a match. It will look for a match again. Looking for duplicates is what your loop is doing, as far as I can tell.
If there are only four records, how difficult would it be to upload a clean copy? Just grab the query and the tables and the report that matter.
I don't think this is what its doing however.
The email that is coming up in OUtlook is not a part of the query at all. Its as if it didnt find anything so grab the first record in the table.
If its suppose to do what you say above, why does isn't pulling the correct match? Is my code wrong?
4 records are what I'm testing with my email address. There are about 20 records that need to be sent.
One of my suggestions was to test your recordset and see what record it was returning.
Post 15 kinda describes this. I like to take things one step at a time. That way you know for sure before starting a loop.
If you need some help with some code to test, let me know.
I do...I couldnt run the sample you sent. Not quite understood...
Can you send me test code?
I will work something up. You may need to do a nested loop. Is there a saved Access query that the report uses?
What is the query name?
What does the field [Employee] represent? Is there a way you can match PK and FK?
How are you determining who gets an email?
What is the PK for the query?
What is the PK for the table?
Is there a saved Access query that the report uses? Yes
What is the query name? "Q_WeeklyEmployeeDispatch_Today"
What does the field [Employee] represent? Employee and Last Name go hand in hand. In the Employees Table we have Last Name First Name. In the Job Tables we Employee (which pull from the Employee Table)
Is there a way you can match PK and FK? THe Employees Table has ID1 as primary key and the JObs Tables have ID1 as primary key. They both have the same name.
How are you determining who gets an email? Based on the query. All who has a job Date().
What is the PK for the query?
What is the PK for the table?