Optimizing the PDF Auto-populating & Outlook Sync Program (Full Script Included)
Image, courtesy of Pinterest artist Layza Rocha.
Here, we will optimize the program made in the previous tutorials and add better features to it. It is highly recommended for you to see the previous blogs if you haven't, "Using an Excel Database to Autofill PDFs Part 1 (Tutorial with a Military DA-4856 PDF Form)", "Using an Excel Database to Autofill PDFs Part 2 (Auto-populating Multiple DA Form 4856s at Once)", and "Send Auto-Populated Forms to hundreds of Emails in a click with Excel and VBA". If you've been following along our tutorials on the PDF Auto-populating Macro and Macro that sends those PDFs via Email, you'll know it works, but it is missing a few features. It simply runs and is susceptible to errors. If we cover everything in one blog, it would take a while, so I decided to separate it into parts so that each blog focuses on its specific area.
In this tutorial, we'll do things a bit differently. First, the updated Excel screenshot and updated code.
You'll notice that "Sheet 1" was renamed to "Soldiers" and we added a second sheet called "Options which we will see after we discuss the "Soldiers" sheet. We will be digging further into this sheet now.
Option Explicit
Sub fillCounsel()
On Error GoTo Stoppage
Dim soldierRank As String
Dim soldierLastName As String
Dim soldierFirstName As String
Dim soldierDate As String
Dim soldierOrganization As String
Dim counselorName As String
Dim counselorTitle As String
Dim soldierEmail As String
Dim soldierCC As String
Dim templatePath As String
Dim outputPath As String
Dim outputPDF As String
Dim fieldSoldierRank As Object
Dim fieldSoldierFullName As Object
Dim fieldSoldierDate As Object
Dim fieldSoldierOrganization As Object
Dim fieldCounselor As Object
Dim PdDoc As Object
Dim jso As Object
Dim objOutlook As Object
Dim objEmail As Object
Dim currentSoldierNum As Integer
Dim soldierCount As Integer
Dim successfulEmails As Integer
Dim soldierWorksheet As Worksheet
Dim optionWorksheet As Worksheet
Set soldierWorksheet = Sheets("Soldiers")
Set optionWorksheet = Sheets("Options")
soldierCount = soldierWorksheet.Cells(soldierWorksheet.Rows.Count, "A").End(xlUp).Row
templatePath = optionWorksheet.Range("A2").Value
outputPath = optionWorksheet.Range("B2").Value
successfulEmails = 0
For currentSoldierNum = 2 To soldierCount
With soldierWorksheet
soldierRank = .Range("A" & currentSoldierNum).Value
soldierLastName = .Range("B" & currentSoldierNum).Value
soldierFirstName = .Range("C" & currentSoldierNum).Value
soldierDate = .Range("D" & currentSoldierNum).Value
soldierOrganization = .Range("E" & currentSoldierNum).Value
counselorName = .Range("F" & currentSoldierNum).Value
counselorTitle = .Range("G" & currentSoldierNum).Value
soldierEmail = .Range("H" & currentSoldierNum).Value
soldierCC = .Range("I" & currentSoldierNum).Value
End With
outputPDF = outputPath & "\Initial Counesling for " & soldierRank & _
" " & soldierLastName & " " & soldierFirstName & ".pdf"
Set PdDoc = CreateObject("AcroExch.PDDoc")
If PdDoc.Open(templatePath) Then
Set jso = PdDoc.GetJSObject
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
With jso
Set fieldSoldierRank = .getField("form1[0].Page1[0].Rank_Grade[0]")
Set fieldSoldierFullName = .getField("form1[0].Page1[0].Name[0]")
Set fieldSoldierDate = .getField("form1[0].Page1[0].Date_Counseling[0]")
Set fieldSoldierOrganization = .getField("form1[0].Page1[0].Organization[0]")
Set fieldCounselor = .getField("form1[0].Page1[0].Name_Title_Counselor[0]")
End With
fieldSoldierRank.Value = soldierRank
fieldSoldierFullName.Value = soldierLastName & " " & soldierFirstName
fieldSoldierDate.Value = soldierDate
fieldSoldierOrganization.Value = soldierOrganization
fieldCounselor.Value = counselorName & " / " & counselorTitle
jso.SaveAs (outputPDF)
With objEmail
.BodyFormat = Olformatplain
.To = soldierEmail
.CC = soldierCC
.Subject = "Initial Counseling for " & soldierRank & " " & soldierLastName & " " & soldierFirstName
.body = "Attached is your initial counseling. " & _
"Read it and sign when possible. Return it once you have fully understood and complete."
.Attachments.Add (outputPDF)
.Send
End With
successfulEmails = successfulEmails + 1
End If
Next
MsgBox successfulEmails & " counseling forms filled out and sent via Email successfully."
Set objEmail = Nothing
Set objOutlook = Nothing
Exit Sub
Stoppage:
MsgBox "The program has encountered a problem. Please contact the administrator."
End SubIn this code, we see the same as in the previous tutorial, except completely modified to be more effective. I recommend saving it aside as we go through it in detail.
Option ExplicitThis is a special commend we can place on the top of the Module for a very special reason. As you initialize variables, you can either declare what data type they are or leave them so that VBA decides. This eliminates the option to let VBA decide. It may sound counterproductive, but it is the best practice. This way, YOU have to assign the data type to every variable or the program fails to run. The purpose is that now you have to know what the data type is and it eliminated the risk of VBA choosing the wrong data type, leading to huge bugs in bigger programs.
On Error GoTo Stoppage
Exit Sub
Stoppage:
MsgBox "The program has encountered a problem. Please contact the administrator."
End SubYou'll see the On Error GoTo command. This is what's called Error Handling. Should an error have happened before, it'll throw the user to the VBA view where they can see the code you've written. To put it nicely, it's ugly and unprofessional. With this code, at the start, the moment there is an error, the program will skip everything and go to where it says the name you give it, in this case, Stoppage. There, it will run the message box, indicating to the user to contact the admin. You can write anything here for the user to see in case of an error. Then it will end the Sub. Should it have no errors, it'll run normally and reach the Exit Sub line first, where it will end the Sub after completion. Note, the On Error should be before any possible error, and the path it goes to after the possible errors can occur.
Dim soldierRank As String
Dim soldierLastName As String
Dim soldierFirstName As String
Dim soldierDate As String
Dim soldierOrganization As String
Dim counselorName As String
Dim counselorTitle As String
Dim soldierEmail As String
Dim soldierCC As String
Dim templatePath As String
Dim outputPath As String
Dim outputPDF As String
Dim fieldSoldierRank As Object
Dim fieldSoldierFullName As Object
Dim fieldSoldierDate As Object
Dim fieldSoldierOrganization As Object
Dim fieldCounselor As Object
Dim PdDoc As Object
Dim jso As Object
Dim objOutlook As Object
Dim objEmail As Object
Dim currentSoldierNum As Integer
Dim soldierCount As Integer
Dim successfulEmails As Integer
Dim soldierWorksheet As Worksheet
Dim optionWorksheet As Worksheet
Set soldierWorksheet = Sheets("Soldiers")
Set optionWorksheet = Sheets("Options")Once the error handler is in place, we'll start declaring all variables to be used in the module. The variables initialized here are available for the entire module, not other modules invoked unless passing on by parameter (We'll discuss this in a later blog as this project will not require it.) Most variables are from the previous tutorial, but there are new additions as well as changes. First, all of them are declared with the correct data type. VBA can not decide on this program thanks to Option Explicit. It is good practice to declare needed variables early on unless required to do otherwise, as well as keeping them organized. The organization can differ, based on what they are to be used for, or data types. Strings are meant to store a collection of characters, while Integer is meant to store a whole number from -32,768 to 32,767. For bigger or smaller numbers, you'd need a Long, but we are not expecting that many PDFs to be output in this program. An Object will store a procedure or a collection of functions, in this case, they will invoke the procedures to interact with PDFs and Outlook. Worksheet is simply a data type to store a worksheet so you can refer to it by variable instead of having to write down the Sheets command. Worksheets and Objects have to be initialized with Set.
soldierCount = soldierWorksheet.Cells(soldierWorksheet.Rows.Count, "A").End(xlUp).Row
templatePath = optionWorksheet.Range("A2").Value
outputPath = optionWorksheet.Range("B2").Value
successfulEmails = 0Here, we are initializing some variables early on to be used throughout the program. The soldierCount variable will count all of the rows in column A that contain values. This will be useful as we will tell the program to start at row 2 and stop at the last row with content, making it more self-sufficient. Add as many Soldiers as you want, the program adapts. The templatePath and outputPath variables will now read from a specific cell in the Options sheet, nullifying the need to alter the code. The successfulEmails variable will be used as a confirmation of how many Emails were sent based on how many iterations occurred. We will see these variables in action soon.
For currentSoldierNum = 2 To soldierCount
With soldierWorksheet
soldierRank = .Range("A" & currentSoldierNum).Value
soldierLastName = .Range("B" & currentSoldierNum).Value
soldierFirstName = .Range("C" & currentSoldierNum).Value
soldierDate = .Range("D" & currentSoldierNum).Value
soldierOrganization = .Range("E" & currentSoldierNum).Value
counselorName = .Range("F" & currentSoldierNum).Value
counselorTitle = .Range("G" & currentSoldierNum).Value
soldierEmail = .Range("H" & currentSoldierNum).Value
soldierCC = .Range("I" & currentSoldierNum).Value
End WithAs explained before, the For line will initialize the currentSoldierNum variable in iterations, starting with 2 to signify the second row, to soldierCount which will be the total number of rows with value. In this case, it'll be 2 to 3. With the worksheet assigned to the soldierWorksheet variable, it'll now be easier to use With to initialize the Soldier and counselor variables during each iteration with the information of the current iteration Soldier. Finally, we just end the With.
outputPDF = outputPath & "\Initial Counesling for " & soldierRank & _
" " & soldierLastName & " " & soldierFirstName & ".pdf"
Set PdDoc = CreateObject("AcroExch.PDDoc")This part seems complex but is rather simple. The outputPDF is where it will store the output PDF and under what file name. outputPath follows the path you placed in the Options sheet, followed by the file name itself which will include rank, name, and the file extension. PdDoc will simply be the object variable that interacts with the PDF that is coming soon.
If PdDoc.Open(templatePath) Then
Set jso = PdDoc.GetJSObject
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
With jso
Set fieldSoldierRank = .getField("form1[0].Page1[0].Rank_Grade[0]")
Set fieldSoldierFullName = .getField("form1[0].Page1[0].Name[0]")
Set fieldSoldierDate = .getField("form1[0].Page1[0].Date_Counseling[0]")
Set fieldSoldierOrganization = .getField("form1[0].Page1[0].Organization[0]")
Set fieldCounselor = .getField("form1[0].Page1[0].Name_Title_Counselor[0]")
End WithHere starts the actual interaction with both the PDF and Outlook. The If states that if the object can interact and open the templatePath, it will return a True value, leading the program to execute the lines inside the If. Here, it will set various objects to include jso, which will get the fillable fields from the PDF, the pdfOutlook which will start the Outlook interaction, and finally, the objEmail which will interact with the Email section of Outlook.
After the objects have been set, with jso, we will save the fillable fields to variables in order to interact with them successfully. Again, every With has to be terminated with an End With.
fieldSoldierRank.Value = soldierRank
fieldSoldierFullName.Value = soldierLastName & " " & soldierFirstName
fieldSoldierDate.Value = soldierDate
fieldSoldierOrganization.Value = soldierOrganization
fieldCounselor.Value = counselorName & " / " & counselorTitle
jso.SaveAs (outputPDF)This section is one of the more simple sections. It simply lets the interaction occur, where it will change the values of the PDF fillable fields with the values from the Excel variables and then save it into the outputPDF file location selected earlier.
With objEmail
.BodyFormat = Olformatplain
.To = soldierEmail
.CC = soldierCC
.Subject = "Initial Counseling for " & soldierRank & " " & soldierLastName & " " & soldierFirstName
.body = "Attached is your initial counseling. " & _
"Read it and sign when possible. Return it once you have fully understood and complete."
.Attachments.Add (outputPDF)
.Send
End WithThis section has not changed from the previous tutorial.
successfulEmails = successfulEmails + 1
End If
NextOnce the Email has been sent, the program adds the value of 1 to the sucessfulEmails variable and then we End If, and start with the Next iteration.
MsgBox successfulEmails & " counseling forms filled out and sent via Email successfully."
Set objEmail = Nothing
Set objOutlook = NothingOnce all of the iterations are complete, the user will receive a message box indicating the amount of counseling sent. Then the objects that interact with the Email will be set with no value in order to terminate the interaction successfully.
Success:
End Sub
Stoppage:
MsgBox "The program has encountered a problem. Please contact the administrator."
End Sub Again, we have the last parts of the error handling procedure before we end the Sub. And that's it for the first sheet names "Soldiers." Now we will head to the next 2 short Macros that allow us to set the file and folder path in the "Options" sheet.
Here is the screenshot that will help feel 2 important variables in the previous macro. You could copy and paste the file path and folder path, but why not make it more automated and efficient which buttons and open browser windows and let you simply select the file and folder? Easier, more please, and faster for the user in the long run. Again, I will give you the Macro now for the file selection browser.
Sub browsePDFTemplate()
On Error GoTo Stoppage
Dim SelectedFile As Variant
With Application.FileDialog(msoFileDialogFilePicker)
.ButtonName = "Select"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "PDF File", "*.pdf", 1
.Title = "Select DA form 4856 file with template."
.Show
For Each SelectedFile In .SelectedItems
Sheets("Options").Range("A2").Value = SelectedFile
Next
End With
Exit Sub
Stoppage:
MsgBox "The file browser has failed to work correctly. Returning to Excel."
End SubMuch easier than the previous one. Again, we include the same Error Handling with a different message box at the end to identify that the file browser failed. Again, you can write any message you want here. We'll want to declare a variable as a variant that will hold the selected item so that it can pass it to Excel later for storage. The With will allow you to set the parameters for the browser window such as button name, how many items can be selected, filters such as what type of files are acceptable, and a nice title for the top of the window. Once that is done, the selected item will store it in the cell mentioned. Simple. Here is an example of how it would look under these parameters.
Personal computer information is hidden for security reasons, but you get the idea. Simply browse for the PDF template, and it will be saved into Excel. Same for the folder browser which the code looks like so.
Sub browsePDFOutput()
On Error GoTo Stoppage
Dim selectedFolder As String
With Application.FileDialog(msoFileDialogFolderPicker)
.ButtonName = "Select"
If .Show = -1 Then
selectedFolder = .SelectedItems(1)
End If
End With
If selectedFolder <> "" Then
Sheets("Options").Range("B2").Value = selectedFolder
End If
Exit Sub
Stoppage:
MsgBox "The folder browser has failed to work correctly. Returning to Excel."
End SubHere we have a similar error handling procedure as well. Aside from that, we declare the variable that will hold the selected folder this time around. Notice the With command has a slightly different procedure this time around. This time, it is not going to select a file, but a folder instead. With the parameters above, here is what you'd have.
You'll notice they are similar but contain different a purpose. We now simply assign the macros to their appropriate buttons on the Excel, and with a few simple clicks, you have the correct template to choose and the folder location of where to store the output.
"Wow that was interesting. Finally complete!"
With that, we've completed an entire program capable of creating and organizing filled PDF forms, as well as sending each PDF to its assigned Soldier. This may seem like a huge project, but if this is something you have to do constantly, note that you can do this project in a single day, and then with a click of a button make it work for the rest of its life.
"What have we just worked on in terms of optimization?"
We did some basic error handling that will help the user have a better experience. We also talked about the organization of declared variables and when best to initialize them in their life cycle. We also mentioned initializing variables early on for more effectiveness in future use. The benefits of With have been mentions and how best to make the program run more user-friendly without having to access the VBA codes.
"I hope you enjoy!"
If this article has helped you in any way, or you read through it and are interested in even more VBA, be sure to let me know through a comment or direct contact. I'll be more than happy to help! Also, be sure to like, subscribe, and share! If the knowledge has been useful for you, it may be very useful to a lot of others as well!
