Choose email to send based on selection list

[BNI.CBD10]

Hi Henrique and Community

 

I am a new user of FormEmail, and it appears to have some good features. I have searched to forum and cannot find an answer to what I am trying to do.

 

I have created a form and one of the fields in the form is a "Choose from list" a drop down with options. There are four options in the list and only one can be selected at any time.

 

I have also set the QTT Emails in advanced options to 4, so now I see email1, email2, email3 and email4. I am able to customise these emails and provide the correct information in each one like different text and different recipients.

 

What I would like to do is choose from email1 ,2, 3 or 4 and send that email based on the selection in the drop down list..

 

E,g Refering to the "Choose From List"

 

If Option 1 selected --> Send Email1

If Option 2 selected --> Send Email2

If Option 3 selected --> Send Email3

If Option 4 selected --> Send Email4

 

Thank you for your assistance.

Ric

 

[Henrique Abreu]

Hi, this is perfectly possible.

For an email to be sent in Apps Script it must have recipients on the "To" field (note that this is not true for gmail, where you can have only Bcc, for example).

So, the trick is to use a formula that returns an empty string if the email should not be sent, or its recipient list if it should. Then use this formula placeholder on the "To" field of the respective email.

In you case, you'll need 4 new columns, one formula for each. And you're formulas will look like this:

=IF( P2 = "Option 1"; "example@domain"; "" )

Considering P to be the column the options answers are, and this to be the formula for email1.

I have explained this before here, please take a look:

Different E-mail to Different people
Getting e-mail from VLOOKUP function


Kind regards,

Henrique Abreu

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.
To view this discussion on the web visit https://groups.google.com/d/msg/formemailer/-/deA1A9NGkAEJ.
To post to this group, send email to forme...@googlegroups.com.
To unsubscribe from this group, send email to formemailer...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/formemailer?hl=en.

[Ed Nelson]

Greetings-

First of all, Thank you for this amazing script!!  I am in a similar situation.  We are building a Work Order form.  One of the selections in the form is "Building".  When they select one of our buildings we would like it to email the form to that custodian.  For example if they select "High School" it would email px...@sspps.org.  Or if they select "Kaposia Elementary" it would email "ex...@sspps.org".  The IF command makes sense to me.  I am more of a visual learner.  Is there an example of what I am looking for so I can learn and model it?  I would be happy to share and work with you on my document.  Thank you in advance!!

On Tuesday, November 6, 2012 3:36:59 AM UTC-6, Henrique Abreu wrote:

Hi, this is perfectly possible.

For an email to be sent in Apps Script it must have recipients on the "To" field (note that this is not true for gmail, where you can have only Bcc, for example).

So, the trick is to use a formula that returns an empty string if the email should not be sent, or its recipient list if it should. Then use this formula placeholder on the "To" field of the respective email.

In you case, you'll need 4 new columns, one formula for each. And you're formulas will look like this:

=IF( P2 = "Option 1"; "example@domain"; "" )

Considering P to be the column the options answers are, and this to be the formula for email1.

I have explained this before here, please take a look:

Different E-mail to Different people
Getting e-mail from VLOOKUP function


Kind regards,

Henrique Abreu

[Roy]

I still can't get this to work.

Could someone supply a simple step-by-step guide from sending different emails to different people based on selections.

Emails will be in the spreadsheet not the form.

Thanks,

Roy

[Mark Lewis]

HI Roy,

Did you manage to get it to work? I'm having problems getting it to work being a novice!

Regards

Mark

 

Providing a 24 Hour service, 365 days of the year to the communities of Mid, West and South Wales

 https://twitter.com/Western_Beacons

https://www.facebook.com/westernbeaconsmrt

This email and any attachments are intended for the addressee(s) only and may be confidential. If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email. If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way. Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility, and it is the responsibility of recipients to carry out their own virus checks.Any views expressed in this email is the view of the sender and may not be the view of WBMSART

[Dan Longley]

Hi

Can you give a specific example of what you're trying to achieve, ideally share a sheet?

Dan

--

You received this message because you are subscribed to the Google Groups "FormEmailer" group.

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer...@googlegroups.com.

To post to this group, send email to forme...@googlegroups.com.

Visit this group at http://groups.google.com/group/formemailer?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Mark Lewis]

Hi Dan

 

I'm a novice as you’ll see!

 

I've added 3 columns;

If Cell H is a 'yes' it copies the email address contained in H to cell J  or if it has a no it copies the email address to cell K, on submit form it tries to fire 2 emails (that have different text in the bodies) only one email goes as the address is taken from cell J & K which is correct (one’s blank).

 

Colume 3 uses a Vlookup, it takes the data from  F and then looks up an email dress from the lookup sheet and places that email address into L, this is so that department get a copy of the person booked into their meeting.

 

The problem is that when you submit the form it deletes my formula so the emails never go, if you manually populate the responses the code works (I guess all down to the ways I’ve tried to do it!)

 

Spreadsheet attached (not real email addresses!)

 

Thanks

 

Mark

On Friday, March 28, 2014 6:02:19 PM UTC, Dan Longley wrote:

Hi

Can you give a specific example of what you're trying to achieve, ideally share a sheet?

Dan

On 5 March 2013 21:13, Roy via FormEmailer <formemailer+noreply-APn2wQeEMbI69jENWYbl2eSFwuWt8_b_qPgjjxBWwq23@googlegroups.com> wrote:

I still can't get this to work.

Could someone supply a simple step-by-step guide from sending different emails to different people based on selections.

Emails will be in the spreadsheet not the form.

Thanks,

Roy

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.
To unsubscribe from this group and stop receiving emails from it, send an email to formemailer...@googlegroups.com.
To post to this group, send email to forme...@googlegroups.com.
Visit this group at http://groups.google.com/group/formemailer?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Dan Longley]

Hi Mark, I'm a novice too, but it doesn't stop me trying to help others even if I do get it wrong sometimes!

I like your use of VLOOKUP (I might use that a bit more!).  I can't see any formemailer script so I don't know how you've set it up, but one thing you said...

"The problem is that when you submit the form it deletes my formula so the emails never go..."

I assume that you are writing your 'master' formulae in row 2 (immediately below the header row)? Consider that row a dummy row. Then, when a form is submitted, formEmailer copies that formula from row 2, down the column to the new form submission row.  So, you do need to make some of the references 'absolute'.

For example, L2 is:

=VLOOKUP(F2,lookup!A1:B7,2,FALSE)

but should be:

=VLOOKUP($F2,lookup!$A$1:$B$7,2,FALSE)

If you now copy the latter from L2 to, say, L6 you'll find the formula is still looking for info from the correct cells:

=VLOOKUP($F6,lookup!$A$1:$B$7,2,FALSE)

Does that solve the problem?

Dan

Visit this group at http://groups.google.com/group/formemailer.
For more options, visit https://groups.google.com/d/optout.

[Mark Lewis]

HI Dan,

well I've learnt so much over the last few days, I had s few problems to resolve and I'm there  :)

• As you mentioned the $'s in the VLOOKUP were required
• You cant have blank rows, I deleted the default blank ones.
• I needed to enter the which columns I was using as formulas in the setup.

It's such a powerful form and by adding your own formulas its the best I've seen, I've been researching for this type of product for a few months 

Well done Henrique and thank you!

Regards

Mark

On Monday, March 31, 2014 8:55:07 PM UTC+1, Dan Longley wrote:

Hi Mark, I'm a novice too, but it doesn't stop me trying to help others even if I do get it wrong sometimes!

I like your use of VLOOKUP (I might use that a bit more!).  I can't see any formemailer script so I don't know how you've set it up, but one thing you said...

"The problem is that when you submit the form it deletes my formula so the emails never go..."

I assume that you are writing your 'master' formulae in row 2 (immediately below the header row)? Consider that row a dummy row. Then, when a form is submitted, formEmailer copies that formula from row 2, down the column to the new form submission row.  So, you do need to make some of the references 'absolute'.

For example, L2 is:

=VLOOKUP(F2,lookup!A1:B7,2,FALSE)

but should be:

=VLOOKUP($F2,lookup!$A$1:$B$7,2,FALSE)

If you now copy the latter from L2 to, say, L6 you'll find the formula is still looking for info from the correct cells:

=VLOOKUP($F6,lookup!$A$1:$B$7,2,FALSE)

Does that solve the problem?

Dan