Very Difficult Email report as PDF, record looping automation code

[Amp]

I am in need of help. I want create a code loops through a table record by
record and emails each record out as a report that has been converted to a
PDF.

Need to know information:

- I am using MS Outlook 2003 and MS Access 2003
- To create using RunReportAsPDF found online at :
http://www.tek-tips.com/viewthread.cfm?qid=1119207&page=1
- I am using the Outlook object model to send the email

Here is my relevant code:

Sub AutomateOrder()

Dim rst As ADODB.Recordset
Dim olApp As Outlook.Application
Dim olMail As MailItem

Set rst = New ADODB.Recordset

rst.Open "UNPROCESSED_ORDERS", CurrentProject.Connection
rst.MoveFirst

Do Until rst.EOF

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail

.To = rst.Fields("TestEmail").Value 'rst!EmailAddress
.subject = rst.Fields("Order Number").Value ' rst!EmailSubject

FileName = "c:\temp\" & _
rst.Fields("Order Number").Value & ".pdf"

PDF = RunReportAsPDF("ORDERS REPORT", "c:\temp\" & _
rst.Fields("Order Number").Value & ".pdf")

.Attachments.Add FileName, olByValue, 1

.Send

End With

rst.MoveNext

Loop

End Sub

Besides the code above I cannot figure out a few things:

1.) How to synchronize the recordset of my ADO connection and the record of
the report?
2.) How to passing arguments through the report filter? I typically see
examples like this Filter = "Client Id = 1" but I want to do something like
Filter = "Order Number" and each time the code loops it uses the order number
of each record as the filter.
3.) I cannot find a way to filter the function RunReportAsPDF at all, any
suggestings?

 

[Graham Mandeno]

Hi Amp

I think your three questions boil down to just one:

"How do I make RunReportAsPDF print only the order corresponding to the
current record in my recordset?"

The answer is, you add an extra optional argument to RunReportAsPDF so that
you can pass a filter string:

Public Function RunReportAsPDF( _
prmRptName As String, _
prmPdfName As String, _
Optional prmFilter As String) As Boolean

.... then, further down:

DoCmd.OpenReport prmRptName, acViewNormal, , prmFilter 'Run the report


Then you pass a valid filter string when you call the function.

I notice that you repeatedly refer to rst.Fields("Order Number").Value. I
suggest you assign this value to a string variable so it is easier to reuse.

Also, I notice you have not declared the variable FileName. You should
ALWAYS declare ALL variables, and you should force yourself to do so by
including Option Explicit at the top of EVERY module.

Also, you should create your Outlook.Application only once, not every time
around the loop.

So, you have:
Dim FileName as String
Dim PDF as Boolean
Dim OrderNumber as Long ' or perhaps String, depending on the data type
....
Set olApp = New Outlook.Application
....
Do Until rst.EOF

OrderNumber = rst.Fields("Order Number").Value

Set olMail = olApp.CreateItem(olMailItem)

With olMail

.To = rst.Fields("TestEmail").Value 'rst!EmailAddress
.subject = "Order No. " & OrderNumber

FileName = "c:\temp\" & OrderNumber & ".pdf"

PDF = RunReportAsPDF("ORDERS REPORT", FileName, _
"Order Number=" & OrderNumber

If PDF Then
.Attachments.Add FileName, olByValue, 1
.Send
End If
End With

' maybe you want to delete the temp file?
Kill FileName

rst.MoveNext

Loop