Receive new articles directly in your inbox!! Register your email address

How to loop through a list of customers' information using VBA to generate individual PDF files

Having customers' data in an excel sheet is very common.

While it's convenient for reporting, it's not the best for personalized communication. This post will show you how to effortlessly turn that data into individualized, polished PDFs for each customer, making your communications stand out. It's like having your own personalized PDF generator for your customer list.

The code will loop through an Excel worksheet where each row reports the customer information. A PDF file for the customer will be created and saved in a specified location.

It is then possible to email to customers the PDF as explained in the post How to send emails with attachments to multiple recipients

Starting point is an excel workbook with a first sheet called "Customers" in which the customer information is reported.

The "Customers" sheets looks like this

Note that the customer data is reported on only one row; in other words customer data is not duplicated on different rows

The code will copy the data reported on each row in the sheet called Letter.

The Letter sheet will be compiled as follows

Cells C1 will contain the customer's email address
Cell C2 will contain the customer's Name and Surname
Cell G4 will contain the customer's order number

Once all the above fields will be completed the sheet Letter will be converted to PDF and saved under the specified path

To convert the Letter sheet to PDF we will be using the ExportAsFixedFormat method

The argument of this method are the following:

Expression: indicating which sheet has to be converted to PDF
Type: the type of file to which we want to export the sheet The choice is betweeen PDF or XPS format
Filename: the name the new PDF file will have. The full file path can be reported. If only the name is reported the PDF file will be saved in the current default folder
Quality: choose between standard or minimum
Includedocproperties: indicate if you want or not the document properties should be included or not in the resulting file
Ignoreprintarea: determine if the PDF file should be published according to the set print area. It is recommended to report the text within the pagebreaks of the sheet Letter and set this parameter to False. This will allow to control that the text reported in the sheet will be published to PDF
From: First page to be exported to PDF. If nothing is specified the download will start from the first page
To: Last page to be exported to PDF. If nothing is specified the download will end at the last page
Openafterpublish: Indicates if the PDF file needs to be opened once ready. As the code is looping through a number of records os preferable to not activate this option

The complete code is reported below

Sub GeneratePDFs()

Dim ws As Worksheet

Dim lastRow As Long, i As Long

' Specify the sheet containing customer information

Set ws = ThisWorkbook.Sheets("Customers")

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = 2 To lastRow ' Start from row 2 as headers are in row 1

' Retrieve customer information

Dim customerName As String

customerName = ws.Cells(i, 1).Value

customerSurname = ws.Cells(i, 2).Value

customerEmail = ws.Cells(i, 3).Value

customerOrder = ws.Cells(i, 4).Value

Sheets("Letter").select

Range("c1").value = customerEmail

range("c2").value = customerName & " " & customerSurname

range("g4"),value = customerOrder

ActiveSheet.ExportAsFixedFormat _

Type:=xlTypePDF, _

' File is saved in the users folder and is given the name as the customer surname

Filename:= "C:\Users\"& customerSurname, _

Quality:=xlQualityStandard, _

IncludeDocProperties:=False, _

'The PDF file is going to be created with the same borders of the sheet's print area

IgnorePrintAreas:=False, _

From:=1, _

To:=5, _

OpenAfterPublish:= False

Sheets("Customers").select

Next i

End Sub

Page updated

Google Sites

Report abuse