E-Mail Single Report, not all records

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

I have a macro that I use to try this procedure, it first opens the report
RMothStmnt , design view, window mode hidden,
next RunCommand SelectRecord
next SelectObject , Report,
next SetWarnings No

This results in it sending all the records instead of one.
I need this to run via a macro and just send one record
 
I think you are trying to SendObject a report?
And you only what one record to the recipient?

If that's the idea, you can filter the report to just the one record by
setting a public string variable to the appropriate filter value for the
report, and then applying that in the report's Open event.

1. From the Database window, click the Modules tab, then the New Button.
Access opens a code window.

2. Immediately under the Option statements, enter:
Public gstrReportFilter As String

3. Place a command button on your form to emailing the record to the email
address shown in the form. The code assumes that you want to email the
record with the ID field matches the ID value in the form:
Private Sub cmdEmail_Click()
If IsNull(Me.ID) Or IsNull(Me.Email) Then
MsgBox "Need ID and email"
Else
gstrReportFilter = "ID = " & Me.ID
DoCmd.SendObject acSendReport, "RMothStmnt",, _
Me.Email,,,"Your statement", "Here tis.", True
End If
End Sub

4. In the Open event procedure of the report:
Private Sub Report_Open(Cancel As Integer)
If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = ""
End If
End Sub
 
Actually the macro comes from a Custom Menu. (Print Menu)
Yes, I am trying to send just one record via e-mail.
I also have to be able to open this report normally in Preview mode which is
done from a form.
The problem is that the report is opened via a form, but not printed via a
form, a menu bar instead.
No ID or link is necessary since it is already done for you via the code on
the form that opens the report.
Printing can only be done via the menu because of DB unique structure.
The macro I have now works except for sending all records via e-mail.
The custom Print Menu lets you Print the statement,Preview the statement ,
e-mail it or fax it.
Cant filter the report since I would have to unfilter it for everything else
to work, i.e. Preview...
Sometimes you want to Preview, sometimes Print, sometimes e-mail or Fax!
Print works, Fax works, Prwview works, but not e-mail....

Suggestions?
 
How are you filtering it to get just one record?

Are you using the WhereCondition of the OpenReport action?
Or do you have the criteria of the report's query referring to a control on
your form?
 
There are no filters for the Report.
Code via form opens the Report; See Next:
However this merely opens the report in Preview
and then I want to either look, print,e-mail or fax.
This has to be done via a macro or code called from the menu.
I have one drop down to print, one to e-mail a snapshot and one to e-mail as
rtf.

On Error GoTo Err_Command88_Click
Dte1 = Forms!frmAutoPayrollReport!StartDate
Dte2 = Forms!frmAutoPayrollReport!EndDat
DoCmd.SetWarnings False
DoCmd.OpenReport "RMothStatmnt", acPreview, "", "[ExpAmtOwed]>.01"
DoCmd.SetWarnings True
Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click
 
You lost me, Dave.

I thought the original question was about how to send one record (not all),
but I don't understand where the "all" are coming from.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave Elliott said:
There are no filters for the Report.
Code via form opens the Report; See Next:
However this merely opens the report in Preview
and then I want to either look, print,e-mail or fax.
This has to be done via a macro or code called from the menu.
I have one drop down to print, one to e-mail a snapshot and one to e-mail as
rtf.

On Error GoTo Err_Command88_Click
Dte1 = Forms!frmAutoPayrollReport!StartDate
Dte2 = Forms!frmAutoPayrollReport!EndDat
DoCmd.SetWarnings False
DoCmd.OpenReport "RMothStatmnt", acPreview, "", "[ExpAmtOwed]>.01"
DoCmd.SetWarnings True
Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

--
Thanks for your help.
Allen Browne said:
How are you filtering it to get just one record?

Are you using the WhereCondition of the OpenReport action?
Or do you have the criteria of the report's query referring to a control on
your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
which
Click to expand...
via
a code
Click to expand...
statement
, everything
else
Click to expand...
Click to expand...
 
Allen, I still need to send just one record.
The report opens showing just one record and then you can go to the next,
etc...
With the macro that I have it sends all of the records, that is all
customers that have a statement due.
Dont want this, just send one statement to one customer.



--
-------------------------------------------------------------------------
Thanks for your Help.
Allen Browne said:
You lost me, Dave.

I thought the original question was about how to send one record (not all),
but I don't understand where the "all" are coming from.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave Elliott said:
There are no filters for the Report.
Code via form opens the Report; See Next:
However this merely opens the report in Preview
and then I want to either look, print,e-mail or fax.
This has to be done via a macro or code called from the menu.
I have one drop down to print, one to e-mail a snapshot and one to
Click to expand...
e-mail
as
rtf.

On Error GoTo Err_Command88_Click
Dte1 = Forms!frmAutoPayrollReport!StartDate
Dte2 = Forms!frmAutoPayrollReport!EndDat
DoCmd.SetWarnings False
DoCmd.OpenReport "RMothStatmnt", acPreview, "", "[ExpAmtOwed]>.01"
DoCmd.SetWarnings True
Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

--
Thanks for your help.
Allen Browne said:
How are you filtering it to get just one record?

Are you using the WhereCondition of the OpenReport action?
Or do you have the criteria of the report's query referring to a
Click to expand...
control
on
your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Actually the macro comes from a Custom Menu. (Print Menu)
Yes, I am trying to send just one record via e-mail.
I also have to be able to open this report normally in Preview mode which
is
done from a form.
The problem is that the report is opened via a form, but not printed
Click to expand...
via
a
form, a menu bar instead.
No ID or link is necessary since it is already done for you via the code
on
the form that opens the report.
Printing can only be done via the menu because of DB unique structure.
The macro I have now works except for sending all records via e-mail.
The custom Print Menu lets you Print the statement,Preview the
Click to expand...
statement
,
e-mail it or fax it.
Cant filter the report since I would have to unfilter it for everything
else
to work, i.e. Preview...
Sometimes you want to Preview, sometimes Print, sometimes e-mail or Fax!
Print works, Fax works, Prwview works, but not e-mail....

Suggestions?


--
Thanks for your help.
I think you are trying to SendObject a report?
And you only what one record to the recipient?

If that's the idea, you can filter the report to just the one
Click to expand...
record
by
setting a public string variable to the appropriate filter value
Click to expand...
for
the
report, and then applying that in the report's Open event.

1. From the Database window, click the Modules tab, then the New Button.
Access opens a code window.

2. Immediately under the Option statements, enter:
Public gstrReportFilter As String

3. Place a command button on your form to emailing the record to the
email
address shown in the form. The code assumes that you want to email the
record with the ID field matches the ID value in the form:
Private Sub cmdEmail_Click()
If IsNull(Me.ID) Or IsNull(Me.Email) Then
MsgBox "Need ID and email"
Else
gstrReportFilter = "ID = " & Me.ID
DoCmd.SendObject acSendReport, "RMothStmnt",, _
Me.Email,,,"Your statement", "Here tis.", True
End If
End Sub

4. In the Open event procedure of the report:
Private Sub Report_Open(Cancel As Integer)
If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = ""
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a macro that I use to try this procedure, it first opens the
report
RMothStmnt , design view, window mode hidden,
next RunCommand SelectRecord
next SelectObject , Report,
next SetWarnings No

This results in it sending all the records instead of one.
I need this to run via a macro and just send one record
Click to expand...
Click to expand...
Click to expand...
 
Allen, now trying to make this work from Customers form instead of Menu.
Doe not work though.

On Error GoTo Err_Command173_Click
Dte2 = Date
DoCmd.OpenReport "RMothStatmnt", acPreview, "",
"[CustID]=[Forms]![Customers]![CustID]"
DoCmd.SendObject acSendNoObject, "FormTyp", acFormatHTML, [E Mail], , , ,
"From Gulf Coast Electric", True, ""
DoCmd.Close acReport, Me.Name
Exit_Command173_Click:
Exit Sub

Err_Command173_Click:
MsgBox Err.Description
Resume Exit_Command173_Click

--
-------------------------------------------------------------------------
Thanks for your Help.
Allen Browne said:
You lost me, Dave.

I thought the original question was about how to send one record (not all),
but I don't understand where the "all" are coming from.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave Elliott said:
There are no filters for the Report.
Code via form opens the Report; See Next:
However this merely opens the report in Preview
and then I want to either look, print,e-mail or fax.
This has to be done via a macro or code called from the menu.
I have one drop down to print, one to e-mail a snapshot and one to
Click to expand...
e-mail
as
rtf.

On Error GoTo Err_Command88_Click
Dte1 = Forms!frmAutoPayrollReport!StartDate
Dte2 = Forms!frmAutoPayrollReport!EndDat
DoCmd.SetWarnings False
DoCmd.OpenReport "RMothStatmnt", acPreview, "", "[ExpAmtOwed]>.01"
DoCmd.SetWarnings True
Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

--
Thanks for your help.
Allen Browne said:
How are you filtering it to get just one record?

Are you using the WhereCondition of the OpenReport action?
Or do you have the criteria of the report's query referring to a
Click to expand...
control
on
your form?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Actually the macro comes from a Custom Menu. (Print Menu)
Yes, I am trying to send just one record via e-mail.
I also have to be able to open this report normally in Preview mode which
is
done from a form.
The problem is that the report is opened via a form, but not printed
Click to expand...
via
a
form, a menu bar instead.
No ID or link is necessary since it is already done for you via the code
on
the form that opens the report.
Printing can only be done via the menu because of DB unique structure.
The macro I have now works except for sending all records via e-mail.
The custom Print Menu lets you Print the statement,Preview the
Click to expand...
statement
,
e-mail it or fax it.
Cant filter the report since I would have to unfilter it for everything
else
to work, i.e. Preview...
Sometimes you want to Preview, sometimes Print, sometimes e-mail or Fax!
Print works, Fax works, Prwview works, but not e-mail....

Suggestions?


--
Thanks for your help.
I think you are trying to SendObject a report?
And you only what one record to the recipient?

If that's the idea, you can filter the report to just the one
Click to expand...
record
by
setting a public string variable to the appropriate filter value
Click to expand...
for
the
report, and then applying that in the report's Open event.

1. From the Database window, click the Modules tab, then the New Button.
Access opens a code window.

2. Immediately under the Option statements, enter:
Public gstrReportFilter As String

3. Place a command button on your form to emailing the record to the
email
address shown in the form. The code assumes that you want to email the
record with the ID field matches the ID value in the form:
Private Sub cmdEmail_Click()
If IsNull(Me.ID) Or IsNull(Me.Email) Then
MsgBox "Need ID and email"
Else
gstrReportFilter = "ID = " & Me.ID
DoCmd.SendObject acSendReport, "RMothStmnt",, _
Me.Email,,,"Your statement", "Here tis.", True
End If
End Sub

4. In the Open event procedure of the report:
Private Sub Report_Open(Cancel As Integer)
If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = ""
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a macro that I use to try this procedure, it first opens the
report
RMothStmnt , design view, window mode hidden,
next RunCommand SelectRecord
next SelectObject , Report,
next SetWarnings No

This results in it sending all the records instead of one.
I need this to run via a macro and just send one record
Click to expand...
Click to expand...
Click to expand...
 
I too have the same problem, Allen. I'm wanting to be able to email
report with only one record on it, instead of preparing every record i
the database to be sent as a Snapshot. I used the original code tha
you put in for this posting and it got the report printing ever
record. I put everything where it needed to be, but now I just need t
narrow it down to one record, not all. Does this explanation help
 
Back
Top