Example of mailmerge package with an Excel spreadsheet

Question

I am looking for an example for using the mailmerge package to write a cover letter, with names and addresses on a spreadsheet. The package comes with an example, but you have to manually enter all the data.

Answer 1

I use the datatool package. I don't use the letter document class, since I use my own letterhead package plus the article class. But the basics of how to do this should be clear, and adaptable to letter class. It's important to do checks for empty fields in address lines, otherwise you will get blank lines.

I use the parskip package with letters, which sets the \parindent to 0 and uses blank lines to separate paragraphs. If you don't like this, then you may want to set the address lines in a {tabular}{@{}l} environment instead.

\documentclass{article}
\usepackage{datatool}
\usepackage{parskip}

\makeatletter
% Blank/missing fields commands
% \skipblank adds \\ to filled field; * version adds \space instead of newline
\newcommand\skipblank{\@ifstar\@spskip\@nlskip}
\newcommand\@nlskip[1]{\ifthenelse{\DTLiseq{#1}{}}{\relax}{#1\\}}
\newcommand\@spskip[1]{\ifthenelse{\DTLiseq{#1}{}}{\relax}{#1\space}}
% \checkblank replaces blank fields with ***
\newcommand\checkblank[1]{\ifthenelse{\DTLiseq{#1}{}}{***}{#1}}
\makeatother
\begin{document}
\DTLloaddb{addresses}{mail-merge-addresses.csv} % use your actual address database here
\DTLforeach*{addresses}{%
    \addtitle=Title,
    \firstname=FirstName,
    \lastname=LastName,
    \position=Position,
    \addressi=Address1,
    \addressii=Address2,
    \addressiii=Address3,
    \city=City,
    \state=State,
    \zip=Zip,
    \country=Country}
    {%
\setcounter{page}{1}
\thispagestyle{empty}
\checkblank{\addtitle}\ \firstname\ \lastname,\\
\skipblank{\position}
\skipblank{\addressi}
\skipblank{\addressii}
\skipblank{\addressiii}
\city, \state\ \zip\\
\skipblank*{\country}

Dear \checkblank{\addtitle}\ \lastname:

This is the text of the letter.

Sincerely,


\clearpage
}
\end{document}

The mail-merge-addresses.csv file is the following: (It deliberately has blank fields to illustrate the field checking.)

Title,  LastName,   FirstName,  Position,   Address1,   Address2,   Address3,   City,   State,  Zip,    Country
Dr.,    Smith,  James,  Chair,  Dept. of Physics,   University of Somewhere,    434 East Hall,  Cambridge,  MA, 02139,  
Mr.,    Jones,  Bill,   Director of Human Resources,    ,   University of Nowhere,  A203 King St.,  Stanford,   CA, 94305,  
,   Brown,  Sally,  ,   Firstline,  ,   3rdline,    East Lansing,   MI, ,   USA