Case Study: Sending Bulk BOI Notice Emails

As part of compliance with the Beneficial Ownership Information (BOI) reporting requirements, I recently sent out notification emails to our clients.

Preparing the Email List

The initial step was to organize the data in an Excel file. This file contained essential information, including names and email addresses associated with the 2023 income tax filings. Since some individuals own multiple companies, there were instances where the same email address appeared multiple times. To streamline the process and avoid sending duplicate emails, I removed all duplicate email addresses from the list.

Google Sheet

---

Apps Script code

A significant portion of our clients are Korean-speaking individuals. To accommodate this, I included both Korean and English messages in the email. This ensured that every client could easily understand the notice, regardless of their preferred language.

function sendEmail_BOI() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with your sheet name
  if (!sheet) {
    Logger.log("Sheet not found. Please check the sheet name.");
    return;
  }

  var emailRange = sheet.getRange("B2:B" + sheet.getLastRow()); // Email addresses
  var values = emailRange.getValues();
  var msgRange = sheet.getRange("D2:D" + sheet.getLastRow()); // Message content
  var messages = msgRange.getValues();
  var emailSentRange = sheet.getRange("E2:E" + sheet.getLastRow()); // Sent status
  var emailSentValues = emailSentRange.getValues();
  const timeZone = Session.getScriptTimeZone();

  for (var i = 0; i < values.length; i++) {
    var emailAddress = values[i][0];
    var message = messages[i][0]; // Message for each email
    var subject = "Beneficial Ownership Information(BOI) Reporting Requirement";

    // Check if email address is valid, message exists, and email hasn't been sent
    if (emailAddress && emailAddress.toString().includes("@")  && emailSentValues[i][0] !== "SENT") {
      MailApp.sendEmail({
        to: emailAddress,
        subject: subject,
        htmlBody: `
        <div>
            <h3 style="background-color:DodgerBlue;text-align:center;border: 2px solid black;color:white;margin: 20px;padding: 20px;">
             Beneficial Ownership Information(BOI) <br>Reporting Requirement 
            </h3>
            <h4>사장님, 안녕하세요. </h4>
            <a style="text-align:center;" href="https://boiefiling.fincen.gov/"> https://boiefiling.fincen.gov/fileboir </a> 
            <p> ${message}-- Customize message content here --></p>
          </div> <hr>
            <button type="In English"> In English</button>
            <div>
            <h4>Hello, </h4>
            <a style="text-align:center;" href="https://boiefiling.fincen.gov/"> https://boiefiling.fincen.gov/fileboir </a> 
            <p> ${message} ---Customize message content here --</p> 
            </div>
        `
      });

      const timestamp = Utilities.formatDate(new Date(), timeZone, "MMM dd, yyyy HH:mm"); 
      sheet.getRange("E" + (i + 2)).setValue(`Sent at: ${timestamp}`); 
       Logger.log("Email sent to: " + emailAddress);  // Mark as SENT in column E
    } else if (!emailAddress.toString().includes("@")) {
      Logger.log("Skipping invalid email: " + emailAddress);
    } 
  }
}

Conclusion

Sending Beneficial Ownership Information (BOI) notices became much more efficient and accurate by organizing a clean email list, incorporating bilingual (Korean and English) messages, and automating the process with Google Apps Script.

Given Google’s daily email limit of 100, I was able to complete the task over several days. The script automatically marked emails as “Sent,” and restarting the process was as simple as clicking “RUN”.

If you’re managing similar tasks, I highly recommend using automation tools like Google Apps Script to save time and minimize errors.