Send mail using google apps script in google sheets

Below function assumes that you have client details in the google sheet and e-mail listed in Column E, it will send the e-mail and then will put X in column F, so that if you run the script again it will not send the e-mail again to the one's already sent to.


function myFunction() {


var sheet = SpreadsheetApp.getActiveSpreadsheet();
var myTab= SpreadsheetApp.getActiveSheet().getName(); //gets the active sheet name
var sstc = sheet.getSheetByName(myTab);
var rowsni = sstc.getDataRange().getValues(); //get the total no. of rows with data in spreadsheet

for (var i = 2; i < rowsni.length; i++) {
if (sstc.getRange(i,6).getValue()=="X" ) //Checks if e-mail has already been sent or not
{}
else{
var toaddress = sstc.getRange(i,5).getValue(); //To which you want to send the email
  var message= "Enter Your message here"
var subject = "Enter subject of e-mail here";
MailApp.sendEmail(toaddress, subject, message, {name: "Enter the sender's name"});}
sstc.getRange(i,6).setValue("X");

}
}

 

VBA code to Select File Open File Dialog with Filter

Sub getfilename()

Dim FileName As String

FileName = Application.GetOpenFilename("CSV files(*.csv),*.csv", , "Select CSV file")

If fname = False Then Exit Sub
Range("D4").Value = fname

End Sub

VBA code to find last used row in a column

Sheets("Sheet1").Range("A65536").End(xlUp).Row

The above code will find last row used in Column A of Sheet1