When you send an email campaign through Gmail, you’ve an option to specify a different reply-to email address for your emails. When the email recipient hits the “Reply” or “Reply All” button, the To field in their email reply will be automatically populated with the email address that you’ve specified as the Reply-to email at the time of sending.
You can even specify more than one email addresses in the reply-to field, separated by commas, and they will all show up in the To field of the reply field. For instance, you could send emails from your own email address but the replies would be received in your email inbox as well as the support team.
Next click on the Configure menu to open the Mail Merge sidebar.
Here go the Reply-to address file and type an email address. If you wish to receive replies on multiple email addresses, type them all here separated by commas.
Now when you send the email campaign, open one of the emails in the sent items folder, expand the message header and you should see the specified email addresses listed in the reply-to field.
Why is Google Ignoring the Reply-to Address
If you send a test email to yourself, you’ll get the email in your inbox. If you hit the reply button in that message, you may notice that that reply-to field contains your own email address and not the custom email address(es) that you’ve specified in your mail merge.
That’s the default behavior in Gmail if the “from” address on an email message is the same as the “to” address or is one of your own email aliases. To test your reply-to functionality, you should send emails to an email address that is not connected to your current Gmail account or set the “From” address as a non-Gmail address.
Say you have two email accounts - personal@gmail.com and work@company.com - the former is your personal email address while the latter is your work (corporate) email address. You are logged into your personal Gmail account but would like the mail merge to go out from your work account.
Consider another scenario where the personal secretary needs to sends out emails on behalf of her boss. So she runs the merge while staying logged into her own Google account but the recipients will see her boss’s email as the sender.
Both these scenarios can be easily handled in Mail Merge and Document Studio by setting up aliases in Gmail.
You can even send emails from your Outlook, Yahoo or Zoho email addresses through aliases in Gmail. Students and teach can send emails from their @school.edu email address while logged into their personal email accounts.
How to Setup Email Aliases in Gmail
Open gmail.com on your desktop computer, go to Settings, click the Accounts tab and, under the “Send mail as” section, click the link that says “Add another email address.”
Next, follow the wizard to add the other email address as an alias to your current email address. You need to specify the sender’s name, the email address on behalf of which you wish to send out th emails and the reply-to address (optional).
Treat as an alias - Yes or No?
If you own both email accounts, check the “treat as alias” option and emails sent to either address will arrive in the same Gmail inbox.
If you are sending emails on behalf on another person, say a secretary sending emails on behalf of his or her boss, the option should be unchecked. When the recipient replies to messages you send from the alias, the To: field in their reply message will be filled with your alias email address.
Gmail will send a verification code to your other email address that you wish to add as an alias to verify whether you really own or have access to that email account.
Log in to your other Gmail account and open confirmation email. Copy the confirmation code and paste it into alias verification box. Your other email address will now be added as an alias to your primary email address.
Change the Email Sender
This is how you can send emails from a different address in Google Sheets.
Mail Merge for Gmail
Open your Google Sheet, choose Addons > Mail Merge with Attachments > Configure to open the merge sidebar. Expand the “Sender’s Email” dropdown and it will have a list of all email aliases that are connected to your Gmail account.
You can now send personalized emails on behalf of any email address that you own or have verified as an alias.
Document Studio
Open Google Sheet, go to Add-ons > Document Studio > Open. Expand the Mail Merge section and click on edit to open the Email Template designer. You’ll have an option to change the email sender as shown in the screenshot below:
Error - Cannot Add Aliases in Gmail
Are you getting an error while adding an alias in Gmail?
You must send through gmail.com SMTP servers you can send as abc@work.com. However, this functionality is not available for your account. Please contact your domain administrator.
If you do not see an option to add aliases in your Gmail settings, it is likely because your Google Workspace (GSuite) domain administrator has disabled the option to send email from aliases in domain settings.
To enable email sender aliases in Google Works, ask the admin to:
Sign into the Google Admin console admin.google.com.
From the dashboard, go to Apps > GSuite > Gmail > End User Access.
Turn on the “Allow per-user outbound gateways” setting that says “Allow users to send mail through an external SMTP server when configuring a “from” address hosted outside your email domains.”
Save the settings and wait for about 30-60 minutes for the changes to propagate. You’ll then be able to added external email addresses as aliases in your Google Workspace email account.
Send from a different email address in Google Workspace
The Mail Merge app merges data from a Google Spreadsheet and sends them as personalized emails. You can format your sheet data in multiple colors, choose different font families, vary the size of your text, include hyperlinks, line breaks and more.
The rich text of spreadsheet cells is internally translated into HTML tags with inline CSS and thus the cell formatting is preserved in the outgoing Gmail messages. Here’s an example:
If you would like to enable this feature, go to the Add-ons menu in Google Sheets > Mail Merge with Attachments > Configure Mail Merge and check the “Preserve Cell Formatting” option.
You can even format your spreadsheet cells with conditional formatting and the text styles will be retained in mail merge. For instance, you can dynamically color the invoice amount column in red and make it bold if the due date has passed and this value would show up in bold red in the email message as well.
Send Rich Text HTML Emails with Google Sheet
This snippet handles the transformation of rich-text Spreadsheet data to HTML. The functions reads the data from a cell, specified in A1 notation, breaks the rich text into blocks that have the same text styles and translate individual blocks into HTML tags.
constsendRichEmail=()=>{const cellAddress ='A1';const sheetName ='Mail Merge';const recipient ='amit@labnol.org';const richTextValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(cellAddress).getRichTextValue();/* Run is a stylized text string used to represent cell text.
This function transforms the run into HTML with CSS
*/constgetRunAsHtml=(richTextRun)=>{const richText = richTextRun.getText();// Returns the rendered style of text in a cell.const style = richTextRun.getTextStyle();// Returns the link URL, or null if there is no link// or if there are multiple different links.const url = richTextRun.getLinkUrl();const styles ={
color: style.getForegroundColor(),'font-family': style.getFontFamily(),'font-size':`${style.getFontSize()}pt`,'font-weight': style.isBold()?'bold':'','font-style': style.isItalic()?'italic':'','text-decoration': style.isUnderline()?'underline':'',};// Gets whether or not the cell has strike-through.if(style.isStrikethrough()){
styles['text-decoration']=`${styles['text-decoration']} line-through`;}const css = Object.keys(styles).filter((attr)=> styles[attr]).map((attr)=>[attr, styles[attr]].join(':')).join(';');const styledText =`<span style='${css}'>${richText}</span>`;return url ?`<a href='${url}'>${styledText}</a>`: styledText;};/* Returns the Rich Text string split into an array of runs,
wherein each run is the longest possible
substring having a consistent text style. */const runs = richTextValue.getRuns();const htmlBody = runs.map((run)=>getRunAsHtml(run)).join('');
MailApp.sendEmail(recipient,'Rich HTML Email','',{ htmlBody });};
Known Limitations
You can format the cells of your Google Spreadsheet in any font family - from the cursive Caveat to the heavy Impact typeface - but if the recipient doesn’t have these fonts installed on their computer, the rendered text in the email would fallback to the default font.
The font colors, font size and text styles (bold, italics, underline) get perfectly transformed into HTML but other properties like background fill colors, borders and text-alignment of the cell are ignored.
Also, if your spreadsheet cells are formatted as dates, the rich text functions may not work.
This tutorial describes how you can easily send HTML emails in Gmail without using any extensions. You can format your Gmail messages to include tables, buttons, custom fonts, social media icons, wrap images around text, and more. A little knowledge of HTML and CSS will come handy but it is not a pre-requisite.
The built-in WYSIWYG editor of Gmail offers basic formatting options - you can make text bold, create lists, change font colors - but that’s pretty much it. There’s no option to insert custom HTML into the message body that is required to send rich emails though Gmail.
Write HTML Emails Directly in Gmail
Let’s start with some basic examples and then we move on to more advanced example where you’ll learn how to send email newsletters that were created separately inside MailChimp.
Insert Buttons in Gmail
This HTML snippet creates a beautiful call-to-action button with a blue background, slightly rounded edges and rendered in the popular Roboto font.
<ahref="https://digitalinspiration.com/"style="background-color:#1a73e8;padding:10px 20px;color: white;text-decoration:none;font-size:15px;font-family:Roboto,sans-serif;border-radius:5px;display:block;margin:20px 0;width: 120px">Explore our work
</a>
We cannot copy-paste this HTML directly into the email editor of Gmail but with the magic of Chrome Dev Tools, we can. Let’s see how:
Open gmail.com and compose a new email message. Add an emoji in the message body to be replaced with the HTML button. Right-click the emoji in the Gmail editor and choose Inspect.
This opens the Developer tools in the bottom half of the browser. Select the <img> tag that contains the emoji, right-click and choose Editor HTML. Replace the selected content with the button HTML and click anywhere outside the dev tools window.
You’ll now see a beautiful HTML button rendered in your email message with all the CSS styles and formatting. Watch the video for a more detailed tutorial.
Insert HTML Tables in Gmail
In the next example, we will embed an HTML table in the Gmail editor. Unlike our button above that had all styles inlined, the CSS of the table is contained in a separate stylesheet.
Therefore, prior to pasting the pasting the table HTML into Gmail, we need to “inline” the styles else the formatting will be ignored by Gmail. This can be easily done through Juice - simply paste the HTML and CSS in the input box and it will inline all the CSS styles in the HTML tags that is compatible with Gmail.
Send Email Newsletters with Gmail
Until now we have seen examples of adding basic HTML blocks inside Gmail but wouldn’t it be nice if we could send professional and responsive email newsletters through Gmail?
If you are new, the term responsive essentially means that the styles change automatically based on the device of the user. So if someone is viewing the email on a mobile phone, they would see a different layout than a person who has opened your email on a desktop computer.
You can use MailChimp or any other email template designer to create the newsletter. The only requirement is that the tool should have an option to download the designs as an HTML file.
You can feed this HTML file into Juice to inline the CSS styles and then insert the transformed HTML into Gmail using the emoji trick. The draft email newsletter can also be used as a template for sending personalized emails with Mail Merge.
The Email Extractor app pulls emails addresses of your contacts from Gmail into a Google Sheet. You can then use Mail Merge or Document Studio to send personalized emails to your contacts from within the sheet itself.
That said, the imported mailing list may sometimes have duplicate email addresses and it is thus be a good idea to clean up the data in your Google Sheet by removing duplicates before sending out the emails.
Highlight Duplicates in Google Sheets
You can use Conditional Formatting in Google Sheets combined with the COUNTIF formula to quickly highlight cells that contain duplicate values.
Here’s how you can find duplicates in Google Sheets:
Open the Google Sheet containing your data and go to the Format menu.
Select Conditional Formatting from the expanded menu. This will help us change the colors of cells, rows or columns that meet a specific criteria.
In the Apply to Range input box, add the range of cells that contain possible duplicates. In our case, the email addresses are in column A so we can put A:A to specify the entire A column.
Under the ‘Format cells if’ section, choose “Custom formula is” from the dropdown list as set the formula as =COUNTIF(A:A, A1) > 1
Click the Done button and you’ll instantly notice that all duplicate cells are highlighted as shows in the screenshot below.
The COUNTIF Function
The COUNTIF function in Google sheets (and Microsoft Excel) essentially counts the number of cells in the range that meet a specific criteria. For instance =COUNTIF(A:A, "apple") will count the number of cells that contain the word apple.
It can accept wildcard characters too so =COUNTIF(A:A, "apple?") will count cells that contain the word apple or apples. Or say =COUNTIF(A:A, "*@gmail.com") and it will highlight all email address that end with a gmail address.
Please note that the COUNTIF function is case-insensitive so values like gmail.com and Gmail.com are seen as duplicates.
Highlight Entire Row Containing Duplicates
If you’ve noticed in the previous screenshot, only specific cells that have duplicate values are highlighted through conditional formatting.
However, if you would like the Google Sheet to highlight the entire spreadsheet row that contains duplicate values, we need to slightly tweak the formatting rule.
Go to the Apply to Range input box and specify the entire spreadsheet range, not just the column that contains duplicates.
In the custom formula,use absolute reference for the range and also change criterion to use $A1 instead of A1. When we use $A1, we are telling Google Sheet to only change the row but lock the column.
The new duplicate detection formula reads =COUNTIF($A$1:$C$50, $A1)>1
Compare Multiple Columns for Duplicates
If you would like to determine duplicates by comparing data in multiple columns of the Google Sheet, use COUNTIFS instead of COUNTIF.
For instance, if column A is Email Address and column B is Company Name and you would like highlight duplicates cell only when the combination of values in Column A and B is identical, the new formula can be written as =COUNTIFS(A:A, A1, B:B, B1)>1
Remove Duplicate Rows in Google Sheets
Now that we have figured out a simple method to highlight duplicates in Google Sheets, the next task is to remove all duplicate rows.
There are two ways to go about it - either use Google Apps script or use the built-in feature of Google Sheets to remove duplicates.
First, highlight the entire column in Google Sheet that contains the duplicate data. Next, go to the Data menu and choose the Remove Duplicates option.
Select which columns to include and whether or not the selected range has any header row. Click Remove duplicates and your list is clean up in one go. Like with COUNTIF function, Google Sheets will ignore case and formatting when determining duplicates.
Remove Duplicates with Google Scripts
If you prefer automation, here’s a little snippet that will remove the duplicates in your active Google Sheet based on data in the first column.
The newer versions of Google Chrome block third-party cookies by default. Cookies are considered “bad” as they are mostly used for tracking users on the Internet but some cookies serve a useful purpose as well.
For instance, a GSuite add-on may open a small sidebar inside Google Sheets and this sidebar may need to communicate with other windows that are opened by the same add-on. Since the windows are on different domains, the only way for them to communicate with each other is through third-party cookies.
This video explains why some third-party cookies are necessary and how you can selectively allow cookies inside Google Chrome.
GSuite add-ons including Document Studio, Form Notifications and Mail Merge require you to enable third-party cookies in your browser to save user preferences and settings.
Allow Third-party Cookies for GSuite
Here are step-by-step instructions on how to enable third-party cookies in your Google Chrome browser. This will only allow cookies for GSuite add-ons in Google Sheets, Form, Docs, and Google Sides.
Step 1: Open Google Chrome browser, click the settings menu in the top right (3 vertical dots).
Step 2: Click the “Privacy and Security” option in the settings pane.
Step 3: Expand the “Cookies and other site data” section under the Privacy and Security page.
Step 4: The “Block third-party cookies” setting should be selected to continue blocking all cookies except those served by GSuite add-ons.
Step 5: Click the “Add” button under the section that says “Sites that can always use cookies” and enter the domain https://[*.]googleusercontent.com in the input box.
Click the “Add” button, reload your Google Form or Google Sheet and the addon will continue to work as before.
Mail Merge for Gmail gives you can easy option to add an “Unsubscribe” link to all your outgoing email campaigns. When an email recipient clicks the unsubscribe link, the status of their email address is set to UNSUBSCRIBED and you will not able to send email campaigns to unsubscribed users in your future mail merge campaigns.
You can also manually resubscribe any unsubscribed contact if they have unsubscribed from your campaigns by accident.
How to Include Unsubscribe Link in Mail Merge
Inside your Google Sheet, go to Addons > Mail Merge with Attachments > Configure Mail Merge and expand the section that says “Enable Email Tracking”;
Provide a Campaign Name, check the option that says “Include an Unsubscribe Link” and also provide the text of the unsubscribe link.
How Unsubscribe Works in Mail Merge
When the unsubscribe option is checked, all outgoing emails sent through Gmail Mail merge will include a remove link in the message footer automatically. The subscriber can click the link to opt-out of your future mailing lists.
When they click the unsubscribe link, they are taken to a special landing page confirming that their request for unsubscribing has been successfully recorded.
How to View Unsubscribed User Reports
Go to Add-ons > Mail Merge with Attachments > Campaign Reports > Open and Click Reports. This will open a list of email campaigns you’ve sent through Mail Merge. Click the Unsubscribe button in any specific campaign to generate a list of all email addresses that have unsubscribed from your mailing list via that particular campaign.
This will add a new “Unsubscribe Report” sheet in your Google Spreadsheet for Mail Merge. The report will include the following data to help you analyze the campaigns that resulted in the most unsubscribes:
Date when the message was sent to the user
Date when the user unsubscribed from the mailing list
Email address of the unsubscriber
Location / IP address from where the user unsubscribed
Web browser (or mobile browser) of the unsubscriber.
Here’s a sample unsubscribe report generated by Mail merge.
The Mail Merge for Gmail app lets you include an unsubscribe link in your email message. If a user clicks the unsubscribe link to opt-out of your mailing lists, you’ll not be able send future email campaigns to that email address.
You can always send emails to the unsubscribed user manually via Gmail but they will not be included in the email campaigns sent via Mail Merge for Gmail.
Reactivate an Email Address
If your contact has unsubscribed their email address from your mailing list accidentally, or if you manually unsubscribed a user from your email system, you can easily reset their subscriber status from withing the Mail merge app. You’ll then be able to resume sending email campaigns to the resubscribed user via Mail Merge.
Resubscribe email addresses manually
Open your Google Sheet, go to the addons menu, select Mail Merge with Attachments, choose Email Campaign Reports and then choose Resubscribe Contacts.
On the next screen, type one or more email addresses that you wish to reactivate for Mail Merge. You can specify up to 4 different email addresses, and each email address should be specified in its own input box.
Click the Resubscribe button to reactivate the subcriber.
John Q Public runs a travel agency and they have thousands of clients across the globe. Other than managing tickets and travel itineraries, the agency also keeps a record of passports and visas of their clients to ensure that customers have valid documents at the time of travel.
Most countries require that a foreigner’s passport must be valid for at least six months from the date of entry. The US government, therefore, recommends that you renew your passport at least nine months before it expires.
Send Automatic Emails with Google Sheets
John’s agency is looking for a reminder system that will automatically send an email notification to their customers when their passports have expired or are due for renewal in the next few months. Let’s see how they can build such a workflow in 10 minutes with the help of Mail Merge for Gmail.
The idea is simple.
We have the customer data in a Google Sheet or a Microsoft Excel spreadsheet. The “Expiry Date” column in the spreadsheet contains the date when the passport is set to expire. We setup a cron job that runs in the background and monitors the expiry date. If any date in the sheet is less than, say, 12 months from today, an automatic email reminder is sent to the customer.
Create the Reminder Email Workflow
To get started, install the Mail Merge for Gmail add-on for Google Sheets. If you have never used the merge add-on earlier, please watch the Mail Merge tutorial for a quick overview.
Next, create a new Google Sheet and choose Addons > Mail Merge with Attachments > Create Merge Template. If you have your customer data in an Excel sheet, you can easily import the records into this Google sheet using the File > Import menu.
Next, we’ll use the Array Formulas to populate the Scheduled Date column based on the date in the Expiry Date column. Go to row #2 of the scheduled date column and paste this formula:
=ArrayFormula(IF(ISBLANK(E2:E),"",E2:E-365))
The date in the Scheduled Date column will automatically be filled with a date that is 12 months before the date in the Expiry Date column. Thus if the passport expiration date is set to July 12, 2021, the follow-up reminder email would be sent exactly a year earlier on July 12, 2020.
Open the Gmail website, compose a new email message that will be the reminder template and save it in your drafts folder. The email body and subject can include column titles, enclosed inside double-curly braces and these will be replaced with actual values from the Google Sheet when the email is sent.
Auto Expiry Reminder Email
Here’s how are sample reminder email template looks like. You can also include emojis, images, and file attachments in your email drafts.
Now that our customer data is ready to be merged, go to the Addons menu in the sheet and choose Configure Mail Merge.
Here follow the step-by-step wizard to add your sender’s name and also specify addresses that you wish to CC/BCC in every merged message.
In the Create Email Template section, choose “Use a Gmail Draft” and select the draft template that you’ve created in the previous step.
Send Date-based Reminder Emails
Expand the “Send Email Campaign” section and choose “Send a Test Email” to preview your message before it gets sent to external users. If all looks good, choose “Run Mail Merge” and hit “Go”.
That’s it. Mail Merge will setup a background task that will continuously run in the background and whenever a passport is due to expire, an email reminder is automatically sent to the client based on the date in the Scheduled Date column.
You can check the “Mail Merge Logs” sheet to track progress and a copy of all emails will also be saved in your Gmail Sent Items folder.
The reminder emails are sent based on the timezone of your spreadsheet. If you would like to send emails in a different timezone, go to the File menu inside Google Sheet, choose Spreadsheet Settings and update the timezone.
You can also make use of Filters in Google Sheets to send automatic emails to rows that meet specific criteria - when the country is “India” or when a cell value contains “Follow-up” and so on.
The same date-based workflow can be utilized to automate email sending in multiple scenarios like sending personalized wishes on birthdays and anniversaries, domain renewal reminders, fee payment reminders, appointments and more.
The General Data Protection Regulation (GDPR) is aimed at giving citizens throughout the European Union (EU) control over their personal data. GDPR applies to you if you are a European Union citizen or, as a business, if your email subscribers are EU citizens, or you deal with any kind of personal data of EU citizens.
How our add-ons enable you to comply with EU’s GDPR policies
As a customer, you operate as the data controller and we are considered a data processor. You have the responsibility for ensuring that the personal data of subjects you are collecting is being processed lawfully and, similar to controllers, processors, that processes personal data on behalf of a data controller, are expected to comply with the GDPR.
Data Collection
With GDPR, you must have explicit consent from your email subscribers that they would like to receive emails from you. It is recommended that you use double opt-in to align with GDPR compliance requirements. For EU individuals who are already on your marketing lists, you may need to contact them by email asking them to confirm their consent.
You should include a visible unsubscribe link in your marketing emails that your subscribers can click to instantly unsubscribe from all your future communications.
Data Storage and Processing
All your customer’s data is stored in your Google account, inside Google Sheets, Docs, Gmail, Google Drive or Google Forms and not on our servers. Our addons read the data directly from your data source and perform the necessary actions (like sending emails, generating documents, saving emails) without transferring any personal data.
The email messages are not stored on our servers. If you choose to attach Google Drive files in your emails, the content of the files are not stored on our servers. Your form submissions are not stored on our servers. If you enable optional campaign tracking (opens, clicks, unsubscribes), the email addresses of recipients, unsubscribers, and bounced emails are stored in the database for reporting.
We store and process user data in Google Cloud database (us-east) and its servers are located in the United States (East) data center.
We use Google’ Stackdriver logging tool for error tracking and debugging errors. It includes stack traces, error messages and the logs do not include any PII data.
We use PayPal, Stripe, and Paddle to manage your payments. The payment processors only provide the customer’s email address and, in case of PayPal, the shipping address for generating invoice. We do not have access to any banking or credit card information of our customers.
Data Portability
We do not transfer, sell, make copies, or share any of your data processed by our Google Add-ons to third party services or companies. We only store data that is absolutely necessary for our add-ons to function.
You can use download and export all your subscriber’s information in Google Sheets. This allows for easier migration to other services.
Data Erasure (Right to be forgotten)
All addons have a deactivate option that will permanently delete all user’s data from the database. You can also contact us to submit a deletion requires and, in compliance towards GDPR, we’ll permanently delete all your data.
If you uninstall a Google Addon, or revoke access to the addon from your Google Account, the add-on will not be able to access any of your data and will instantly stop functioning.
Our Google Addons use your own Gmail account to send emails. You can feed your customer profile data directly into our Google Add-ons - through Google Sheets and Google Forms - to send marketing emails, transaction emails and form notifications. Our tools only facilitate your compliance to GDPR, your sending practice is key to complying with GDPR.
Contact Us
If you have any questions, please contact us by email: amit@labnol.org
Let’s say you are sending out a personalized mass email using Mail Merge for Gmail but would still like to add certain recipients in the CC or BCC list.
If you would like to include the same recipient in the CC or BCC field for all merged email, go to the Add-ons menu in your Google Spreadsheet, choose Mail Merge with Attachments, Configure and put the emails in the CC or BCC input box as shown in the following screenshot.
How to CC / BCC Different People in Mail Merge
If you would like to use different CC or BCC email addresses for each recipient of Merge, here’s how to do it.
Open your Mail Merge sheet and create two additional columns. Set the header title of these columns as CC and BCC respectively. Now you can include one or more email addresses under these columns and they’ll be CCed or BCCed automatically during the merge.
Create new columns, CC & BCC, and include one or more emails (comma separated)
CC / BCC - Things to Know
Please note that Gmail counts every CC or BCC as a separate email and thus they would be counted against your mail merge quota. If you send 2 emails and each email has 1 recipient in the CC box, your effective merge quota will be reduced by 4 (2 + 2).
Also, it is not required to include yourself in the BCC list since mail merge stores all sent emails in your Gmail Sent items folder.
The Mail Merge spreadsheet contains a mandatory column called Mail Merge Status that indicates the sending/scheduling status of merge for the recipient of that particular row.
For instance, the mail merge status column of value “Mail Sent” indicates that the email has already been sent to the recipient in that row. If you run Mail Merge again, the row will be skipped and the email will not go out to recipients mentioned in that row.
Rows #4 and #5 will not be included in Mail Merge since the Mail Status column for these rows was manually set to SKIP
Mail Merge status column can have 5 values:
Email Sent - The messages has been sent and this row will not be included in future merge until you clear the cell value.
Email Scheduled - The message is queued for future delivery. The status will auto change to Email Sent after the scheduled message is delivered.
Draft Created - If you are using Mail Merge to create drafts in Gmail, this status indicates that the draft has been created in Gmail for that recipient.
Quota Over - This indicates that you have run out of your daily email sending limit, new emails won’t be sent but you can still schedule messages.
Error Message - If merge encountered an error, maybe the email address was not valid, the status column will show the full error message.
How to Skip Sending Mails to Specific Rows
Let’s say you have a Google Spreadsheet with a dozen entries but you would only like to include, say, 5 rows in Mail Merge and skip the other rows.
You can easily do this by manually setting the Mail Merge Status column as SKIP for rows that you would not like to be included in the merge.
Also, rows with status as “Email Sent” will also not included in the merges.
Use Google Sheets Filters to Hide Rows
Mail Merge for Gmail will only send emails to rows that are visible (not hidden) in your Google Sheet. If you are using Filters inside Google Sheets to hide rows that don’t meet a certain criteria, the emails will not be sent to the hidden rows.
Say you have a Google Sheet with hundreds of rows and the column titles are Name, Country, Age and Email Address. You would only like to send emails to rows where the Country is United States and Age > 18 years.
All you have to do is create a filter inside Google Spreadsheet with the specified criteria and all the unmatched rows will be hidden. Mail merge will automatically skip the rows hidden by the Google Sheets filter.
Step 1: Select the Data Range, go to Data and Choose Filter.
Step 2: Click the Dropdown next to the column name and specify the criteria.
You can either Filter by Values (show rows that have United States as value) or Filter by Contain (show rows where the age > 18).
Google Sheets will now display rows that match your filter criteria and when you run Mail Merge, it will only email visible rows provided the corresponding Mail Merge Status column is blank.
