Programming : Access VBA Report Filtering and Emailing

Print Page | Close Window

Access VBA Report Filtering and Emailing

Printed From: www.exp-systems.com
Category: PDF reDirect
Forum Name: Programming
Forum Discription: VBA and Batch Tools to control PDF reDirect Pro
URL: http://www.exp-systems.com/Forum_exp/forum_posts.asp?TID=493
Printed Date: 22 Nov 24 at 8:13AM

Topic: Access VBA Report Filtering and Emailing

Posted By: David_Carle
Subject: Access VBA Report Filtering and Emailing
Date Posted: 26 May 09 at 7:52PM

Hi
I'm reviewing PDF generating tools to replace a well known product that doesn't work reliably for me, and would like to ask a few questions before using PDF reDirect.

1. Using Access 2003, is it possible to apply criteria (eg a where clause) to an access report at run time, just prior to printing to pdf?
(eg printing an invoice report for each individual invoice, to be emailed to each customer in turn)
If so, what is the best way to do this?

2. Do you have any sample code that emails the newly created pdf file?

3. Have you encountered any problems with pdf's being created and emailed faster than the email client (Outlook 2003) can handle them?

4. Are there issues with the Outlook security "feature" prompting the user to allow access to its address book?
If so, does PDF reDirect work with other products such as ClickYes?

Thanks for any advice.

Replies:

Posted By: Michel_K17
Date Posted: 26 May 09 at 11:32PM

Hello,

   Thank you for considerering PDF reDirect.

   First off, if you want to control PDF reDirect remotely, you will need to use the "Professional" version plus add the ActiveX component and sample code which includes e-mail support, or you can use your own e-mailing code.

   With respect to the 1st question, yes, you can generate reports for individual customers using Access. However, there are plenty of programming help sites that can help you with that problem. Unfortunately, I do not provide programming advice through this business: sorry. I would check any number of VBA help sites and forums. The biggest one is: http://www.vbforums.com - www.vbforums.com and there is also http://www.xtremevbtalk.com - www.xtremevbtalk.com .

   Question #2: Yes. Please check the sample VBA code that comes with the ActiveX component [ http://www.exp-systems.com/Forum_exp/forum_posts.asp?TID=254&FID=7&PR=3 - here ].

   Question #3. Yes, you can definitely overwhelm the system. Best thing is to throttle your conversion so that you stay within the bounds of your system. This is done by checking that the PDF file has been fully written and the e-mail sent before converting the next form to a PDF (assuming you send the e-mail). However, the sample code I provide shows how to send the e-mail using my component. Again, all you really need to do is to try to not generate PDF files more quickly than they are processed, ie: wait for the component to complete creating the PDF and sending the e-mail before moving on to the next job. Look at the "PdfCreated" function for which I have included the code.

   Question #4. Yes, the Outlook security feature is a pain, and is very hard to fully automate without requiring some sort of human intervention. Therefore, you might be best to use the SMTP e-mail feature in my component that does not use Outlook, or to use a 3rd party e-mail component. In PDF reDirect Pro, I use the SMTP component from http://www.ostrosoft.com - www.ostrosoft.com , but not their latest version (v7) which uses .NET technology. I use an older COM version.

   I hope that helps,

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Posted By: David_Carle
Date Posted: 28 May 09 at 3:10PM

I have now had a chance to examine the VBA code of the Access demo in more detail, and have the answer to my first question, namely how to filter the data in the report.

What I did was to amend the code in the modCode module as follows:

In Convert2Pdf, I added a parameter:

Optional ByRef WhereClause As String = ""

This is then passed as a parameter to PrintOutputToBatchPrinter as follows:

TempBool = PrintOutputToBatchPrinter(MyDocumentName, MyBatchPrinter, WhereClause)

Next, in PrintOutputToBatchPrinter I added the same parameter as for Convert2Pdf

Then I added the parameter to the OpenReport lines as follows:

DoCmd.OpenReport MyDocumentName, acViewPreview, , WhereClause ' Generate a Preview First
DoCmd.OpenReport MyDocumentName, acViewNormal, , WhereClause ' Print the Report

To Test this, I amended cmdExportPDF_Click in frmMain as follows:

Convert2Pdf MyDocumentName, MyBatchPrinter, MyOutputPath, MyOutputFilename, , , "UserName = 'Michel_K17'"

(Note the 3 commas before the where condition.

I hope this helps anyone else who is evaluating or using your software.

Posted By: Michel_K17
Date Posted: 28 May 09 at 11:03PM

Hi,

Wow: good job! Thanks for posting your improvements.

Cheers!

-------------
Michel Korwin-Szymanowski
EXP Systems LLC

Print Page | Close Window