Exercise 10: Mail Merging using the scrlttr2 Class (Solution)

This is a solution to Exercise 10. You need the sample files people.csv and country-codes.csv. Alternatively, if you want to use data from the samples SQL database, remember to use datatooltk to import the data to a .dbtex file and load it using \DTLloaddbtex as described in Loading Data From a .dbtex File.

\documentclass[12pt,parskip=full]{scrlttr2}

\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage{graphicx}

\usepackage[british]{babel}

\usepackage{datatool}

\newcommand{\ifcsbool}[3]{%
  \ifboolexpr
  {
    test{\ifdefstring{#1}{true}} or
    test{\ifdefstring{#1}{1}}
  }
  {#2}{#3}%
}

\DTLloaddb{people}{people.csv}
\DTLloaddb{countries}{country-codes.csv}

\KOMAoption{subject}{afteropening,right,underlined,titled}
\KOMAoptions{backaddress=false,foldmarks=false,fromalign=right,locfield=wide}

\setkomavar{signature}{Mr Big Head, Managing Director}
\setkomavar{subject}{A sample letter}
\setkomavar{fromaddress}{University of Somewhere\\Some City\\AB3 4YZ}
\setkomavar{fromurl}{www.somewhere.ac.uk}
\setkomavar{location}{Secret Lab of Experimental Stuff\\Gov.\ Grant No.~1234}
\setkomavar{fromphone}{0123456789}
\setkomavar{fromemail}{big.head@somewhere.ac.uk}

\KOMAoption{fromlogo}{true}
\setkomavar{fromlogo}{\includegraphics[width=2cm]{dummy-logo}}

\begin{document}

\DTLforeach*{people}% data
{% assignments
  \Id=id,%
  \Surname=surname,%
  \Forenames=forenames,%
  \Title=title,%
  \AddressI=address1,%
  \AddressII=address2,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode,%
  \CountryCode=country,%
  \Subscribed=subscribed%
}
{% body
  \ifcsbool{\Subscribed}
  {%
   \xDTLassignfirstmatch{countries}{code}{\CountryCode}{\CountryName=name}

 \begin{letter}{%
  \DTLifnullorempty{\Title}{}{\Title\ }\Forenames\ \Surname\\%
  \AddressI\\
  \DTLifnullorempty{\AddressII}{}{\AddressII\\}%
  \Town\\%
  \DTLifnullorempty{\County}{}{\County\\}%
  \Postcode\ifdefstring{\CountryCode}{gb}{}{\\\CountryName}}

   \setkomavar{myref}{ABC/123}
   \setkomavar{customer}{\Id}

   \opening{Dear \DTLifnullorempty{\Title}{\Forenames}{\Title} \Surname}

   This is an imaginary letter.

   This is the second paragraph of the letter.

   \closing{Yours sincerely}

   \ps PS: this is a postscript.

   \encl{Photocopy of something interesting\\
    Photocopy of something rather dull}

   \cc{Prof Important Person\\Dr Bor Ing}
  \end{letter}
  }{}%
}

\end{document}

Another possibility is to use the optional argument of \DTLforeach* so that it excludes entries where the subscribed field isn't equal to 1 like this:

\documentclass[12pt,parskip=full]{scrlttr2}

\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage{graphicx}

\usepackage[british]{babel}

\usepackage{datatool}

\DTLloaddb{people}{people.csv}
\DTLloaddb{countries}{country-codes.csv}

\KOMAoption{subject}{afteropening,right,underlined,titled}
\KOMAoptions{backaddress=false,foldmarks=false,fromalign=right,locfield=wide}

\setkomavar{signature}{Mr Big Head, Managing Director}
\setkomavar{subject}{A sample letter}
\setkomavar{fromaddress}{University of Somewhere\\Some City\\AB3 4YZ}
\setkomavar{fromurl}{www.somewhere.ac.uk}
\setkomavar{location}{Secret Lab of Experimental Stuff\\Gov.\ Grant No.~1234}
\setkomavar{fromphone}{0123456789}
\setkomavar{fromemail}{big.head@somewhere.ac.uk}

\KOMAoption{fromlogo}{true}
\setkomavar{fromlogo}{\includegraphics[width=2cm]{dummy-logo}}

\begin{document}

\DTLforeach*[\equal{\Subscribed}{1}]{people}% data
{% assignments
  \Id=id,%
  \Surname=surname,%
  \Forenames=forenames,%
  \Title=title,%
  \AddressI=address1,%
  \AddressII=address2,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode,%
  \CountryCode=country,%
  \Subscribed=subscribed%
}
{% body
   \xDTLassignfirstmatch{countries}{code}{\CountryCode}{\CountryName=name}

 \begin{letter}{%
  \DTLifnullorempty{\Title}{}{\Title\ }\Forenames\ \Surname\\%
  \AddressI\\
  \DTLifnullorempty{\AddressII}{}{\AddressII\\}%
  \Town\\%
  \DTLifnullorempty{\County}{}{\County\\}%
  \Postcode\ifdefstring{\CountryCode}{gb}{}{\\\CountryName}}

   \setkomavar{myref}{ABC/123}
   \setkomavar{customer}{\Id}

   \opening{Dear \DTLifnullorempty{\Title}{\Forenames}{\Title} \Surname}

   This is an imaginary letter.

   This is the second paragraph of the letter.

   \closing{Yours sincerely}

   \ps PS: this is a postscript.

   \encl{Photocopy of something interesting\\
    Photocopy of something rather dull}

   \cc{Prof Important Person\\Dr Bor Ing}
  \end{letter}
}

\end{document}

Download mail-merging-scrlttr2.tex or mail-merging-scrlttr2.pdf.