3

I am starting with Google Scripts and I was hoping to create a sheet that could email selected cells from a row. What I want to do by selecting Send Email at the end of a row it then selects pre defined cells on the said row and then emails it. I created something in VBA for Excel that does the same trick as shown below.

I am essentially trying to replicate what I have done below using Google Sheets if possible to do via Scripts. Any advice would be much appreciated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Long
If Target.Count = 1 Then

    If Target.Value = "Send Email" Then
        r = Target.Row
        With CreateObject("Outlook.Application").CreateItem(0)
            .Subject = Cells(r, 5).Text
            .Body = "============" & vbNewLine & Cells(r, 7).Text & vbNewLine & "============" & vbNewLine & Cells(r, 6).Text
            .To = Cells(r, 4).Text
            .SentOnBehalfOfName = "7828691"
            .Display
        End With

End If 
End Sub

I have also attached my Google sheet so far to show an example.

Here is my Google Sheet so far

1 Answer 1

2

While it's possible to insert a button (Insert > Drawing) and assign a script to it, the resulting buttons float over the sheet in their own layer, not being bound to any cell. This wouldn't work for you since you want the action to be related to a particular row of the sheet.

Instead, I would use a custom item of the main menu, added by the function onOpen below, every time the spreadsheet is open (so, after entering the script you'll need to close and open the spreadsheet for it to appear). The menu command "send email" sends email using the data from the active row (the one where the cursor is now). It doesn't matter where in the row the cursor is.

Otherwise the logic is pretty straightforward: get the handle of the active sheet, get the number of the active row, get values in the active row, use them to compose an email.

function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var row = SpreadsheetApp.getActiveRange().getRow();
  var values = sheet.getRange(row, 1, 1, 10).getValues();   // or how many columns you want from that row; 10 seem to be enough for your data
  var recipient = values[0][2];   // indices of JavaScript are 0-based.  
  var subject = values[0][3];     // 0th row of the acquired range, 3rd column
  var body = values[0][6] + '\n========\n' + values[0][4]; 
  MailApp.sendEmail(recipient, subject, body);
}  

function onOpen() {
  var menu = [{name: "Send Email", functionName: "sendEmail"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

You can add options with

var options = {cc: '[email protected]', replyTo: '[email protected]'}; 

or

var options = {cc: values[0][7], replyTo: values[0][8]}; 

if you take them from the spreadsheet. Then the sending command will be

MailApp.sendEmail(recipient, subject, body, options);
9
  • Wow this is awesome Normal Human. Is it also possible to Carbon Copy (cc) and sent on behalf of? Or is this something that cat be done?
    – neosegauk
    Nov 3, 2015 at 19:54
  • 1
    You can add cc and replyTo as options: see documentation. No "on behalf of" that I know of.
    – user79865
    Nov 3, 2015 at 20:05
  • Hi Normal Human. This is now the amended code but the cc is not working, any ideas? (see below)
    – neosegauk
    Nov 3, 2015 at 20:48
  • So Normal Human if I want to always CC my email address (which is [email protected]) whenever I send an email using your code above in my spreadsheet what's the best way to do this?
    – neosegauk
    Nov 3, 2015 at 21:24
  • 1
    Did you read what I added to my answer?
    – user79865
    Nov 3, 2015 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.