Using katalon studio excel to send email

[josephchan9032]

hi i would like to find out is there be possibility that i could send out an email base on the variable expiry to alert the user.

[albert.vu]

Hi Joseph,

Thanks for your question. We will be moving your question to a public area of our forum shortly as you would get better support that way.

Please also help to include more information in your topic, you can refer to the thread below on how to do so

[TIP] How To Help Us Help You!

My test is BROKEN! NOTHING WORKS! HELP ME! Relax. We get it. We really do. There are some pretty smart and clever people on this forum, but you must realize, they are not magicians or wizards or gods. They cannot see your code, they can’t see your errors and they don’t even know what your AUT is meant to do even though it might seem perfectly obvious to you. So please relax and follow the advice below, before you start writing your post. The advice below will tell you the exact information …

Thanks,
Albert

[kazurayam]

Excel and Outlook would be nice friends for you.

How to Send Automatic Email from Excel to Outlook (4 Methods)

You shouldn’t try to reinvent wheels in Katalon Studio.

[himanshupawar.work]

Certainly! In Katalon Studio, you can utilize the built-in libraries such as Apache POI for handling Excel files and JavaMail for sending emails. Here’s a simple example of how you can upload an Excel data file, read the expiry date column, and send an email based on the expiry date.

1. Set up Email Configuration:
   Use the same EmailConfig class from the previous example to set up your email configuration.

2. Create a Test Case in Katalon Studio:
   Create a new test case in Katalon Studio. Here’s a simplified example:

import com.kms.katalon.core.testdata.ExcelData
import com.kms.katalon.core.testdata.TestDataFactory

// Set the path to your Excel file
String excelFilePath = "path/to/your/excel/file.xlsx"

// Load Excel data
ExcelData excelData = TestDataFactory.findExcelData(excelFilePath)

// Get column index for the expiry date
int expiryDateColumnIndex = excelData.getColumnIndex("expiry Date")

// Get the total number of rows in the Excel sheet
int totalRows = excelData.getRowNumbers()

// Iterate through rows and send emails based on expiry date
for (int i = 1; i <= totalRows; i++) {
   // Get the expiry date value for the current row
   String expiryDate = excelData.getValue(i, expiryDateColumnIndex)

   // Add your logic to check if the expiry date is approaching or has passed
   // For simplicity, let's assume that the expiry date is in the format "dd-MM-yyyy"

   Date currentDate = new Date()
   Date expiryDateObj = Date.parse("dd-MM-yyyy", expiryDate)

   if (currentDate.after(expiryDateObj)) {
       // Send email
       sendEmail("recipient@example.com", "Subject: Alert - SSL Certificate Expiry", "Dear User, Your SSL certificate will expire soon!")
   }
}

// Email sending function - use the one from the previous example
// ...

Replace "ExpiryDate" with the actual header name of the column containing expiry dates in your Excel file. Adjust the file path, recipient email address, and email content according to your requirements.

3. Run Your Test Case:
   Execute your Katalon test case, and it will read the Excel file, check the expiry dates, and send emails accordingly.

Ensure that you have the necessary libraries (Apache POI and JavaMail) added to your Katalon project. You can add them through Katalon Studio’s built-in libraries management.

This is a basic example, and you may need to adapt it based on your specific Excel file format and data structure. Additionally, consider adding error handling and logging for a more robust solution.