Send or save report as .pdf (1 Viewer)

[oxicottin]

Hello, I have a right click code in my reports and am having trouble with errors and getting it to work right. First error is:

2487 The Object Type argument for the action or method is blank or invalid.

Now, I get this error when I right click in report and select:

'Adds the Save As .PDF command.
    Set CBB = CB.Controls.Add(msoControlButton, 12499, , , True)
    CBB.Caption = "Save As PDF..."
    CBB.Tag = "Save As PDF..."
    CBB.OnAction = "=SaveOpenReportAsPDF()"  'Calls a module

Then debug takes me to:
DoCmd.OutputTo acReport, "", acFormatPDF, myReportOutput, , , , acExportQualityPrint

What's weird is I only get this when I open my database on the user end and I select the report and it gives me the error BUT if I shift open and do the same process it saves the report as .pdf and works correctly.

Second thing that's wrong. If I open the db on the user end and right click on a report and select send as attachment then.

'Adds the Email As .PDF command.
    Set CBB = CB.Controls.Add(msoControlButton, 2188, , , True)
    CBB.Caption = "Send E-mail..."
    CBB.Tag = "Send E-mail..."
    CBB.OnAction = "=EmailAsPDF()"  'Calls a module

Then it opens outlook and im able to send the email BUT if I decide not to send and close the email then it throws the error and I have to ctl + del out.

The SendObject action was canceled

BUT like above if I shift open and do the same process it attaches to the report as .pdf and if I close it it doesn't throw that SendObject error.

Here is my module:

Public Function EmailAsPDF()
'==================================================================================================
'//Code works with right click for my reports
'==================================================================================================

    Dim strSubject As String
    Dim strMessageText As String
    Dim strAttachmentName As String
    Dim strExistingReportName As String
    Dim strDepartment As String
    
    Dim rptCur As Access.Report
    Set rptCur = Screen.ActiveReport
    
    strExistingReportName = rptCur.Name
    strAttachmentName = Reports(strExistingReportName).Controls("txtVWInum") & " - " & Reports(strExistingReportName).Controls("txtTitle")
    'Debug.Print strAttachmentName
    
    strSubject = Reports(strExistingReportName).Controls("txtDepartment") & " VWI-LOTO Database Report"
    strMessageText = "Please review the attached report from the Operations VWI-LOTO Database."
                
  '//By changing the report caption you effectively change the name used for the attachment in the .SendObject method
    Reports(strExistingReportName).Caption = strAttachmentName
 
  'Write the e-mail content for sending to assignee
    On Error Resume Next
    DoCmd.SendObject acReport, strExistingReportName, acFormatPDF, "", "", "", strSubject, strMessageText, True, ""
    
'//Close hidden open report (Make sure not maximized)
    DoCmd.Close acReport, strExistingReportName

End Function

Public Function SaveOpenReportAsPDF()
'==================================================================================================
'Create report and save as an attachment to the desktop
'==================================================================================================
    Dim strAttachmentName As String
    Dim strExistingReportName As String
    Dim myCurrentDir As String
    Dim myReportOutput As String
    Dim rptCur As Access.Report
    
    Set rptCur = Screen.ActiveReport
    
    strExistingReportName = rptCur.Name
    strAttachmentName = Reports(strExistingReportName).Controls("txtVWInum") & " - " & Reports(strExistingReportName).Controls("txtTitle")
    'Debug.Print strAttachmentName
 
    myCurrentDir = "C:\Documents and Settings\" & Environ("username") & "\Desktop\"
    myReportOutput = myCurrentDir & strAttachmentName & ".pdf"
    'Debug.Print myReportOutput
    
    If Dir(myReportOutput) <> "" Then    ' the file already exists--delete it first.
        VBA.SetAttr myReportOutput, vbNormal    ' Remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill myReportOutput    ' Delete the file.
    End If
    DoCmd.OutputTo acReport, "", acFormatPDF, myReportOutput, , , , acExportQualityPrint
    
    SaveOpenReportAsPDF = myReportOutput
    
End Function

 

[Gasman]

You are not supplying a report name?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.outputto

 

[oxicottin]

Yes i am supplying the report name and debug.print shows it.

strExistingReportName = rptCur.Name

 

[Ranman256]

docmd.OutputTo acOutputReport , sRptName ,acFormatPDF, sFile

 

[Gasman]

Yes i am supplying the report name and debug.print shows it.

strExistingReportName = rptCur.Name

So what do you call this? which coincidentally is the line you reported the error on.?
Do you not use Intellisense?

DoCmd.OutputTo acReport, "", acFormatPDF, myReportOutput, , , , acExportQualityPrint

 

[onur_can]

DoCmd.OutputTo acReport, "", acFormatPDF, myReportOutput, , , , acExportQualityPrint

The problem with no report name is caused by this, specify the report name and try again.

 

[oxicottin]

Ok so now the SaveOpenReportAsPDF is fixed using below as suggested.

DoCmd.OutputTo acOutputReport, strExistingReportName, acFormatPDF, myReportOutput

Now for the EmailAsPDF Function. Like explained above, everything works EXCEPT if I decide not to send the email and close it then I get:

I then click OK and it takes me back to my form, but now everything I click on it wont allow it. If I click on the X to close the DB I get this message and am forced to Ctl+Alt+Del to close the DB.

Function:

Public Function EmailAsPDF()
'==================================================================================================
'//Code works with right click for my reports
'==================================================================================================

    Dim strSubject As String
    Dim strMessageText As String
    Dim strAttachmentName As String
    Dim strExistingReportName As String
    Dim strDepartment As String
    
    Dim rptCur As Access.Report
    Set rptCur = Screen.ActiveReport
    
    strExistingReportName = rptCur.Name
    strAttachmentName = Reports(strExistingReportName).Controls("txtVWInum") & " - " & Reports(strExistingReportName).Controls("txtTitle")
    'Debug.Print strAttachmentName
    
    strSubject = Reports(strExistingReportName).Controls("txtDepartment") & " VWI-LOTO Database Report"
    strMessageText = "Please review the attached report from the Operations VWI-LOTO Database."
                
  '//By changing the report caption you effectively change the name used for the attachment in the .SendObject method
    Reports(strExistingReportName).Caption = strAttachmentName
 
  'Write the e-mail content for sending to assignee
    On Error Resume Next

    DoCmd.SendObject acReport, strExistingReportName, acFormatPDF, , , , strSubject, strMessageText, True
    
'//Close hidden open report
    DoCmd.Close acReport, strExistingReportName

End Function

 

[oxicottin]

I got it working... I save it to my desktop with new name, then I open outlook and attach it from my desktop and then I delete the .pdf on my desktop and close reports.

Public Function EmailAsPDF()
'==================================================================================================
'//Code works with right click for my reports
'
'//Reference: Microsoft Outlook 12.0 Object Library
'==================================================================================================
'//Create report and save as an attachment to the users desktop
    Dim strAttachmentName As String
    Dim strExistingReportName As String
    Dim myCurrentDir As String
    Dim myReportOutput As String
    Dim rptCur As Access.Report
    Dim SaveOpenReportAsPDF2 As String
    
    Set rptCur = Screen.ActiveReport
    
    strExistingReportName = rptCur.Name
    strAttachmentName = Reports(strExistingReportName).Controls("txtVWInum") & " - " & Reports(strExistingReportName).Controls("txtTitle")
'Debug.Print strAttachmentName
    
    myCurrentDir = "C:\Documents and Settings\" & Environ("username") & "\Desktop\"
    myReportOutput = myCurrentDir & strAttachmentName & ".pdf"
'Debug.Print myReportOutput
    
    If Dir(myReportOutput) <> "" Then    ' The file already exists delete it first.
        VBA.SetAttr myReportOutput, vbNormal    ' Remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill myReportOutput    ' Delete the file.
    End If
    DoCmd.OutputTo acOutputReport, strExistingReportName, acFormatPDF, myReportOutput
    
'//Email the saved reprot
    Dim objOutlook As Outlook.Application
    Dim objEmail As Outlook.MailItem
    Dim strSubject As String
    Dim strMessageText As String
    Dim AttachmentName As String
    
    strSubject = Reports(strExistingReportName).Controls("txtDepartment") & " VWI-LOTO Database Report"
    strMessageText = "Please review the attached report from the Operations VWI-LOTO Database."
    
    Set objOutlook = CreateObject("Outlook.application")
    Set objEmail = objOutlook.CreateItem(olMailItem)
    With objEmail
       '.To = strgTo
        .Subject = strSubject
        .Body = strMessageText
        .Attachments.Add myReportOutput 'Attach the .pdf
        .Display
    End With
    
'//Delete the report saved on desktop
    If Dir(myReportOutput) <> "" Then    ' The .pdf file already exists--delete it
        VBA.SetAttr myReportOutput, vbNormal    ' Remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill myReportOutput  ' Delete the .pdf file.
    End If
    
'//Close the current report
      CloseAllReports
        
    End Function