Optimizing the PDF Auto-populating & Outlook Sync Program (Full Script Included)


Image, courtesy of Pinterest artist Layza Rocha.

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 Sub

In 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 Explicit

This 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 Sub

You'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 = 0

Here, 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 With

As 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 With

Here 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 With

This section has not changed from the previous tutorial.

            successfulEmails = successfulEmails + 1
            
        End If
    
    Next

Once 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 = Nothing

Once 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 Sub

Much 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 Sub

Here 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!

58 views0 comments
WhatsApp Image 2021-06-11 at 17.35.25.jp

Hi,
I'm Raul Cintron

As a US Army Military Police Staff Sargeant, I work hard to ensure our Soldiers are performing to the best of their abilities. In my civilian life, I work hard to ensure computers perform fast and efficiently. If a task can be automated, why not? I work on Excel automation with VBA Macros, Web Development, I manage a SharePoint site, and know the fundamentals to assist in C# and Python. "Simplifying work and life."

Post Archive 

Tags