Sending reports to email recipients

[cetacean]

I am trying to write a macro or VBA module that will automatically send a
report tailored to a specific individual to multiple individuals
sequentially. I think I need to use the For Next Loop with a variable that
keeps pulling names and email addresses for the filter and sendobject
functions but I do no have a clue as to how to write that code? Can anyone
help?!
Thanks in advance!

 

[Rick A.B.]

I am trying to write a macro or VBA module that will automatically send a
report tailored to a specific individual to multiple individuals
sequentially. I think I need to use the For Next Loop with a variable that
keeps pulling names and email addresses for the filter and sendobject
functions but I do no have a clue as to how to write that code? Can anyone
help?!
Thanks in advance!

Cetacean,

The way I've handled this in the past is to create a form and base it
on a query that displays everyone I want to email. Then I've used a
button to intiate the email by Diming my variables, opening up a
recordset clone to loop through and printing then sending the report.
You want to check out SendObject command.

Set RS = Me.RecordsetClone
If RS.RecordCount > 0 Then

RS.MoveFirst
While Not RS.EOF
strEmail = RS!EmailName '[EmailName]
strID = RS!StudentID
strName = RS!LastName
[ThisID] = strID
Msg = "You have Emailed report to " & [strName]

'Print Report
DoCmd.OpenReport "NotComplete1", acViewNormal, ,
"[StudentID] =" & Forms![frm_qry_22_EmailIncomplete]![ThisID]
'Send Report
DoCmd.SendObject acSendReport, "NotComplete1",
acFormatRTF, strEmail, , , txtSubject, txtMessage, False
MsgBox Msg


DoCmd.Close acReport, "NotComplete1"



RS.MoveNext


Wend
RS.Close
Set RS = Nothing

End If

End If