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.Page1.Name") Set fieldSoldierRank = jso.getField("form1.Page1.Rank_Grade") Set fieldSoldierDate = jso.getField("form1.Page1.Date_Counseling") Set fieldSoldierOrganization = jso.getField("form1.Page1.Organization") Set fieldCounselor = jso.getField("form1.Page1.Name_Title_Counselor") 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