?
Solved

ACCESS 97 send report to pdf in an email

Posted on 2015-01-30
7
Medium Priority
?
651 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

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…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

752 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