How to mail-merge Bitcoin Cash Private Key QR codes for mass giveaways
(Note: This article is free, without any paid content. If you wish, you may donate any amount you choose, and it will all be used to promote Bitcoin Cash in Belize.)
On Saturday 27th of January 2018, we will be hosting the First Ever Bitcoin Cash meet-up in Belize.
I thought it would be fun to introduce a 'wow' factor, and give away real money in the flyer announcing our event. I needed an efficient way to achieve this.
I couldn't really use the default paper wallets, as created by sites such as bitaddress.org, because they wouldn't allow as much customization as I wanted. I also didn't fancy the idea of copying and pasting a hundred QR codes (or more), and cumbersomely funding all these addresses one by one. There had to be a better way.
I was also restricted by the software I had available. I decided to use my Linux machine, with LibreOffice installed (it should work the same way on Windows). And set out to mail-merge a few hundred flyers.
The basic idea
The design for our Flyer evolved into half a Letter page. I wanted to print two flyers on each page, and then cut the pages in half.
After a few iterations, our flyer looked like this:
All I had to do now, is create the mail merge, make sure I had Private Key QR codes, print the flyers and fund the addresses - all as efficiently as possible.
Doing the mail-merge
Mail merges in LibreOffice are, let's put it politely, a pain. They are possible, and quite a powerful feature, however. I'll walk through the process here.
Creating the data source
The first step is to create a source data file, i.e. a file that contains all the Private Keys that we want to mail-merge into our document. You can use any source of Private Keys that will output a CSV file, I decided to use the bitaddress.org website (the offline version).
Note: for this example, I will be showing Private Keys. These are for demo purposes only, I have used completely different keys for the real thing! So sorry, no easy funds here today.
I saved the contents of the "Comma Separated Values" list into a CSV file, in my case "bchaddr.csv". That file was opened in LibreOffice Calc, and the default CSV import options were accepted.
Because I have two flyers per page, it was easiest to copy the bottom half of the data and paste it to the right of the original list, ie make the list half as long, and twice as wide). I also added a row with header names, and ended up with a file such as this:
The document was saved as an "ODF Spreadsheet (.ods)", in this case with the file name bchaddr.ods. I'll use this document as a data source for the mail merge.
Linking the data source
Back in LibreOffice Writer, go to Edit -> Exchange Database. Click Browse, and select the file you just created (in my case, bchaddr.ods). Click on Define.
Next, go to View -> Data Sources. On the left hand side, you will see the bchaddr data source, which you can open, slect Tables, and bchaddr. The data source viewer will give a list of the data that is found in this data source.
From here, you can drag and drop the header of the Private Key fields (in my case PrivKey1 and PrivKey2) to their respective locations in the template file.
Next, choose the Mail Merge option (it's just above the datasource, the icon has an envelope with a document right behind it)
On the first screen, choose the option "Use the current document". Click Next.
On the second screen, choose the option "Letter". Click Next.
On the third screen, ensure that the Current address list is "bchaddr" (or whatever your data source is called), and that none of the other options are selected. Click Next.
On the fourth screen, ensure that nothing is selected (no Salutations). Click Finish.
At first glance, nothing appears to have happened. You still need to click the "Save Merged Documents" button, also above the data source view.
In the "Save merged document" popup, choose "Save as a single large document" and click the "Save Documents" button. Choose a file name, and click "Save". It will now take a few moments to generate the file. Once it is done, go to the explorer and open the file.
The result will look something like this:
So wait, what, NO QR CODE. Nope, not yet, That requires some more work. I believe this is where Microsoft Word 2013 and higher differentiates itself, as I think you can put the Private Key in a hidden field, and use that hidden field to display a QR Code. No such luxury in LibreOffice.
Creating QR Codes in LibreOffice
First, you'll need to install an extension in LibreOffice. I used this one:
After downloading it, go to Tools -> Extension Manager. Click on "Add" and choose the "qrcodejs-1.1.1.oxt" file (or, if there is a newer one by the time you follow this guide - use the newer one).
This should be the result of a successful installation:
You may have to restart LibreOffice at this point for the application to recognize the plugin.
I am now able to select the Private Key anywhere in the document, and convert it into a QR Code. To do this, select the Private Key, go to Insert -> QR-Code JS. It'll remove the Private Key text, and in it's place, put a QR Code.
This is great, but still quite a few clicks. There is a more efficient way.
Go to Tools -> Customize and select the Keyboard tab. Find the shortcut key Ctrl-Q (which should be unused, otherwise use any other key combination).
Select Ctrl+Q in the Shortcut Keys, and under Functions select LibreOffice Macros -> user -> QRcodeJS -> QRcodeJS. In Function, select "Main". Click on "Load", and on "OK".
Now, you can select any Private Key, press Ctrl-Q, and it will instantly transform into a QR Code. It is still a bit of manual work to go through all of these keys, but in reality it is very fast and you're not likely to make any mistakes doing this.
Funding the addresses
This section is purposefully quite brief. There are many good tutorials out there that will help with sending a Bitcoin Cash transaction to many outputs efficiently.
For example, Bitcoin ABC or Bitcoin Unlimited Cash Edition will have the "sendmany" function built-in. That requires a JSON input with the target Bitcoin Cash address with the amount. You can create such a JSON quite easily, by using the CONCATENATE command in LibreOffice Calc.
Another option, if you have a Trezor, is to use the Advanced sending options and upload a CSV file of addresses and funding amounts.
So, while not immediately obvious, and not extremely easy, it is very doable to create a mail merged document in LibreOffice or OpenOffice, with custom content and scannable QR Codes.
This time, I'll use it to create flyers. Because I'll be handing them out randomly I have specified an expiration date on the flyer. That allows me to sweep any unused funds after that data, and re-use them at a future date.
Next time, I'm thinking about sending mail-merged letters to lots of businesses (probably with a focus on the Tourism sector) in Belize. I'm planning on giving them some more Bitcoin Cash to make it worth their while, and giving them longer to claim).
The possibilities are endless. Let me know how you will mail merge Bitcoin Cash Private Keys!
The End Result
Here's the end result:
Note: There's nothing except a Thank you note behind the paywall. Any amount you pay or donate will go towards spreading Bitcoin Cash adoption in Belize.
1 of 1 reviewers say it's worth paying for
0 of 1 reviewers say it's not worth paying for