How to send emails to multiple addresses at once from Google Sheets

I recommend you to check out this article if you are looking for a tool to send emails to multiple addresses.

I want to share how to make a tool to send a lot of emails at once with Google Sheets and Google Apps Script in this article.

Create a new spreadsheet

Prepare a new spreadsheet at first and create two sheets like below.

Email List
Email Content

Email List Sheet

Write down a title and descriptions in the first row, and then fill the items' names in the second row.

No.	Company	Department	Person in charge	Email address
1	Test Inc.	QA	Test Name	`example@test.com`
2	Smaple Inc.	Marketing	Sample Name	`example@test2.com`

Click on Insert > Drawing from within Google Sheets.
Make a send button as you like and assign a main script.

📖 Add A Google Sheets Button To Run Scripts

Email Content Sheet

Create this table and fill the content of the email you want to send.
If you want to specify multiple CCs and BCCs, connect them with commas.

You can also use these variables to change each content as send emails.



Company: {COMPANY}
Department: {DEPARTMENT}
Person in charge: {PIC}

Item	Description
Test email address	`example@test.com`
CC	`example@testcc.com`
BCC	`example@testbcc.com`
Body	Email content

Click on Insert > Drawing from within Google Sheets.
Make a test email button as you like and assign a testEmail script.

Prepare Apps Script

Open Apps Script from within Google Sheets.

📖 Extending Google Sheets

Use this template if you would like to customize Google Apps Script.

🗂 gas-spreadsheet-mail

Otherwise create a sendEmail.gs, and then copy and paste this code.



function main(){}(()=>{"use strict";var e,t,a,r={708:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.getUserName=void 0,t.getUserName=function(){var e=Session.getActiveUser(),t=ContactsApp.getContact(e.toString());return{fullName:t.getFullName(),familyName:t.getFamilyName(),givenName:t.getGivenName()}}},598:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.sendEmailToAll=void 0;var r=a(708);t.sendEmailToAll=function(e,t,a,n){var i=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Content"),l=SpreadsheetApp.getUi(),o=(0,r.getUserName)(),s=o.fullName,c=o.familyName,u=o.givenName;if(i){for(var d="",p="",m="",g="",f=i.getLastRow()-1,v=0;v<f;v++){var h=i.getRange(2+v,1).getValue(),A=i.getRange(2+v,2).getValue();"CC"===h&&(m=A),"BCC"===h&&(g=A),"Subject"===h&&(d=A.replace("{COMPANY}",t).replace("{DEPARTMENT}",a).replace("{PIC}",n)),"Body"===h&&(p=A.replace("{COMPANY}",t).replace("{DEPARTMENT}",a).replace("{PIC}",n).replace("{MY_FULL_NAME}",s).replace("{MY_FAMILY_NAME}",c).replace("{MY_LAST_NAME}",u))}var E={cc:m,bcc:g};GmailApp.sendEmail(e,d,p,E)}else l.alert("🚨 The sheet name may be incorrect. It should be Email Content.")}},690:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.checkDuplicate=void 0,t.checkDuplicate=function(e){return e.filter((function(e,t,a){return a.indexOf(e)===t&&t!==a.lastIndexOf(e)}))}},506:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.validateEmail=void 0,t.validateEmail=function(e){return/^[A-Za-z0-9]{1}[A-Za-z0-9_.-]*@{1}[A-Za-z0-9_.-]+.[A-Za-z0-9]+$/.test(e)}}},n={};function i(e){var t=n[e];if(void 0!==t)return t.exports;var a=n[e]={exports:{}};return r[e](a,a.exports,i),a.exports}i.g=function(){if("object"==typeof globalThis)return globalThis;try{return this||new Function("return this")()}catch(e){if("object"==typeof window)return window}}(),e=i(598),t=i(506),a=i(690),i.g.main=function(){var r=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email List"),n=SpreadsheetApp.getUi(),i=Browser.msgBox("Confirmation","Are you sure to send email？👀",Browser.Buttons.YES_NO);if(r&&"yes"==i){for(var l=2,o=3,s=4,c=5,u=r.getLastColumn(),d=1;d<=u;d++){var p=r.getRange(2,d).getValue();"Company"===p&&(l=d),"Department"===p&&(o=d),"Person in charge"===p&&(s=d),"Email address"===p&&(c=d)}var m=r.getLastRow()-2,g=[],f=[],v=[];for(d=0;d<m;d++){var h=r.getRange(3+d,l).getValue(),A=r.getRange(3+d,o).getValue(),E=r.getRange(3+d,s).getValue(),N=r.getRange(3+d,c).getValue(),y={company:h,department:A,pic:E,address:N};(0,t.validateEmail)(N)?(g.push(y),f.push(N)):""===N||(0,t.validateEmail)(N)||v.push(N)}if(v.length>0)return n.alert("🚨 Invalid email address: ".concat(v.join(", ")));var _=(0,a.checkDuplicate)(f);if(_.length>0)return n.alert("🚨 Duplicate email address: ".concat(_.join(", ")));g.forEach((function(t){""!=t.address&&(0,e.sendEmailToAll)(t.address,t.company,t.department,t.pic)})),n.alert("📤 Sent email","It's done!",n.ButtonSet.OK)}else"no"==i?n.alert("Send canceled!"):n.alert("🚨 The sheet name may be incorrect. It should be Email List.")}})();

🗂 sendEmail.gs

Create one more script file named testEmail.gs in Apps Script.



function testEmail(){}(()=>{"use strict";var e,t,a={598:(e,t)=>{t.__esModule=!0,t.sendEmailToAll=void 0,t.sendEmailToAll=function(e,t,a,r){var i=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Content"),n=SpreadsheetApp.getUi();if(i){for(var l="",o="",s="",d="",c=i.getLastRow()-1,p=0;p<c;p++){var g=i.getRange(2+p,1).getValue(),u=i.getRange(2+p,2).getValue();"CC"===g&&(s=u),"BCC"===g&&(d=u),"Subject"===g&&(l=u),"Body"===g&&(o=u.replace("{COMPANY}",t).replace("{DEPARTMENT}",a).replace("{PIC}",r))}var m={cc:s,bcc:d};GmailApp.sendEmail(e,l,o,m),n.alert("📤 Sent email","It's done!",n.ButtonSet.OK)}else n.alert("🚨 The sheet name may be incorrect. It should be Email Content.")}},506:(e,t)=>{t.__esModule=!0,t.validateEmail=void 0,t.validateEmail=function(e){return/^[A-Za-z0-9]{1}[A-Za-z0-9_.-]*@{1}[A-Za-z0-9_.-]+.[A-Za-z0-9]+$/.test(e)}}},r={};function i(e){var t=r[e];if(void 0!==t)return t.exports;var n=r[e]={exports:{}};return a[e](n,n.exports,i),n.exports}i.g=function(){if("object"==typeof globalThis)return globalThis;try{return this||new Function("return this")()}catch(e){if("object"==typeof window)return window}}(),e=i(598),t=i(506),i.g.testEmail=function(){var a=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Content"),r=SpreadsheetApp.getUi();if(a){for(var i="",n=a.getLastRow()-1,l=0;l<n;l++){var o=a.getRange(2+l,1).getValue(),s=a.getRange(2+l,2).getValue();"Test email address"===o&&""!==s&&(i=s)}(0,t.validateEmail)(i)?(0,e.sendEmailToAll)(i,"Test Inc.","QA","Test Name"):r.alert("🚨 Invalid email address!")}}})();

🗂 testEmail.gs

Test Apps Script

That's all to set up the script. Let's send emails to confirm it is not a problem with this tool.

Notes

You need to pay attention to Gmail sending limits when you use Google Apps Script to send emails.

https://support.google.com/a/answer/166852?hl=en

Blog