Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!
  • Students Click Here

*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.

Students Click Here


Convert Access report to PDF send via Outlook

Convert Access report to PDF send via Outlook

Convert Access report to PDF send via Outlook

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?

Option Compare Database
Option Explicit

Private Sub cmdConvertCombineAndEmail_Click()
   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
      Progress = "Generating Email"
      DocWrd.SaveAs "c:\temp\" & EventTitle & ".doc", wdFormatDocument
      DocWrd.BuiltInDocumentProperties("Title").Value = "Set the title of  the combined reports - " & Date
      AppWord.Options.SendMailAttach = True
      Set DocWrd = Nothing
      'Word Instance stays open??
      Set AppWord = Nothing
   End If

   Progress = ""
   Exit Sub

    Progress = ""
    If MsgBox("Do you want to start over?", vbCritical + vbYesNo) = vbYes Then
        DocWrd.Close wdDoNotSaveChanges
        Exit Sub
    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
    End If

End Sub

RE: Convert Access report to PDF send via Outlook

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

RE: Convert Access report to PDF send via Outlook

I phrased that badly. Access 2007 has its own built-in PDF routines. However, you may still find CutePDF useful.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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