Google Sheets – Send Selected Cells from a Row via Email

google sheetsgoogle-apps-script

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

Best Answer

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: 'someemail@gmail.com', replyTo: 'another@gmail.com'};

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);
Related Topic