Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Convert Access report to PDF send via OutlookHelpful Member! 

debq (TechnicalUser) (OP)
30 Oct 08 17:12
I am using the code below to convert multiple reports from Access into Word Doc and then send to various recipients via Outlook email.The code works great but the rtf format loses graphics and colors. I would like to convert to PDF instead. How can I change this code to convert to PDF and then send the PDF report as an attachment via Outlook?

CODE:
Option Compare Database
Option Explicit

Private Sub cmdConvertCombineAndEmail_Click()
On Error GoTo ERRORHANDLER
   Dim varItem As Variant
   If lstReports.ItemsSelected.Count > 0 Then
      Dim AppWord As New Word.Application
      Dim DocWrd As Word.Document
      Dim i As Integer
      Dim Progress As String
      Dim EventTitle As String
      AppWord.Visible = True
      Set DocWrd = AppWord.Documents.Add
      DocWrd.PageSetup.TopMargin = 36
      DocWrd.PageSetup.BottomMargin = 36
      DocWrd.PageSetup.LeftMargin = 36
      DocWrd.PageSetup.RightMargin = 18
      i = 0
      
      EventTitle = "Mystery Caller Report"
      
      For Each varItem In lstReports.ItemsSelected
         i = i + 1
         Progress = "Processing... " & lstReports.ItemData(varItem)
         DoCmd.OutputTo acOutputReport, lstReports.Column(0, varItem), acFormatRTF, "c:\temp\" & lstReports.ItemData(varItem) & ".rtf", False
         AppWord.Selection.InsertFile "c:\temp\" & lstReports.ItemData(varItem) & ".rtf", "", False, False, False
         If i < lstReports.ItemsSelected.Count Then
            AppWord.Selection.InsertBreak wdSectionBreakNextPage
         End If
      Next
      Progress = "Generating Email"
      DocWrd.SaveAs "c:\temp\" & EventTitle & ".doc", wdFormatDocument
      DocWrd.BuiltInDocumentProperties("Title").Value = "Set the title of  the combined reports - " & Date
      DocWrd.Save
      AppWord.Activate
      AppWord.Options.SendMailAttach = True
      DocWrd.SendMail
      DocWrd.Close
      Set DocWrd = Nothing
      'Word Instance stays open??
      'AppWord.Quit
      Set AppWord = Nothing
   End If

   Progress = ""
   Exit Sub

ERRORHANDLER:
    Progress = ""
    If MsgBox("Do you want to start over?", vbCritical + vbYesNo) = vbYes Then
        Err.Clear
        DocWrd.Close wdDoNotSaveChanges
        AppWord.Quit
        Exit Sub
    Else
        Err.Clear
        Resume
    End If

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim lngRow As Long

    If lstReports.MultiSelect Then
        For lngRow = 0 To lstReports.ListCount - 1
            lstReports.Selected(lngRow) = True
        Next
    End If

End Sub
 
Helpful Member!  Remou (TechnicalUser)
30 Oct 08 18:10
Office 2007 allows for this. I have found PDFCreator (http://sourceforge.net/projects/pdfcreator/) to be good, the VBA is included in sample files, and have heard that CutePDF (http://www.cutepdf.com/) is also good. PDFCreator and CutePDF are free.
 
debq (TechnicalUser) (OP)
1 Nov 08 22:56
Thank You Thank You Thank you. This works and has saved me in a big way. I do not have MS 2003 at work and cannot install outside programs without permission from IT. BUT I do have my personal Laptop with MS 2007 installed. I added the Cute pdf and downloaded the pdf creator. This will work for me. I am still learning VBA so this is a great help.

Thanks again
Remou (TechnicalUser)
2 Nov 08 7:30
I phrased that badly. Access 2007 has its own built-in PDF routines. However, you may still find CutePDF useful.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close