Email Current Record from Form

[MaryP]

I would like to email only the record currently being viewed within the form.
The ideal way for this to work is to click a button on the form to email the
current record only to the specified address within the form. Can anyone help
with this?

 

[Allen Browne]

SendObject can email a report. Unlike OpenReport, it does not have a
WhereCondition, so the problem becomes, "How do I limit SendObject to just
the current record?"

One approach is to use a string variable to act as the filter for the
report. You set this to the filter you want before you open the report (with
SendObject), and in the report's Open event, you set its filter and clear
the string again.

Steps:

1. In a standard module (one on see on the Modules tab of the Database
window), in the General Declarations section (at the top, with the Option
statements), declare you variable like this:
Public gstrReportFilter As String
Save the module.

2. In the Open event procedure of the report:
Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
DoCmd.SendObject acSendReport, "Report1",,Me.EmailAddr,,,"Here's
your report","Attached find Report1, etc", True= vbNullString
End If
End Sub

3. In the Click event procedure of your form, set the filter before you
SendObject. This example assumes a numeric primary key named ID to identify
the record, and a field named EmailAddr that contains the email address to
send to:

Private Sub cmdEmail_Click()
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Select a record to email."
ElseIf IsNull(Me.EmailAddr) Then
MsgBox "No email address to send to"
Else
gstrReportFilter = "ID = " & Me.[ID]
DoCmd.SendObject acSendReport, "Report1", ,Me.EmailAddr, , , _
"Here's your report","Attached find Report1, etc", True
End If
End Sub

 

[Arvin Meyer [MVP]]

I would like to email only the record currently being viewed within the
form.
The ideal way for this to work is to click a button on the form to email
the
current record only to the specified address within the form. Can anyone
help
with this?

If you are using Outlook, you can use:

http://www.datastrat.com/Code/OutlookEmail.txt

or you can use SendObject. Like:

Dim strTo As String
Dim strSubject As String
Dim strMsg As String

strTo = Me.txtEmailAddress
strSubject = "Current data"
strMsg = Me.txtData1 & vbCrLf & _
Me.txtData2 & vbCrLf & _
Me.txtData3 & vbCrLf & _
' ...
Me.txtData9 & vbCrLf & _

"Thanks," & vbCrLf & _
"Mary" & vbCrLf & _
"(e-mail address removed)"

DoCmd.SendObject , acSendNoObject, , _
strTo , , , strSubject, strMessage, False

 

[MaryP]

Thank you for responding.

I put all the suggested code in place and I can trigger both message boxes.
So, I know it is working correctly. When I do have a legit email and record
to mail to I do get the send mail box with the selection of Rich Text Format,
Excel 97-2003, Text Files, Excel 5-7, HTML, or snapshot format. The Output
area is grayed out with All selected. When I click <OK> nothing happens...no
error..no email is being sent..

SendObject can email a report. Unlike OpenReport, it does not have a
WhereCondition, so the problem becomes, "How do I limit SendObject to just
the current record?"

One approach is to use a string variable to act as the filter for the
report. You set this to the filter you want before you open the report (with
SendObject), and in the report's Open event, you set its filter and clear
the string again.

Steps:

1. In a standard module (one on see on the Modules tab of the Database
window), in the General Declarations section (at the top, with the Option
statements), declare you variable like this:
Public gstrReportFilter As String
Save the module.

2. In the Open event procedure of the report:
Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
DoCmd.SendObject acSendReport, "Report1",,Me.EmailAddr,,,"Here's
your report","Attached find Report1, etc", True= vbNullString
End If
End Sub

3. In the Click event procedure of your form, set the filter before you
SendObject. This example assumes a numeric primary key named ID to identify
the record, and a field named EmailAddr that contains the email address to
send to:

Private Sub cmdEmail_Click()
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Select a record to email."
ElseIf IsNull(Me.EmailAddr) Then
MsgBox "No email address to send to"
Else
gstrReportFilter = "ID = " & Me.[ID]
DoCmd.SendObject acSendReport, "Report1", ,Me.EmailAddr, , , _
"Here's your report","Attached find Report1, etc", True
End If
End Sub

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

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

I would like to email only the record currently being viewed within the
form.
The ideal way for this to work is to click a button on the form to email
the
current record only to the specified address within the form. Can anyone
help
with this?

 

[Allen Browne]

SendObject just activates whatever email program you have installed to fire
off the email. If no MAPI-compliant email program is installed, or there is
a problem with the installation, it doesn't work. Most Access users have
Outlook installed; it should work with that.

It's worth persuing down that track, but if you want to investigate
alternatives, see what Tony Toews suggests here:
http://www.granite.ab.ca/access/email.htm

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

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

I put all the suggested code in place and I can trigger both message
boxes.
So, I know it is working correctly. When I do have a legit email and
record
to mail to I do get the send mail box with the selection of Rich Text
Format,
Excel 97-2003, Text Files, Excel 5-7, HTML, or snapshot format. The Output
area is grayed out with All selected. When I click <OK> nothing
happens...no
error..no email is being sent..

SendObject can email a report. Unlike OpenReport, it does not have a
WhereCondition, so the problem becomes, "How do I limit SendObject to
just
the current record?"

One approach is to use a string variable to act as the filter for the
report. You set this to the filter you want before you open the report
(with
SendObject), and in the report's Open event, you set its filter and clear
the string again.

Steps:

1. In a standard module (one on see on the Modules tab of the Database
window), in the General Declarations section (at the top, with the Option
statements), declare you variable like this:
Public gstrReportFilter As String
Save the module.

2. In the Open event procedure of the report:
Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
DoCmd.SendObject acSendReport,
"Report1",,Me.EmailAddr,,,"Here's
your report","Attached find Report1, etc", True= vbNullString
End If
End Sub

3. In the Click event procedure of your form, set the filter before you
SendObject. This example assumes a numeric primary key named ID to
identify
the record, and a field named EmailAddr that contains the email address
to
send to:

Private Sub cmdEmail_Click()
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Select a record to email."
ElseIf IsNull(Me.EmailAddr) Then
MsgBox "No email address to send to"
Else
gstrReportFilter = "ID = " & Me.[ID]
DoCmd.SendObject acSendReport, "Report1", ,Me.EmailAddr, , ,
_
"Here's your report","Attached find Report1, etc", True
End If
End Sub

I would like to email only the record currently being viewed within
the form The ideal way for this to work is to click a button on the
form to email the current record only to the specified address
within the form. Can anyone anyone help with this?