Case Study: Send bulk 2024 Income Tax Notice Emails

As we approach the 2024 income tax season, it’s essential to streamline communication with clients. To ensure all necessary information is collected in a timely manner, I’m planning to send personalized emails tailored to each client’s needs. Here’s how the process is set up:

1. Organize Client Data in Google Sheets

The email will be sent to clients who own small businesses (primarily S-corp, but sometimes C-corp or self-employed). Other cases will be addressed individually. The income tax due date is 3/15 for S-corp and 4/15 for most other cases. Self-employed individuals need to fill out the Schedule C worksheet, while other business types require a business tax form, which is created at my office. However, businesses that request bookkeeping services from us do not need to complete these forms. When sending the emails, we will ask specific questions and attach the relevant files for each individual at the same time.

Before sending the emails, it’s important to organize the client data. The following information is required:

• Email: The client’s email address, used for sending the notifications.
• Condition: Indicates the preferred language for the email (e.g., eng for English).
• Messages: Include personalized instructions or reminders for each client.
• Attachment: Default attachments include two files (pdfFile_schE, pdfFile_Forg). Additionally:
  • “ind”: Attach individual income tax forms, such as the Schedule C worksheet.
  • “s”: Attach business tax forms, such as the 1120-S worksheet.
  • Businesses that request bookkeeping services don’t need neither.
• Type: Identifies whether the client’s business is subject to 1120-S.
  • “s”: For S-corporations.
• Date: Automatically calculated tax filing due date for the client’s business type:
  • For S-corporations: 03/15/2025
  • For other cases: 04/15/2025
  • Function: “=if(isnumber(search(“s”,G2)),”03/15/2025″,”04/15/2025″)”

Google Sheet

---

2. Sample Email Workflow

Once your data is organized, you can automate the email sending process. Here’s an example of how the emails can be structured:

3. Apps Script

Use Google Apps Script to automate the process. Below is a script tailored to send emails with the default two attachments and additional ones based on the client’s requirements.

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

  var data = sheet.getDataRange().getValues();
  if (data.length <= 1) {
    Logger.log("No data to process.");
    return;
  }

  // File attachments
  const pdfFile_schC = DriveApp.getFileById("111111111").getAs(MimeType.PDF);
  const pdfFile_schE = DriveApp.getFileById("111111111").getAs(MimeType.PDF);
  const pdfFile_Corp = DriveApp.getFileById("111111111").getAs(MimeType.PDF);
  const pdfFile_Forg = DriveApp.getFileById("111111111").getAs(MimeType.PDF);

  const subject = "2024 Income Tax Return";
  const timeZone = Session.getScriptTimeZone();

  // Loop through rows of data, starting from the second row
for (let i = 1; i < data.length; i++) {
    const [name, email, eng, msg, sentStatus, form, type, due] = data[i];
    if (!email || !email.includes("@") || (sentStatus && sentStatus.toLowerCase().includes("sent"))) {
      continue;
    }

    let attachments = [pdfFile_schE, pdfFile_Forg];          // ************************Default Attachments
    if (form && form.toLowerCase().includes("ind")) {
      attachments.push(pdfFile_schC);
    }
    if (form && form.toLowerCase().includes("s")) {
      attachments.push(pdfFile_Corp);
    }

    // Build the email message    ************************************** Korean
    let htmlMessage = `
        <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; line-height: 1.5;">
        <h1 style="background-color:DodgerBlue;text-align:center;border: 2px solid black;color:white;margin: 20px;padding: 20px;">
          Notice: 2024 Income Tax Return  </h1>
            <h4>${name} 사장님, 안녕하세요. </h4>
            <p> 2024년도 인컴 텍스 리턴 관련 내용 안내해드립니다.  회사는 ${due}이 due date입니다. 첨부파일은 pdf 파일 있습니다. ${msg} </p>
        <p>  <h4>I. 회사 관련:</h4>
      <p> 회사 관련  서류도 있으시면 보내주세요. </p>
        </div> 
      `;
    
      // Append different paragraphs based on message value
    if (form.toLowerCase().includes("ind")) {
        htmlMessage += `
           <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; line-height: 1.5;"> <p> 그리고 첨부해드린 Sch.C Worksheet 서류를 작성하셔서 보내주세요.</p></div> 
        `;
    } 
    if (form.toLowerCase().includes("s")) {
        htmlMessage += `
     <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; line-height: 1.5;"> <p>  그리고 첨부해드린 Corporation Income Tax Worksheet 서류를 작성하셔서 보내주세요.</p></div> 
        `;
    } 
      // Append the common "Ending" message
      htmlMessage += `
     <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; line-height: 1.5;"> 
            <h4>II.  개인 질문:</h4>
            <p> 질문. </p>           
            <ol>
              <li>1</li> <br>
              <li>2</li><br>
              <li>3</li><br>  
            </ol>  
            감사합니다.  </p>
      </div>
    `;  

    if (eng=="eng"){       // *********************************************  English
       htmlMessage = `
    <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; line-height: 1.5;">
     <h4 style="background-color:DodgerBlue;text-align:center;border: 2px solid black;color:white;margin: 20px;padding: 20px;"> Notice: 2024 Income Tax Return </h4>
        <h4>Dear ${name}, </h4>
        <p> We would like to provide you with details regarding the 2024 Income Tax Return. <br> The due date for businesses is ${due}. <br> PDF files are attached. ${msg} </p>
        <h4>I. For Businesses:</h4>
        </div> 
      `;

// Append different paragraphs based on message value
if (form.toLowerCase().includes("ind")) {
    htmlMessage += `<div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; line-height: 1.5;">
        <p> Also, please fill out and send the attached Sch.C Worksheet.</p></div>
    `;
} 
if (form.toLowerCase().includes("s")) {
    htmlMessage += `<div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; line-height: 1.5;">
        <p> Please fill out and send the attached Corporation Income Tax Worksheet.</p></div>
    `;
} 
// Append the common "Ending" message
htmlMessage += `
<div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; line-height: 1.5;">
          <h4>II. For Individuals:</h4>
        <p> Questions </p>    
        <ol>
              <li>1</li> <br>
              <li>2</li><br>
              <li>3</li><br>  
             </ol>  <br>
        Thank you.  </p>
</div>
`;
    }
    try {
      MailApp.sendEmail({
        to: email,
        subject: subject,
        htmlBody: htmlMessage,
        attachments: attachments,
      });

      // Log the timestamp in the sheet
      const timestamp = Utilities.formatDate(new Date(), timeZone, "MMM dd, yyyy HH:mm");
      sheet.getRange(i + 1, 5).setValue(`Sent at: ${timestamp}`);
      Logger.log("Email sent to: " + email);
    } catch (error) {
      Logger.log("Error sending email to " + email + ": " + error.message);
    }
  }
}

3. Conclusion

Organizing client data is essential for efficient tax email notifications. By gathering key information—such as email, language preference, messages, attachments, business type, and due date—you can streamline the process. Automating email sending saves time and ensures consistency, keeping clients informed and on track with their tax filings.