Send Auto-Populated Forms to hundreds of Emails in a click with Excel and VBA

With the other two tutorials complete, we now take it up a huge notch.

"Let's go over possible scenarios."

If you run a schoolhouse and have to send reports to parents or the students, this could be a huge help. Let the Excel auto-populate the form you need, then let it run through a list of Emails to send each one to the right person. Maybe you are a service member, or run a company and need a document to reach each of your employees, however, each Email must be tailored for each employee. There are many other scenarios where this could be useful and some of you may think, "Excel can't do all of that. Or can it?" In this tutorial, we'll go through the process and show you how it not only is possible but is a lot simpler than it sounds.

If you did not view "Using an Excel Database to Autofill PDFs Part 1 (Tutorial with a Military DA-4856 PDF Form)" or "Using an Excel Database to Autofill PDFs Part 2 (Auto-populating Multiple DA Form 4856s at Once)" I highly recommend you view them first to fully understand the following steps.

"Let's begin where we left off in part 2."

Now that we have an excel that is capable of creating hundreds of PDFs tailored from a database in Excel, we want those PDFs to get to those people. Obviously we'll need more columns. In this example, we'll want the Soldier's Email as well as our own so that we can get a CC of the Email to ensure it worked correctly. For this example, we will also lower the number of rows from 10 to only 2. We do not want to receive 10 Email notifications during a test now do we?

In the examples, you'll notice the columns have been created and populated (hidden for security reasons.) Then you'll see the same Sub we worked on previously with the new additions. Two more variables have been initiated and then declared to save the values for each iteration. Also, the For has changed from "2 to 11" to "2 to 3".

The next step would be to begin working on the Email, but before we do so, we need to verify two details. Do you have Outlook installed? If not, install it and open it with your credentials. Ensure it is live.

"How can VBA communicate with Outlook? "

VBA has a list of references in order to communicate with many programs. The problem will be that if it is not told to look into those references, the objects to communicate with Outlook will not be available. On top, go to Tools, then References. There, you'll want to look for "Microsoft Outlook 16.0 Object Library" and check it. Now VBA has the references available.

"We have everything in order. Let's make it happen."

With everything set, let's add the Objects in order to make that communication happen. In the example below, you'll see the new additions.

First, we initiate and declare an object that communicated with Outlook. Then we initiate and declare an object deeper that communicates specifically with the Email section of Outlook. Also, note that this is done within the if portion of the code. That way, with each PDF, each iteration of the For will be able to successfully collect the new variables. Now, let's look at the Email code itself. It's a lot easier than it sounds.

After the PDF has been saved, you'll want to have the Macro send it, so it only makes sense to write this portion of code after that. The With command lets you run multiple lines of code without having to reference the variable or object that follows. Consider the first line as such, "objEmail.BodyFormat = Olformatplain".

It's easier to use With and more understandable to a programmer. Let's go over each line.

.BodyFormat is just that. The format to be used for the body of the Email. We'll be using a standard string, not HTML, so plain would suffice.

.To is the receiver of the Email. For each iteration of For, the variable soldierEmail will change to the next Soldier's Email.

.CC is simply to receive a Carbon Copy of the Email to ensure that they were sent successfully. This part is optional and can be discarded once the Macro has been tested thoroughly.

.Subject is exactly that. The subject should identify with the receiver to make it feel more legit, as a generic subject might be ignored for Spam or Junk. You'll notice the subject has a concatenation that includes the Soldier's own rank, and full name.

.Body can be a bit more generic as everyone is receiving the same type of document.

.Attachments.Add is where the PDF that was just created will be attached, as it pertained to the person in the current iteration.

.Send finalizes the Email and sends it through your Outlook under the current account Email.

Finally, we end the With as we are done referencing objEmail. Placing a message box helps you know that everything was done successfully, though if you have notifications active, you'll see shortly after testing the CC Emails. Once that's done, we'll want to do just a few final lines of code to terminate the object that is communicating with Outlook.

This clears the objects and then terminates the Macro.

"With that, the Macro is complete. Congratulations programmer!"

If you've made it this far, that means this just made your life easier in the future. Give it a test run. If successful, there are still many things you can do with this program to make it run more efficiently and useful. For example, there are ways to make it so that it knows automatically how many rows it is going to have, therefore automatically adjusting its iterations. Error handling is a must as well if you plan to keep playing around with VBA. There is so much more that it can do. We have only scraped the top of its limits. Tailor it to your needs, and if you need assistance be sure to get in contact through the comments or directly through the site. If you found this useful or would like to request a specific tutorial on VBA, let me know, and don't forget the "Like" and "Follow" buttons!

Here is the script. Enjoy!

Sub fillCounsel()

    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 AcroApp, PdDoc
    Dim jso As Object

    For i = 2 To 3
    
        soldierRank = Sheets("Sheet1").Range("A" & i).Value
        soldierLastName = Sheets("Sheet1").Range("B" & i).Value
        soldierFirstName = Sheets("Sheet1").Range("C" & i).Value
        soldierDate = Sheets("Sheet1").Range("D" & i).Value
        soldierOrganization = Sheets("Sheet1").Range("E" & i).Value
        counselorName = Sheets("Sheet1").Range("F" & i).Value
        counselorTitle = Sheets("Sheet1").Range("G" & i).Value
        soldierEmail = Sheets("Sheet1").Range("H" & i).Value
        soldierCC = Sheets("Sheet1").Range("I" & i).Value
        
        'templatePath is YOUR template path.
        templatePath = "C:\YOUR LOCATION\Original DA 4856.pdf"
        
        'outputPath is where YOU want it to save the updated PDF.
        outputPath = "C:\YOUR LOCATION\Initial Counesling for " & _
        soldierRank & " " & soldierLastName & " " & soldierFirstName & ".pdf"
        
        Set PdDoc = CreateObject("AcroExch.PDDoc")
    
        If PdDoc.Open(templatePath) Then
            
            Dim objOutlook As Object
            Set objOutlook = CreateObject("Outlook.Application")
            
            Dim objEmail As Object
            Set objEmail = objOutlook.CreateItem(olMailItem)
    
            Dim fieldSoldierRank As Object
            Dim fieldSoldierFullName As Object
            Dim fieldSoldierDate As Object
            Dim fieldSoldierOrganization As Object
            Dim fieldCounselor As Object
            
            Set jso = PdDoc.GetJSObject
            
            Set fieldSoldierFullName = jso.getField("form1[0].Page1[0].Name[0]")
            Set fieldSoldierRank = jso.getField("form1[0].Page1[0].Rank_Grade[0]")
            Set fieldSoldierDate = jso.getField("form1[0].Page1[0].Date_Counseling[0]")
            Set fieldSoldierOrganization = jso.getField("form1[0].Page1[0].Organization[0]")
            Set fieldCounselor = jso.getField("form1[0].Page1[0].Name_Title_Counselor[0]")
    
            fieldSoldierRank.Value = soldierRank
            fieldSoldierFullName.Value = soldierLastName & " " & soldierFirstName
            fieldSoldierDate.Value = soldierDate
            fieldSoldierOrganization.Value = soldierOrganization
            fieldCounselor.Value = counselorName & " / " & counselorTitle
            
            jso.SaveAs (outputPath)
            
            With objEmail
            
                'Format of the mail
                .BodyFormat = Olformatplain
        
                'To the person who will receive the Email
                .To = soldierEmail
                
                'Send a CC to yourself to ensure it sent successfully
                .CC = soldierCC
        
                'Subject of Email
                .Subject = "Initial Counseling for " & soldierRank & " " & soldierLastName & " " & soldierFirstName
                
                'Body of the Email
                .body = "Attached is your initial counseling. " & _
                    "Read it and sign when possible. Return it once you have fully understood and complete."
                
                'To add the PDF as an attachment
                .Attachments.Add (outputPath)
                
                'Sends the mail
                .Send
               
            End With
                
            MsgBox "Counseling Form filled out and sent via Email successfully."
            
        End If
    
    Next
    
    Set objEmail = Nothing
    Set objOutlook = Nothing
    
End Sub