Email PDF certificates based on a table of completers to all in the table (1 Viewer)

nortonm

Registered User.
Local time
Today, 22:44
Joined
Feb 11, 2016
Messages
49
I'll try to be succinct: I have a report mocked up as a certificate, merged to multiple course completers in a table. I want to send a certificate to each completer in the table, but all I can do with my current code is send the whole report containing everyone in the table as a separate PFD to each completer. I've done quite a lot of searching all day and can't find a way of doing it as yet. I'll attach the code I'm using. The report is "rptCertificates", and it is based on the recordset i.e. "qryDataToSend".

I'm not sure which direction to go in, is it quite a way off?

Code:
Private Sub cmdSendCerts_Click()

Dim FileName As String, FullName As String
Dim MyRS As Recordset

Dim DateCompleted As String
Dim Email As String

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryDataToSend")

MyRS.MoveFirst

Set objOutlook = CreateObject("Outlook.application")

Do Until MyRS.EOF

FileName = Application.CurrentProject.Path & "\PDF_Output\" & "\Certificates_" & FullName & ".pdf"
DoCmd.OutputTo acReport, "rptCertificates", acFormatPDF, FileName, False

FullName = MyRS!FullName
DateCompleted = MyRS!DateCompleted
Email = MyRS!Email

Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = Email
.Subject = "Your Practice Placement Certificate of Attendance"
'.CC
.HTMLBody = "<font face=Calibri><p>NB: Do not REPLY to this email, select FORWARD, and send to <a href = mailto:SHLS_Placements@****.**.uk>SHLSTechnicians@****.**.uk</a></p>" _
& "<p>FAO " & FullName & ",</p>" _
& "Please find attached your Certificate of Attendance for the Preparation for Practie Assessor Workshop (3 Hours) course.<br>" _
& "<p>Kind Regards,<br></p>" _
& "The Practice Placements Team</p><br>"

.Attachments.Add FileName
.Display

End With

MyRS.MoveNext
Loop

'objOutlook.Quit
'Set objEmail = Nothing

MyRS.Close
 

Attachments

  • Certificates.txt
    1.3 KB · Views: 424
Last edited by a moderator:

Gasman

Enthusiastic Amateur
Local time
Today, 22:44
Joined
Sep 21, 2011
Messages
14,048
I would use the recordset to get each recipient's data, create a report just for them, create an email and attach that report, that would be saved as a pdf.
Plenty of examples on here to do that, as it gets asked time and time again.
See the last thread in Similar threads below for the pdf. Then search for email attachment here
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Looks like you're already using a loop to create multiple emails with attachments. All you have to do is limit the content of each PDF to the current "completer" in your loop. You can do this by opening the report with a criteria first before outputting the report into PDF.
 

nortonm

Registered User.
Local time
Today, 22:44
Joined
Feb 11, 2016
Messages
49
I would use the recordset to get each recipient's data, create a report just for them, create an email and attach that report, that would be saved as a pdf.
Plenty of examples on here to do that, as it gets asked time and time again.
See the last thread in Similar threads below for the pdf. Then search for email attachment here
Thanks Gasman. I am OK doing that for individual emails, that's where i'm up to, but the problem is there are hundreds of completers, and what they want is to press one button and each recipient gets a copy of their certificate attached as a pdf. At the moment the report contains the whole recordset, and attaches this complete to each email; i'm trying to get just the one pdf outputto with one person's record in it. DBGuy just suggested something that rings a bell from another thread....
 

nortonm

Registered User.
Local time
Today, 22:44
Joined
Feb 11, 2016
Messages
49
Hi. Looks like you're already using a loop to create multiple emails with attachments. All you have to do is limit the content of each PDF to the current "completer" in your loop. You can do this by opening the report with a criteria first before outputting the report into PDF.
Thanks theDBGuy, i'll try and figure something out using that. I'm off for an autumn birdwatch though until Thursday, i'll leave it till then....
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:44
Joined
Jul 9, 2003
Messages
16,245
I want to send a certificate to each completer in the table

I answered a similar question for an Access World Forums (AWF) member a while back. I made notes on my website, along with some YouTube videos demonstrating the process. You can find my notes and videos on my website here:-


You are welcome to a free copy of the download, just send me a private message and I will explain how you can get a free copy..

The solution I show is for a particular database and may not completely relate to your problem, however I think it's pretty close. If you need any help shoehorning it into your database let me know and I will be only too pleased to help.
 

nortonm

Registered User.
Local time
Today, 22:44
Joined
Feb 11, 2016
Messages
49
I answered a similar question for an Access World Forums (AWF) member a while back. I made notes on my website, along with some YouTube videos demonstrating the process. You can find my notes and videos on my website here:-


You are welcome to a free copy of the download, just send me a private message and I will explain how you can get a free copy..

The solution I show is for a particular database and may not completely relate to your problem, however I think it's pretty close. If you need any help shoehorning it into your database let me know and I will be only too pleased to help.
That's really kind of you, thanks very much. I am away for a few days now, but i'll be in touch as soon as i'm back onto it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:44
Joined
Sep 21, 2011
Messages
14,048
Rather than reinvent the wheel, have a look at this thread.

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:44
Joined
Feb 19, 2002
Messages
42,976
There is one thing I would add. Both of the suggestions use the same technique which is to provide a variable to open the report to a specific user, invoice, company, or whatever. Then to use the OutputTo method to export the open report to a .pdf. This method works fine as long as you don't have to create too many pdf's. If you do, the overhead of instantiating the report in memory first add significantly to the overhead.

You can speed up the process significantly if you have enough .pdf's to send by NOT opening the report first. The problem lies with the OutputTo method. Unlike the OpenReport method, it does not allow for a where argument to be passed in so you have to work around that limitation. Opening the report to a specific ID first, does that. However, what I do is as I move through the outer loop, I save the ID of the record I want to pass to the report in a hidden form field. Then instead of using the where argument of the OpenReport, I use a querydef that references the hidden form field as the recordsource for the report. That allows me to bypass the OpenReport step and go directly to the OutputTo step. The report finds its own criteria using the form field reference without having to open the report twice.

The query would look something like:

Select fld1, fld2, fld3, ...
From YourTable
Where TheID = Forms!yourForm!txtTheID
 

nortonm

Registered User.
Local time
Today, 22:44
Joined
Feb 11, 2016
Messages
49
Rather than reinvent the wheel, have a look at this thread.

Many Thanks, there was a database made available by a 'Vlad' on the thread which helped me construct my process along similar lines
 

nortonm

Registered User.
Local time
Today, 22:44
Joined
Feb 11, 2016
Messages
49
Many Thanks all, I managed to find a solution from a thread provided by gasman, but all the suggestions helped me to understand what I needed to make happen, and will be used to perhaps cut a corner or two off what i'm currently doing.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom