Create multiple Pdf's from records in a report and email pds's

[neelsfer]

I have a query = qryCyclistCard2a
Report = rptCyclistEntries
Unique field - RaceNo (number field)
The report shows one record per page with many fields
Each of these pages needs to be converted to a Pdf based on the strReportname.
The email field also appears on every page of the report and each pdf document.
Can i create a Pdf document and then email it to the email address listed together with RaceNo field on each page
Please assist.This is driving me insane after 3 days.
An attachment shows the report page that is converted to Pdf
Problem -its not functioning now

Dim MyDB As DAO.Database
Dim rs As DAO.Recordset
Dim RecordSetString As String
Dim stDocName As String
Dim strUserName As String, strPath As String
Dim strReportname As String
strPath = "C:\Pdf\"
strUserName = [Reports]![rptCyclistEntries]![RaceNo]
strReportname = "Race_Number" + "--" + strUserName + "--" & Format(Date, "dd-mm-yyyy") + ".pdf"
 
RecordSetString = rptCyclistEntries.RecordSource
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset(RecordSetString)
 
rs.MoveFirst
With rs
Do While Not rs.EOF
 
    stDocName = "rptCyclistEntries"
 
     DoCmd.OpenReport "rptCyclistEntries", acViewPreview, , "[RaceNo] = " & ![RaceNo]
 
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, strPath + strReportname, False
        DoCmd.close acReport, "rptCyclistEntries"
 
.MoveNext
Loop
End With
rs.close
Set rs = Nothing

[twinnyfo]

neelsfer,

I think you have the right concept here.

First, you say

Problem -its not functioning now

Exactly what is not functioning, so we know where to start.....

Second, there are two basic ways to do what you are doing. The first way, as it appears you are trying to do, is to create the PDF attachments and to save them. However, according to your code (Line 23) you will be overwriting your PDFs, as you are not changing the file name. Perhaps the following:

Line 9:

strReportname = "Race_Number" + "--" & _
    strUserName & "--" & Format(Date, "dd-mm-yyyy")

Then, Line 23:

DoCmd.OutputTo acOutputReport, "", acFormatPDF, _
    strPath & strReportname & ![RaceNo] & ".pdf", False

The other way to do it (if you have the e-mail addresses available in your db) is to use the SendObject method, including the e-mail addresses, making sure that the Report is filtered by the Race Number in your recordset.

Will be glad to help, if we get some more information on what is not working.

[neelsfer]

Thx Twinnyfo i fiddled with the code and it tells me now - "compile error variable not defined" and hangs

What i want to do:
take each individual page in the report and export it to an individual pdf using

strReportname = "Race_Number" + "--" + strUserName + "--" & Format(Date, "dd-mm-yyyy") + ".pdf"

I then also use the email field in this "qryCyclistCard2a" query as the record source of the "rptCyclistEntries" report, and send an email to the individual persons listed on each report page.
I imagine i will probably have to use something like this to email in the code:

DoCmd.SendObject acSendReport, "rptCyclistEntries" acFormatPDF, rs!Email, , , "TestMessage Title", "Race Entries", True

thx for your assistance so far

I attach a mini version of what i have done in Access 2007 to assist you. create a c:\pdf folder to export

[twinnyfo]

For your error, it looks like you Dim MyDB, but you set the variable db, so that could be the initial source of your problem.

[twinnyfo]

Concerning the sending of the e-mails, if you are able to produce a one-page report for each race results, then you should be able to build code to cycle through the races, then cycle through the e-mail addresses in your query, building the various parts of your e-mail.

Your second block of code is exactly the direction you want to go (don't forget the comma before acFormatPDF).

Play around with your code sequences. Let us know if you hit any more snags and we will try to trouble shoot to a solution.

Hope this helps!

[neelsfer]

Twinnyfo
I have changed direction to prevent spam emails getting blocked, as it is send to numerous persons.

1. open a continious form with all the recipients in different rows
2. add a button on the left of row that is named "email"
3. create a personalised Pdf file in a specific folder
4. take this file and put it into the "Outbox" of the email with the To:, subject etc added, and the file attached.
5. delete file from folder
The code i am using:

Function SendFiles(fldName As String, Optional FileType As String = "*.*")
 
Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Dim strPath As String
    Dim strFilter As String
    Dim strFile As String
 
    strPath = "C:\Pdf\"      'Edit to your path
    strFilter = "*.Pdf"
    strFile = Dir(strPath & strFilter)
 
    If strFile <> "" Then
 
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
        With MailOutLook
            .BodyFormat = olFormatRichText
            .To = [Forms]![frmExportEntries]![email]
            ''.cc = ""
            ''.bcc = ""
            .Subject = "Race Entry"
            .HTMLBody = "Please print out this Race Entry Form in good quality, and take with to the race. It will speed up your entry."
            .Attachments.add (strPath & strFile)
            .Send
            '.Display    'Used during testing without sending (Comment out .Send if using this line)
        End With
    Else
        MsgBox "No file matching " & strPath & strFilter & " found." & vbCrLf & _
                "Processing terminated."
       ' Exit Sub    'This line only required if more code past End If
    End If
 
End Function

The rest of the code to rename the Pdf file and call the function

On Error GoTo Command99_Click_Err
Dim strUserName As String, strPath As String
Dim strReportname As String
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
Dim strbody As String
Dim attachmentname As String
Dim myAttachments As String
 
 
Me.Email_Sent.Value = "-1"
'rptCyclistEntries
DoCmd.OpenReport "rptCyclistEntries", acPreview
strPath = "C:\Pdf\"
strUserName = [Reports]![rptCyclistEntries]![RaceNo]
strReportname = "Race_Number" + "-" + strUserName + "-" & Format(Date, "dd-mm-yyyy") + ".pdf"
 
DoCmd.OutputTo acOutputReport, "", acFormatPDF, strPath + strReportname, False
DoCmd.close acReport, "rptCyclistEntries"
 
'-------------
Call SendFiles("C:\Pdf\")
 
Kill "C:\pdf\*.pdf"
 

Thanks for your assistance. I hope this code also helps somebody else to email reports in Pdf.

[twinnyfo]

neelsfer,

Glad we coudl get you in the right direction for a solution!