e-mail macro

M

mahadevan.swamy

Hi,

I have a report which i want to send it to different people and I have
created a macro which can do that. Now I want to include a query
object (it is a long number) in the subject line or report filename so
that I dont have to type it again and again. Does anyone have any
ideas?

Thanks in advance

Mahadevan Swamy
 
M

mahadevan.swamy

I'll give you a VBA coding of what I am talking about.

In this VB code,

Option Compare Database

'------------------------------------------------------------
' mcrEmailClosure
'
'------------------------------------------------------------
Function mcrEmailClosure()
On Error GoTo mcrEmailClosure_Err

' Email as SNP closure report
DoCmd.SendObject acReport, "rptCloseOut",
"SnapshotFormat(*.snp)",
"", "", "", "Closure Report - Concern Number", "Please see attached
closure report", True, ""

mcrEmailClosure_Exit:
Exit Function

mcrEmailClosure_Err:
MsgBox Error$
Resume mcrEmailClosure_Exit

End Function
---------------------------------------------------------------------------­-----------------------
I want to add a query object called Report # in the subject or
filename. This query object is just a number for a particular report
so that I dont have to type it again and again when i want to send it
to someone else. Any ideas?

Thanks
Swamy
 
T

Tom Wickerath

Any ideas?

No. The reason is that you failed to answer the question I asked. I did not
ask you to provide VBA code for sending an attached .SNP file. I asked you to
please explain what you mean by "a query object (it is a long number)...". I
also asked you to provide some sample data, along with table and field names.
I want to add a query object called Report # in the subject or
filename.
Click to expand...

Please....define "query object". What does this term mean to you?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
M

mahadevan.swamy

This will be difficult for me to explain but I'll try my best. I have
a query called "qryClosure". In this database, I have fields like
Report #, Customer #, CA Resp, Defect Code etc.... and only ONE
record. This query is linked to the report called rptCloseout and
Report # from qryClosure is in this report. Now what I want to do is
when I send a snapshot of this report to someone using the macro, I
want to include this report # in the subject or filename without
typing it in. Basically what I want the macro to do is to take the
info (report #) from the qryClosure file -- > Report # field and put
that number in the subject or filename. Does that help or do you need
me to explain more?

Thanks for your assistance
 
T

Tom Wickerath

In this database, I have fields like
Report #, Customer #, CA Resp, Defect Code etc....
Click to expand...

You will be doing yourself a huge favor in the future if you avoid using
special characters, including # signs and spaces, in anything that you assign
a name to in Access. Here is a KB article that talks about this:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

.... and only ONE record.
Click to expand...

So the query named "qryClosure" returns only one record, correct?
This query is linked to the report called rptCloseout....
Click to expand...

What do you mean by "linked to the report"? Are you saying that this query
serves as the Record Source for your report? If so, then what you need to do
should be very easy. (The term "query linked to a report" really has no
meaning--at least to me it does not).
...and Report # from qryClosure is in this report.
Click to expand...

Okay, so I am thinking that qryClosure does indeed serve as the record
source for your report. It certainly sounds that way to me. Try the following
VBA code in the report's Activate event procedure:

Private Sub Report_Activate()
On Error GoTo ProcError

' The caption is used for the Snapshot filename (caption.snp)
Me.Caption = "Report No. " & [Report #]

DoCmd.Maximize '<---Optional. Use DoCmd.Restore in Report_Close()

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


Private Sub Report_Close()
On Error GoTo ProcError

DoCmd.Restore
ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
"Error in Report_Close event procedure..."
Resume ExitProc
End Sub

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ProcError

MsgBox "There is no data available for this report.", _
vbInformation, "Your title goes here."
Cancel = True

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


You will need to have the [Report #] field in a bound text box (ie. the
control source of the text box is the Report # field) on the report. You can
set the visible property to No, if you don't want to display it. If you add
the optional NoData event procedure, as indicated above, you will need to
trap for Error 2501 in the click event procedure for the command button that
you are using to open the report. In addition, in order for this to work, you
will need to first open the report in normal preview mode, and then use the
DoCmd.SendObject method. Here is an example, which includes trapping for
error 2501. I declared a string variable and set it to your report name, so
that you only need to change it in one place:

Dim strReport As String
strReport = "rptCloseOut"

You could also rewrite the code slightly, so that it accepted the name of
the report as a parameter:


Option Compare Database
Option Explicit

Private Sub cmdSendReport_Click()
On Error GoTo ProcError

Dim strReport As String
strReport = "rptCloseOut"

' If this is a bound form (ie. includes a recordsource)
If Me.Dirty = True Then
Me.Dirty = False
End If

' Open report in preview
' DoCmd.OpenReport strReport, View:=acViewPreview

' or...

' Open report in preview, filtered for one record using the optional
' WhereCondition argument with the primary key:

DoCmd.OpenReport strReport, View:=acViewPreview, _
WhereCondition:="pkRequestID =" & Me.pkRequestID

' Email as SNP closure report
DoCmd.SendObject acReport, strReport, _
OutputFormat:="SnapshotFormat(*.snp)", _
Subject:="Closure Report - Concern Number", _
MessageText:="Please see attached closure report", _
EditMessage:=True

' Optional
DoCmd.Close acReport, strReport

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'No Data, so trap for this error and ignore it.

Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdSendReport_Click..."
End Select
Resume ExitProc
End Sub


I hope this helps!

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

PS. For the Private Sub cmdSendReport_Click() code that I showed in my
previous reply, if you are using the second form, which filters the report
using the optional WhereCondition argument, you should take steps to protect
against a run-time error in the event that the user is sitting on a brand new
(not saved) record. You can test the form property Me.NewRecord to determine
this. Substitute the code fragment shown below into this procedure:


' Open report in preview, filtered for one record using the optional
' WhereCondition argument with the primary key:

If Not Me.NewRecord Then
DoCmd.OpenReport strReport, View:=acViewPreview, _
WhereCondition:="pkRequestID =" & Me.pkRequestID

' Email as SNP closure report
DoCmd.SendObject acReport, strReport, _
OutputFormat:="SnapshotFormat(*.snp)", _
Subject:="Closure Report - Concern Number", _
MessageText:="Please see attached closure report", _
EditMessage:=True

' Optional
DoCmd.Close acReport, strReport
Else
MsgBox "Enter some data before attempting to send a report.", _
vbInformation, "No Data Available..."

End If



Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
 
Top