Email Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to email a report based on the current record (you'd think there'd
be a wizard for sending ONLY the current record; I mean, who sends every
Invoice every time?). I've used the code below along with several
variations, but still can't seem to send only the current record. Any help
with the code would be appreciated.
Notes:
"Invoice" is the name of the report.
"frmWorkOrder" is the name of the form with the "EmailInvoice" command button.
"tblwoWorkOrder#" is the field name (and primary key)
"acFormatSNP" is the abbreviation for sending a Snapshot format (right?)

Private Sub EmailInvoice_Click()
On Error GoTo Err_EmailInvoice_Click
Dim stDocName As String
stDocName = "Invoice"
DoCmd.SendObject acReport, [Forms]![frmWorkOrder]![tblwoWorkOrder#],
acFormatSNP, "(e-mail address removed)", "", "", "testing...",
"testing,testing", True
Exit_EmailInvoice_Click:
Exit Sub
Err_EmailInvoice_Click:
MsgBox Err.Description
Resume Exit_EmailInvoice_Click
End Sub
 
Nevermind...
I added [Forms]![frmWorkOrder]![tblwoWorkOrder#] to the criteria field of
the report's query. Then I changed the code's command string to:

DoCmd.SendObject acReport, stDocName, acFormatSNP, "(e-mail address removed)",
"", "", "testing...", "testing,testing", True
 
Dim stDocName As String
stDocName = "Invoice"
DoCmd.SendObject acReport, [Forms]![frmWorkOrder]![tblwoWorkOrder#],
Click to expand...
acFormatSNP, "(e-mail address removed)", "", "", "testing...",
"testing,testing", True

the stDocName variable isn't used in the above code, and you really don't
need to use a variable anyway - unless you need to change the ObjectName
argument dynamically, in order to output different reports using this one
procedure.

that brings us to the ObjectName argument. if you look up the SendObject
Method in Access VBA Help, it tells you that this argument requires "A
string expression that's the valid name of an object of the type selected by
the objecttype argument." your object type is acReport, so the next argument
must be the name of the report, as a string.

AFAIK, there is no way to filter the records in the report *from within the
SendObject method*. you have to filter the report directly. try the
following SendObject code, as

DoCmd.SendObject acReport, "Invoice", acFormatSNP, "(e-mail address removed)",
"", "", "testing...", "testing,testing", True

add the following procedure to the report's OnOpen event, as

Private Sub Report_Open(Cancel As Integer)

Me.Filter = "tblwoWorkOrder# = " &
[Forms]![frmWorkOrder]![tblwoWorkOrder#]
Me.FilterOn = True

End Sub

the "Me.Filter = ..." should all be on one line, of course. the above code
assumes that frmWorkOrder is open at the time the SendObject code runs. the
code also assumes that the work order number field is a Number data type. if
it is a Text data type, you need to add single quotes to the code, as

Me.Filter = "tblwoWorkOrder# = '" &
[Forms]![frmWorkOrder]![tblwoWorkOrder#] & "'"

btw, suggest you avoid using special characters in *any* names in the
database in future - field names, table names, any other object names. the
recommended standard is alpha characters and underscores only, no spaces
(even numeric characters in a name can be a headache).

hth


tvh said:
I'm trying to email a report based on the current record (you'd think there'd
be a wizard for sending ONLY the current record; I mean, who sends every
Invoice every time?). I've used the code below along with several
variations, but still can't seem to send only the current record. Any help
with the code would be appreciated.
Notes:
"Invoice" is the name of the report.
"frmWorkOrder" is the name of the form with the "EmailInvoice" command button.
"tblwoWorkOrder#" is the field name (and primary key)
"acFormatSNP" is the abbreviation for sending a Snapshot format (right?)

Private Sub EmailInvoice_Click()
On Error GoTo Err_EmailInvoice_Click
Dim stDocName As String
stDocName = "Invoice"
DoCmd.SendObject acReport, [Forms]![frmWorkOrder]![tblwoWorkOrder#],
acFormatSNP, "(e-mail address removed)", "", "", "testing...",
"testing,testing", True
Exit_EmailInvoice_Click:
Exit Sub
Err_EmailInvoice_Click:
MsgBox Err.Description
Resume Exit_EmailInvoice_Click
End Sub
Click to expand...
 
Back
Top