Save Report as PDF and send as attachment through Outlook - Compile Error

Good Morning,

I would like to use the following code that I found here on Experts Exchange.  I have altered some for my needs.  I get a compile error if I attempt to use this as an On Click Event.  I have also copied and pasted this in a module to use it as a Private Function.  I am not familiar with functions and how to call them or if you can even call them from an on click event.  The compile error occurs at the "DIM appoutlook as new outlook application.  I am using Access 2013.  I appreciate any help with this.  

Private Function SendPDFReport()
'Created by Helen Feddema 17-Jan-2010
'Last modified 17-Jan-2010
 
On Error GoTo ErrorHandler
 
   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   Dim strFileName As String
   Dim rpt As Access.Report
   Dim strReport As String
   Dim strCurrentPath As String
   Dim strFileNameAndPath As String
   
   strCurrentPath = "C:\users\13618\desktop\pdfs & " \ ""
   strReport = "R_UnitCheckByEmp"
   strFileName = Me.TxtFileName.pdf
   strFileNameAndPath = strCurrentPath & strFileName
   
   'Output report to PDF in current path
   DoCmd.OutputTo objecttype:=acOutputReport, _
      objectname:=strReport, _
      outputformat:=acFormatPDF, _
      outputfile:=strFileNameAndPath, _
      autostart:=False
   
   'Create new mail message and attach text file to it
   Set itm = appOutlook.CreateItem(olMailItem)
   With itm
      .To = "someone@xyz.com"
      .Subject = "Daily report"
      .Body = "Your message"
      .Attachments.Add strFileNameAndPath
      'To edit before sending
      .Display
      'To send automatically
      '.Send
   End With
   
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit
 
End Function
Lisa NortonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Lisa

What's the error message?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< I am using Access 2013. >>

 You need to set a reference to Outlook. With the code displayed, click on Tools on the menu bar and then references.

 Scroll down Until you see "Microsoft Outlook 15.0 Object Library"

 Check the box and then click "OK".

 Then click "Debug" and "Compile".   Your error should be gone.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I have also copied and pasted this in a module to use it as a Private Function.

 You should modify this a bit so you can reuse it:

Public Function SendPDFReport(strReceipent as string, strReport as string, strCurrentPath  as string, strFileName  as string ) as Boolean
 
On Error GoTo ErrorHandler
 
   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   Dim rpt As Access.Report

   Dim strFileNameAndPath As String
    
   strFileNameAndPath = strCurrentPath & strFileName
    
   'Output report to PDF in current path
   DoCmd.OutputTo objecttype:=acOutputReport, _
      objectname:=strReport, _
      outputformat:=acFormatPDF, _
      outputfile:=strFileNameAndPath, _
      autostart:=False
    
   'Create new mail message and attach text file to it
   Set itm = appOutlook.CreateItem(olMailItem)
   With itm
      .To = strReceipent
      .Subject = "Daily report"
      .Body = "Your message"
      .Attachments.Add strFileNameAndPath
      'To edit before sending
      .Display
      'To send automatically
      '.Send
   End With
    
ErrorHandlerExit:
   Exit Sub
 
ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit
 
End Function

Open in new window


  This code should be placed in a Standard module, not in the forms code module.

 Now for the OnClick of your button, change it to "[Event Procedure]" from the drop down, then click the Builder button off to the right (three dots ...).

 You'll get the code window up.  In there, put:


  Dim bolReturn as boolean


 bolReturn =  SendPDFReport("j@aol.com", "myReportName","C:\Temp\", "myReport.PDF")

  replacing the values above with what you actually want to use.   Note that you can make a reference to a control on the form, so you can allow someone to type the value.   That might look like this:

bolReturn =  SendPDFReport(Me.txtemailAddress, "myReportName","C:\Temp\", "myReport.PDF")

  txtemailAddress being a text control on the form.

 I'll stop here and let you ask any questions.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lisa NortonAuthor Commented:
Setting the outlook reference corrected the compile error.  I am still getting an error on the "send" line.  I seems that has a problem with sending multiple emails.  I will close this question and open a new one regarding that error.  Thank you Jim so much for your help and suggestions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Exchange

From novice to tech pro — start learning today.