Email canceled (1 Viewer)

Gismo

Registered User.
Local time
Today, 23:59
Joined
Jun 12, 2017
Messages
1,298
Hi,

i am emailing a report, if the report records are null, then the email send must be canceled
maybe with a comment that there is no records to be mailed
at this point in time if there is no record, the message i receive is the sendobject action was canceled then a end and debug screen

Below is my code, what should I do to change the pop us screen to indicate no records
you help would me appreciated

1611904478272.png


Private Sub Command0_Click()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String


Set rs = CurrentDb.OpenRecordset("SELECT * FROM TechSupportEmail ")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!email) Then
vRecipientList = vRecipientList & rs!email & ";"
rs.MoveNext
Else
rs.MoveNext
End If

Loop Until rs.EOF

vMsg = "Please find attached new document loaded"
vSubject = "New Document Loaded"

DoCmd.SendObject acSendReport, "Email SB Notification - From TechPubs to Tech Support", acFormatPDF, vRecipientList, , , vSubject, vMsg, False
MsgBox ("Report successfully eMailed!")

Else
MsgBox "No contacts."
End If

DoCmd.RunMacro "Save Dual Inspection"
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:59
Joined
May 7, 2009
Messages
19,169
what is the recordsource of the report? table or query?
then DCount() the table/query before sending the email.
if it returns 0, (no record).
 

Gismo

Registered User.
Local time
Today, 23:59
Joined
Jun 12, 2017
Messages
1,298
what is the recordsource of the report? table or query?
then DCount() the table/query before sending the email.
if it returns 0, (no record).
the query already filters the report
in this case, there is no records
i have a code in "On No Data"
Private Sub Report_NoData(Cancel As Integer)
Cancel = True
End Sub

My problem now is, when emailing, there is no report to mail then i get the error message
1611905702663.png


I need to change the code to display a message that there in no data to report and not to cancel the send then to pop up the debug screen
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:59
Joined
May 7, 2009
Messages
19,169
no, my mistake again.

you need to DCount("1", "TheQueryOfReport")
Code:
Private Sub Command0_Click()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String

If DCount("1", "TheReportQuery") < 1 Then
    Msgbox "Report has no data. Click Ok to Exit"
    Exit Sub
End If
'continue to code if there is data
.
...
...
 

Gismo

Registered User.
Local time
Today, 23:59
Joined
Jun 12, 2017
Messages
1,298
no, my mistake again.

you need to DCount("1", "TheQueryOfReport")
Code:
Private Sub Command0_Click()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String

If DCount("1", "TheReportQuery") < 1 Then
    Msgbox "Report has no data. Click Ok to Exit"
    Exit Sub
End If
'continue to code if there is data
.
...
...
thank you,

that works perfect

now i still need to run my macro which is at the end of the code, this must run weather the email was send or not
the macro can only run if the mail was send, if there was data so send
if no data, the the macro must run as well
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:59
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub Command0_Click()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String

If DCount("1", "TheReportQuery") < 1 Then
    Msgbox "Report has no data. Click Ok to Exit"
Else
'run your code here
...
...
...
End If
'run the macro here
DoCmd.RunMacro "theMacro"
End Sub
 

Gismo

Registered User.
Local time
Today, 23:59
Joined
Jun 12, 2017
Messages
1,298
Code:
Private Sub Command0_Click()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String

If DCount("1", "TheReportQuery") < 1 Then
    Msgbox "Report has no data. Click Ok to Exit"
Else
'run your code here
...
...
...
End If
'run the macro here
DoCmd.RunMacro "theMacro"
End Sub
that is the how I have my code but if there is no records to print, i get the no report to print message then code ends, it does not run my macro
Private Sub Save_Click()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String


If DCount("1", "EmailToSupport") < 1 Then
MsgBox "Report has no data. Click Ok to Exit"
Exit Sub
End If

Set rs = CurrentDb.OpenRecordset("SELECT * FROM TechSupportEmail ")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!email) Then
vRecipientList = vRecipientList & rs!email & ";"
rs.MoveNext
Else
rs.MoveNext
End If

Loop Until rs.EOF

vMsg = "Please find attached new document loaded"
vSubject = "New Document Loaded"

DoCmd.SendObject acSendReport, "Email SB Notification - From TechPubs to Tech Support", acFormatPDF, vRecipientList, , , vSubject, vMsg, False
MsgBox ("Report successfully eMailed!")

Else
MsgBox "No Documents to Display."
End If

DoCmd.RunMacro "Save Dual Inspection"
End Sub
 

Minty

AWF VIP
Local time
Today, 21:59
Joined
Jul 26, 2013
Messages
10,355
You haven't set your initial IF THEN code up correctly

It should be

Code:
If DCount("1", "EmailToSupport") < 1 Then
        MsgBox "Report has no data. Click Ok to Exit"
        
    Else
.....
 

Gismo

Registered User.
Local time
Today, 23:59
Joined
Jun 12, 2017
Messages
1,298
I missed the Else, but changing to Else

1611923756708.png


Private Sub Save_Click()
Dim rs As Recordset
Dim vRecipientList As String
Dim vMsg As String
Dim vSubject As String


If DCount("1", "EmailToSupport") < 1 Then
MsgBox "Report has no data. Click Ok to Exit"
Else


Set rs = CurrentDb.OpenRecordset("SELECT * FROM TechSupportEmail ")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs!email) Then
vRecipientList = vRecipientList & rs!email & ";"
rs.MoveNext
Else
rs.MoveNext
End If

Loop Until rs.EOF

vMsg = "Please find attached new document loaded"
vSubject = "New Document Loaded"

DoCmd.SendObject acSendReport, "Email SB Notification - From TechPubs to Tech Support", acFormatPDF, vRecipientList, , , vSubject, vMsg, False
MsgBox ("Report successfully eMailed!")

Else
MsgBox "No Documents to Display."

End If
DoCmd.RunMacro "Save Dual Inspection"

End Sub
 

Minty

AWF VIP
Local time
Today, 21:59
Joined
Jul 26, 2013
Messages
10,355
It's telling you the problem.
These things are much easier to see if you indent your code;
SQL:
Private Sub Save_Click()
    Dim rs As Recordset
    Dim vRecipientList As String
    Dim vMsg As String
    Dim vSubject As String


    If DCount("1", "EmailToSupport") < 1 Then
        MsgBox "Report has no data. Click Ok to Exit"
      
    Else
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM TechSupportEmail ")
        If rs.RecordCount > 0 Then
            rs.MoveFirst
            Do
                If Not IsNull(rs!Email) Then
                    vRecipientList = vRecipientList & rs!Email & ";"
                    rs.MoveNext
                Else
                    rs.MoveNext
                End If

            Loop Until rs.EOF

            vMsg = "Please find attached new document loaded"
            vSubject = "New Document Loaded"

            DoCmd.SendObject acSendReport, "Email SB Notification - From TechPubs to Tech Support", acFormatPDF, vRecipientList, , , vSubject, vMsg, False
            MsgBox ("Report successfully eMailed!")

        Else
            MsgBox "No Documents to Display."
        End If
    End IF
    
    DoCmd.RunMacro "Save Dual Inspection"
    
End Sub
 

Gismo

Registered User.
Local time
Today, 23:59
Joined
Jun 12, 2017
Messages
1,298
:) :) :)
This looks so good

thank you very much all
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:59
Joined
Feb 19, 2002
Messages
42,976
You may be happy but the real solution is to always include an error trap in code that opens a report when the report has code to cancel opening.
Code:
On Error GoTo ErrProc
....


ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2501    'report cancelled'
            Resume Next
        Case Else
            Msgbox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub
 

Users who are viewing this thread

Top Bottom