Send a mail merge campaign to just selected rows of a spreadsheet

When connecting to a Google Sheets spreadsheet to send a mail merge campaign, you can now specify filter criteria to pull only certain email addresses that match the criteria.

You’ll notice a new Filter Rows box when connecting to a spreadsheet.

This post explains how to use the Filter Rows box and what to type to send email to just the rows that match your criteria. Specify one criteria per line, in the format:

ColumnName=Value

For example, if you have a spreadsheet column called Company, and you want to send a mail merge campaign to just everyone who’s Company is “Microsoft”, you would enter:

Company=Microsoft

Instead of the = sign, you can instead use the ~ operator to represent “contains”. For example, let’s say that your email addresses are in a column called Email. You want to send to only @yahoo.com addresses. You would enter:

Email~yahoo

meaning all rows where the Email value contains “yahoo”.

Full List of Operators

Along with = and ~ you can also use these operators:

!= for "not equals"

> for "greater than"

>= for "greater than or equal to"

< for "less than"

<= for "less than or equal to"

~ for "contains"

= for "equals"

Examples

You can use the operators above to compare strings, numbers, and dates. For example, if you have a spreadsheet column called Age, and you wanted to email just the adults in the spreadsheet, you could set:

Age >= 18

Or, let’s say you didn’t have an Age column but instead had a DateOfBirth column. Then you could set:

DateOfBirth <= 1/1/1999

assuming that anyone born before 1/1/1999 is an adult.

Special Values

There are two special values you can use to represent date values. These are:

CurrentDate

CurrentDateIgnoreYear

You can use these values to compare the data in your spreadsheet to the current system date (in the GMT time zone). For example, if you have a column in your spreadsheet called ShipDate, which represents when a customer’s order will be shipped, you could set:

ShipDate=CurrentDate

to pull just the rows where the order’s shipping date is today to let the customer know that their order has been shipped and will arrive soon.

Using CurrentDateIgnoreYear matches just the Month and Day parts of the date to the current Month and Day. For example, if you have a column called Birthday and you want to send an email to people only on their birthdays, but birthdays include the year the person was born, then this will be useful. If three of your birthday values are: 1/1/72, 4/5/80, and 3/1/90, then using “Birthday=CurrentDate” would never match the rows because the rows contain the birth year. Using “Birthday=CurrentDateIgnoreYear” however would match rows on a person’s birthday. Also, see the detailed guide to sending birthday emails with Gmail and GMass.

Multiple Criteria

You can also specify multiple criteria. Let’s say your spreadsheet has the columns Company and Position. Let’s say you want to send to everyone whose Company=Microsoft, and Position=Manager. You would enter:

Company=Microsoft
Position=Manager

Or, let’s say that in your actual spreadsheet, the Position column had values like “Product Manager” and “Technical Support Manager”, but you still wanted to email everyone at Microsoft that was some type of manager. In that case you would set the Position criteria to just “contain” the word “manager”. So:

Company=Microsoft
Position~Manager

In these cases, you want rows that match both criteria. So in these cases, the boolean operator should be set to AND. You might, however, want to switch to OR in certain cases. Let’s say your spreadsheet has all of your customers but you want to send a campaign to only customers with an email address at a consumer domain, like hotmail.com, yahoo.com, aol.com, and gmail.com. You would enter:

Email~gmail.com
Email~yahoo.com
Email~hotmail.com
Email~aol.com

And you would set the boolean dropdown to OR. Meaning you want to send to everyone where Email contains gmail.com OR Email contains yahoo.com OR Email contains hotmail.com OR Email contains aol.com.

As another example, let’s go back to our spreadsheet containing the Company column. You’re sending a mail merge campaign to executives at billion-dollar tech companies, so you want to only send to people where Company is either Microsoft, Apple, or Facebook. You would enter:

Company=Microsoft
Company=Apple
Company=Facebook

and set the boolean dropdown to OR. If you entered this criteria and set the boolean dropdown to AND, you would get an error saying that no rows could be selected, since there isn’t a single row where the Company is equal to all three of those values, as that would be impossible!

47 Replies to “Send a mail merge campaign to just selected rows of a spreadsheet”

  1. Unknown

    I've got an error after selecting the google sheet (no optional settings has been added) :

    "GMass: No recipients found for the specified Filter."

    Please help

  2. Ajay Goel

    I believe I've already helped you via email, but the answer to this is:

    This happens if your spreadsheet isn't in the right format. You must have column headings in the first row and the data must start on the second row. In the past, it was more flexible with misformatted spreadsheets, but with this new feature, spreadsheets do have to be in the right format. More info on how to make sure your spreadsheet is in the right format: http://blog.gmass.co/2015/10/mass-email-mail-merge-google-sheets.html

  3. Dennis

    Hi, I want to filter by a column with null value.

    I have tried
    col1 = 0
    and
    col1 = null

    but neither 0 or null worked.

    How do I filter by empty value?

    I got
    "GMass: No recipients found for the specified Filter."

    thank you
    Dennis

  4. Archana Hari

    Hi,

    I have a spread sheet which has multiple columns with email ids of people playing different roles in the given organisation. For eg, one column has CEO Contacts, one has fundraising contacts etc. Each row is one organisation. Is it possible for me to send only to a given column of ids?

  5. Paul Williams

    Any chance to amend this to allow for exclusions? I.e., send this email to everyone in the sheet, EXCEPT those with the company name "Microsoft". I would think this would look like this:
    Company<>Microsoft

    However, this does not work.

    Even more advanced would be "not containing Microsoft":
    Company<>~Microsoft

  6. Derek

    I’m having trouble with the Date Column=CurrentDate filter when setting this up and it is saying “string was not recognized as a valid date time” It works if I say Date=09/21/2017, but it needs to move automatically with our year.

    • Jordan

      Me too – same problem: it is saying “string was not recognized as a valid date time”

      I have data formatted as

      1| DateScheduled
      ———————————-
      2| 16/10/2017
      3| 16/10/2017

      etc

      And am using:

      DateScheduled=CurrentDate

  7. David

    I would like to filter based on only sending to a school with that has the name A – N. Is this possible. If I try sending to all of them, then I will go over my email limits. My filter will look like this
    Div=I-AA and
    (this is where I need help.)

  8. Eric

    Not sure where the rest of my comment went. Anyway, I’m getting the “string was not recognized as a valid date time” error with dates.

    • Ajay Goel

      When using a date-based filter, and using the > or < signs, EVERY row has to contain a valid date. If you have blanks, that's where an error can be thrown, because GMass doesn't know how to compute the date's value of a blank cell.

  9. zhhan

    Hi, I have 2 columns in my Google Sheets with email addresses (column 1 and 2). Is there any way I can select a specific column for the automated email to send the addresses to i.e. column 2 instead of 1.

    Thank you.

  10. Mostafa

    Thank you all @GMASS for your great support. I was wondering if there was a way to concatenate 2 operators to work together in same line. Case: I have a list of 1000 Rows and i will like GMASS to import “Rows>500 but Rows<701" (Meaning: Import rows 501 – 700) thereby selecting only 200 Emails from the spreadsheet. Thanks in advance for your help!

  11. Emerald Frederick

    Is there a way to use these filters as a suppression feature?

    For example:
    I DO NOT want it to send an email to someone with “yahoo.com” in the domain.

Leave a Reply

Your email address will not be published. Required fields are marked *