Solved

ACCESS 97 send report to pdf in an email

Posted on 2015-01-30
7
632 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 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 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 47

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

724 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