Rename PDF to send to email (1 Viewer)

Ozzboss

New member
Local time
Today, 23:47
Joined
Mar 11, 2022
Messages
19
So much help with the last question so I'll try my luck again lol.

I have created a button on my form and used the following code to save a quote as a PDF and name it "[QuoteNumber].PDF" (6001.PDF)
Code:
Private Sub SaveTtoPDF_Click()
Dim strFileName As String

strFileName = Me.[QuoteNumber]
DoCmd.OutputTo acOutputReport, "QuotePrintout", acFormatPDF, "C:\Users\roomeatsmash\Desktop\" & FileName & ".PDF"
End Sub

Now I am trying to create a button to email a PDF to the client. Everything is auto-completed and works just fine EXCEPT for the name of the PDF.
It currently attaches to the email as

Code:
Private Sub PDFAndEmail_Click()

    Dim strTo As String
    Dim strSubject As String
    Dim strMessageText As String
    Dim strFileName As String

    
    Me.Dirty = False
    strFileName = Me.[QuoteNumber]
    
    strTo = Me.ClientEmail
    strSubject = "Southside Bitumen Driveways Quotation Number " & Me.QuoteNumber
    strMessageText = Me.ClientName & ":" & _
        vbNewLine & vbNewLine & _
        "Please find attached quotation for your approval." & _
        vbNewLine & vbNewLine & _
        "If you have any questions please feel free to contact me by return email or by calling 0404 040 404." & _
        vbNewLine & vbNewLine & _
        "If everything is correct and you wish to proceed, please complete the bottom of BOTH pages and return to us by email" & _
        vbNewLine & vbNewLine & _
        vbNewLine & vbNewLine & _
        "Thanking You" & _
        vbNewLine & vbNewLine & _
        "Troy Rankine" & _
        vbNewLine & vbNewLine & _
        "Southside Bitumen Driveways"


    DoCmd.SendObject ObjectType:=acSendReport, _
        ObjectName:="QuotePrintout", _
        OutputFormat:=acFormatPDF, _
        To:=strTo, _
        Subject:=strSubject, _
        MESSAGETEXT:=strMessageText, _
        EditMessage:=True

End Sub

I have added the Dim and string for the FileName but I can't figure out how to add it to the correct line (OutputFormat:=acFormatPDF, _)
:unsure:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:47
Joined
Oct 29, 2018
Messages
18,972
The way you would do it with SendObject is by opening the report first and assign the filename in the report's Caption property. Don't forget to close the report after sending the email.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:47
Joined
May 7, 2009
Messages
16,394
you can also try to Copy the report to new name (copy and paste to new module):
Code:
Public Function fncCopyObject( _
                ByVal objType As AcObjectType, _
                ByVal ObjName As String, _
                ByVal newObjName As String) As Boolean
    fncCopyObject = True
    On Error Resume Next
    'delete if already exists
    DoCmd.DeleteObject objType, newObjName
    Application.RefreshDatabaseWindow
    On Error GoTo error_line
    DoCmd.CopyObject , newObjName, objType, ObjName
    Application.RefreshDatabaseWindow
exit_function:
    Exit Function
error_line:
    fncCopyObject = False
    MsgBox Err.Number & ": " & Err.Description
    Resume exit_function
End Function

then you call the function from your button:
Code:
Private Sub PDFAndEmail_Click()
    Dim strTo As String
    Dim strSubject As String
    Dim strMessageText As String
    Dim strFileName As String
    
    Me.Dirty = False
    strFileName = Me.[QuoteNumber]
    
    
    'arnelgp
    If Not fncCopyObject(acReport, "QuotePrintout", strFileName) Then
        Exit Sub
    End If
    
    strTo = Me.ClientEmail
    strSubject = "Southside Bitumen Driveways Quotation Number " & Me.QuoteNumber
    strMessageText = Me.ClientName & ":" & _
        vbNewLine & vbNewLine & _
        "Please find attached quotation for your approval." & _
        vbNewLine & vbNewLine & _
        "If you have any questions please feel free to contact me by return email or by calling 0404 040 404." & _
        vbNewLine & vbNewLine & _
        "If everything is correct and you wish to proceed, please complete the bottom of BOTH pages and return to us by email" & _
        vbNewLine & vbNewLine & _
        vbNewLine & vbNewLine & _
        "Thanking You" & _
        vbNewLine & vbNewLine & _
        "Troy Rankine" & _
        vbNewLine & vbNewLine & _
        "Southside Bitumen Driveways"


    DoCmd.SendObject ObjectType:=acSendReport, _
        ObjectName:=strFileName, _
        OutputFormat:=acFormatPDF, _
        To:=strTo, _
        Subject:=strSubject, _
        MESSAGETEXT:=strMessageText, _
        EditMessage:=True

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:47
Joined
Sep 21, 2011
Messages
10,544
That is how I created reports from a master report when I only had 2003 and no output to pdf option
Code:
    stRptName = "Main_by_Ship"
    
    stParam = Replace(LCase(Me.cboShip.Value), " ", "_")
    stLinkCriteria = "[Ship] = '" & Me.cboShip.Value & "'"
    
    'DoCmd.CopyObject , stParam, acReport, stRptName
 

Users who are viewing this thread

Top Bottom