NOTE: This example was created based on outdated versions of the Lob API and legacy Node SDK. Please see the latest Lob API documentation for the most up-to-date information on integrating with Lob.
Here at Lob we see our Letter API used in a lot of interesting ways. Since the most common use cases we see are companies wanting to send invoices, bills, or payment reminders to clients, today I’ll walk you through creating customized invoices. So I'll show you how to take a CSV spreadsheet that contains clients' bill information, and perform a mail merge that will send each one a customized invoice.
This tutorial uses Lob’s Letter API and is written in Node.js. The CSV file contains a client’s name, mailing address, and bill amount as the input. I will show you how take this CSV file and perform a mail merge that will print and mail a customized invoice letter to each client about their outstanding bill.
NOTE: If you would like to just grab the source code for this tutorial, head over to our Node.js GitHub repository and peek inside the examples folder.
Here is a preview of the letter we will be sending out. This invoice template is built using HTML/CSS so you can easily customize the logo, text, and colors to fit your needs. For more information on templates (including code samples and links to examples), check out the docs page.
I’m assuming you are starting with a empty project directory so the first thing you will want to do is initialize the project with NPM to create our package.json file. If you already have a package.json setup you can skip this step.
npm init
The next step will be to install the Node module dependencies we will be using.
npm install lob --save
npm install json-2-csv --save
npm install moment --save
npm install csv-parse --save
Undoubtedly your real world spreadsheet will look a little different than our example CSV. The important thing to note here is that we have have column headers in our CSV file that will become object property names when we use the csv-parse module. Just make sure that you change the code to reflect the column names in your CSV file.
For the purposes of this example, let's create a file called input.csv and paste the following text into it.
name,amount,address1,address2,city,state,zip
Kelly Jones,185.54,185 Berry St,,San Francisco,CA,94107
Margaret Smith,387.45,6575 W Rialto,,fresno,CA,93723
Jess Smith,19.87,537 Fillmore Street, Apt #2,San Francicso,ca,94117
Michael Thruman,347.21,1625 Post St,,San Francisco,CA,94115
Gerald Merritt,278.4,333 Post St,,San Francisco,CA,94108
Linda Anderson,425.4,655 Fake Address,Fake Apartment,Not a City,CA,06475-1246
John Travolta,175.84,111 NotAnAddress,,San Francisco,CA,94107
I’ve purposely included some invalid addresses in this example file so that we can check the validity of an address before sending invoices. This is a good practice before sending mail with Lob.
One of the coolest features of the Letters API is the ability to use HTML/CSS as a template. This allows us to create template variables inside the HTML which will be replaced with data from our spreadsheet during the mail merge. To create a template variable called name, simply surround the variable with {{ }} brackets like this:
<p>Dear {{name}},</p>
I’ve put together a ready-made template for this example so create a file called letter_template.html with the following code. Notice the {{name}}, {{amount}}, and {{date}} variables that we will replace with data from our spreadsheet.
<!doctype html>
<html></html>
<head></head>
<title></title>
<style></style>
@font-face {
font-family: 'Loved by the King';
font-style: normal;
font-weight: 400;
src: url('https://s3-us-west-2.amazonaws.com/lob-assets/LovedbytheKing.ttf') format('truetype');
}
*, *:before, *:after {
-webkit-box-sizing: border-box;
-moz-box-sizing: border-box;
box-sizing: border-box;
}
body {
width: 8.5in;
height: 11in;
margin: 0;
padding: 0;
font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
}
.page {
page-break-after: always;
}
.page-content {
position: relative;
width: 7in;
height: 10.625in;
left: 0.75in;
top: 0.1875in;
}
#logo {
position: absolute;
right: 0;
}
.wrapper {
position: absolute;
top: 2.75in;
}
.signature {
font-family: 'Loved by the King';
font-size: 45px;
}
<body></body>
<div class="page"></div>
<div class="page-content"></div>
<img id="logo" src="https://s3-us-west-2.amazonaws.com/lob-assets/deluxe-logo.png" width="150px">
<div class="wrapper"></div>
<p>{{date}}</p>
<p>Dear {{name}},</p>
<p>This is a friendly reminder that your account with us appears as past due. Our records indicate that you have</p>
a total outstanding balance of <b>${{amountDue}}</b>.<p></p>
<p>We would much appreciate if you could let us know the status of this payment. Please do not hesitate to call</p>
us if you have any questions about the balance due on your account. If you have already sent us your payment,
please disregard this reminder.<p></p>
<p>Thank you very much for your attention to this matter and your continued business.</p>
<p>Sincerely,</p>
<p class="signature">Deluxe</p>
Now that we have our input file and our Template file in place, we can begin writing the Node.js script that will perform the mail merge. Create a file called index.js inside your project directory and let's get started.
The first thing we need to do is include the node modules we installed earlier:
var converter = require('json-2-csv');
var fs = require('fs');
var moment = require('moment');
var parse = require('csv-parse');
var Lob = require('lob')('test_0000000000000000000000');
Notice that we are passing our Lob API key when including the Lob Node Module. If you have already registered you can find your test API key here.
Next we will open up our input.csv and letter_template.html files for reading:
var inputFile = fs.createReadStream(__dirname + '/input.csv');
var letterTemplate = fs.readFileSync(__dirname + '/letter_template.html').toString();
We will also create two CSV files called success.csv and errors.csv that will contain the results of our mail merge.
var successFd = fs.openSync(__dirname + '/success.csv', 'w');
var errorFd = fs.openSync(__dirname + '/error.csv', 'w');
Include your company address that will serve as the return address for each letter.
var companyInfo = {
name: 'Deluxe Virgina Realty',
address_line1: '185 Berry St.',
address_line2: 'Ste 170',
address_city: 'San Francsisco',
address_state: 'CA',
address_zip: 94107,
address_country: 'US'
};
Now it’s time to parse input.csv and send an invoice to each client. We are using the csv-parse module which will return an array containing objects for each row in the spreadsheet. Once we have this array we will iterate through it to perform our mail merge.
Notice we use the .pipe method on our input file to direct the file stream to our csv-parser object. This is the most performant way when working with potentially large CSV files.
Also notice we are passing the {columns: true} config parameter specifying that we have column headings in our CSV file.
var parser = parse({ columns: true }, function (err, data) {
if (err) {
return console.log(err);
}
data.forEach(function (client) {
/* More Code to Come */
});
});
inputFile.pipe(parser);
The following code should go inside our data.forEach loop which makes available a single row of data from the CSV file as the client variable.
The first thing we want to do is extract the client name and bill amount into the name and amount variables as well as create and address object for their mailing address. Again, notice that the client.property_name is the same as our column headers from our CSV.
var name = client.name;
var amount = parseFloat(client.amount).toFixed(2);
var address = {
address_line1: client.address1,
address_line2: client.address2,
address_city: client.city,
address_state: client.state,
address_zip: client.zip,
address_country: 'US'
};
Now that we have this data, the plan is to first check if the mailing address is valid using Lob’s Address Verification Service. If the address is valid we will create the letter and add the client to our success.csv file. If the address is invalid we will add the client to our errors.csv file.
Here is the code that verifies the mailing address and creates the letter. I’ll break down the important parts below. It will also help to look at the letter documentation for a better understanding of each data field we are passing to the API
Lob.verification.verify(address)
.then(function (verifiedAddress) {
return Lob.letters.create({
description: 'Automated Past Due Bill for ' + name,
to: {
name: name,
address_line1: verifiedAddress.address.address_line1,
address_line2: verifiedAddress.address.address_line12,
address_city: verifiedAddress.address.address_city,
address_state: verifiedAddress.address.address_state,
address_zip: verifiedAddress.address.address_zip,
address_country: 'US'
},
from: companyInfo,
file: letterTemplate,
data: {
date: moment().format('LL'),
name: name,
amountDue: amount
},
color: true
});
})
Make sure to take a close look at how we are passing our template file and template variables to the API.
file: letterTemplate,
data: {
date: moment().format('LL'),
name: name,
amountDue: amount
},
All we need to do is pass our HTML template to the file parameter and then pass our template variables as an object to the data parameter. The Letter API will automatically handle merging the data to create each customized letter.
If the address passed verification and the letter was successfully sent, we will want to add the client to our success.csv file. Notice that our promise will return the successful letter object .then(function (letter). We can use this object to see information about the letter we just created like the preview URL and the letter ID. We will want to include this information in our success.csv for reference and also print a success message to the terminal screen.
.then(function (letter)
{ console.log('Successfully sent a letter to ' + client.name); client.letter_id = letter.id; client.letter_url = letter.url; converter.json2csv(client, function (err, csv) { if (err) { throw err; } fs.write(successFd, csv); }, { PREPEND_HEADER: false }); })
If the address passed failed verification and the letter was not successfully sent, we will also want to add the client to our errors.csv file. We can do this by catching one of our failed promises along with the error message.
.catch(function (error) {
console.log('Could not send letter to ' + client.name);
converter.json2csv(client, function (err, csv) {
if (err) {
throw err;
}
fs.write(errorFd, csv);
}, { PREPEND_HEADER: false });
});
Everything should be in place to run our mail merge now and see if things worked. From your terminal you should be able to run
node index.js
If everything is working you should see the following output on your terminal screen.
Also, check to make sure that your output files were created and reflect the correct information.
success.csv
errors.csv
Finally you should login in to the Dashboard and check to see that your letter shows up.
Hopefully this example can get you started sending letters with Lob. If you have any questions or suggestions for the tutorial please don't hesitate to reach out for help.
