SendEmail Code having problems...

[wnicole]

I'm trying to run this code:
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 "CurrentJobs", acViewPreview, , "Last Name='" & rs!LastName & "'"
DoCmd.SendObject acSendReport, , acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
DoCmd.Close acReport, "CurrentJobs", acSaveNo
rs.MoveNext
Wend

End Sub
***************

The problem is in red above. The error keeps going to db as DAO.Database. (Do I need to add anything to the db?) Not sure why.

Also rs!LastName. When I try to put a space between it doesn't let me. The field is Last Name not LastName. So I'm not sure if it would work without the space.

Can you assist?

[RuralGuy]

Try including the "Microsoft DAO 3.6 Object Library" in your references. As for the Field name, try rs![Last Name]

[ItsMe]

try

set db = currentdb
Set rs = db.OpenRecordset("SELECT Last Name, Email FROM T_Inspectors;")

[wnicole]

I tried this and Im getting error message regarding query expression on Last Name. Is this correct in red?

Private Sub Command5_Click()


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Last Name], [Email] FROM T_Inspectors;")

While Not rs.EOF

DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Last Name]=" & rs![Last Name] & "'"
DoCmd.SendObject acSendReport, , acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
DoCmd.Close acReport, "CurrentJobs", acSaveNo
rs.MoveNext
Wend

End Sub

[wnicole]

I tried this and Im getting error message regarding query expression on Last Name. Is this correct in red?

Private Sub Command5_Click()


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Last Name], [Email] FROM T_Inspectors;")

While Not rs.EOF

DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Last Name]=" & rs![Last Name] & "'"
DoCmd.SendObject acSendReport, , acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
DoCmd.Close acReport, "CurrentJobs", acSaveNo
rs.MoveNext
Wend

End Sub

[RuralGuy]

This: "[Last Name]=" & rs![Last Name] & "'"
should be: "[Last Name]='" & rs![Last Name] & "'"

[ItsMe]

Try this


, "[Last Name]=" & "'" & rs![Last Name] & "'"

Actually, Rural Guy has it right. My code has an extra & and no need.

[wnicole]

I think I'm almost there.
1. What happens now is the parameter "Last Name" appears. Once I hit ok it goes to MS Office to mail. What is the parameter coming up.
2. The other problem is its pulling up the report but not according to the query I have attached to the report. (For ex: R_WeeklyDispatch_Today is the report. I have this attached to a query that only pull up todays report. However when it goes to Outlook, it pulls up an empty report and email address that do not have a report for today. At a lost. Here is the code:

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, , "[Last Name]='" & rs![Last Name] & "'"
DoCmd.SendObject acSendReport, , acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
DoCmd.Close acReport, "R_WeeklyDispatch_Today", acSaveNo
rs.MoveNext
Wend

End Sub

[wnicole]

The exact error message is "Syntax Error (missing operator) in query expression Last Name.

[RuralGuy]

The parameter usually means you have misspelled something. Are you including the "Last Name" field in your report?

[wnicole]

You were right...The Report was using "Employee". So now it goes to Outlook with out any problem. However, it pulling up the right report but for some reason the report is blank when it goes to outlook.
When I go to the report it has the correct query and works but when it goes to send from Outlook, it send blank and uses one of the emails that do not belong in the query. At a lost.
Why isn't the report in Outlook pulling the query that is attached to it?

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.SendObject acSendReport, , acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
DoCmd.Close acReport, "R_WeeklyDispatch_Today", acSaveNo
rs.MoveNext
Wend

End Sub

[wnicole]

I just realized that its pulling the first record from the T_Inspectors not pulling the records from the query attached to the report. Is the code not written correctly?
How do I make sure the pdf is the R_WeeklyDispatch_Today report that has the attached query of jobs only for today and the people who have those jobs only is the correct one that is attached to email.
Its sending only one email out to the first record in the T_inspectors table.

Can you assist by looking at code make sure correct?

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.SendObject acSendReport, , acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
DoCmd.Close acReport, "R_WeeklyDispatch_Today", acSaveNo
rs.MoveNext
Wend

End Sub

[ItsMe]

So the

DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Employee]='" & rs![Last Name] & "'"

Opens the report in Preview Mode? It shows the record you want within the Report? How many reports are opening?

[wnicole]

Actually No. The report comes up blank then in Outlook it send to first email address in T_Inspectors.
The R_WeeklyDispatch_Today has a query Date() with four records. But none appear its blank and as mentioned Outlook report is empty as well. However the email address on in To: is the first records in table T_Inspectors.

Make sense?

[ItsMe]

Yes, most of it does make sense. I have not seen the .SendObject used this way before.

Maybe you can take a step back and test the recordset in a click event for a test button. Then you can test the Docmd.OpenReport. Each test without the loop.

You can do a rs.findfirst "[Employee]='" & rs![Last Name] & "'"

Get a return with ID

dim intId as integer

intId = rs![ID]

msgbox intID