Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44

    Unanswered: Custom File Name when creating a pdf for email

    I am using Access 2007. I have a button on a form, lets call it btnEmail, that will take the current record, send it to the report (rptInspection) create a .pdf, send it to Outlook as an attachment, and populate the email addresses, subject, and body of the email. What I would like to happen is for the file it attaches to the email pull a field (txtRCnumber) and append it to the file name of the attachment. The code i am using follows:

    Private Sub btnEmail_Click()
    Dim stReport As String
    Dim stWhere As String
    Dim stEmailadd As String
    Dim stSubject As String
    Dim stEmailMessage As String

    stEmailMessage = "Please see the attached Inspection Reports(PDF) for " & Me.RCnumber '
    stSubject = "Inspection for " & Me.RCnumber '
    stReport = "rptInspection"
    stWhere = "ISP_ID = " & Me.ISP_ID "'"
    stEmailadd = "person1@email.com;person2@email.com;person3@email .com"
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""
    DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", stEmailadd, , , stSubject, stEmailMessage, True, ""


    The code works great for everything that we want it to do except the file name thing. What I cannot figure out is how to modify the the file name using the DoCmd.SendObject method. Any help or suggestions would be greatly appreciated.

    Dan

  2. #2
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    I figured it out. I used the DoCmd.SetProperty after the report was opened. The code now looks like this:

    Code:
    Private Sub btnEmail_Click()
    Dim stReport As String
    Dim stWhere As String
    Dim stEmailadd As String
    Dim stSubject As String
    Dim stEmailMessage As String
    
    stEmailMessage = "Please see the attached Inspection Reports(PDF) for " & Me.RCnumber '
    stSubject = "Inspection for " & Me.RCnumber '
    stReport = "rptInspection"
    stWhere = "ISP_ID = " & Me.ISP_ID "'"
    stEmailadd = "person1@email.com;person2@email.com;person3@email .com"
    DoCmd.OpenReport stReport, acViewPreview, "", stWhere, acWindowNormal, ""
    DoCmd.SetProperty stReport, acPropertyCaption, "Inspection for " & Me.RCnumber '
    DoCmd.SendObject acSendReport, stReport, "PDFFormat(*.pdf)", stEmailadd, , , stSubject, stEmailMessage, True, ""
    I got the idea from a post at UtterAccess

    Apparently the file name is derived from the caption property of the report.

    Thanks,

    Dan

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I created this neat little exporting routine: http://www.dbforums.com/6390529-post84.html if you're interested (you can ignore the excel type form which opens with the data), just click the Export Data button at the top to see how it works when the excel type form opens.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    Thanks pkstormy, I will check it out

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There's also some pdf creation code in the code bank as well as emailing code. There's several different examples throughout the code bank so I won't list all the links here. Hopefully one of them will be helpful. If not, post back. PDF creation in vba is a challenge in itself and with the new Outlook security on attachments (of any kind), it makes it even more challenging. I personally try to avoid automatic pdf creation via vba code.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •