email a report

[Guest]

I have used code kindly supplied by Allen Browne to make a report from the
current record

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

But I would now like to add some code that will email it as an attachment in
snapshot file format, so all the user has to do is add the email address.

I am a complete VB novice so step by step instructions would be good!

Can anyone help?

 

[Allen Browne]

There are several things to address here.

The basic idea is to use SendObject instead of OpenReport. However,
SendObject does not have a WhereCondition, so you need to set up a public
string variable to hold the filter value, and assign it in the Open event of
the report.

1. On the Modules tab of the Database window, click New.
Access opens a code window.
Immediately below the Option statements, enter:
Public gstrReportFilter As String
Save the module with a name such as Module1. Close.

2. Open the report in design view.
Open the Properties box (View menu).
With "Report" as the title in the properties box, choose the Event tab.
Set the On Open property to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:
If len(gstrReportFilter) > 0 Then
Me.Filter = gstrFilterString
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
Save. Close.

3. Replace the code for your command button with this:
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else

gstrReportFilter = "[ID] = " & Me.[ID]
DoCmd.SendObject acSendReport, "Report1", acFormatSNP,,,, _
"Your report", "Here's the report", True
End If
End Sub

 

[Guest]

Brilliant, thank you.

There are several things to address here.

The basic idea is to use SendObject instead of OpenReport. However,
SendObject does not have a WhereCondition, so you need to set up a public
string variable to hold the filter value, and assign it in the Open event of
the report.

1. On the Modules tab of the Database window, click New.
Access opens a code window.
Immediately below the Option statements, enter:
Public gstrReportFilter As String
Save the module with a name such as Module1. Close.

2. Open the report in design view.
Open the Properties box (View menu).
With "Report" as the title in the properties box, choose the Event tab.
Set the On Open property to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:
If len(gstrReportFilter) > 0 Then
Me.Filter = gstrFilterString
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
Save. Close.

3. Replace the code for your command button with this:
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else

gstrReportFilter = "[ID] = " & Me.[ID]
DoCmd.SendObject acSendReport, "Report1", acFormatSNP,,,, _
"Your report", "Here's the report", True
End If
End Sub

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

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

I have used code kindly supplied by Allen Browne to make a report from the
current record

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

But I would now like to add some code that will email it as an attachment
in
snapshot file format, so all the user has to do is add the email address.

I am a complete VB novice so step by step instructions would be good!

Can anyone help?

 

[Guest]

Question would this work as well?
If Me.NewRecord Then ' checks if there is a record to mail
MsgBox " select a record to Mail."
Else
strWhere = "[report number] = " & Me.[report number]
DoCmd.OpenReport "Tolerance_report", acViewPreview, , strWhere
DoCmd.SendObject acSendReport, "Tolerance_report", acFormatSNP, , , ,
"Sedalia Out of Tolerance Report", , True
DoCmd.Close acReport, "Tolerance_report"
End If

Thanks for the advise,
Pixie

Brilliant, thank you.

There are several things to address here.

The basic idea is to use SendObject instead of OpenReport. However,
SendObject does not have a WhereCondition, so you need to set up a public
string variable to hold the filter value, and assign it in the Open event of
the report.

1. On the Modules tab of the Database window, click New.
Access opens a code window.
Immediately below the Option statements, enter:
Public gstrReportFilter As String
Save the module with a name such as Module1. Close.

2. Open the report in design view.
Open the Properties box (View menu).
With "Report" as the title in the properties box, choose the Event tab.
Set the On Open property to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:
If len(gstrReportFilter) > 0 Then
Me.Filter = gstrFilterString
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
Save. Close.

3. Replace the code for your command button with this:
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else

gstrReportFilter = "[ID] = " & Me.[ID]
DoCmd.SendObject acSendReport, "Report1", acFormatSNP,,,, _
"Your report", "Here's the report", True
End If
End Sub

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

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

I have used code kindly supplied by Allen Browne to make a report from the
current record

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

But I would now like to add some code that will email it as an attachment
in
snapshot file format, so all the user has to do is add the email address.

I am a complete VB novice so step by step instructions would be good!

Can anyone help?