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?
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
Last edited by a moderator: