Home>

GAS is making a code to send an email to the email address in the spreadsheet.

function mail_test ()
function gmail ()
There are two functions, but it seems that mail_test and gmail can only be executed independently.
I want to use mail_test to extract the email address to send an email from a spreadsheet and send it via gmail.

mail_test seems to be running well, but when I run gmail, maybe because I can't get the address
Exception: Could not send email: No recipient specified (line 46, file "code")
Message is displayed.
I'm thinking that the email address extracted by mail_test has not been successfully passed to gmail.

At one point, it worked as expected for some reason, but it didn't work again.
I'm just starting to work on GAS and I don't know the basics ... I'm sorry.

Corresponding source code

This is GAS.

function mail_test () {

// Sheet information
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheet1 = ss.getSheetByName ('sheet 1');
var addr = ss.getSheetByName ('address management');
const EValues ​​= sheet1.getRange ('E: E'). getValues ​​();
const lastRow = EValues.filter (String) .length;

// Get subject and body
const body0 ='A is an internal contact. \ n'
const body1 ='from';
const body2 ='is';
It arrived with const body3 =', so please check it. ';
Mail has arrived at const options ='[A internal notification]';

// Loop address
for (let i = 1;i<= lastRow;i ++) {

// Extract only unsent information (If column K is blank, send it. If it has been sent, "Done" is entered in column K)
var blank = sheet1.getRange (i, 11);
if (blank.isBlank ()) {

// Title (date and notification information)
// var title = sheet1.getRange (i, 2) .getValue () + blank + sheet1.getRange (i, 5) .getValue () + options;
var title = sheet1.getRange (i, 5) .getValue () + options;
//Text
var body = body0 + sheet1.getRange (i, 3) .getValue () + body1 + sheet1.getRange (i, 7) .getValue () + body2 + sheet1.getRange (i, 8) .getValue () + body3;
//address
var address = sheet1.getRange (i, 6) .getValue ();
// Enter "Done"
var done ='done'
sheet1.getRange (i, 11) .setValue (done);

//send e-mail
gmail (address, title, body);
}
}
}

function gmail (address, title, body) {

GmailApp.sendEmail (address, title, body);

}
`` ```

What I tried

In the execution, i am supposed to select either mail_test or gmail,
I'd like to run mail_test → gmail in succession, but I don't know how to do it.
Also, as mentioned above, I was able to continue to execute successfully for a while, but why did it work?
And I don't know why it went wrong again.

Supplementary information (FW/tool version, etc.)

Thank you.

,

  • Answer # 1

    In the execution, you are supposed to select either mail_test or gmail,
    I'd like to run mail_test → gmail in succession, but I don't know how to do it.

    It's the "Run" menu in the script editor, isn't it? In the code you wrote, the process of "calling (executing) gmail in mail_test" is written, soIf you run mail_test from the menu, gmail will also be run automatically.. You don't need to run gmail from the menu.

    On the other hand, if you try to execute only gmail from the menu, an error will naturally occur because the address etc. of the argument is not specified. That is the error "Exception: Could not send email (abbreviated below)".

    Looking at the contents of gmail,GmailApp.sendEmail (address, title, body);You're just running. In this case, even if you do not bother to create a function (gmail), the part that calls gmail in mail_test is left as it isGmailApp.sendEmail (address, title, body);Should be replaced with.

    That way, there will be only one function in the script that belongs to the spreadsheet, so you can do the same thing as selecting test_mail from the Run menu with the ▶ button in the script editor.

  • Answer # 2

    When I got the last row, some cells in column E that I was referring to were blank, so the last row was a fairly low number so that it would not be hit by the if statement.