Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ACCESS 97 send report to pdf in an email

Posted on 2015-01-30
7
Medium Priority
?
676 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 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 58
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 49

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

636 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