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\pd
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:=strFileNameAnd
autostart:=False
'Create new mail message and attach text file to it
Set itm = appOutlook.CreateItem(olMa
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
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\pd
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:=strFileNameAnd
autostart:=False
'Create new mail message and attach text file to it
Set itm = appOutlook.CreateItem(olMa
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<< 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.
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.
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
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",
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.txtemailA
txtemailAddress being a text control on the form.
I'll stop here and let you ask any questions.
Jim.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial