Emailing Reports

[Gordon]

How would I go about emailing a single record of a compiled report instead of
the whole report. Can this be done?

[Larry Linson]

"Gordon" <Gor...@discussions.microsoft.com> wrote

> How would I go about emailing a single
> record of a compiled report instead of
> the whole report. Can this be done?

I'm presuming you want to mail a report with only the detail section for a
single record of the RecordSource. You can't extract that from a full
report, but you can use a WHERE clause in the Report's Record Source to
limit a report to a single record, or some selected subset of records, or
filter the report using full retail Access, or use the WhereCondtion or
Filter arguments of the DoCmd.OpenReport method.

Larry Linson
Microsoft Access MVP

[Tom Wickerath]

Hi Gordon,

You need to limit the report to just showing the record(s) that you wish to
send. A common method of doing this is using the optional WhereCondition
argument of the DoCmd.OpenReport method. For example, if a record is
displayed on a form, you can add a command button with code to open a report:

DoCmd.OpenReport "ReportName", acPreview, _
WhereCondition:="PKFieldName = " & Me.PKFieldName

where ReportName is the name of your report, and PKFieldName is the name of
the field on your form that represents the primary key. This same fieldname
is included in the report's recordsource.

Once you have the report limited to just displaying the record(s) of
interest, then you can send the report as a Snapshot (*.snp) file, or as a
.PDF file.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Crettol@discussions.microsoft.com Christopher Crettol]

Hello Tom,

Gordon and I are both working on this project. First let me thank you for
the information you have already provided; it has helped out greatly.

I was able to set up the "DoCmd" okay and created the email command that I
need, though I have a question regarding the "WhereCondition" statement.

The scenario I am looking at requires that when we fill out the form, prior
to saving the record, we need to have an email of that current form sent to a
group.

I believe I understand your thinking on how to the "WhereCondition" should
work. If my "PKFieldName" is "ID" Then my "& Me.PKFieldName" would be "&
Me.ID"":

WhereCondition = "ID = " & Me.ID (Is this the proper command?)

This is what I created based on what you have provided.

stDocName = "Operations Update" (The database the form resides in)

DoCmd.SendObject acPreview, stDocName (Sends a preview of the database
report)

WhereCondition = "ID = " & Me.ID (Specifies a particular form out of
the database to send)

Though this still pulls the full report, are we missing something? Should
the "WhereCondition" be more specific if there are multiple records? Or is
that statement only targeted to the current open form?

We appreciate your input and if you need more specifics let us know.

Christopher

[Tom Wickerath]

Hi Christopher,

> The scenario I am looking at requires that when we fill out the form, prior
> to saving the record, we need to have an email of that current form sent to a
> group.

You need to commit the save operation first if this is a new record. Try
this (I'm assuming the name of your report is Operations Update):

If Me.Dirty = True Then
Me.Dirty = False
End If

DoCmd.OpenReport "Operations Update", acPreview, _
WhereCondition:="ID = " & Me.ID

> WhereCondition = "ID = " & Me.ID (Is this the proper command?)

Yes, however, now that I look at it again, I gave you the form for a numeric
primary key field. If your primary key is text, then use this instead:

DoCmd.OpenReport "Operations Update", acPreview, _
WhereCondition:="ID = " & Chr(34) & Me.ID & Chr(34)

> This is what I created based on what you have provided.
> stDocName = "Operations Update" (The database the form resides in)

> Though this still pulls the full report, are we missing something?

The SendObject method does not include a WhereCondition arguement.
Therefore, you'll have to approach this a little differently. Create a query
that includes all the fields that you want. Add the primary key field. Add
criteria to the primary key field like this:

=Forms![FormName]![ID]

For example, in the Northwind sample database, the SQL statement for a query
might look like this:

SELECT Customers.*
FROM Customers
WHERE CustomerID=[Forms]![Customers]![CustomerID];

You would then set this query as the recordsource for your report. Of
course, the form must be open in preview mode (not design view) when you run
this query. You must be sitting on a record that has already been committed
(saved) to the database. You can either have fields on the form for the
various e-mail addresses (To, CC & BCC), the subject, and the message text,
or you can hard code these values if you want. The code below includes both
methods as an example, with the fields method commented out. I am also
exporting the results of the report to the Microsoft Snapshot format:

Option Compare Database
Option Explicit

Private Sub cmdSendReport_Click()

Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strSubject As String
Dim strMessage As String

'strTo = Nz(Me.txtToEmailAddress, "")
'strCC = Nz(Me.txtCCEmailAddress, "")
'strBCC = Nz(Me.txtBCCEmailAddress, "")
'strSubject = Nz(Me.txtSubject, "")
'strMessage = Nz(Me.txtMessage, "")

strTo = "J...@abc.com"
strCC = "B...@abc.com; Ph...@xyz.com"
strBCC = "Ma...@abc.com"
strSubject = "Latest Results"
strMessage = "Here are the latest customer sales results. " _
& vbCrLf & vbCrLf _
& "In order to view the attached report, you must have " _
& "the Microsoft Snapshot Viewer utility installed. You can " _
& "download a free copy from Microsoft at this address:" _
& vbCrLf & "http://support.microsoft.com/?id=175274" _
& vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "Christopher Crettol"

DoCmd.SendObject acReport, "Operations Update", _
OutputFormat:=acFormatSNP, _
To:=strTo, CC:=strCC, BCC:=strBCC, _
Subject:=strSubject, MessageText:=strMessage,
EditMessage:=True

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdSendReport_Click..."
Resume ExitProc
End Sub

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Tom Wickerath]

In the procedure I included, I forgot to add the code to save a new record.
You'll want to add this:

If Me.Dirty = True Then
Me.Dirty = False
End If

prior to using the DoCmd.SendObject.

[Christopher Crettol]

Tom,

Great information!

Let me make sure I understand the process you’re proposing.

Before I can get a snapshot view of the form it has to be saved to the
database. I was able to get the form to be set up for email and to send, but
ran into the issue of not having the snapshot view available in the selection
of formats.

What it looks like, is that the coding you have provided runs the process in
the background saving the form to the database then setting up the preview of
the form to be emailed in the snapshot format.

Does this sound right?

Also in the "WhereCondidtion" statement you provided. The primary key is a
numerical value that is automatically set by access. Does this change the
formula?

I will play around with this and see if it will solve my problem.

I really appreciate this advice, thanks.

Christopher