Emailing Reports In Access

[jeison]

I have created a report that shows commission details for each rep. Each rep
is identified by a unique Rep ID. Each day, different reps will be on the
report. Is there a way to automatically email their section of the report
based on ID?

Database Name: Event_Management
Report Name: Asset_Report
Table Name: Contact List (has Rep ID and Email Address)

I searched the forum but could not figure out how to implement the answers
given. if anyone is willing to give me a detailed step by step answer that
would be very helpful.
Thanks,

 

[Wayne-I-M]

Hi This is a slight change of an answer I gave a while ago

In the 2nd example your form IDcontrol name would go where the
"FormName!EMailaddress" is



_______

Private Sub ButtonName_Click()
DoCmd.SendObject acReport, "Name of report here", "RichTextFormat(*.rtf)",
"(e-mail address removed)", "(e-mail address removed)", "", "Hey - our machine is
broken", "Please will you come and fix our machine as soon as possible.
Thanks", False, ""
End Sub


Or


DoCmd.SendObject acReport, "Labels Jackets_Orders",
"RichTextFormat(*.rtf)", "(e-mail address removed);[email protected]", "", "",
"Hey - our machine is broken", "Please will you come and fix our machine as
soon as possible. Thanks", False, ""


If sending the mail to addresses shown in 2 controls on the form (where the
CC may or may not be empty) - I use the If Not IsNull to stop the errors
caused by the empty address in the code in outllook


Private Sub ButtonName_Click()
If Not IsNull(Me.CCMailAdress) Then
DoCmd.SendObject acReport, "Report Name Here", "RichTextFormat(*.rtf)",
FormName!EMailaddress, Forms!CCMailAdress, "", "Hey - our machine is broken",
"Please will you come and fix our machine as soon as possible. Thanks",
False, ""
Else
DoCmd.SendObject acReport, "Report Name Here", "RichTextFormat(*.rtf)",
FormName!EMailaddress, , "", "Hey - our machine is broken", "Please will you
come and fix our machine as soon as possible. Thanks", False, ""
End If
End Sub

 

[jeison]

Im not sure i follow. im not good enough in Access. I did find the
following information. Will this work if i can figure out how to implement
it?

Produce your report for all members.

Hold the query that the report is based on (‘BaseSQL’ in the code below).

Open a recordset to provide the recipients.

Loop through this recordset, and for each recipient:

Change the query that the report is based on to include an Where clause, to
individualise it.

Email the individualised report to that recipient.

Return the query to its previous state (without Where clause).

So the code will look something like this:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT ManagerID, Email FROM ManagersTable")
Set qdf = dbs.QueryDefs("YourReportQuery"­)
BaseSQL = qdf.SQL
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE ManagerID =" &
!ManagerID
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "YourReport", "Snapshot Format",
!Email
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

 

[Wayne-I-M]

OMG - much too difficult for me all that - do it simpler


You have a table with some "stuff" in it - one of which is a unique field
(ID).

OK - next create a form based on the table (make sure the ID field is there
somewhere)

You have a report that you want to send out to certain people. Make sure
the ID field is on the report somewhere - it can be set to visible = no if
you want to hide it.
Open the report in design view
Open the properties box
In the filter row put ths

([IDField]=[Forms]![FormName]![IDField And
[DateField]=[Forms]![FormName]![DateField])

Save and close

(See the bottom of this about changeing the names)

An even simpler method would be to use query by form but that not a good
idea if yu want to use a query for other "stuff"

Next (on your form) create a button and put an OnClick event (Use the
properties box for this).

Onpen your form in design view.
Select the button
Right click
Open the properties box
Select the event column
Select on click
Select the built option (...)
select code
Put this between the 2 lines of you code you can see

DoCmd.SendObject acReport, "Report Name Here", "RichTextFormat(*.rtf)",
FormName!IDFieldHere, , "", "Some e mail title here", "Soome message to go
in the body", False, ""


Important Note - all that goes on one line

Next make sure you have a date field somewhere on the form. This is a good
idea so you can (if you want) send out different reports for different dates
to different people)

Change the names in all the above to what they really are in your
application and give it a try