Email a Report (1 Viewer)

W

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
I have a form with a command button to email a report.
The criteria for the report is based on the ProjectID on the form that has the email button.

This may be all wrong but it is what I've come up with so far. See code below for the command button.

Initially I had a problem with error code 2501.
I tried fixing it with the ErrHandler. (I don't think this really worked due to the problem below.)
If I click it once, and choose not to send the email, everything is fine.
If I click it again, the report stays open in the background and I will have to close it manually.

What do I need to fix in the code?

1666811439670.png




Second time I click the command button to email the report, I get this.

1666811597799.png



Code: 
Private Sub cmdbuttonEMAIL_Click()
 
On Error GoTo ErrHandler

    Dim reportName As String
    Dim criteria As String
    Dim lngResult As String
    
    DoCmd.RefreshRecord 'This saves a dirty record prior to opening the report.
        
    lngResult = "ALCON," & vbCrLf & "Attached is a PA Request for:" & vbCrLf & "" & vbCrLf & _
                "    Bergen Number:   " & Me![BergenNumber] & "." & vbCrLf & _
                "    Project Name:       " & Me![ProjectName] & "." & vbCrLf & "" & vbCrLf & _
                "Let me know if you need anything else."
    reportName = "R_PANumRequest"
    criteria = "[ProjectID] =" & ProjectID & ""
    
    'Need to open and close report in order for this to work.
    DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
    
    DoCmd.SendObject acSendReport, reportName, acFormatPDF, , , , "PA Request for - " & _
    "" & Me![LocationNumber] & "", lngResult
    
    'Need to close the report.
    DoCmd.Close acReport, reportName, acSaveYes
    
ErrHandler_exit:
    Exit Sub
    
ErrHandler:
   If Err = 2501 Then
      'User canceled the message - ignore this
   Else
      'Report the error
      MsgBox Err.Description, vbExclamation
      Resume ErrHandler_exit
   End If
End Sub
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:36
Joined
Jun 21, 2011
Messages
5,899
Hmm, the way I eMail a report is save it to a Temp folder, attach and send the eMail and then delete the file out of the Temp folder. This way the report is closed no matter what.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 20:36
Joined
Sep 21, 2011
Messages
14,310
I am going to go to my deathbed saying this. :(
Walk through your code and see what is happening.
If you choose not to send, is that an error? If so, you skip closing the report.

Regardless. Walk through your code. That should at least give you more idea as to what is happening.
 
W

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
GinaWhipp said:
Hmm, the way I eMail a report is save it to a Temp folder, attach and send the eMail and then delete the file out of the Temp folder. This way the report is closed no matter what.
Click to expand...
I’d have to research that but would the work for a split db? Currently the FE is on each of the users PCs. I’d have to be sure that each PC could store it in the exact location, right?
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:36
Joined
Jun 21, 2011
Messages
5,899
I only use split files and you could use a folder on the Server. Just create one to use to save your report to.
 
W

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
I managed to make it work, although I'm confused on exactly how the error handling works.
Here's my solution.
Please chime in on:
1. How this error handling steps through this?
2. Is this is a solid solution?

Note the error 2501 only happens when you cancel the email from the Outlook dialog box.

Code: 
Private Sub cmdbuttonEMAIL_Click()
 
    Dim reportName As String
    Dim criteria As String
    Dim lngResult As String
    
    If Me.Dirty Then Me.Dirty = False
            
    lngResult = "ALCON," & vbCrLf & "Attached is a PA Request for:" & vbCrLf & "" & vbCrLf & _
                "    Bergen Number:   " & Me![BergenNumber] & "." & vbCrLf & _
                "    Project Name:       " & Me![ProjectName] & "." & vbCrLf & "" & vbCrLf & _
                "Let me know if you need anything else."
    reportName = "R_PANumRequest"
    criteria = "[ProjectID] =" & ProjectID & ""
    
    
'Set the error traping
   On Error GoTo zerrortrap
  
    'Need to open and close report in order for this to work, with the specified criteria.
    DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden

    DoCmd.SendObject acSendReport, reportName, acFormatPDF, , , , "PA Request for - " & _
    "" & Me![LocationNumber] & "", lngResult

    DoCmd.Close acReport, reportName, acSaveNo
    
error_Exit:
   'Clean up after code is ran or if there was an error
  
    Exit Sub
    
zerrortrap:
   Select Case Err.Number
      Case 2501
         MsgBox "User Canceled the Email.", vbInformation
         DoCmd.Close acReport, reportName, acSaveNo
      Case Else
         MsgBox "Error " & Err.Number & " " & Err.Description
      End Select
    Resume error_Exit
    
End Sub
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 20:36
Joined
Sep 21, 2011
Messages
14,310
Error handling is pretty easy.
If an error occurs, you go to a a label you have defined. There you allow for all the errors you want to handle or expect and perhaps just put an error message up for those you did not expect?

In your case you could resume at a label just before you close the report?
That way you are not duplicating code.?

Code: 
Err_Resume:
    DoCmd.Close acReport, reportName, acSaveNo
   
error_Exit:
   'Clean up after code is ran or if there was an error
 
    Exit Sub

It would be very rare NOT to have error handling in a procedure.
Why Access does not add a basic error handling code on each event is beyond me. :(

FWIW, you are the first person I have seen that does not supply the To,CC and BCC to the Docmd.SendObject command?
 
W

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
Gasman said:
Error handling is pretty easy.
If an error occurs, you go to a a label you have defined. There you allow for all the errors you want to handle or expect and perhaps just put an error message up for those you did not expect?

In your case you could resume at a label just before you close the report?
That way you are not duplicating code.?

Code: 
Err_Resume:
    DoCmd.Close acReport, reportName, acSaveNo
  
error_Exit:
   'Clean up after code is ran or if there was an error

    Exit Sub

It would be very rare NOT to have error handling in a procedure.
Why Access does not add a basic error handling code on each event is beyond me. :(

FWIW, you are the first person I have seen that does not supply the To,CC and BCC to the Docmd.SendObject command?
Click to expand...
When the user clicks the Email command button, it opens the Outlook email form and stays open for the user to decide who they would like to send the email to. That's way no To, CC, BCC. See below. It doesn't send it out right away but rather displays it first.

I have not learned much about error handling.
Thus, I have very little error handling in my databases. Sounds like I need to learn that aspect and apply it.

1666894601204.png
 
W

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
Weekleyba said:
When the user clicks the Email command button, it opens the Outlook email form and stays open for the user to decide who they would like to send the email to. That's way no To, CC, BCC. See below. It doesn't send it out right away but rather displays it first.

I have not learned much about error handling.
Thus, I have very little error handling in my databases. Sounds like I need to learn that aspect and apply it.

View attachment 104146
Click to expand...

Perhaps I spoke to soon....
I tested this over and over with no trouble and now... the error message pops up again.
To cause the error, you have to cancel the email form, ie. not send it, twice in a row, without closing the form that the Email command button is on.
So why doesn't it work the second time around?
It appears it skips the error handling the second time around.
Help!

1666896228765.png
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 20:36
Joined
Sep 21, 2011
Messages
14,310
No idea TBH.
I would have the email addresses in the DB, and they would be selected from there.
Although Outlook would send the email, it would not get involved GUI wise.

In fact all my email sending was via Outlook automation.

Again, all I can suggest is walk though your code and see where the code path is.
 
W

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
Frustrating for sure....
If I close the from with the command button and reopen and click the Email button, it will continue to work.
It appears I need to some how refresh the form or something like that.
 
W

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
I decided to close the form, via VBA, where the Email command button resides. (F_PANumRequest)
This seems to work.
If someone else sees a better solution around this problem, let me know.

Code: 
Private Sub cmdbuttonEMAIL_Click()
  'https://answers.microsoft.com/en-us/msoffice/forum/all/error-message-run-time-error-2501-the-sendobject/7a6f6ca2-65e3-4615-b2cc-cc372c83e04c
  'https://bytes.com/topic/access/answers/955419-sendobject-error-2501-a
 
    Dim reportName As String
    Dim criteria As String
    Dim lngResult As String
    
    If Me.Dirty Then Me.Dirty = False
            
    lngResult = "ALCON," & vbCrLf & "Attached is a PA Request for:" & vbCrLf & "" & vbCrLf & _
                "    Bergen Number:   " & Me![BergenNumber] & "." & vbCrLf & _
                "    Project Name:       " & Me![ProjectName] & "." & vbCrLf & "" & vbCrLf & _
                "Let me know if you need anything else."
    reportName = "R_PANumRequest"
    criteria = "[ProjectID] =" & ProjectID & ""
    
    
'Set the error traping
   On Error GoTo zerrortrap
  
    'Need to open and close report in order for this to work, with the specified criteria.
    DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden

    DoCmd.SendObject acSendReport, reportName, acFormatPDF, , , , "PA Request for - " & _
    "" & Me![LocationNumber] & "", lngResult

    DoCmd.Close acReport, reportName, acSaveNo
    
error_Exit:
   'Clean up after code is ran or if there was an error
  
    Exit Sub
    
zerrortrap:
   Select Case Err.Number
      Case 2501
         MsgBox "You Canceled the Email.", vbInformation
         DoCmd.Close acReport, reportName, acSaveNo
         'Added the line below to close the form.
         DoCmd.Close acForm, "F_PANumRequest", acSaveYes
      Case Else
         MsgBox "Error " & Err.Number & " " & Err.Description
      End Select
    Resume error_Exit
    
End Sub
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 20:36
Joined
Sep 21, 2011
Messages
14,310
You could just use Me.Name as that is the form your control is on?
I believe acSaveYes or the opposite only applies to a form in design mode?
 
You must log in or register to reply here.

Similar threads

L
Send looping Email
Replies
3
Views
325
looma123
L
M
VBA output report to PDF and email
Replies
18
Views
778
Pat Hartman
P
C
Filter report with two criteria using combo boxes and button on form.
Replies
4
Views
284
CraigDouglas
C
A
Sending Email SMTP setting
Replies
7
Views
408
ions
I
A
Report output to filepath variable
Replies
4
Views
423
Antony Cole
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom