Guys,
I have many reports that need to be reviewed prior to emailing. Once the user has reviewed the the report, I want a msgbox to ask the user if they are ready to email it.
So, I have a list box of the reports for the users to select from with this code in the DoubleClick:
PREVIEW REPORT CODE (Note: rptDept is a Public Variable):
[indent]
Private Sub lst_PH_Reports_DblClick(Cancel As Integer)
Call RptType(1)
End Sub
Public Sub RptType(myDept As Integer)
Dim RspAnswer As String, RspReturnAnswer As String
Dim MyNote As String, myReturnNote As String
MyNote = “Do you need to view email contacts first?”
myReturnNote = “Send Email Now?”
RspAnswer = MsgBox(MyNote, vbQuestion + vbYesNo, “Email Contact List”)
If RspAnswer = vbNo Then
Select Case rptDept ‘PH
Case 1
rptName = Me.lst_PH_Reports.Column(0)
rptSubject = Me.lst_PH_Reports.Column(1)
ShowReports lst_PH_Reports.Value, Forms!frm_ReportSelection (**this is a public function**)
Case 2
rptName = Me.lst_BH_Reports.Column(0)
rptSubject = Me.lst_BH_Reports.Column(1)
ShowReports lst_BH_Reports.Value, Forms!frm_ReportSelection (**this is a public function**)
End Select
Else
DoCmd.OpenForm “frm_EmailContacts”, acNormal, , , , acWindowNormal
Exit Sub
End If
Call EmailIt (**this is a form level procedure**, I couldn’t get it to work from a PUBLIC PROCEDURE due to a Runtime Error 2585)
End Sub
[/indent]
As you can see, the Select Statement is based on rptDept and calls the PUBLIC FUNCTION “ShowReports” which opens the report selected from the listbox.
The problem is, once the report opens, the “CALL EMAIL” procedure runs. I only want it to run after the report is closed and the user has returned to the form. What do I need to accomplish this?
CALL EMAIL CODE:
Public Function EmailIt()
Select Case rptDept
Case 1
sReportName = rptName
sReportReason = rptSubject
Call SendEmailNow1
Case 2
sReportName = rptName
sReportReason = rptSubject
Call SendEmailNow1
End Select
End Function
Public Function SendEmailNow1()
Dim RspReturnAnswer As String, myEmailRequest As String
myEmailRequest = “Send Email Now?”
ReturnAnswer = MsgBox(myEmailRequest, vbQuestion + vbYesNo, “Email Process”)
If ReturnAnswer = vbNo Then
Exit Function
Else
msg = “….is attached for your review”
sSubject = sReportReason & ” Report”
Set loDb = CurrentDb
Set loRst = loDb.OpenRecordset(“qContactEMails”)
‘Collect the email recipients from the query “qContactEmails”
‘Each person who has a check mark next to their name will get the eMail
With loRst
Do Until .EOF
em = .Fields(“emailaddy”) & “;” & em
.MoveNext
Loop
End With
‘Prepare the eMail and Provide a link the
‘in the eMail with the file location
DoCmd.SendObject acReport, sReportName, acFormatSNP, _
To:=em, _
Subject:=sSubject, _
MessageText:=msg, _
EditMessage:=False
‘Inform user the email is sent and process is complete
‘Give the user a chance to decide if they want to process another report or
‘return the report menu
‘Response = MsgBox(“Email Sent! Send Another Report?”, vbYesNo)
loRst.Close
‘If user selected NO, then send them to the report menu and close this form
If vbNo Then
DoCmd.Close
stDocName = “frmReportsMenu”
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
