Solved

Multiple access reports to single pdf, then email

Posted on 2004-09-17
28
689 Views
Last Modified: 2012-06-27
Hi!
I am trying to create a single pdf file from 2 access reports and then email it to the client.  I can do this manually, however, I need to automate it.  Can anyone help me with this code?  

The manual procedure is:
I Run this code from within access:

Private Sub PrintReports_Click()
On Error GoTo Err_PrintReports_Click

    Dim stDocName As String

    'Print Option Worksheet to pdf
    stDocName = "OptionWorksheetNew"
    'DoCmd.OpenReport stDocName, acPrint, , "[Customer ID] = " & Me![Customer ID]
    'Print Cover Page to pdf
    stDocName = "ReportCoverPage"
    DoCmd.OpenReport stDocName, acPrint, , "[Customer ID] = " & Me![Customer ID]
    'Print Option Letters to pdf
    stDocName = "OptionLettersNew"
    DoCmd.OpenReport stDocName, acPrint, , "[Customer ID] = " & Me![Customer ID]

Exit_PrintReports_Click:
    Exit Sub

Err_PrintReports_Click:
    MsgBox Err.Description
    Resume Exit_PrintReports_Click
   
End Sub

THEN, from Acrobat 6.0 standard, I click on [Create pdf - from multiple files], hit OK to execute then I click on the email button, type in the client's address and insert a [signature] which fills in the email text protion.  I hit send and am done.


I've searched most of the experts former advice and there are lots of questions out there, unfortunately, most of the links to the answers are no longer active.  I would really appreciate your help.  I'm on a big deadline.
Thanks....Debbie
0
Comment
Question by:Debbie12
  • 14
  • 12
  • +1
28 Comments
 
LVL 13

Expert Comment

by:lucas911
ID: 12086531
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12086535
Easiest way would be in your first report, in the report footer add a page break and a subreport.  Link the subreport to be your second report.  That way, in code you only have to DoCmd.OpenReport the first report.

Hope this helps.
-Jim
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 12086560
You can also use certain programs to combine PDF files, such as PDF Meld:

http://www.fytek.com/products.php?pg=pdfmeld

This can be easily called from within your program.
0
 

Author Comment

by:Debbie12
ID: 12087059
The manual procedure works so well, I hate to start over with a new method.  
Is there a way to find out the code to simply automate the procedure.  I have the libraries loades, but don't know how to find out how to use them.
Deb
0
 

Author Comment

by:Debbie12
ID: 12096190
Wow, Thanks shanesuebsahakam, pdfmeld looks like it can do it all.  Can you tell me how I would go about calling it from within my program.  My program code is listed above and I would like to use the pdfmail code to send an email (setMail).
What would I add to my above code??
Thanks...Debbie
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12096203
There's actually a code sample included with the download, IIRC. It's for VBScript, but it does work with VBA as well. I used the DLL version of the program as it's easier to use in code (plus you don't have to worry about where the program file is).
0
 

Author Comment

by:Debbie12
ID: 12096286
OK, maybe I'm too much of an amateur, but, I can't find this sample code.  How do I access it???
Deb
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12096317
Sorry, it's actually in the PDF documentation file. Scroll down to the bottom of the download page and you'll see a link that says "Documentation PDF". The example is on page 71 - there's one for VB and one for VBScript. The VB one should work in VBA without modification.
0
 

Author Comment

by:Debbie12
ID: 12128730
shanesuebsahakarn,
I know this PDF.meld thing can work - but, I can't figure it out.  This is what I get out of the documentation, however, it is not working.  Can you help me figure out what I'm doing incorrectly??
Thanks so much,  Debbie

I have a command button with the code I wrote in my original question, then I'm guessing I would do something along these lines>>>>
_____________________________
Set PDF = CreateObject("pdf.meld")
PDF.setInFile("c:\access program\ReportCoverPage.pdf,c:\access program\OptionLettersNew.pdf")
PDF.setOutFile("c:\access program\Quote.pdf")
rslt=PDF.buildPDF
If rslt <> 0 Then
   MsgBox("Error " & Rslt)
End If
setMailBCC("myaddress@yahoo.com")
setMailSubject("The quote you requested")
setMailBody("Attached is the quote you requested, let me know if you have any questions.\n Sincerely, \n Debbie")
setMailFiles("c:\access program\Quote.pdf")
Set PDF = Nothing
_____________________________
I'm at a complete loss, and I was suppose to be finished with this months ago. .....
Please help!!
Debbie
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12128756
That looks right - what error are you getting?
0
 

Author Comment

by:Debbie12
ID: 12128769
It comes back with an error code of -1, which the documentation says it means "Can't open input file"
Deb
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12128790
Hmm - are you sure the two files exist at the point that your code above is running?
0
 

Author Comment

by:Debbie12
ID: 12128869
Well, I think you were right about that because it was still trying to create the pdf files when it started to try to access them.  I didn't know how to tell the code to "wait" so ....
So I split the code up into two different command buttons.  (Hate to make it a 2 step process though - do you have a better way to handle this??)

Here's the exact code and sequence now.
The user enters the data, and presses command button 1: [PrintReports_Click()]
Private Sub PrintReports_Click()
On Error GoTo Err_PrintReports_Click

    Dim stDocName As String
       
    'Print Option Worksheet to pdf
    stDocName = "OptionWorksheetNew"
    DoCmd.OpenReport stDocName, acPrint, , "[Customer ID] = " & Me![Customer ID]
    'Print Cover Page to pdf
    stDocName = "ReportCoverPage"
    DoCmd.OpenReport stDocName, acPrint, , "[Customer ID] = " & Me![Customer ID]
    'Print Option Letters to pdf
    stDocName = "OptionLettersNew"
    DoCmd.OpenReport stDocName, acPrint, , "[Customer ID] = " & Me![Customer ID]

Exit_PrintReports_Click:
    Exit Sub

Err_PrintReports_Click:
    MsgBox Err.Description
    Resume Exit_PrintReports_Click
   
End Sub

THEN THEY EXIT ACROBAT AND PUSH THIS COMMAND BUTTON FROM WITHIN ACCESS
(THERE MUST BE A BETTER WAY!)
Private Sub Run_Acrobat_Click()
On Error GoTo Err_Run_Acrobat_Click

    Set PDF = CreateObject("pdf.meld")
        PDF.setInFile ("c:\access program\ReportCoverPage.pdf,c:\access program\OptionLettersNew.pdf")
        PDF.setOutFile ("c:\access program\Quote.pdf")
        Rslt = PDF.buildPDF
        If Rslt <> 0 Then
            MsgBox ("Error " & Rslt)
        End If
        PDF.setMailBCC ("myaddress@yahoo.com")
        PDF.setMailSubject ("The quote you requested")
        PDF.setMailBody ("Attached is the quote you requested, let me know if you have any questions.\n Sincerely, \n Debbie")
        PDF.setMailFiles ("c:\access program\Quote.pdf")
        Set PDF = Nothing

Exit_Run_Acrobat_Click:
    Exit Sub

Err_Run_Acrobat_Click:
    MsgBox Err.Description
    Resume Exit_Run_Acrobat_Click
   
End Sub
****************8
AFTER THIS I HEAR THE COMPUTER THINKING LIKE CRAZY, BUT NOTHING HAPPENS AT ALL.
HELP!!!!!
DEB
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12128906
I do have a better way of handling it, but that's slightly more involved. Just for the moment, let's see if we can get this working. I do know that this works because I've used PDFMeld myself.

I don't know what these lines do:
        PDF.setMailBCC ("myaddress@yahoo.com")
        PDF.setMailSubject ("The quote you requested")
        PDF.setMailBody ("Attached is the quote you requested, let me know if you have any questions.\n Sincerely, \n Debbie")
        PDF.setMailFiles ("c:\access program\Quote.pdf")

PDF is a PDFMeld object - I don't know if PDF Meld can send e-mails but can you remove these lines and run the code. Has the combined file been produced?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:Debbie12
ID: 12128944
Well, what do you know -- it's there!   AWESOME!!!! THANK YOU, THANK YOU, THANK YOU!!!
.................................
 I don't know what these lines do:
        PDF.setMail
        PDF.setMailBCC ("myaddress@yahoo.com")
        PDF.setMailSubject ("The quote you requested")
        PDF.setMailBody ("Attached is the quote you requested, let me know if you have any questions.\n Sincerely, \n Debbie")
        PDF.setMailFiles ("c:\access program\Quote.pdf")
.................................These lines are suppose to open outlook, fill in the bcc address, the subject, body and file attachment so all we have to do is type the To address and hit send.
That part, however, is not working.
Deb
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12128959
OK, dump those lines. Instead, use code like that here:
http://support.microsoft.com/?kbid=161088

to send your e-mail, using the newly created PDF file. Let me know if you need any help with it.
0
 

Author Comment

by:Debbie12
ID: 12129142
OK, did that - need help.
The system asks if it is ok to allow access to the address book -- I click OK, then an error message pops up which says object required.  
The object library I have is 11.0 (instead of 8.0) and I have access 2002 (instead of '97) Do you think this has anything to do with it??
Deb
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12129292
It shouldn't do - I know the same code works in both A2K2 and 97. Here's a slightly modified version of the function which I use in A2K2.

You can call it with something like:
SendMessage True, "", "Your Subject Line", "Your body message", "C:\access program\", False, False

      Sub SendMessage(DisplayMsg As Boolean, SendTo As String, Optional Subject As String, Optional Body As String, Optional AttachmentPath, Optional SaveMsg As Boolean, Optional SavePath As String)
          Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment
          Dim FileStr As String, sFiles As String

          ' 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.
              Set objOutlookRecip = .Recipients.Add(SendTo)
              objOutlookRecip.Type = olTo

             ' Set the Subject, Body, and Importance of the message.
             If Not IsMissing(Subject) Then
                .Subject = Subject
            End If
            If Not IsMissing(Body) Then
                .Body = Body
            End If
           
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
             If Not IsMissing(AttachmentPath) Then
                FileStr = Dir(AttachmentPath & "*")
                While FileStr <> ""
                 Set objOutlookAttach = .Attachments.Add(AttachmentPath & FileStr)
                 FileStr = Dir()
                Wend
             End If

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                If Not SaveMsg = False Then
                    .SaveAs SavePath
                    .Delete
                Else
                    .Save
                    .Send
                End If
             End If
          End With
          Set objOutlook = Nothing
      End Sub
0
 

Author Comment

by:Debbie12
ID: 12133749
Good morning!
Now, when you say ...
You can call it with something like:
SendMessage True, "", "Your Subject Line", "Your body message", "C:\access program\", False, False
...Do I simply type this line at the end of the Run_Acrobat_Click function/procedure???
and put the SendMessage code as a separate function/procedure??
Debbie
0
 

Author Comment

by:Debbie12
ID: 12134087
Oh, woo hoo, ignore my last comment, it works!  Fantastic!!!

Just a few quick clean up problems.
1)  I would like to set the Bcc address instead of the To address. What do I need to change?
2) A lovely pop up comes up every time that says "You are using FyTek, Inc.'s PDF Meld, single user version. Click OK"
***Can I get rid of this popup?****
3) When It tries to access the address book, I get the following Microsoft Office Outlook message
"A program is trying to access e-mail addresses you have stored in Outlok.  Do you want to allow this?
Yes or No"
***Is there a way I can program a Yes for this program only within the code??***

Thanks...Debbie
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12135452
Just a question - how many pages is the combined report - is it, or will it ever exceed 10 pages?

As for the Outlook message, you have a few choices:
http://www.granite.ab.ca/access/email/outlook.htm

You can install the ClickYes program that will auto-click on the prompts for you.
0
 

Author Comment

by:Debbie12
ID: 12142336
I doubt it would ever exceed 10 pages.  It would be a VERY rare occurance
Debbie
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12144380
If it won't, you can use the trial version of PDF Meld which doesn't show the message (!!). If the report is 10 pages or less, it doesn't put the trial watermark on.
0
 

Author Comment

by:Debbie12
ID: 12152366
Hello again,
I'm trying the ClickYes program, and I get the following error message: "Can't find DLL entry point RegisterWindowMessageA in user 32"
Any suggestions???
Deb
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12152370
Sorry, I haven't seen that message before when I was using it. You'd need to contact them for help.
0
 

Author Comment

by:Debbie12
ID: 12152408
You're right!  It works great!!!
On the program you sent me the code for, "SendMessage," what is the syntax change needed to send the email the BCC address instead of the To address???
Deb
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12152442
Try changing this line:
objOutlookRecip.Type = olTo

to:
objOutlookRecip.Type = olBCC

This will make the address that you pass the function go into the BCC field rather than the To field.
0
 

Author Comment

by:Debbie12
ID: 12152937
Thank you so much for all of your help.  Everything works, and I couldn't have done it without you!!
Much gratitude,
Debbie
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

744 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

11 Experts available now in Live!

Get 1:1 Help Now