How to automate email sending from MySQL databases

published on 19 April 2024

An Integration platform as a service (iPaaS), like Boomi, is known for integrating platforms and data from different services. However, another interesting feature of the iPaaS solution is its ability to ensure efficient communication with your business clients. 

Automating email sending directly from your MySQL databases using Boomi allows you to establish personalized and timely communication without manual intervention. This eliminates the time-consuming task of manually sending emails and ensures that your customers receive relevant information promptly. 

This guide discusses automating sending out emails, empowering you to leverage your MySQL data to trigger custom email notifications.

TL;DR

To automate email sending from your MySQL database with Boomi, follow these steps:

  • Create a new Boomi integration process
  • Collect the data from the database
  • Transform the data type from database to CSV
  • Split the CSV files  by line
  • Set Properties
  • Create the Message
  • Send the email

Prerequisites

To get the most out of this article, you must have the following:

Inserting data in your MySQL database 

MySQL is an open-source relational database management system (RDBMS) that stores, organizes, and accesses data in a structured format. The prerequisites section discussed Connecting your Boomi Atom runtime and MySQL on Docker, and this section will build on that knowledge.

The MySQL database will hold the user information, including their names and the email addresses to which you will send your desired message.  To do this, you must change your folder structure and the docker-compose.yml file you created when you connected your Boomi Atom runtime to your MySQL database.

Next, in your docker-compose.yml file, add a volume mapping for the MySQL service. This line will link your host machine's ‘mysql-data’ folder to the container's internal data directory. 

 mysql:
    image: mysql:latest
    container_name: mysql
# add this line  
  volumes:
      - mysql-data:/var/lib/mysql:Z
    environment: 
      - MYSQL_ROOT_PASSWORD=<root user password>
      - MYSQL_USER=<username>
      - MYSQL_PASSWORD=<password>
      - MYSQL_DATABASE=<name of your MySQL database>
    ports: 
      - "3306:3306"

After updating your `docker-compose.yml` file, run this command to build and run your images.

docker compose up -d

Creating a table in your MySQL Database

Run this command in your terminal to start an interactive shell in your running MySQL Docker container.

docker exec -it mysql bash

Next, log into this shell using this command.

mysql -u <mysql_user> -p<mysql_password> <mysql_database>

The next step is to create a table named 'customers' to store your customer data. This table will include ‘id’, ‘first_name’, ‘last_name’, and  ‘email’ fields.

CREATE TABLE users (
 id integer,
 first_name varchar(255), 
 last_name varchar(255),
 email varchar(255)
);

To check if your tables have been successfully created, run this command.

SHOW TABLES;

Inserting data in your MySQL database 

After you create your tables, use the following command to insert data into this table, replacing the placeholders with their actual values.

INSERT INTO users  (id, first_name, last_name, email)
VALUES (1, 'user1 first name', 'user1 last name’, ‘user1 email'),
(2, 'user2 first name', 'user2 last name’, ‘user2 email');

Run this command to see your data.

SELECT * FROM users

Automate email sending with Boomi

This section explains how to set up a Boomi process to automatically collect data from your MySQL database and send a customized email.

To create this process, follow these steps:

1. Create a new Boomi integration process

Go to the Integration page, click the Create New button, and choose Process from the dropdown menu.

The Create New dropdown
The Create New dropdown

This action will open the Start Shape sidebar. Choose the No Data radio button, indicating that the process will not receive external data, and then confirm by clicking the OK button.

The Start Shape sidebar
The Start Shape sidebar

2. Collect the data from the database

After initiating your new Boomi process, add your MySQL database connector, which holds the user information.

To add a shape to a Boomi process, click on the small box at the end of the dotted lines, leaving the previous shape (in this case, the Start Shape). Then, search for the shape you want to add to the process (the Database shape).

The Database connector
The Database connector

Next, click on the Database connector to configure the shape. This action opens up the Connector Shape sidebar.

The Database Connector Sidebar
The Database Connector Sidebar

In this sidebar, click the Connection input field and select the MySQL connection you created in the prerequisites section. 

If you have not created the MySQL database connection, check out How to connect your Boomi Atom runtime and MySQL on Docker to do so.

The Connector Shape Action dropdown
The Connector Shape Action dropdown

Next, click on + sign in the Connector Operation input field to create a new operation. In Boomi, a database operation defines how you interact with the database specified in the connection.

The Database Connector Operation input field
The Database Connector Operation input field

This action opens up the Database Options page.

In the Database Options page, click on the + symbol to create a new Database profile for the operation.

The Database Profile input field
The Database Profile input field

On the Database Profile page, click the Statement menu, followed by the Import button on the top right-hand corner to import your database connection.

The Statement details page
The Statement details page

The Import button opens up a Database Import Wizard menu. Here, fill out the following fields with your credentials: 

  • The Boomi Atom you want to run this Boomi process on
  • The Database connection you created
  • A schema filter of users

Once you are done, your Database Import Wizard modal should look like the image below:

The Database Import Wizard
The Database Import Wizard

After clicking Next, you'll be prompted to choose a table for synchronization. Select the desired table and click Next again.

The Choose Tables Modal
The Choose Tables Modal

Next, select the columns you want to move to collect your data. You can choose all columns or exclude any as needed. Once you've selected, click Next, followed by the Finish button.

The Choose Columns Modal
The Choose Columns Modal

Finally, expand the Fields menu to verify that your Database profile has been imported correctly into the Connector.

The Statement Details page with the imported fields
The Statement Details page with the imported fields

After saving and closing the Profile page, you'll be returned to the Database Options page. Here, set up the link between your data points:

  • Click on the Link Element field.
  • From the Fields menu, choose id. This tells Boomi to group records that share the same id into a single document.
The Link Element input field
The Link Element input field

Set the Batch Count to 1 for data retrieval to process data one record at a time. Leave Max Rows at 0 to retrieve all rows from the database.

The Database Options page
The Database Options page

Finally, click the Save and Close button to return to the connector sidebar. 

The Database connector with the loaded database connection and operation
The Database connector with the loaded database connection and operation

In the sidebar, click the OK button to return to the canvas.

3. Transform the data type from database to CSV

Next, transform the files from the database to a CSV format, which will make it easier for you to parse and retrieve information from the database.

Add a Map Shape to the Boomi process to change the data format. Click on the Shape to configure it, which opens up a Map sidebar.

The Map Shape
The Map Shape

Click the + sign in the Map input field in this sidebar to open the dedicated Map page.

The Map Shape input field
The Map Shape input field

On the Map page, go to the Map Source section and select the Choose link.

The Map source Choose link
The Map source Choose link

This action opens the Choose a Source Profile. In this modal, select the Profile Type of Database, and in the Profile field, select the database profile you created for your Database Options page.

The Choose a Source Profile modal
The Choose a Source Profile modal

Click the OK button to see your data fields populated in the Elements menu.

The loaded fields in the Elements memnu
The loaded fields in the Elements memnu

On the right side of the page, the Map Destination, find the Choose button and click on it.

The Map destination Choose link
The Map destination Choose link

This action will open the Choose a Destination Profile modal.

The Choose a Destination Profile
The Choose a Destination Profile

Select Flat file from the Profile Type dropdown menu within the modal. Then, click the + button in the Profile input field to create a new profile for the CSV file.

The Choose a Destination Profile modal
The Choose a Destination Profile modal

This action opens up a New Flat File Profile page. Right-click the downwards-facing arrow next to Elements, and select Add Multiple Elements from the menu. 

The Elements menu
The Elements menu
Screenshot-2024-04-11-212314-ycbtt

This opens up a modal that asks you to choose how many elements you'd like to add. This tutorial decides to create 4 new elements. Click on the OK button to close the modal.

The How many modal, with an input field of 4
The How many modal, with an input field of 4

Next, click on each of the newly created elements and change them to correspond with the fields in your database. This tutorial creates fields for id, first_name, last_name, and email.

The data fields in the Elements menu
The data fields in the Elements menu

Next, navigate to the Options tab, check the Use Column Headers checkbox, and select the Comma Delimited option from the File Delimiter dropdown.  This indicates that you want to use the column headers present in the data and separate the data fields by a comma. 

Click on Save and Close and the OK buttons to save this profile.

The options tab describing how to present the CSV data
The options tab describing how to present the CSV data

To see all your newly created fields, click the downward-facing arrow next to the Elements menu in the Map Destination section and select Expand.

The Map source and Map destination loaded with data fields
The Map source and Map destination loaded with data fields

Next, map the corresponding fields between your database profile and your CSV profile.

Simply drag your cursor from a field on the left side and connect it to its matching field on the right. Once you've mapped all the fields, your page should resemble the example provided.

The fields on both the Map source and Map destination mapped to each other
The fields on both the Map source and Map destination mapped to each other

Click the Save and Close button to save this Map and return to the Map sidebar. 

The Map sidebar loaded with the Map information
The Map sidebar loaded with the Map information

Click the OK to save the Map configuration and return to the Boomi canvas.

4. Split the CSV files by line

Next, you want to split each piece of information in the file by users. To do this,  you need to add the Data Process Shape to the Boomi process.

The Data process Shape
The Data process Shape

This Data Process shape allows you to split your document line by line, which in your case means user by user.

Next, click on the Data Process shape to open up its dedicated sidebar. In this dedicated sidebar, click on the + sign to specify a processing step for your data.

The Data Process Properties 
The Data Process Properties 

This action opens up a form; in this form, select:

  •  Split Documents from the Processing Step dropdown
  •  A Profile Type of Flat File to match the CSV file format 
  • Split Options of Split By Line 
  • A Headers Option of Retain first line as Column Headers
The Data Process Properties form
The Data Process Properties form

Click the OK button to save this Data Process Shape configuration.

5. Set Properties

Next, you will use the Set Properties shape to create and set two properties dynamically:

  • The user's first name property is set as the first_name in the data
  • The receiver's email address property is set as the email address stated in the data file.

You will need these dynamic properties to create and send out these emails. To create the first name property, click the + button in the Properties to Set section. 

The Set Properties Shape 
The Set Properties Shape 

This action opens up a Choose Property modal. In this modal, select the Property Type of Dynamic Document Property and select the Property Name as first_name.

The Choose Property modal
The Choose Property modal

Click on the OK button to save this property.  

You should see the Dynamic Document Property parameter. Select this parameter, and click the + sign in the Property Value section to create the parameter’s value. 

The Property Value 
The Property Value 

This action opens up the Parameter Value modal. Fill out the Parameter Value modal with the following information:

  • Type -> Profile Element
  • Profile Type -> Flat File
  • Profile ->  The CSV profile you created during the Map process.
  • Element -> Expand the menu, and select the first_name field

Your Parameter Value modal should look like this when you are done. Click the OK button to save the parameter value information.

The Property Value modal
The Property Value modal

Next, create another parameter for the receiver’s email address. To create this parameter, click the + sign in the Properties to Set section. This action opens up the Choose Property modal. Fill out this modal with the following information:

  • Property Type -> Document Property
  • Source Type -> Connectors
  • Connector -> Mail
  • Property -> To Address 

This creates a dynamic property that holds the receiver's email address.

The Choose Property modal
The Choose Property modal

Click the OK button to save this property and return to the sidebar. In this sidebar, you should see a Mail - To Address property. 

Select this parameter, and click the + sign in the Property Value section. This action opens up the Parameter Value modal. Fill out the Parameter Value modal with the following information:

  • Type  -> Profile Element
  • Profile Type -> Flat File
  • Profile -> Mail Flat File Profile
  • Element -> Expand the Elements menu and select the email field

When you are done, your Parameter Value modal should look like this. Click the OK button to save this parameter value and return to the sidebar.

The Parameter Value modal
The Parameter Value modal

Click the OK button in this sidebar to save your Set Properties configuration shape and return to the Boomi canvas.

The Set Properties Shape with all your loaded properties 
The Set Properties Shape with all your loaded properties 

6. Create the Message

Next, you need to create the message in the email you want to send out. Add the Message shape to your Boomi process to create this custom message.

The Message Shape
The Message Shape

Click this process shape to open its dedicated sidebar.

The Message shape sidebar
The Message shape sidebar

In this sidebar, write the message you want to send out in the Message input field. This tutorial writes out this message.

> Hey there {1}!

I hope everything is going great for you.

The {1} is a variable you will set to the first_name property you created in the Set Properties Shape. 

Click the + button in the Variables section to set this variable value. This action opens a Parameter Value modal. 

The Message shape variables
The Message shape variables

In this modal, fill in a Type of Document Property. Next, click the search icon next to the Document Property input field to open up the Choose Property modal.

The Parameter Value modal
The Parameter Value modal

In this modal, fill out the following credentials:

  • Property Type -> Dynamic Document Property
  • Property Name -> first_name

Click the OK button to save and close this modal. 

The choose property modal
The choose property modal

You should see your Document Property loaded in the Parameter Value modal. 

The Parameter Value modal
The Parameter Value modal

Click the OK button to save your parameter value and return to the sidebar. Your sidebar should look like this.

The message Shape loaded with the message information 
The message Shape loaded with the message information 

After creating your message and setting your dynamic variable, you instruct Boomi to replace the` {1}` with the user’s first_name from the file when it sends the email. Finally, click the OK button to save your message configuration and return to the Boomi canvas.

7. Send the email

Now, the best part is actually sending the email. The Mail Connector Shape allows you to add this shape to your Boomi process.

The mail connector
The mail connector

Click this Mail connector to configure this shape. This opens up the Mail connector sidebar. 

The connector shape database 
The connector shape database 

In this sidebar, go to the Connection input field and select the Mail Connection you created in the prerequisites section. If you have not created the Mail Connection yet, check out Creating the Mail connection and Operation.

Next, select an Action of Send, as you will be sending emails.

In the Operation input field, you can either select a previously created Mail operation or create a new one. This tutorial shows you how to create a new operation.

Click the + sign in the Operation input field to create a new operation. This action opens up a new Mail operation page.

The Mail connector Operation input field
The Mail connector Operation input field

Fill out the input fields on this page with the following information:

  • From: This is the email you used when creating your connection. It is the email address that shows up as the sender in your Mail app
  • To: Leave To as blank as you want to dynamically set that property depending on the email address in the data file from your database
  • Disposition: Select Inline because the document data is sent in the body of the email message instead of as an attachment.
  • Content-Type: text/plain, as you will be sending texts
The Operation page
The Operation page

When you are done, your sidebar should look like this: click the OK button to save this sidebar and return to the Boomi canvas.

The Mail connector sidebar with its connection and operation values
The Mail connector sidebar with its connection and operation values
The full Boomi process
The full Boomi process

8. Test the process

To verify if the process is working, click the Arrange button, then click the Test button to test the process.

The Arrange tab
The Arrange tab

This action asks you to select the Atom you want to run this Boomi process on; select the Atom you created in the prerequisites section. After running the process, you should see the custom email in the provided email address.

That’s a Wrap!

This guide has shown you how to dynamically send custom emails to different email addresses in your MySQL database using Boomi. You can also build on this tutorial to create personalized messages based on user information and trigger automated emails based on specific actions.

However, as interesting as this exercise is, this is only just a starting point for exploring the full capabilities of Boomi.  Refer to the official documentation for more information on what Boomi can do.

Read more