function NoticeEmailer() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var today = Utilities.formatDate(new Date(), "GMT+2", "d/M"); if(ss.getSheetByName(today)) { ss.setActiveSheet(ss.getSheetByName(today)); var sheet = ss.getActiveSheet(); var range = sheet.getRange(1, 1, 2); var values = range.getValues(); var Email_Title = (values[0][0]); var Email_Sent = (values[1][0]); var Email_Message = ""; var Notice_Count = 0; if (Email_Sent == "These daily notices have been sent") { // Email already sent. } else { var range = sheet.getRange(3, 1, 30); var values = range.getValues(); for (var row in values) { for (var col in values[row]) { var notice = values[row][col]; if (notice == "") { // Blank Notice } else { Notice_Count++; Email_Message += "\n\n- " + notice; } } } if (Notice_Count > 0) { MailApp.sendEmail("nparker@dainferncollege.co.za", Email_Title, Email_Message); // Email me MailApp.sendEmail("highschoolstaff@dainferncollege.co.za", Email_Title, Email_Message); // Email the High School Staff MailApp.sendEmail("highschool@dainferncollege.co.za", Email_Title, Email_Message); // Email the High School Students } sheet.getRange(2,1).setValue("These daily notices have been sent"); SpreadsheetApp.flush(); } } }