converting the report to PDF, naming each report distinctly

[Bevo]

Hi there,

I have a report in ms access that is grouped by customer number. I have +- 300 customers which means I have +- 300 pages. How would I go about converting the report to PDF, naming each report distinctly, i.e by customer number and then emailing it to the relevant customer? I would like to do this via a macro or vb/access code.

Regards

Bevo

[sierra7]

Hi Bevo
I have not got the solution for you but want to subscribe this thread in case someone else knows of a good way to do what you want.

I understand from your post that you want to create 300 emails each with a single page report attached; ideally in PDF format.

As you know there are packages called 'PDF Printers' that allow you to generate a PDF document from any Windows document and most allow you to save it to disc or open your email program. This is OK to send reports one at a time but would probably be a fulltime job for a month to send 300+ reports.

What you need is a PDF printer package that accepts command line parameters (email address particularly, and report name desireably) I suspect that to do this you will need the 'paid for' versions rather than the 'free' versions, but will look into it.

From within Access you can 'Send' a report as a Snapshot or as an HTML file. The Snapshort is more akin to the PDF in that it preserves the formatting of your report, HTML less so. This would require you Customers to have a Snapshot viewer installed; not impossible but not convenient I suppose. An additional problem with this route is that you may have to 'Preview' the report on screen to get access to the 'Send To' command. Once again, needs investigating and comment.

Let's see what anyone else can add.

S7

PS
Createring a procedure to run the report for each customer in a list will not be a problem; The SendObject method can be use to create the emails to the customers and be made to work automatically. It's how it will format the attched report that will be the issue. The Types available being only HTML, RTF, TXT, XLS and something called DAP (to be investigated)

[ADezii]

Hi there,

I have a report in ms access that is grouped by customer number. I have +- 300 customers which means I have +- 300 pages. How would I go about converting the report to PDF, naming each report distinctly, i.e by customer number and then emailing it to the relevant customer? I would like to do this via a macro or vb/access code.

Regards

Bevo

Do a little research on Cute PDF.

[Bevo]

Hi,

I got it to work perfectly, BUT with snapshot. I used the following code.

Private Sub Command7_Click( )

Dim dbs As Database
Dim rst As Recordset
Dim sSQL As String
Dim i As Integer
Dim DlrC As String
Dim email As String

Set dbs = CurrentDb

sSQL = "SELECT Dealer.DLRC, Dealer.Brand, Dealer.TradeNam e, Dealer.[E-Mail], Dealer.[SME-Mail] FROM Dealer;"

Set rst = dbs.OpenRecords et(sSQL)
With rst


Do

If rst!DlrC = "1000" Then
.MoveNext
End If
DlrC = ![DlrC]
txtDlrTest = DlrC
comboModelCat.S etFocus
email = ![E-Mail]

DoCmd.OpenQuery "4Totals for Period test"
DoCmd.SetWarnin gs False
DoCmd.SendObjec t acSendReport, "Report VW test", acFormatSNP, email, , , "Performanc e Report - Dlr" & DlrC, "Hi, Please find attached your Performance Report", False
.MoveNext
Loop While Not .EOF
End With
DoCmd.SetWarnin gs True
MsgBox "Its done", vbOKOnly

rst.Close
End Sub

My problem, as you said above, is that my customers dont have snapshot. I've heard that ms access 2007 can do the following:

DoCmd.SendObjec t acSendReport, "Report VW test", acFormatPDF...

If this is true, it would be the perfect solution to my problem.

Thanks for your reply tho... Its highly appreciated

[sierra7]

Hi Bevo
It's good news youv'e got it working! It's just a pity MS did not make Snapshot viewer part of a standard Office installation and kept it specific to Access.

I've just Googled 'acFormatPDF' but came up with lots of people with the same problem as you but no mention that it was included in Access 2007.

There are a number of third-party solutions to the pdf problem, one being at groupacg.com if you want to check it out (we're not allowed to set external links)

I must set up a 2007 workstation to check the new features myself. If true, it will be the first good thing I've heard about 2007!

S7

[Bevo]

Hi S7

I read that one can output a report to PDF in MS access 2007, ONLY after you install the PDF add-in. It embeds itself in your MS access application and excel and others. I read this on msdn2.microsoft .com under the SendObject Macro Action, under library.

When you do get to work with MS access 2007, let me know if it works.

Thanks!

Bevo

[n8kindt]

Hi,

I got it to work perfectly, BUT with snapshot. I used the following code.

Private Sub Command7_Click( )

Dim dbs As Database
Dim rst As Recordset
Dim sSQL As String
Dim i As Integer
Dim DlrC As String
Dim email As String

Set dbs = CurrentDb

sSQL = "SELECT Dealer.DLRC, Dealer.Brand, Dealer.TradeNam e, Dealer.[E-Mail], Dealer.[SME-Mail] FROM Dealer;"

Set rst = dbs.OpenRecords et(sSQL)
With rst


Do

If rst!DlrC = "1000" Then
.MoveNext
End If
DlrC = ![DlrC]
txtDlrTest = DlrC
comboModelCat.S etFocus
email = ![E-Mail]

DoCmd.OpenQuery "4Totals for Period test"
DoCmd.SetWarnin gs False
DoCmd.SendObjec t acSendReport, "Report VW test", acFormatSNP, email, , , "Performanc e Report - Dlr" & DlrC, "Hi, Please find attached your Performance Report", False
.MoveNext
Loop While Not .EOF
End With
DoCmd.SetWarnin gs True
MsgBox "Its done", vbOKOnly

rst.Close
End Sub

My problem, as you said above, is that my customers dont have snapshot. I've heard that ms access 2007 can do the following:

DoCmd.SendObjec t acSendReport, "Report VW test", acFormatPDF...

If this is true, it would be the perfect solution to my problem.

Thanks for your reply tho... Its highly appreciated

just fyi... the microsoft access pdf plugin works beautifully in 2007 using the acFormatPDF