VBA Export to single pdf files: Names not appearing on the report being exported?

**Oblio** · 08-19-2020, 06:25 AM

I have a Certificate of Achievement report, based on a query. When I run the report, the certificate shows the person Nickname and Lastname and a text type ID# (called ON#...it is a unique text type ID).

When I export the report to individual pdfs, I have 2 problems:

1. The report prompts for each individual person, which is tedious
2. The exported report certificate is missing their name and ID# (It appears in the regular multi-page report, so not sure why this is happening)

The query is called: q_Certificates_To_Email

Code:

SELECT t_Restart_Teaching.[ON#], q_Contacts_Extended.FileAs, q_Contacts_Extended.[Last Name] & "_" & [Nickname] AS FullName, t_Restart_Teaching.[Emailed_Instructor_Good_To_Go?], t_Restart_Teaching.Date_Emailed, q_Contacts_Extended.[Home Email], t_Restart_Teaching.[Insurance Pd], t_Restart_Teaching.[Good To Go], t_Restart_Teaching.Notes, q_Contacts_Extended.Nickname, q_Contacts_Extended.[Last Name], t_Restart_Teaching.[Last Name] AS LName
FROM t_Restart_Teaching INNER JOIN q_Contacts_Extended ON t_Restart_Teaching.[ON#] = q_Contacts_Extended.[ON#]
WHERE (((t_Restart_Teaching.[Emailed_Instructor_Good_To_Go?])=Yes) AND ((t_Restart_Teaching.[Insurance Pd])="Yes") AND ((t_Restart_Teaching.[Good To Go])="Yes"))
ORDER BY q_Contacts_Extended.Nickname, t_Restart_Teaching.[Last Name];

The form f_Certificates, is bound to the query above. It has a command button named: cmd_Output_Certificates_To_Individual_PDFs

The on click event code is:

Code:

Private Sub cmd_Output_Certificates_To_Individual_PDFs_Click()    Dim rs As DAO.Recordset
    Dim sFolder As String
    Dim sFile As String
    Dim MyDB As DAO.Database
    
    'On Error GoTo Error_Handler


    sFolder = Application.CurrentProject.path & "\"


    'Set rs = CurrentDb.OpenRecordset("SELECT FullName,Nickname,LName FROM q_Certificates_To_Email", dbOpenSnapshot)
    Set MyDB = CurrentDb
    Set rs = MyDB.OpenRecordset("q_Certificates_To_Email")


    With rs
        .MoveFirst
        Do While Not .EOF
            DoCmd.OpenReport "r_Certificates", acViewPreview, , "[LName]=" & ![LName], acHidden
            'sFile = Nz(![FirstName], "") & "_" & Nz(![LName], "") & ".pdf"
            sFile = Nz(![Nickname], "") & "_" & Nz(![LName], "") & "_Restart_Certificate" & ".pdf"
            sFile = sFolder & sFile
            DoCmd.OutputTo acOutputReport, "r_Certificates", acFormatPDF, sFile, , , , acExportQualityPrint
            'If you wanted to create an e-mail and include an individual report, you would do so now
            DoCmd.Close acReport, "r_Certificates"
            .MoveNext
        Loop
    End With


    Application.FollowHyperlink sFolder    'Optional / Open the folder housing the files


Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Exit Sub


Error_Handler:
    If Err.Number <> 2501 Then    'Let's ignore user cancellation of this action!
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: cmd_Output_Certificates_To_Individual_PDFs" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler
End Sub

I need to email these certificates and I am way overdue having tried so many different variations on the internet. can anybody help me ? I can attach a db if required...

Thanks SO MUCH,

Oblio

**CJ_London** · 08-19-2020, 07:17 AM

using non alphanumeric characters in field names can cause issues, as can using reserved words. You have a field called ON# which might be causing the problem (and ON is a reserved word)

are your [good to go] fields a boolean or a text field? if the former yes should not be enclosed in quotes.

[Emailed_Instructor_Good_To_Go?])=Yes
[Good To Go])="Yes"
[Insurance Pd])="Yes"

if your lname is text then your filter on openreport needs quotes

"[LName]= '" & ![LName] & "'"

using a name as your filter is not a good idea - what if you have two students with the same surname? You should use a PK.

I think that is all the obvious stuff that is wrong. Make those corrections and see if you still have a problem.

**Oblio** · 08-19-2020, 10:48 AM

Originally Posted by Ajax

using non alphanumeric characters in field names can cause issues, as can using reserved words. You have a field called ON# which might be causing the problem (and ON is a reserved word)

are your [good to go] fields a boolean or a text field? if the former yes should not be enclosed in quotes.

[Emailed_Instructor_Good_To_Go?])=Yes
[Good To Go])="Yes"
[Insurance Pd])="Yes"

if your lname is text then your filter on openreport needs quotes

"[LName]= '" & ![LName] & "'"

using a name as your filter is not a good idea - what if you have two students with the same surname? You should use a PK.

I think that is all the obvious stuff that is wrong. Make those corrections and see if you still have a problem.

OH MY GOSH I cannot possibly THANK YOU ENOUGH !!!!!!!!!!!!!!!!!!!!!!!!!

It was the lastname field single missing quote that was the issue...

Once I changed the line to:

Code:

DoCmd.OpenReport "r_Certificates", acViewPreview, , "[LName]= '" & ![LName] & "'", acHidden

I was able to press the button on the form, and it ran without requiring me to ok each certificate...it popped up and then closed and then the next certificate popped up and closed etc. etc.

I confirmed everything was correct in the designated folder.

However, I did have a debug window pop up for one the recipeints... his Last Name is D'Erente

I am assuming that the apostrophe is the issue... any advice on how to fix that ?

and THANK YOU from the bottom of my aching brain and heart... this has been such a stressful return to work, but you just made it all go away, so thank you thank you THANK YOU !!!!!!!

Oblio

**Gicu** · 08-19-2020, 10:55 AM

Replace the single quote with two double quotes:

Code:

DoCmd.OpenReport "r_Certificates", acViewPreview, , "[LName]= """ & ![LName] & """", acHidden

Cheers,

**CJ_London** · 08-19-2020, 11:21 AM

or replace the single quote with two single quotes

[LName]= '" & replace(![LName],"'","''") & "'"

**ssanfu** · 08-19-2020, 07:48 PM

I use a Function to double the apostrophes

Code:

Function ConvertQuotesSingle(InputVal)
   ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function

Call it like this

Code:

 DoCmd.OpenReport "r_Certificates", acViewPreview, , "[LName]= '" & ConvertQuotesSingle(![LName]) & "'", acHidden

**Oblio** · 08-20-2020, 07:20 PM

Thank you so much for your help !!!

**Oblio** · 08-20-2020, 07:21 PM

Amazing !!! Thanks so much for the help !!!

**Oblio** · 08-21-2020, 08:52 AM

Originally Posted by Oblio

I have a Certificate of Achievement report, based on a query. When I run the report, the certificate shows the person Nickname and Lastname and a text type ID# (called ON#...it is a unique text type ID).

When I export the report to individual pdfs, I have 2 problems:

1. The report prompts for each individual person, which is tedious
2. The exported report certificate is missing their name and ID# (It appears in the regular multi-page report, so not sure why this is happening)

The query is called: q_Certificates_To_Email

Code:

SELECT t_Restart_Teaching.[ON#], q_Contacts_Extended.FileAs, q_Contacts_Extended.[Last Name] & "_" & [Nickname] AS FullName, t_Restart_Teaching.[Emailed_Instructor_Good_To_Go?], t_Restart_Teaching.Date_Emailed, q_Contacts_Extended.[Home Email], t_Restart_Teaching.[Insurance Pd], t_Restart_Teaching.[Good To Go], t_Restart_Teaching.Notes, q_Contacts_Extended.Nickname, q_Contacts_Extended.[Last Name], t_Restart_Teaching.[Last Name] AS LName
FROM t_Restart_Teaching INNER JOIN q_Contacts_Extended ON t_Restart_Teaching.[ON#] = q_Contacts_Extended.[ON#]
WHERE (((t_Restart_Teaching.[Emailed_Instructor_Good_To_Go?])=Yes) AND ((t_Restart_Teaching.[Insurance Pd])="Yes") AND ((t_Restart_Teaching.[Good To Go])="Yes"))
ORDER BY q_Contacts_Extended.Nickname, t_Restart_Teaching.[Last Name];

The form f_Certificates, is bound to the query above. It has a command button named: cmd_Output_Certificates_To_Individual_PDFs

The on click event code is:

Code:

Private Sub cmd_Output_Certificates_To_Individual_PDFs_Click()    Dim rs As DAO.Recordset
    Dim sFolder As String
    Dim sFile As String
    Dim MyDB As DAO.Database
    
    'On Error GoTo Error_Handler


    sFolder = Application.CurrentProject.path & "\"


    'Set rs = CurrentDb.OpenRecordset("SELECT FullName,Nickname,LName FROM q_Certificates_To_Email", dbOpenSnapshot)
    Set MyDB = CurrentDb
    Set rs = MyDB.OpenRecordset("q_Certificates_To_Email")


    With rs
        .MoveFirst
        Do While Not .EOF
            DoCmd.OpenReport "r_Certificates", acViewPreview, , "[LName]=" & ![LName], acHidden
            'sFile = Nz(![FirstName], "") & "_" & Nz(![LName], "") & ".pdf"
            sFile = Nz(![Nickname], "") & "_" & Nz(![LName], "") & "_Restart_Certificate" & ".pdf"
            sFile = sFolder & sFile
            DoCmd.OutputTo acOutputReport, "r_Certificates", acFormatPDF, sFile, , , , acExportQualityPrint
            'If you wanted to create an e-mail and include an individual report, you would do so now
            DoCmd.Close acReport, "r_Certificates"
            .MoveNext
        Loop
    End With


    Application.FollowHyperlink sFolder    'Optional / Open the folder housing the files


Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Exit Sub


Error_Handler:
    If Err.Number <> 2501 Then    'Let's ignore user cancellation of this action!
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: cmd_Output_Certificates_To_Individual_PDFs" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler
End Sub

I need to email these certificates and I am way overdue having tried so many different variations on the internet. can anybody help me ? I can attach a db if required...

Thanks SO MUCH,

Oblio

Can I ask if anybody would know what to do about emailing the report, using Outlook ?

Thanks,

Oblio

**Gicu** · 08-21-2020, 09:51 AM

Add the following function to a standard module:

Code:

Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object
   
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    
    Set OutMail = OutApp.CreateItem(0)
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    If sBody <> "" Then OutMail.HTMLBody = sBody  'pr20111227
'    Debug.Print sAttachment
'   Stop
    OutMail.Attachments.Add (sAttachment)

    OutMail.Display  'Send | Display
'    OutMail.Inspector.Activate
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

In your code add this line:

Code:

oCmd.Close acReport, "r_Certificates"
 vcSendEmail_Outlook_With_Attachment sSubject, sEmailAddress, "", , sFile, sBody
            .MoveNext

You will need to define and populate the variables of course (sSubject, sEmailAddress,sBody).

Cheers,
Vlad

**Oblio** · 08-21-2020, 12:14 PM

AWESOME... going to work on this now !!!

If I have an outlook template I want to use, how would I include it versus a regular email ???

Say the Outlook Templates I use are in the folder "C:\My Outlook Templates"
and the template I want to use is called "Restart Process Completion Certificate"

Thanks again SO MUCH... I might not have to work all weekend sending these now if I can get it working

Oblio

**Gicu** · 08-21-2020, 03:30 PM

Hi, look into incorporating CreateItemFromTemplate method into my emailing function, something like:

Code:

Function vcSendEmail_Outlook_With_Attachment(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object
   
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    
    'Set OutMail = OutApp.CreateItem(0)
    Set OutMail = Application.CreateItemFromTemplate("C:\My Outlook Templates\Restart Process Completion Certificate.oft") 
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    If sBody <> "" Then OutMail.HTMLBody = sBody 
'    Debug.Print sAttachment
'   Stop
    OutMail.Attachments.Add (sAttachment)




    OutMail.Display  'Send | Display
'    OutMail.Inspector.Activate
    Set OutMail = Nothing
    Set OutApp = Nothing
End Function

Cheers,

**Oblio** · 08-23-2020, 04:14 AM

Hi,

Should this be DoCmd.Close ? and where do I add the DIM statements for "sEmailAddress" I see sBody and sSubject are in the standard module...is it correct to put it there as well, and should it be As String ? or as Hyperlink ?
oCmd.Close acReport, "r_Certificates"

**Gicu** · 08-23-2020, 09:57 AM

Yes, sorry for the typo.
As for the sEmailAddress you need to Dim it in your original sub Dcmd_Output_Certificates_To_Individual_PDFs_Click) . Does the q_Certificates_To_Email have a field for the email? If not can you add it? If you do you use it from the recordset:

Code:

Call vcSendEmail_Outlook_With_Attachment ("Enter your subject here if template doesn't provide it",![EmailAddress],,,sFile,"Enter body of message if template doesn't provide it")

Cheers,
Vlad

**Oblio** · 08-24-2020, 07:39 AM

Originally Posted by Gicu

Yes, sorry for the typo.
As for the sEmailAddress you need to Dim it in your original sub Dcmd_Output_Certificates_To_Individual_PDFs_Click) . Does the q_Certificates_To_Email have a field for the email? If not can you add it? If you do you use it from the recordset:

Code:

Call vcSendEmail_Outlook_With_Attachment ("Enter your subject here if template doesn't provide it",![EmailAddress],,,sFile,"Enter body of message if template doesn't provide it")

Cheers,
Vlad

Code:

Option Compare DatabaseOption Explicit


Private Sub cmd_Output_Certificates_To_Individual_PDFs_Click()
    Dim rs As DAO.Recordset
    Dim sFolder As String
    Dim sFile As String
    Dim sSubject As String
    Dim sBody As String
    Dim sEmailAddress As Hyperlink
    Dim MyDB As DAO.Database
    
    'On Error GoTo Error_Handler


    'sFolder = Application.CurrentProject.path & "\"
    sFolder = "C:\Users\FSESO\Desktop\Back to Work\0 Reconciliation\Certificates\Certificates To Be Sent" & "\"
    'sEmailAddress = [f_Certificates]![txt_Home_Email]
    Set MyDB = CurrentDb
    Set rs = MyDB.OpenRecordset("q_Certificates_To_Email")


    With rs
        .MoveFirst
        Do While Not .EOF
            'DoCmd.OpenReport "r_Certificates", acViewPreview, , "[LName]= '" & ![LName] & "'", acHidden
            DoCmd.OpenReport "r_Certificates", acViewPreview, , "[LName]= '" & Replace(![LName], "'", "''") & "'"
            sFile = Nz(![LName], "") & "_" & Nz(![Nickname], "") & "_Restart_Certificate" & ".pdf"
            sFile = sFolder & sFile
            DoCmd.OutputTo acOutputReport, "r_Certificates", acFormatPDF, sFile, , , , acExportQualityPrint
            'If you wanted to create an e-mail and include an individual report, you would do so now
            DoCmd.Close acReport, "r_Certificates"
            Call vcSendEmail_Outlook_With_Attachment("Enter your subject here if template does not provide it", ![Home Email], , , sFile, "Enter body of message if template does not provide it")
            .MoveNext
        Loop
    End With


    Application.FollowHyperlink sFolder    'Optional / Open the folder housing the files


Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Exit Sub


Error_Handler:
    If Err.Number <> 2501 Then    'Let's ignore user cancellation of this action!
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: cmd_Output_Certificates_To_Individual_PDFs" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler

I am receiving an Argument not optional on the

Code:

Call vcSendEmail_Outlook_With_Attachment("Enter your subject here if template does not provide it", ![Home Email], , , sFile, "Enter body of message if template does not provide it")

and should the Dim for the email address be As String or As Hyperlink ? and yes, the email address is provided in the query...

Thanks,

I just cannot seem to get it to work :/

Thanks SO MUCH for your help... I realllllly appreciate it !!!!!

Oblio

VBA Export to single pdf files: Names not appearing on the report being exported?

Thread Tools

VBA Export to single pdf files: Names not appearing on the report being exported?

Thank you so much !

Similar Threads

VBA code to export table names and their field names to MS Excel

export report to multiple PDF files and multiple directories using field names

Export Single Query to Multiple Excel Files

Export Report to Single PDF Pages

Select query return no records when exported (Excel or XML files)

Tags for this Thread

Posting Permissions