I have been using a script from Christos (not sure if he is on here or anyone knows him). The script fills out a PDF with info from sheet and saves PDF. I have it working fine with my info but I want to be able to have it also email the, getting the email address from a field on pdf or sheet and email them as they are saved. Not sure if this is possible... Maybe seperate script that gets email address from pdf, attaches and email to that address. Any help would be greatly appreciated. Write part of the script below.
VBA Code:
Sub WritePDFForms()
'Declaring the necessary variables.
Dim strPDFPath As String
Dim strFieldNames(1 To 11) As String
Dim i As Long
Dim j As Integer
Dim LastRow As Long
Dim objAcroApp As Object
Dim objAcroAVDoc As Object
Dim objAcroPDDoc As Object
Dim objJSO As Object
Dim strPDFOutPath As String
'Disable screen flickering.
Application.ScreenUpdating = False
'Specify the path of the sample PDF form.
'Full path example:
'strPDFPath = "C:\Users\Christos\Desktop\Test Form.pdf"
'Using workbook path:
strPDFPath = ThisWorkbook.Path & "\" & "Test Form.pdf"
'Set the required field names in the PDF form.
strFieldNames(1) = "First Name"
strFieldNames(2) = "Last Name"
strFieldNames(3) = "Street Address"
strFieldNames(4) = "City"
strFieldNames(5) = "State"
strFieldNames(6) = "Zip Code"
strFieldNames(7) = "Country"
strFieldNames(8) = "E-mail"
strFieldNames(9) = "Phone Number"
strFieldNames(10) = "Type Of Registration"
strFieldNames(11) = "Previous Attendee"
'Find the last row of data in sheet Write.
With shWrite
.Activate
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
'Loop through all rows of sheet Write and use the data to fill the PDF form.
For i = 4 To LastRow
On Error Resume Next
'Initialize Acrobat by creating the App object.
Set objAcroApp = CreateObject("AcroExch.App")
'Check if the object was created.
If Err.Number <> 0 Then
MsgBox "Could not create the App object!", vbCritical, "Object error"
'Release the object and exit.
Set objAcroApp = Nothing
Exit Sub
End If
'Create the AVDoc object.
Set objAcroAVDoc = CreateObject("AcroExch.AVDoc")
'Check if the object was created.
If Err.Number <> 0 Then
MsgBox "Could not create the AVDoc object!", vbCritical, "Object error"
'Release the objects and exit.
Set objAcroAVDoc = Nothing
Set objAcroApp = Nothing
Exit Sub
End If
On Error GoTo 0
'Open the PDF file.
If objAcroAVDoc.Open(strPDFPath, "") = True Then
'Set the PDDoc object.
Set objAcroPDDoc = objAcroAVDoc.GetPDDoc
'Set the JS Object - Java Script Object.
Set objJSO = objAcroPDDoc.GetJSObject
On Error Resume Next
'Fill the form fields.
For j = 1 To 10
objJSO.GetField(strFieldNames(j)).Value = CStr(shWrite.Cells(i, j + 1).Value)
If Err.Number <> 0 Then
'Close the form without saving the changes.
objAcroAVDoc.Close True
'Close the Acrobat application.
objAcroApp.Exit
'Inform the user about the error.
MsgBox "The field """ & strFieldNames(j) & """ could not be found!", vbCritical, "Field error"
'Release the objects and exit.
Set objJSO = Nothing
Set objAcroPDDoc = Nothing
Set objAcroAVDoc = Nothing
Set objAcroApp = Nothing
Exit Sub
End If
Next j
'Fill the checkbox field.
If shWrite.Cells(i, j + 1).Value = "True" Then
objJSO.GetField(strFieldNames(11)).Value = "Yes"
End If
On Error GoTo 0
'Create the output path, which will be like C:\Users\Christos\Desktop\Forms\01) First Name Last Name.pdf.
With shWrite
If i - 3 < 10 Then
strPDFOutPath = ThisWorkbook.Path & "\Forms\0" & i - 3 & ") " & .Cells(i, 2).Value & " " & .Cells(i, 3).Value & ".pdf"
Else
strPDFOutPath = ThisWorkbook.Path & "\Forms\" & i - 3 & ") " & .Cells(i, 2).Value & " " & .Cells(i, 3).Value & ".pdf"
End If
End With
'Save the form as new PDF file.
objAcroPDDoc.Save 1, strPDFOutPath
'Close the form without saving the changes.
objAcroAVDoc.Close True
'Close the Acrobat application.
objAcroApp.Exit
'Release the objects.
Set objJSO = Nothing
Set objAcroPDDoc = Nothing
Set objAcroAVDoc = Nothing
Set objAcroApp = Nothing
Else
MsgBox "Could not open the file!", vbCritical, "File error"
'Close the Acrobat application.
objAcroApp.Exit
'Release the objects and exit.
Set objAcroAVDoc = Nothing
Set objAcroApp = Nothing
Exit Sub
End If
Next i
'Enable the screen.
Application.ScreenUpdating = True
'Inform the user that forms were filled.
MsgBox "All forms were created successfully!", vbInformation, "Finished"
End Sub
