In your work, you often write emails to your boss or clients. You may need to write and send emails to multiple people. What if you could do both with a state-of-the-art AI tool, ChatGPT-5? In this article, I guide you on how to write and even batch-send an email with this AI tool, Google Sheets, Gmail, and Google Apps Script. Before getting started, you will need a Google account.

Step-by-Step Guide

Step 1: Generate an Email Draft with ChatGPT-5

First, generate an email draft with ChatGPT-5.

Visit https://chatgpt.com/.

Type this prompt template in the textbox with the placeholder "Ask anything."

[Task]: Write an email.
[Purpose]: (set a scenario to write an email)
[Tone]: (Set the tone, such as "polite yet friendly.")
[Recipient]: (Set to who you write the email)
[Context]: (Type why you write the email. If you haven't any, type "none.")

Fill bold placeholders enclosed with "(" and ")" in the prompt.
Click Enter.

Step 2: Check and Edit the Email Draft

After ChatGPT-5 generates an email draft. Check if the email is sendable and edit the email with ChatGPT if needed. Please make sure that you don't enter personal information in your prompt.

Step 3: Generate a Google Apps Script for Email Auto-submission

After your email is ready, you will need to generate an email auto-submission Google Apps Script with ChatGPT-5.

Type this prompt template in the same textbox that you entered the previous prompt.

[Task]: Fill the gaps, "(The subject of the email here.)" and "(The body of the email here.)" in the GAS code with the subject and full HTML email. 
[Number of recipients]: (Type the number of recipients)
[Constraints]:
  - Your output must be a single code snippet.

```javascript
(Your code here)
```

  - When writing the greeting section, display the value of the variable "recipient" in a template literal to call with the recipient's name, e.g.,

    ```
Dear ${recipient};
    ```

[Code]:
```
function sendEmailsToList() {
  // Open the active spreadsheet and select the first sheet
  const spreadsheetId = "(Your spreadsheet id here.)";
  const email_address_column = "Sheet1!A2:A13"; 
  const recipient_name_column = "Sheet1!B2:B13";
  const your_info_column = "Sheet1!E1:E5";
  const otherVariableNames = ["Your full name", "Your job title", "Your company", "Your or your company's email address", "Your or your company's phone number"];

  try {
    const data = Sheets.Spreadsheets.Values;
    const email_address_data = data.get(spreadsheetId, email_address_column).values;
    const recipient_name_data = data.get(spreadsheetId, recipient_name_column).values;
    const your_info = data.get(spreadsheetId, your_info_column).values;

    let completeEmailAddressData; // validated `email_address_data`
    let completeRecipientNameData; // validated `recipient_name_data`
    let yourCompleteInfo; // validated `your_info`

    const the_last_row_number_of_the_range_for_email_addresses = Number(email_address_column.substring(email_address_column.length - 1, email_address_column.length));
    const the_last_row_number_of_the_range_for_recipients = Number(email_address_column.substring(recipient_name_column.length - 1, recipient_name_column.length));

    const the_total_cells_for_the_range_of_email_addresses = the_last_row_number_of_the_range_for_email_addresses - 1;
    const the_total_cells_for_the_range_of_recipients = the_last_row_number_of_the_range_for_recipients - 1;


    if(!email_address_data || the_total_cells_for_the_range_of_email_addresses !== email_address_data.length) { // validate if there is a data for the range or is a value at the end cell of the range.
      throw new Error(`There are missing info between A2 to A${the_last_row_number_of_the_range_for_email_addresses} cells, or you are missing value for the range's end cell.`);
    } else {
      completeEmailAddressData = email_address_data.map((email, index) => {
        if(!email[0]) { // validate if there is a email address in the cell.
          throw new Error(`A${index + 2} is missing an email.`)
        } else {
          return email[0];
        }
      });
    }

    if(!recipient_name_data || the_total_cells_for_the_range_of_recipients !== recipient_name_data.length) { // validate if there is a data for the range or is a value at the end cell of the range.
      throw new Error(`There are missing info between B2 to B${the_last_row_number_of_the_range_for_recipients} cells, or you are missing value for the range's end cell.`);  
    } else {
      completeRecipientNameData = recipient_name_data.map((recipient, index) => {
        if(!recipient[0]) { // validate if there is a recipient name in the cell.
          throw new Error(`B${index + 2} is missing a recipient's name.`);
        } else {
          return recipient[0];
        }
      });
    }

    if(!your_info || your_info.length !== 5) { // validate if there is a data for the range or is a value at the end cell of the range.
      throw new Error(`There are missing info between E1 to E5 cells, or your or your company's phone number is missing.`);
    } else {
      yourCompleteInfo = your_info.map((info, index) => {
        if(!info[0]) { // validate if there is a your info in the cell.
          throw new Error(`${otherVariableNames[index]} is missing.`);
        } else {
          return info[0];
        }
      });
    }

    // Loop through the emails and recipient names and send
    for (let i = 0; i < completeEmailAddressData.length; i++) {
      const email = completeEmailAddressData[i];
      const recipient = completeRecipientNameData[i];

      const subject = "(The subject of the email here.)"; 
      const body = `(The body of the email here.)`;

      if (email && email.toString().includes('@')) { 
        GmailApp.sendEmail(email, subject, body); 
      }
    }
  } catch (err) {
    console.error(err.message);
    return;
  }
} 
```

- Adjust the cell ranges for the 2 columns for emails and recipients according to the numbers, starting from A2 or B2 cell.

Replace (Type the number of recipients) in the prompt with the number of recipients you want to send to.
Click Enter.
After ChatGPT generates GAS code, click "Copy code" in the top right corner of the generated code block.

Source: https://chatgpt.com

Step 4: Execute the Google Apps Script

Go to https://docs.google.com/spreadsheets/.
Make sure that the Google Sheets page is your account's.
Click "Blank spreadsheet."

Source: https://docs.google.com/spreadsheets
Fill the cells as you can see below. For "name@example.com" and "name (number)," replace them with recipients' emails and names, and for "Your name", "sales director", "XYZ company," "Your or your company's email address," and "Yours or your company's phone number," replace them with yours. I recommend going through the rest of the steps with your email address and name first to test if the script works. After the test, go back to this step, and do the same with your recipients' email addresses.
Visit https://script.google.com.
Click "New project."

Source: https://script.google.com
A new project will be created. Replace the existing code with the copied code.

Source: https://script.google.com
Replace "(Your spreadsheet id here.)" in the code with the id of the spreadsheet. You can find the spreadsheet id between the "d/" and "/edit" in the URL.
```
https://docs.google.com/spreadsheets/d/${id}/edit
```
Source: https://script.google.com
Click "Ctrl + S" if your operating system is Windows or "Command (⌘) + S" if your operating system is Mac to save the code.
Click "Service."

Source: https://script.google.com
A pop-up will be opened. Scroll down the list of the APIs until you can see "Google Sheets API."

Source: https://script.google.com
Click "Google Sheets API."

Source: https://script.google.com
Click "Add."

Source: https://script.google.com
Click "Run."

Source: https://script.google.com
A pop-up will be opened. Click "Review permissions."

Source: https://script.google.com
Sign in to your account.
Another pop-up will be opened. Click "Advanced."

Source: https://account.google.com
Click "Go to Untitled project (unsafe)."

Source: https://account.google.com
Put a tick in the checkbox to give permission to access Google services to your project.

Source: https://account.google.com
Click "Continue."

Source: https://account.google.com
The script will be executed.

Here's the example of an email sent.

Source: https://mail.google.com

Advanced Option: Schedule Email Submission

When you send an email, you want to send it at an optimal time. To schedule email submission, you can use Google Apps Script's time trigger. Here's how to do this.

Go back to your Google Apps Script project.
Click the button with a timer in the sidebar.

Source: https://script.google.com
Click "Add Trigger" in the bottom right corner of the screen.

Source: https://script.google.com
Click "Hour timer."

Source: https://script.google.com
Choose "specific date and time."

Source: https://script.google.com
Enter when your email will be sent in this format: (year)-(month)-(day) (hour):(minute). For hour, if it's less than 10, append 0 before the hour, e.g., 08.

Source: https://script.google.com
Click "Save."

Source: https://script.google.com
Email submission will be scheduled.

Source: https://script.google.com

Use Cases

Customer Feedback Requests

Real customer feedbacks help your business improve quickly. Especially, after your customers use your product or service, you want their real opinions. To increase the chance of getting them, ChatGPT can generate tailored survey invitations, and the batch-submmision script sends them to recent customers listed in Sheets. It helps businesses collect data for service improvement and cycle PDCA quickly. Here's the prompt parameter example and the result.

Parameter Example

[Task]: Write an email.
[Purpose]: To invite customers for customer feedback survey. 
[Tone]: Polite, but not pussy. 
[Recipient]: our customers 
[Context]: Our customers recently uses our social media marketing agency service.

Result

Subject: We’d love your feedback on our services

Dear [Customer’s First Name],

Thank you for choosing our social media marketing services. We’re committed to helping your business grow, and your opinion plays a key role in shaping how we improve.

We’d greatly appreciate it if you could take a few minutes to complete our short feedback survey. Your insights will help us refine our strategies and continue delivering the results you deserve.

👉 [Take the Survey]

Thank you for your time and trust. We look forward to serving you even better.

Warm regards,
[Your Name]
[Your Position]
[Your Agency Name]

Event Reminder

When you hold an event or invite someone to the event, you will often write an email to remind them about the event. To ensure that an appropriate email will be sent timely, you can schedule email submission using Google Apps Script's time trigger. This way, you won't need to hurry for the submission delay. Here's the prompt parameter example and the result.

Parameter Example

[Task]: Write an email.
[Purpose]: Remind the team about the upcoming team-building lunch.
[Tone]: Casual and upbeat.
[Recipient]: Marketing team members.
[Context]: Lunch will be held at 12:30 PM in the cafeteria, and attendance is encouraged but optional.

Result

Subject: Reminder: Q3 Report Deadline – Friday EOD

Dear Finance and Analytics Team,

I hope this message finds you well. This is a friendly reminder that the deadline for submitting the Q3 reports is Friday, end of day. Timely submission is essential to ensure the executive team has adequate time to review before the upcoming meeting.

If you are facing any issues or anticipate delays, please let me know as soon as possible so we can address them proactively.

Thank you for your cooperation and attention to this important deadline.

Best regards,
[Your Name]

Internal Team Notification

If you are communicating with other team members as a team manager, you may have recurring scenarios like deadline notifications across projects. You can automate this task by generating these emails with ChatGPT and scheduling the submission with Google Apps Script's time trigger. Here's the prompt paramter example and the result.

Paramter Example

[Task]: Write an email.
[Purpose]: Remind the team of the approaching deadline for the Q3 report.
[Tone]: Polite yet urgent.
[Recipient]: Finance and analytics team.
[Context]: Reports must be submitted by Friday EOD to meet the executive review schedule.

Result

Subject: Reminder: Q3 Report Deadline – Friday EOD

Dear Finance and Analytics Team,

I hope this message finds you well. This is a friendly reminder that the deadline for submitting the Q3 reports is Friday, end of day. Timely submission is essential to ensure the executive team has adequate time to review before the upcoming meeting.

If you are facing any issues or anticipate delays, please let me know as soon as possible so we can address them proactively.

Thank you for your cooperation and attention to this important deadline.

Best regards,
[Your Name]

Troubleshooting

Emojis in an HTML email body are displayed incorrectly.

If some emojis in an HTML email body are displayed incorrectly as you can see below, ask ChatGPT with this prompt below.

You didn't change all emojis into HTML entities.

In summary, you learned how to write and auto-send an email with ChatGPT, Google Sheets, Gmail, and Google Apps Script. If you want to streamline sales email creation and submission with ChatGPT-5, this article below helps with this.

Gen AI Times Tool

Search This Blog

How to Write and Auto-Send an Email with ChatGPT

Step-by-Step Guide

Step 1: Generate an Email Draft with ChatGPT-5

Step 2: Check and Edit the Email Draft

Step 3: Generate a Google Apps Script for Email Auto-submission

Step 4: Execute the Google Apps Script

Advanced Option: Schedule Email Submission

Use Cases

Customer Feedback Requests

Event Reminder

Internal Team Notification

Troubleshooting

Emojis in an HTML email body are displayed incorrectly.

Comments

Post a Comment