It is no secret that at kJube we like Pentaho Data Integration. This year, in order to send all our best wishes for the new year to customers and partners, we used our kettle mailer engine again (aka Normal Mailer). Since it has been something we wanted to put out there, I thought it was a good idea to share a bit of code. Even though this isn't the whole solution, I still thought, that there might be one or two people that still need to send cards. So serve yourself.

The basics
How does it work? If you unzip the code, you'll see that there is just one transformation, named "mailer.ktr".



This transformation uses two input files: "mailing_list.xls" and "test.html" as inputs.
  • "Mailing_list.xls" contains the list of persons to who you want to send mails.
  • "test.html" is the standard mail that will be sent when running the transformation. Obviously you can create whatever mail template you want. You can provide it as input parameter when running the transformation, as the name of the mail template is a named parameter. In the example you'll find our new year's card "2011.html"
The transformation also has an output file, which is the same as the input file. It will add a tab in your "mailing_list.xls" with the name of your mail template and the result of the mailing. By the way, we've used the Excel Writer step here. So you might need to add that to your kettle deployment, or you could just disable the tracking of results if you don't care about that part.

In order for the transformation to work you also need to add some variables to your kettle.properties which are more or less self-explanatory.
  • KFF_MAIL_SMTP_SERVER
  • KFF_MAIL_SMTP_PORT
  • NORMAN_MAILER_SENDER_NAME
  • NORMAN_MAILER_SENDER_ADDRESS
Ideas for extending


Obviously this idea can be extended. Some things that have crossed my mind are the following.

  • It would be nice to be able to insert variables into the mail template, which can then be replace at run time. E.g. add somewhere in your mail template "Dear ${SALUTATION} ${LASTNAME}" and replace this with actual values from the mailing list. I guess Roland's latest blog post should offer some possibilities there.
  • Obviously when you send these mails, the sender mail address that you use, will probably get some "undeliverable" replies. Most of the time your mailing list isn't correct. Reading out the mailbox and automatically figuring out which email addresses are invalid would be a great feature. With the POP3 step and some Regex magic that shouldn't be to hard either.
  • Another cute feature would be to offer people to "unsubscribe" from your mailing list. Again, providing a mail address and a standard subject, like "Unsuscribe" in combination with the POP3 step and some Regex should take care of that.

If any one wants feels like extending this a bit, feel free to contact us.

Oh, and a happy new year to all of you !