How to Automate Sending Email Task – Send Medium Story to Your List

Original article was published on Artificial Intelligence on Medium

Step-1: Project Set-up

Like all my previous stories. I firstly set-up the project folder to match with the proper naming convention. This helps in standardizing the process and minimize the learning curve for other developers.

Here, we require input, output, transformation folders. We will be creating multiple transformations for various activities.

We require the below-mentioned structure. As I have mentioned, I prefer to store all the work-related in one single location ‘Work’; helps in maintaining backups. (D:\Work\SendEmail).

  1. Input – This is where we will download our statements.
  2. Output – Storing our output file
  3. Main.kjb – This is where we will configure and integrate multiple transformations for looping through our mailing list.
  4. ReadFile and SendEmail – In the transformation folder, we will create these files. Please note, ReadFiles is a transformation file and SendEmail is a job file.
Project Folder Structure

Step – 2 Read Files

We need to read two worksheet within a transformation. The idea is to read the entire mailing list and loop through each member and send an email with a custom message.

Open the transformation ReadFile.ktr on your Spoon (Desktop App). We need to read the input file in this transformation.

We need to perform the below-mentioned steps.

  1. Add a description to the transformation by right-clicking>‘New Note’. Documentation is very essential in any programming language.
  2. Drag two ‘Text file input’, one ‘Join rows (cartesian product)’ and ‘Copy rows to result’ plugins from the transformation design tab onto the canvas.
  3. Rename the same ‘read_email_list’ (text input 1), ‘read_latest_post’ (text input 1), ‘merge’ and ‘copy_result’ respectively.
  4. In the read_email_list step, browse for the file (D:\Work\SendEmail\Input\EmailList.csv) in the File or directory field and File tab, in the Content tab, change Separator field to comma instead of semi-comma, and in Fields tab just click on Get Fields button.
  5. Similarly, in the read_latest_post step, browse for the file (D:\Work\SendEmail\Input\LatestPost.csv) in the File or directory field and File tab, in the Content tab, change Separator field to comma instead of semi-comma, and in Fields tab just click on Get Fields button.
  6. That’s it, join rows step will perform its magic without changing even a single parameter. Here, I wanted to show the capability of merging two files seamlessly using join rows step. We can also provide conditions in case required.
ReadFile Transformation Structure

We can test this unit by running the transformation and previewing the output.

Step – 3 Pass Variables

We need to pass variables to our child job for sending emails one-by-one. This will require us to set-up the parent Main job file before we configure the SendEmail child job file.

Passing variables is a simple process in PDI. If we end our transformation with ‘Copy rows to result’, then all our variables are accessible in our next transformations. However, we will be passing variables differently at the job level.

We need to perform the below steps to pass variables.

  1. In the Main.kjb file, we need drag ‘Start’, ‘Transformation’, ‘Job’ and a ‘Success’ step.
  2. Rename the steps as per our naming convention to ‘start’, ‘read_files’, ‘send_email’ and ‘success’.
  3. We need to browse for our transformation files in the read_files and send_email step respectively.
  4. In the send_email job step, we need to check Execute every input row field in the Options tab.
  5. In the Parameters tab, we need to add four parameters viz. FirstName, EmailAddress Title and BlogPost. Now, all these names should match with our input file fields.
Click on Execute every input row for looping through the list
Parameters/Variables which we want to pass in the child job SendEmail.

Now, we need to grab the values in the child/sub job. Below is the step.

Open the SendEmail.kjb file, double-click on the canvas. Spoon will open the job properties window. We need to add the same fields in the Parameters tab here as well.

Parameter tab of the Job Properties

Step – 3 Send Emails

We can now set up and send custom emails to our mailing list. We require Open Authentication (OAuth) for sending across the email. To set up OAuth on your Gmail account, please refer the below story – Step-2; I am not going through the entire process again in this post.

Let’s quickly set up our SendEmail job.

  1. Add a description of the job file. Here we will use this job file to send emails.
  2. Drag ‘Start’, ‘Mail’, and ‘Success’ from the list of plugins in the design tab.
  3. Rename it as per our naming convention, start, email and success respectively.
  4. In the email step, we need to configure the below-mentioned properties.
  • In the Destination address field, we need to use the variable ${EmailAddress} which is passed from our previous transformation and parent job.
  • In the Sender name and address field, we need to provide the name and email address of the sender; i.e. our information.
  • In the Server tab, we need to provide the OAuth information for authentication. This is an outgoing server and we require SMTP details instead of IMAP.
  • In the EMail Message tab, we need check ‘Use HTML format in mail body’, in the message section, our Subject field will be ${Title} and write your custom message for the email body. I have used below.
Hi ${FirstName},I have published a new blog on Medium. Here's link. ${BlogPost}I hope you like it. Please feel to provide your suggestions. Thanks,
Shravankumar Suvarna

See, how we used the variables in multiple fields. PDI does not support the use of variables across all the fields. The key is to look for a small blue dollar sign beside the fields. If it is present, then it accepts or it does not.

SendEmail Job Flow
Use of Variable in Destination address
OAuth Details to Authenticate. Please note, is a dummy email address.
Email Message Tab — Check for the Small Dollar Sign beside Field

That’s it, we should be good to go with our automated pipeline. We can now run our main job file and test the same using our test cases.



Well, that was the easiest data pipeline. Here, the idea is not to use this method to send blogs to the mailing list instead use this case study as per our conveniences. We can build similar pipelines for sending across the log files, status report etc. Since most of our pipelines will run on server machines. It would be a nice feature to incorporate real-time status report from jobs/transformation pipelines.