Sending reports to email recipients

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cetacean - 29 Aug 2007 21:06 GMT

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!

Reply to this Message

Rick A.B. - 30 Aug 2007 12:51 GMT

On Aug 29, 3:06 pm, cetacean <cetac...@discussions.microsoft.com>
wrote:

> I am trying to write a macro or VBA module that will automatically send a
> report tailored to a specific individual to multiple individuals
[quoted text clipped - 3 lines]
> 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

Reply to this Message