Error 2501

[mike60smart]

Hi Everyone

I am trying to attach a PDF file as an attachment to an EMail but when I run the Code I get the following Error.

The Report opens just fine but no EMail is generated.

Any help appreciated.

Private Sub cmdPreview_Click()

10        If Me.Dirty Then Me.Dirty = False
20        On Error GoTo cmdPreview_Click_Error
30        If Me.Dirty Then Me.Dirty = False ' force a save
          Dim strPDFFilenameToStore As String
40        strPDFFilenameToStore = "C:\Quotes\" & Format(Date, "yyyymmdd") & " - QuoteNo Nr - '" & [OrderNo] & "'.pdf"
          Dim strDocName As String
          Dim strWhere As String
          Dim strSubject As String
          Dim strBody As String
          Dim strJN As String
50        strJN = Me.OrderNo
60        strDocName = "rptCustomerQuote"
70        strWhere = "[OrderNo]='" & Me!OrderNo & "'"
80        strSubject = "Quote Attached"

90        strBody = "Find attached the Quote for your Project"
          
100       DoCmd.OutputTo acOutputReport, strDocName, acFormatPDF, strPDFFilenameToStore
110       DoCmd.OpenReport "rptCustomerQuote", acViewPreview, , "[OrderNo]='" & Me!OrderNo & "'"

120       Debug.Print strPDFFilenameToStore
130       DoCmd.SendObject _
              ObjectType:=acSendReport, _
              ObjectName:="rptCustomerQuote", _
              OutputFormat:=acFormatPDF, _
              To:=Me.Email, _
              Subject:=strSubject, _
              MessageText:=strBody, _
              EditMessage:=True
          
140       On Error GoTo 0
150       Exit Sub

cmdPreview_Click_Error:

160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPreview_Click, line " & Erl & "."




End Sub

 

[June7]

I never use the posted syntax for commands - just means more typing. However, I don't see anything wrong with it.

What debugging have you done? Are values populating as expected? Do you get same error with another report object? Try the acSendNoObject parameter and not attaching PDF - will email open? Disable error handler and see what happens.

 

[The_Doc_Man]

The first thing that comes to mind is what would have been printed by the Debug.Print in line 120, which didn't print ANYTHING because your code barfed at line 100 before that point. Move the Debug.Print to an earlier spot to verify that the file name is valid. That would be the simplest thing to do to check the most likely culprit, a bad file name.

 

[mike60smart]

I never use the posted syntax for commands - just means more typing. However, I don't see anything wrong with it.

What debugging have you done? Are values populating as expected? Do you get same error with another report object? Try the acSendNoObject parameter and not attaching PDF - will email open? Disable error handler and see what happens.

Hi June

I could not see anything wrong and when I put a breakpoint on the error line all relevant details were correct.

I changed the Code to the following and it now works.

Private Sub cmdPreview_Click()

10        On Error GoTo cmdPreview_Click_Error

20    If Me.Dirty = True Then Me.Dirty = False
30    MsgBox "A copy of the Quote will be saved to the C Drive Quotes Folder", vbInformation
      Dim strDocname As String
      Dim strWhere As String
      Dim strToWhom As String
      Dim strMsgBody As String
      Dim strSubject As String
      Dim strtxtName As String
      Dim strDir As String

40    strDocname = "rptCustomerQuote"
50    strWhere = "[OrderNo]='" & Me.OrderNo & "'"
60    strSubject = "Quote Attached"
70    strToWhom = Me.Email
80    strMsgBody = "Find attached latest Quote Details"
90    strDir = "C:\Quotes\"

100   DoCmd.OutputTo acOutputReport, "rptCustomerQuote", acFormatPDF, strDir & "\" & Format(Date, "yyyymmdd") & ", False"
110   DoCmd.OpenReport strDocname, acPreview, , strWhere
120   DoCmd.SendObject acSendReport, "rptCustomerQuote", acFormatPDF, strToWhom, , , strSubject, strMsgBody, True
      
          
130       On Error GoTo 0
140       Exit Sub

cmdPreview_Click_Error:

150       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPreview_Click, line " & Erl & "."

End Sub

 

[June7]

Doc, Mike states report opens fine so OutputTo must run just fine.

Mike, saved PDF will be an unfiltered version of report because you run OutputTo before opening filtered report. Is that what you want?

 

[tvanstiphout]

Why are you calling DoCmd.OutputTo? The resulting file is not used in this code.

 

[mike60smart]

Doc, Mike states report opens fine so OutputTo must run just fine.

Mike, saved PDF will be an unfiltered version of report because you run OutputTo before opening filtered report. Is that what you want?

Hi June
OK Thanks - changed the Code as follows.

Only thing to fix now is add the actual QuoteNo to the saved File

The saved file path now looks like the attached. How would I modify the code to include the Quote number?

Private Sub cmdPreview_Click()

10        On Error GoTo cmdPreview_Click_Error

20    If Me.Dirty = True Then Me.Dirty = False
30    MsgBox "A copy of the letter will be saved to the C Drive Quotes Folder", vbInformation
      Dim strDocname As String
      Dim strWhere As String
      Dim strToWhom As String
      Dim strMsgBody As String
      Dim strSubject As String
      Dim strDir As String

40    strDocname = "rptCustomerQuote"
50    strWhere = "[OrderNo]='" & Me.OrderNo & "'"
60    strSubject = "Quotes"
70    strToWhom = Me.Email
80    strMsgBody = "Find attached latest Quote Details"
90    strDir = "C:\Quotes\"

100   DoCmd.SendObject acSendReport, "rptCustomerQuote", acFormatPDF, strToWhom, , , strSubject, strMsgBody, True
110   DoCmd.OutputTo acOutputReport, "rptCustomerQuote", acFormatPDF, strDir & "\" & Format(Date, "yyyymmdd") & ", False"
120   DoCmd.OpenReport strDocname, acPreview, , strWhere
        
130       On Error GoTo 0
140       Exit Sub

cmdPreview_Click_Error:

150       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPreview_Click, line " & Erl & "."

End Sub

 

[The_Doc_Man]

The attachment seems to show that line 110 has a syntax error of the "unmatched parenthesis" or "improperly matched" kind, because that ", False" makes no sense in context of the message in your attachment. Putting special characters (like commas) in what appears to be a file name is not a good idea.

 

[June7]

Where would QuoteNo come from? You show OrderNo available.

Doc, has a point. Why are you concatenating ", False" to output file name? Should that False be the AutoStart parameter?

 

[The_Doc_Man]

I think you are right, June7. The FALSE should indeed be another parameter but it has been concatenated into the name string. Since it would APPEAR to be intended for the AutoStart parameter, but the default for that is false, it is a nothing-burger for the parameter... but not for the filename that contains what appears to be an illegal construct.

Characters to Avoid in Filenames and Directories

Your web files will be viewed by numerous users who use a wide variety of operating systems and devices. It is essential to avoid common illegal directory and filename characters.

www.mtu.edu

Commas are not forbidden, but there is a space following the command and spaces ARE considered improper in some cases. (You have to "escape" the illegal characters to use them.)

 

[mike60smart]

I think you are right, June7. The FALSE should indeed be another parameter but it has been concatenated into the name string. Since it would APPEAR to be intended for the AutoStart parameter, but the default for that is false, it is a nothing-burger for the parameter... but not for the filename that contains what appears to be an illegal construct.

Characters to Avoid in Filenames and Directories

Your web files will be viewed by numerous users who use a wide variety of operating systems and devices. It is essential to avoid common illegal directory and filename characters.

www.mtu.edu

Commas are not forbidden, but there is a space following the command and spaces ARE considered improper in some cases. (You have to "escape" the illegal characters to use them.)

Hi Everyone
Apologies for delay in getting back to you on this one.

I have been playing around with this problem for a few hours with no resolution.

I modified the code as shown and now when run:-
1. it Displays an Email ready to be sent.
2. Click Send to dispatch the Email.
3. Report is displayed as expected.
4. Error displays regarding Line 110
5. The Immediate window displays the correct Path from the debug.

Can someone point out where exactly I am going wrong with this one?
Any help is appreciated.

Private Sub cmdPreview_Click()

10        On Error GoTo cmdPreview_Click_Error

20        If Me.Dirty Then Me.Dirty = False ' force a save
          Dim strPath As String
30        strPath = "C:\Quotes\" & Format(Date, "mmddyyyy") & "- Order No -" & [OrderNo] & ".pdf"

          Dim strDocName As String
          Dim strWhere As String
          Dim strSubject As String
          Dim strBody As String

40        strDocName = "rptCustomerQuote"
50        strWhere = "[OrderNo]=" & Me.OrderNo
60        strSubject = "Quote Attached"

70        strBody = "Find attached the latest Quote"
          
80        DoCmd.SendObject _
              ObjectType:=acSendReport, _
              ObjectName:="rptCustomerQuote", _
              OutputFormat:=acFormatPDF, _
              To:=Me.Email, _
              Subject:=strSubject, _
              MessageText:=strBody, _
              EditMessage:=True

90        Debug.Print strPath
100        DoCmd.OpenReport "rptCustomerQuote", acViewPreview, , "[OrderNo]='" & Me!OrderNo & "'"
110       DoCmd.OutputTo acOutputReport, "rptCustomerQuote", acFormatPDF, "C:\Quotes\" & Format(Date, "mmddyyyy") & "- Order No -" & [OrderNo] & ".pdf"




        
120       On Error GoTo 0
130       Exit Sub

cmdPreview_Click_Error:

140       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPreview_Click, line " & Erl & "."

End Sub

Immediate Window
C:\Quotes\02262024- Order No -1/2024.pdf

 

[MajP]

Normally those errors using docmd are because that function is not available in the at the given time. With the Docmd this is often a focus thing where depending on what has the focus you get different menu choices. However, I cannot see anything there. Is there code in the report's load or open event? Could be a timing issue and may want to add DoEvents before 110.

 

[mike60smart]

Normally those errors using docmd are because that function is not available in the at the given time. With the Docmd this is often a focus thing where depending on what has the focus you get different menu choices. However, I cannot see anything there. Is there code in the report's load or open event? Could be a timing issue and may want to add DoEvents before 110.

Hi MajP

Not understanding?

My Line 110 is the last line of code so do you mean add them after 110?

 

[MajP]

I would add do events prior to 110. Not hopeful, but some action is hanging in the open report that is not allowing the the output to to function. Likely you can see this if you open the report manually and find that the Export function is greyed out. You get into a state where the function is not available. can you run code and comment out 110. Then look at the report and see if you can manually export.

As I said these are often simple focus problems. For example (not inferring this is the case) If the report has the focus it may work. If the focus goes to the opening form or navigation window then the export is not available and thus you get the Cancel error.

 

[mike60smart]

I would add do events prior to 110. Not hopeful, but some action is hanging in the open report that is not allowing the the output to to function. Likely you can see this if you open the report manually and find that the Export function is greyed out. You get into a state where the function is not available. can you run code and comment out 110. Then look at the report and see if you can manually export.

As I said these are often simple focus problems. For example (not inferring this is the case) If the report has the focus it may work. If the focus goes to the opening form or navigation window then the export is not available and thus you get the Cancel error.

Hi MajP

OK I commented out Line 110 and I am able to Export the Report manually.

 

[MajP]

I am pretty stumped unless you tried DoEvents.
You call an action and access starts doing things
You call another action immediately after

If the the first action is not complete and causes other events to take place the second thing may not be able to happen until all the events are complete.

Openreport
DoEvents
Outputto

Not too hopefull but often the solution for timing issues.

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.

DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component. In the latter case, the task can continue completely independent of your application, and the operating system takes care of multitasking and time slicing.

Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, don't use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.

 

[mike60smart]

I am pretty stumped unless you tried DoEvents.
You call an action and access starts doing things
You call another action immediately after

If the the first action is not complete and causes other events to take place the second thing may not be able to happen until all the events are complete.

Openreport
DoEvents
Outputto

Not too hopefull but often the solution for timing issues.

Hi MajP

Rearranged the Events as suggested and still the same problem.
Many thanks for looking.

 

[mike60smart]

Hi Everyone

I Have posted the question on Utter Access to see if anyone has an answer

 

[MajP]

Rearranged the Events as suggested and still the same problem

To verify you added the line of code? You stated rearranged, which is not what I suggested.
DoEvents

DoCmd.OpenReport "rptCustomerQuote", acViewPreview, , "[OrderNo]='" & Me!OrderNo & "'"
DoEvents
 DoCmd.OutputTo acOutputReport, "rptCustomerQuote", acFormatPDF, "C:\Quotes\" & Format(Date, "mmddyyyy") & "- Order No -" & [OrderNo] & ".pdf"

 

[Gasman]

Completely different filename out over at UA?

UtterAccess.com

www.utteraccess.com

FWIW OrderNo contains a slash which is NOT allowed in a filename. :-(

From post #11
C:\Quotes\02262024- Order No -1/2024.pdf