Solved

Multiple access reports to single pdf, then email

Posted on 2004-09-17
28
702 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
[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
  • 14
  • 12
  • +1
28 Comments
 
LVL 13

Expert Comment

by:lucas911
ID: 12086531
0
 
LVL 66

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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
 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
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.

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