Very Difficult Email report as PDF, record looping automation code

A

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?
 
G

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
 
A

Amp

Thanks Graham for the quick response.

Yes, my question actually does boil down to, "How do I make RunReportAsPDF
print only the order corresponding to the current record in my recordset?"

Now I can a little more specific in what I am trying to do.....At the point
where the report is created I want the report to show the data (one record)
for the specific order number.

I added an extra optional argument to RunReportAsPDF, prmFilter. That onlt
produced a blank report.

Do I need to make any changes to the report for it to retrieve the filtered
information? The report's data source is a table called unprocessed_orders.
As of right now I haven't did anything to the report itself as filters go.

I actually want to save the file to the temp for testing purposes.

When I didn't create Outlook.Application in the loop I recieved the a run
time error, 'The item has been moved or deleted'

Graham Mandeno said:
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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Amp said:
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?
Click to expand...
Click to expand...
 
G

Graham Mandeno

I didn't allow for your field name having spaces in it. Try this:

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

Also, is [Order Number] a numeric field or text? If it is text, you must
enclose the value in quotes:

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

If you are still having trouble, try this from the Immediate window:

DoCmd.OpenReport "ORDERS REPORT", acViewPreview, "[Order Number]=123"

where 123 is a valid order number. It should open the report showing that
order only, otherwise something else is wrong.

I don't understand why you were getting the error with Set olApp outside the
loop.

Are you sure you didn't move Set olMail outside the loop as well?
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Amp said:
Thanks Graham for the quick response.

Yes, my question actually does boil down to, "How do I make RunReportAsPDF
print only the order corresponding to the current record in my recordset?"

Now I can a little more specific in what I am trying to do.....At the
point
where the report is created I want the report to show the data (one
record)
for the specific order number.

I added an extra optional argument to RunReportAsPDF, prmFilter. That
onlt
produced a blank report.

Do I need to make any changes to the report for it to retrieve the
filtered
information? The report's data source is a table called
unprocessed_orders.
As of right now I haven't did anything to the report itself as filters
go.

I actually want to save the file to the temp for testing purposes.

When I didn't create Outlook.Application in the loop I recieved the a run
time error, 'The item has been moved or deleted'

Graham Mandeno said:
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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Amp said:
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?
Click to expand...
Click to expand...
Click to expand...
 
A

Amp

Thanks Graham!!! I made the change for the field name having spaces and you
were correct I put the Set olMail outside the loop as well instead of just
the Set olApp. The code now runs properly producing the desired report and
emailing it as a PDF.

Graham Mandeno said:
I didn't allow for your field name having spaces in it. Try this:

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

Also, is [Order Number] a numeric field or text? If it is text, you must
enclose the value in quotes:

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

If you are still having trouble, try this from the Immediate window:

DoCmd.OpenReport "ORDERS REPORT", acViewPreview, "[Order Number]=123"

where 123 is a valid order number. It should open the report showing that
order only, otherwise something else is wrong.

I don't understand why you were getting the error with Set olApp outside the
loop.

Are you sure you didn't move Set olMail outside the loop as well?
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Amp said:
Thanks Graham for the quick response.

Yes, my question actually does boil down to, "How do I make RunReportAsPDF
print only the order corresponding to the current record in my recordset?"

Now I can a little more specific in what I am trying to do.....At the
point
where the report is created I want the report to show the data (one
record)
for the specific order number.

I added an extra optional argument to RunReportAsPDF, prmFilter. That
onlt
produced a blank report.

Do I need to make any changes to the report for it to retrieve the
filtered
information? The report's data source is a table called
unprocessed_orders.
As of right now I haven't did anything to the report itself as filters
go.

I actually want to save the file to the temp for testing purposes.

When I didn't create Outlook.Application in the loop I recieved the a run
time error, 'The item has been moved or deleted'

Graham Mandeno said:
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

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter a function that runs a report as a pdf (RunReportAsPDF) 0
Trying to fill a form/table importing emails from outlook 10
Loop through ADO recordset, update records, and count updated reco 1
Email a report as a PDF 8
Recordset is not filtering Report- version 2 6
Bulk Email From Access 1
Follow-up to DAO.Recordset as Report record source 3
DLookup returns Error '3464'-Data Type Mismatch in Criteria Expres 24

Top