Solved

ACCESS 97 send report to pdf in an email

Posted on 2015-01-30
7
598 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
ID: 40581001
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
ID: 40581127
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
ID: 40581388
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 10

Expert Comment

by:Luke Chung
ID: 40581873
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
ID: 40582027
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 46

Expert Comment

by:Martin Liss
ID: 40629220
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

766 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