Send report individually by email address (DoCmd.SendObject)

[wnicole]

I have a template report (R_CurrentJobs) with fields from database.
Fields: FirstName, LastName, Email, JoB Number, TechID, TechName, JobLocation etc...

The query Q_CurrentJobs is a list of jobs to send to the tech for today. (These are open jobs queried).

I need to run this query on the report (R_CurrentJObs) and send the report to each tech based on their individual jobs. The jobs are connected to each tech by its TechID.
The report list the job information for each tech (JOb number, Location details etc.)

When I click the button to send the email. I want to email each tech individually based on the Q_CurrentJobs query using R_CurrentJobs.

Im just getting started with VBA so am aware of SendObject but not skilled at it. Can someone please walk me thru how I would do this?
Prefer not to direct me to a link as I go to the link and really don't understand the code fully. Tried some and it just doesn't work for me.
Hoping someone can possibly take the time on my specific fields and help me understand how to generate this code.
Appreciate it.

[June7]

Open a recordset of the techs. In looping code, cycle through the recordset, open report filtered to techID, output report, close report, move to next record, repeat.

Like it or not, I am going to refer you to a link. This is a common topic on forum and examples are available.
https://www.accessforums.net/program...ses-37200.html

Show code you attempt and we will analyze.

[wnicole]

Hi,
I have the code below that allows me to send a report to a set of email addresses in my table.
However, what I need this code to do is send the report individually to each email address based on their jobs.
(I have a Jobs Table and Inspectors Table. The JobID connects to the InspectorID)

Can you add to this code to make sure it send individually to each email the reports based on their jobs?

Private Sub Command2_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmail As String

Set db = CurrentDb
Set rs = db.OpenRecordset("MyEmailAddresses")
With rs
Do While Not .EOF
strEmail = strEmail & .Fields("Email") & ";"
.MoveNext
Loop
.Close
End With

strEmail = Left(strEmail, Len(strEmail) - 1)

DoCmd.SendObject acSendReport, "CurrentJobs", acFormatRTF, strEmail, , , , , True

End Sub

[June7]

If you follow the example in the link you will see that within the loop code will send report. You also need to within the loop open and close filtered report.

Do While Not .EOF
DoCmd.OpenReport "CurrentJobs", acViewPreview, , "InspectorID=" & !InspectorID
DoCmd.SendObject acSendReport, , acFormatRTF, !Email, , , "Current Jobs", "See attached list."
Docmd.CloseReport "CurrentJobs"
.MoveNext
Loop

[wnicole]

This works. When all emails are sent before exit I get this error message.
RUn Time Error 5
Invalid procedure call or argument.
End or Debug buttons.
ANy reason?

[wnicole]

SHould I have an OnError Handler at the end?

[June7]

Exit what? What line does the debugger hit?

[wnicole]

Sorry confusing word.
When all emails are sent before it ends I get this box see image

[June7]

When that happens click the Debug button. What line of code is highlighted?

Learn debugging techniques - see link at bottom of my post.

[wnicole]

This is highlighted.
Also, When I checked the emails I'm only getting separate emails but the same information not separate information. ANy reason you can think of?

[June7]

You say all the emails actually got sent? So you are sending everyone unnecessary reports? Might want to test code that sends only to yourself before trying the en masse transmission. Just put your email in place of the reference to recordset email field.

The SendObject works for me. If I set the EditMessage property to True, it opens the Outlook message editor. If I set to False then Outlook opens warning dialog and I have to click Allow.

Also, try:

DoCmd.Close acReport, "CurrentJobs", acSaveNo

I did a test of this similar code structure and it works for me. Here is my exact code:
Sub testEM()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT AirportName, Email FROM Airports;")
While Not rs.EOF
DoCmd.OpenReport "Imagery", acViewPreview, , "AirportName='" & rs!AirportName & "'"
DoCmd.SendObject acSendReport, , acFormatRTF, rs!Email, , , "test", "test " & rs!AirportName, False
DoCmd.Close acReport, "Imagery", acSaveNo
rs.MoveNext
Wend
End Sub

I don't know why yours fails since I can't debug it directly.

[wnicole]

Ok..this worked. THanks so much!!!
I'm not sure I fully understand what the codes mean like While No rs.EOF but it works!!!.
If I have other questions hope I can bother you again. Appreciate your patience with me :-)