Mailmerge with Perl

• ... and Word/Excel and PDF24 on Windows
• Custom mail ...
• with optional custom PDF-attachment
• All in one directory (USB-drive)
• ... also Strawberry Perl portable

1. step: Mail template

• I get it via mail or send it to myself.
• Get it as MIME from server
• and save it as .eml-file
• You can open it with Thunderbird or theBat (not Outlook)
• ... or any text-editor (e.g. remove "Received:")

Directory

• perl - subdir with strawberry portable
• setcmd.cmd - put local Perl in PATH

set PATH=perl\perl\bin;%PATH%
cmd

				

my $pop = new Mail::POP3Client(
	USER     => "<username>",
	PASSWORD => "<password>",
	HOST     => "pop3.mailserver.de",
	USESSL   => 1,
);						   
open my $fh, ">:raw", "mail.eml" or die "open out";
$pop->HeadAndBodyToFile( $fh, $pop->Count() );
$pop->Close();

• View in Thunderbird (with doubleclick)
• or move to draft for editing

2. step: Data as csv-file

• that is what I get: notpaid.csv

• and some extra infos
• in adressen.csv

convert for serial letter

• one line for each invoice (mail): file.csv

• and one line for each invoice item
• 20 lines for each invoid/mail: codelist.csv
  empty lines if less that 20 items

my %adr;
open ADR, "<", "adressen.csv" or die "open ...";
<ADR>;	# skip header
while (<ADR>) { chomp;
	my @a = split ";", $_;
	my $email = shift @a;
	$adr{$email} = join ";", @a;
}
close ADR;

open IN, "<", $filename or die "open in $filename";
...
open FILE, ">", "file.csv" or die "open out file";
print FILE "Nr;Summe;EMail;Name;Ignore;EMail;pdf\n";
open LIST, ">", "codelist.csv" or die "open out...";
print LIST "nr;email;name;...;ts;price\n";
...
close <everything>;

<IN>;	# skip first line (header)
while (<IN>) {
	chomp;
	my @a = split ";", $_;
	my $mail = $a[4];
	
	next if $a[1] ne "false";
	...
}

• pf() for each new mail
• and count codes per page

	pf() if defined($last_m) and $mail ne $last_m;
	$codepp++;
	my $price = $a[8];
	$sum += $price;
	print LIST "$codepp;$mail;$a[5];..;$price\n";

	$last_m = $mail;
}
pf();

• pf() for each new mail
• and count codes per page
• fill with empty lines for 20 per page

sub pf { # print into file.csv
	$c_mail++;
	my $nr = sprintf("%03d", $c_mail);
	my $adr = $adr{$last_mail};
	print FILE "$c_mail;$sum;$adr;;$last_mail;pdf.\n";

	print LIST ";;;;;;;;;\n" while $codepp++ < 20;
	$codepp = 0; $sum = 0;
}

• Serial letter in Word for all attachments
• Data source is codelist.csv

• and one line for each invoice item
• 20 lines for each invoid/mail: codelist.csv
  empty lines if less that 20 items

• The sum can be done in Word table as =SUM(ABOVE)

• Merging all data gives a doc with all invoices

• One invoice per page

• Split pages into files

• With GUI or command line:
  "C:\Programs\PDF24\pdf24-DocTool.exe" -splitByPage
  -outputDir . -outputFile pdf Codes.pdf

remember this slide?

• one line for each invoice (mail): file.csv
• actually now: file.xlsx

and now ... finally sending: send.pl

my $parser = Spreadsheet::ParseXLSX->new;
my $workbook = $parser->parse("file.xlsx");
my $worksheet = $workbook->worksheet(0);
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
for my $r ($row_min .. $row_max) {
  for my $c ( $col_min .. $col_max ) {
	my $cell = $worksheet->get_cell($r, $c);

Field "To:" and mail template

	my $to = $feldval[$col_email];
	utf8::decode($to);
	$to =~ s/[;\n]/,/gm;	# there might be more 
	...
	open IN, "<", $filename or die "open $filename";
	my $mime = Email::MIME->new(join('', <IN>));
	close IN;
	$mime->header_str_set("To" => $to);

Setting "Date:" and "Message-Id:"

	my $date = email_date; 		# now
	print "date: $date\n";
	$mime->header_str_set("Date" => $date);

	my $msgid = Email::MessageID->new(host=>'domain.de')
							->in_brackets;
	$mime->header_str_set("Message-Id" => $msgid);

We need to walk the template

	$mime->walk_parts(sub { my ($part) = @_;	  
	  if ( $part->content_type =~ m[text/plain]i
			or $part->content_type =~ m[text/html]i ) {
		my $b = $part->body;
		$b =~ s/\r\n\r\n/\n/g;	# convert newlines
		...
		$part->body_set($b);
	  }
	});

Template variable substitution

		for my $c ($col_min .. $col_email) {
		  my $s=$feldval[$c];
		  if ($part->content_type 
				=~ m[charset="iso-8859-1"]i) {
		    $s = Encode::encode("ISO-8859-1", $s);
		  }
		  $b =~ s/%%$feld[$c]%%/$s/gm;
		}

If template without attachments

		if ( $mime->content_type
					=~ m[multipart/alternative]i ) {
			$mime->content_type_set( 'multipart/mixed' );
			my @parts = $mime->subparts;
			my $neu = Email::MIME->create(parts => [@parts] );
			$neu->content_type_set('multipart/alternative');
			$mime->parts_set( [ $neu ] );
		}

Attach the pdf-file

		my @parts = (	Email::MIME->create(
				attributes => {
					filename     => "$name$suffix",
					content_type => "application/pdf",
					disposition  => "attachment",
					encoding     => "base64",
					name         => "$name$suffix",
				}, body => io($feldval[$c])->binary->all));
		$mime->parts_add( [@parts] );

Sending

	my $mime_as_string=$mime->as_string;
	$mime_as_string =~ s/\r\n/\n/gm;
	utf8::downgrade($mime_as_string);
	if (!$dryrun) {	# really send
	  my $sender = Email::Send->new({mailer => 'SMTP'});
	  $sender->mailer_args([Host => 'smtp.host.de:465',
	  ssl => 1, username => 'u', password => 'p']);
	  $sender->send($mime_as_string); sleep 10;
	}

• There was even some more fiddling with header and encoding
• and logging of cource.
• Bounces and errors still need manual care

Thank you

• Slides: https://www.wieland-pusch.de/slides/gpw2022/
• Code: mail me wieland@wielandpusch.de