Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save report to PDF (2003)

    Hi,

    I have Adobe Acrobat 8.0 Professional installed in my computer. I have use the following codes to convert report to PDF:

    Dim strDefaultPrinter As String

    'Get the default printer being used
    strDefaultPrinter = Application.Printer.DeviceName

    ' Switch the Default Printer to print to Adobe
    Set Application.Printer = Application.Printers("Adobe PDF")

    'Create the PDF File / Print to PDF
    DoCmd.OpenReport "R_EmployeeByDept"

    'Reset the printer to the original default printer
    Set Application.Printer = Application.Printers(strDefaultPrinter)

    Now I would like to have the following improvement:

    1) Auto save the PDF on a network drive
    2) Rename the PDF title as same as the source data modification date & time
    3) Open outlook email and attach the pdf with default to, topic and body

    Thank you so much if anyone can provide some codes that give to me what I needs.

    Regards,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save report to PDF (2003)

    You already know most of what you need - see the threads starting at <post:=560,068>post 560,068</post:> and <post:=563,512>post 563,512</post:>.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save report to PDF (2003)

    Thanks, Hans.

    Finally I decide to use stephen lebans ReportToPDF codes. By using the reference from your post (638,268), I don't have any problems to convert report to PDF, save it to local disk and open an email with PDF as attachment.

    However, I would like the PDF title (When the PDF is saved on local disk) is "Report-" plus the time of last time modify the text file that link in my database. By review your post 699219, I don't know how can I combined with ConvertReportToPDF codes together. Below are the current codes in the click event of "Email" button:

    Dim strFile As String

    Dim dtmModified As Date

    If ConvertReportToPDF(RptName:="rpt_Report", OutputPDFname:="rpt_Report", StartPDFViewer:=False) = True Then
    SendMail "test@test.com", "My Report", "Hello World", "C:Testrpt_Report.pdf", ShowMail:=True
    End If

    Thanks in advance.

    Regards

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Save report to PDF (2003)

    <post#=669219>post 669219</post#> showed you how to get the date and time as a string.

    So if you add to that
    dim strPDFFile as string
    strPDFFile ="Report:" & Format(dtmModified, "mm/dd/yyyy")

    ConvertReportToPDF(RptName:="rpt_Report", OutputPDFname:=strPDFFile, etc

    If you want to nominate a saving location add that at the start of strPDFFile
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save report to PDF (2003)

    Thanks, but where should I put the saving location. For example, I would like to save it at C:Test?

    Regards

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Save report to PDF (2003)

    You can save it anywhere you like, but if you want it to be c:test then use

    strPDFFile ="c:test'Report:" & Format(dtmModified, "mmddyyyy") & ".pdf"

    * I just noticed that the format had / in it. You cannot use / or in filenames, so I have removed them.
    Regards
    John



  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save report to PDF (2003)

    I tried the codes above, but I don't find any pdf file in teh c:test folder. Then I think it's better to do step by step. I just want to convert report to pdf first, so I put the following codes:

    Dim strFile As String
    Dim strPDFFile As String
    Dim dtmModified As Date

    strFile = "K:File" ' substitute the path and filename
    dtmModified = CreateObject("Scripting.FileSystemObject").GetFile (strFile).DateLastModified

    strPDFFile = "C:Test'Report:" & Format(dtmModified, "mmddyyyy") & ".pdf"

    ConvertReportToPDF(RptName:="rpt_Report", OutputPDFname:=strPDFFile, StartPDFViewer:=False) = True

    I get Compile Error: Function call on left-hand side of assignment must return Variant or Object.

    Please advise.

    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save report to PDF (2003)

    You cannot use

    ConvertReportToPDF(RptName:="rpt_Report", OutputPDFname:=strPDFFile, StartPDFViewer:=False) = True

    Either use

    ConvertReportToPDF RptName:="rpt_Report", OutputPDFname:=strPDFFile, StartPDFViewer:=False

    or

    If ConvertReportToPDF(RptName:="rpt_Report", OutputPDFname:=strPDFFile, StartPDFViewer:=False) = True Then
    ...
    End If

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Save report to PDF (2003)

    Also you have

    strPDFFile = "C:Test'Report:" & Format(dtmModified, "mmddyyyy") & ".pdf"

    instead of

    strPDFFile = "C:TestReport:" & Format(dtmModified, "mmddyyyy") & ".pdf"

    There was an Apostrophe ' at the end of Test
    Regards
    John



  10. #10
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save report to PDF (2003)

    Thank you so much, Hans.

    Now I can see the PDF file with correct name in the folder. Now I would like to have the email topic as same as the PDF file name, so what codes should I change in below:

    SendMail "test@test.com", "My Report", "Hello World", strPDFFile, ShowMail:=True

    I tried use strPDFFile, but it doesn't work .

    Thanks again.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save report to PDF (2003)

    You should replace "My Report" with strPDFFile.

  12. #12
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save report to PDF (2003)

    Thank you all of your help. Now everthing is working very well.

    However, an outlook message pops up before the email opens: A program is trying to access e-mail
    adresses you have stored in Outlook. Do you want to allow this." And then i can allow access for max 10 minutes.


    Is anyway to disable this message? I am using Outlook 2003.

    Thanks again.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save report to PDF (2003)

    See <post:=488,173>post 488,173</post:>.

  14. #14
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save report to PDF (2003)

    Thanks, Hans.

    I hope this is the last question for you on this topic: If I need to cc the email to more than 3 people, where should I add the codes in?

    Thanks again.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save report to PDF (2003)

    The SendMail function from <post:=324,559>post 324,559</post:> (I presume that's the one you're using) doesn't let you supply CC or BCC addresses. You'll have to modify the code for that.

    Study the SendMail function to see how it splits the recipient string into parts and adds each part to the Recipients collection of the mail message.
    You can add addresses to the CC or BCC box the same way, but you have to set the Type of the recipient to olCC or olBCC after creating it. Look up Recipients in the VBA help.

Page 1 of 3 123 LastLast

Posting Permissions

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