Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rating: Thread Rating: 132 votes, 5.00 average. Display Modes
Old 08-15-2011, 02:49 AM   #1
HairyArse
Newly Registered User
 
Join Date: Mar 2005
Posts: 83
Thanks: 5
Thanked 1 Time in 1 Post
HairyArse is on a distinguished road
Access 2010 PDF and Email a Report Using VBA Code

Hello,

I'm using Access 2010 and found the Send to Email as PDF command indispensible. However, I have a problem in that I've hidden the main ribbon from my users which means they can't actually click that button.

Can anyone tell me the VBA code that will let me PDF and email an indiviual report based on a given unique ID. I am happy for the user to add the email address, subject and message body and for them to click send.

Thanks

HairyArse is offline   Reply With Quote
Old 09-04-2011, 09:21 PM   #2
sonof27
Newly Registered User
 
Join Date: Sep 2010
Posts: 29
Thanks: 0
Thanked 1 Time in 1 Post
sonof27 is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

Not sure if there is a quicker way to do this in 2010 yet as I'm still navigating my way around it, but here is something like what I do. (Sorry if not posted correctly, I always forget how it is meant to go in here)

Code:
 
'Behind a comand button save the report as PDF file 
DoCmd.OutputTo acOutputReport, "rptReportName", acFormatPDF, "FullPathAndFileName.pdf", False
 
'follow the output with a function call to send the PDF via outlook
strValue = Email_Via_Outlook(strAddress, "Message Title ", strMessage, True, "FullPathAndFileName.PDF")
 
 
'Put this in a module somewhere
Function Email_Via_Outlook(varAddress, varSubject, varBody, DisplayMsg As Boolean, Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
On Error GoTo errorHandler
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
    ' Add the To recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add(varAddress)
    objOutlookRecip.Type = olTo
    .Subject = varSubject 
    .body = varBody 
 
    ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) And AttachmentPath <> "" Then
        Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    End If
 
    ' Resolve each Recipient's name.
    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
    Next
    ' Should we display the message before sending?
    If DisplayMsg Then
        .Display
    Else
        .Save
        .send
    End If
End With
Set objOutlook = Nothing
Exit Function
errorHandler:
errNameFrom = "Email_Via_Outlook"
MsgBox "Error occured at " errNameFrom:" & Err.Number & ";" & Err.Description
 
End Function
You may need a reference to outlook to get this to work.

Another option could be for you to create a custom ribbon and put the button you want on that ribbon instead of completely hiding the ribbon.
sonof27 is offline   Reply With Quote
The Following User Says Thank You to sonof27 For This Useful Post:
araffay (02-09-2012)
Old 09-04-2011, 09:34 PM   #3
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Access 2010 PDF and Email a Report Using VBA Code

Quote:
Originally Posted by sonof27 View Post
You may need a reference to outlook to get this to work.
You don't need a reference if you change to LATE BINDING, which would entail changing this part:
Code:
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
to this:
Code:
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 09-05-2011, 01:04 PM   #4
sonof27
Newly Registered User
 
Join Date: Sep 2010
Posts: 29
Thanks: 0
Thanked 1 Time in 1 Post
sonof27 is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

Thank-you will give your suggestion a go the next time I use this function.
sonof27 is offline   Reply With Quote
Old 02-22-2012, 08:23 AM   #5
HairyArse
Newly Registered User
 
Join Date: Mar 2005
Posts: 83
Thanks: 5
Thanked 1 Time in 1 Post
HairyArse is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

Following on from the excellent responses to this post of mine some time ago, that all works perfectly apart from the entire report is PDFed when I need only the current record based on the primary key.

How do I modify the acOutputReport function to only PDF a single record?
HairyArse is offline   Reply With Quote
Old 02-22-2012, 04:41 PM   #6
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,828 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Access 2010 PDF and Email a Report Using VBA Code

Quote:
Originally Posted by HairyArse View Post
Following on from the excellent responses to this post of mine some time ago, that all works perfectly apart from the entire report is PDFed when I need only the current record based on the primary key.

How do I modify the acOutputReport function to only PDF a single record?
You could ensure that the report has a saved query as the recordsource and then use a QueryDef object to modify the SQL of it before sending the output.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 08-31-2012, 04:22 AM   #7
s_solt
Newly Registered User
 
Join Date: Aug 2012
Posts: 7
Thanks: 0
Thanked 3 Times in 3 Posts
s_solt is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

Why not just use the Docmd.SendObject command:



DoCmd.SendObject acSendReport, [name of report], acFormatPDF, "recipient@company.com",[cc email(s)],[bcc email(s)], [Message Subject], [MessageBody], True

Works a treat!

I am planning to add it to all my report dialogs.

Steve

s_solt is offline   Reply With Quote
The Following User Says Thank You to s_solt For This Useful Post:
DataDude (03-06-2013)
Old 09-03-2012, 07:56 PM   #8
156sting
Newly Registered User
 
Join Date: Aug 2012
Posts: 24
Thanks: 3
Thanked 1 Time in 1 Post
156sting is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

hey guys,

this thread has been really really helpful.

All i needed was to print a report to pdf and this code works perfect, and it was really easy to understand.

but i would like to know, how do i set the directory to where the pdf files save, right now it just saves to My Documents.... i want it to send the pdf files to a specific folder, is this possible ?

this is the code i have right now.

Code:
' set variables
    Dim FileNamePDF As String
    Dim TheTenNum
    Dim TenementLookup
    Dim FindTenementPre
    Dim FindTenementSuff

    TenementLookup = Me.TenNum

' separate tenement number to subtract the /
    FindTenementPre = Left(TenementLookup, InStr(TenementLookup, "/") - 1)
    FindTenementSuff = Right(TenementLookup, InStr(TenementLookup, "/") + 1)
    
'set the filename
    FileNamePDF = "Form30 - " & FindTenementPre & "_" & FindTenementSuff & ".pdf"

' print to pdf document
    DoCmd.OutputTo acOutputReport, "AppToAmendForm", acFormatPDF, FileNamePDF, False
156sting is offline   Reply With Quote
Old 09-03-2012, 09:40 PM   #9
156sting
Newly Registered User
 
Join Date: Aug 2012
Posts: 24
Thanks: 3
Thanked 1 Time in 1 Post
156sting is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

its ok i figured it out.

here is how i solved my issue... very simple really.

Code:
' set variables
    Dim FileNamePDF As String
    Dim TheTenNum
    Dim TenementLookup
    Dim FindTenementPre
    Dim FindTenementSuff
    Dim SetDirectoryPDF As String
        
' set directory to save to
    SetDirectoryPDF = "AtoADBPrints\"

    TenementLookup = Me.TenNum

' separate tenement number to subtract the /
    FindTenementPre = Left(TenementLookup, InStr(TenementLookup, "/") - 1)
    FindTenementSuff = Right(TenementLookup, InStr(TenementLookup, "/") + 1)
    
'set the filename and save location
    FileNamePDF = SetDirectoryPDF & "Form30 - " & FindTenementPre & "_" & FindTenementSuff & ".pdf"

' print to pdf document
    DoCmd.OutputTo acOutputReport, "AppToAmendForm", acFormatPDF, FileNamePDF, False
this saves to a sub-folder i created in My Documents - if someone knows how to actually save it to a specific folder outside the My Documents area, please share. I will need to know this for future development.


Sting
156sting is offline   Reply With Quote
Old 09-04-2012, 01:41 AM   #10
s_solt
Newly Registered User
 
Join Date: Aug 2012
Posts: 7
Thanks: 0
Thanked 3 Times in 3 Posts
s_solt is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

Hi Sting

You specified a folder but without an explicit drive or root, so Access assumed the path was relative to the default path, in this case My Documents.

Just specify any valid full path:

SetDirectoryPDF = "P:\Project xxx\reports\AtoADBPrints\"

etc

PS I haven't actually tried this but I'm sure it would work....

Steve
s_solt is offline   Reply With Quote
The Following User Says Thank You to s_solt For This Useful Post:
156sting (09-04-2012)
Old 09-04-2012, 07:22 PM   #11
156sting
Newly Registered User
 
Join Date: Aug 2012
Posts: 24
Thanks: 3
Thanked 1 Time in 1 Post
156sting is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

thanks a lot s_solt. i'll try this now, and let you know if it works!
156sting is offline   Reply With Quote
Old 09-04-2012, 07:24 PM   #12
156sting
Newly Registered User
 
Join Date: Aug 2012
Posts: 24
Thanks: 3
Thanked 1 Time in 1 Post
156sting is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

YEP!! it works... who would of thought it would be that easy!!!

thanks heaps!!
156sting is offline   Reply With Quote
Old 09-14-2012, 04:34 PM   #13
s_solt
Newly Registered User
 
Join Date: Aug 2012
Posts: 7
Thanks: 0
Thanked 3 Times in 3 Posts
s_solt is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

Just to finally round this out...

If you want to send the report straight to email:

Code:
On Error Resume Next
DoCmd.SendObject acSendReport, strReport, acFormatPDF, , , , , "Report Attached"
On Error GoTo 0
Why use the On Error ?

Well, the email appears ready for any editing in a modal window. If you cancel without sending, you get an Access error. As shown you don't.

Steve
s_solt is offline   Reply With Quote
Old 09-14-2012, 04:54 PM   #14
s_solt
Newly Registered User
 
Join Date: Aug 2012
Posts: 7
Thanks: 0
Thanked 3 Times in 3 Posts
s_solt is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

Finally, finally

You can use DoCmd.OutputTo instead of DoCmd.SendObject

Why not offer a combo with a list of possible formats:

Create a combobox with default value "Preview" and RowSource:
Code:
"Preview";"HTML (*.html)";"PDF Format (*.pdf)";"Rich Text Format (*.rtf)";"Microsoft Excel (*.xls)"
Then do

Code:
    If strFormat = "Preview" Then
        DoCmd.OpenReport strReport, acViewPreview
    Else
        On Error Resume Next
        DoCmd.SendObject acSendReport, strReport, strFormat, , , , , "Planchest Drawing Report Attached"
        On Error GoTo 0
    End If

Steve
s_solt is offline   Reply With Quote
Old 09-21-2012, 01:07 AM   #15
BenHauto
Newly Registered User
 
Join Date: Sep 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
BenHauto is on a distinguished road
Re: Access 2010 PDF and Email a Report Using VBA Code

does anybody know why I can 't use the SendObject while my Outlook is running?? I'm using office 2010 32bit.

Also when I try to send a mail with the Outlook.Application object, I only can use this when Outlook is closed (other I get the errcode 429 ActiveX can't create object)

BenHauto is offline   Reply With Quote
Reply

Tags
email , microsoft access 2010 , pdf , report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Access 2010 Report to Excel 2010 Starchildren3317 General 1 02-01-2012 10:14 AM
Access 2010 Error 3021 while porting code from Access 2007 :: Help Request basstard80 General 19 08-04-2011 02:03 PM
Question ms access 2010 email advice PNGBill General 5 07-17-2011 05:13 PM
Access 2010 code for lockdb CeCe Modules & VBA 0 06-30-2011 10:39 AM
Access 2010 now creates macros instead of VBA code mafhobb Macros 2 01-07-2011 04:40 AM




All times are GMT -8. The time now is 06:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World