Solved

ACCESS 97 send report to pdf in an email

Posted on 2015-01-30
7
556 Views
Last Modified: 2016-02-11
Hi Experts,
we are still using MS ACCESS 97 . I just created a report in it that I would like to be able to be sent by email as a pdf . I know I can generate the report and then click print to PDF and then attach it to an email. but I would like with a click of a button from a form to be able to accomplish this task .
If it's not possible , I wouldn't mind to have a button that will do the print to pdf and naming on it's own the file that will be created and save it to a defined location.
I am trying to make this task easier for my user.

Thank you in advance.
David
0
Comment
Question by:taverny
7 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
I know I can generate the report and then click print to PDF and then attach it to an email.
Are you sure?
As I remember, output to PDF was not an Option in Access 97?.

In any event the standard way of doing this was to use a utility like this:
http://www.lebans.com/reporttopdf.htm
...then create an email in code, ...and send the PDF as an attachment.
A sample of such code would be something like this:
Dim strReportName       As String
Dim blRet               As Boolean
Dim objOutlook          As Outlook.Application
Dim objOutlookMsg       As Outlook.MailItem
Dim objOutlookRecip     As Outlook.Recipient
Dim objOutlookAttach    As Outlook.Attachment

'Set the Report name variable
strReportName = "rptDetailVideos"

    'Open the report Hidden
    DoCmd.OpenReport strReportName, acViewPreview, , , acHidden
    
    'This is the call to the Stephen Lebans code
    ' that creates the PDF.
    blRet = ConvertReportToPDF(strReportName, vbNullString, "C:\YourFolder\PDF\" & strReportName & ".pdf", False, False, 150, "", "", 0, 0, 0)
    
    'Close the report once the PDF is created
    DoCmd.Close acReport, strReportName

'DoCmd.OpenReport "ReportName", acViewNormal
' 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. Substitute
        ' your names here.
        Set objOutlookRecip = .Recipients.Add("SomeEmail@aaa.com")   '<--Recipient's name or email address
        'objOutlookRecip.Type = olTo
        ' Set the Subject, Body, and Importance of the message.
        .Subject = "This is an Automation test with Microsoft Outlook"
        .Body = "This is a Test Email"              '<--Email Body text.
        'Add attachments to the message.
        Set objOutlookAttach = .Attachments.Add("C:\YourFolder\PDF\" & strReportName & ".pdf")

        ' Resolve each Recipient's name.
'        For Each objOutlookRecip In .Recipients
'            If Not objOutlookRecip.Resolve Then
'                objOutlookMsg.Display
'            End If
'        Next
        
        'Send email without viewing it.
        '.Send
        
        'Dispay email before sending.
        .Display
    
    End With

'Cleanup Code
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing

Open in new window


You will have to add a reference to the Outlook Object library in the VBA editor, ...and read the code comments for more info.
This code was modified form this popular link:
http://support.microsoft.com/kb/161088

Hope this helps

JeffCoachman
0
 

Author Comment

by:taverny
Comment Utility
Jeff,
Thank you for your prompt response.
you are right there is no easy way to output to PDF. the only way to do it is to have PDF writer installed on the machine and then when the report is displayed on the screen I can print it to the PDF driver printer, then this way I have the PDF file.

I will take a look tomorrow at your code and link to see if I can figure it out.
Thank you again,
David
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
The stuff from Lebans can be fully automated and is what you want.

One other point though and the main reason for my comment;   I'd get off A97 fairly soon.   Support for JET 3.x has been dropped in all current products.  So within a few years, you're going to find it difficult to find anything that will be able to read it.  To get data out of it would require using A97 itself, dumping to something like a CSV, then importing to another format.

 A97 was a solid release and product, but your dealing with a version of the database engine that is now 18 years old.

  JET 4.0/ACE is still in widespread use and will be for some time.   So consider upgrading to A2003 or A2010.

Jim.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 10

Expert Comment

by:LukeChung-FMS
Comment Utility
If you upgrade to Access 2007, you get PDF output for "free". One of the great things about building applications on a platform like Access is that over time, when Microsoft enhances the underlying product, your solutions can take advantage of them at little to no cost. It seems like a wasted opportunity to not take advantage of that, since solutions on other platforms are much more difficult to enhance that way.

Here's a paper I wrote about what you can get by going to Access 2007 or later: Top 13 Features of Microsoft Access That Aren't Available in Access 2003 or Earlier

If you are distributing it to other users, you can use the free runtime version of Access to do so: http://www.fmsinc.com/MicrosoftAccess/runtime/index.htm
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
taverny,

You may have to tweak the code a bit...
But I am sure you can get it working.

Give it a good try on your own, ...post back if you have any questions...

Jeff
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 500 points for Jeffrey Coachman's comment #a40581001

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now