Automatic send mail ceoit by google sheet

 /* Copyright © TNS Networking Solutions Pvt. Ltd.

   This software is specially developed by TNS for use by its clients

   Unauthorized use & copying of this software will attract penalties. 

   For support contact ea@tnspl.in 

*/

function onOpen() {

  var mySheet = SpreadsheetApp.getActiveSpreadsheet();   

  var menuEntries = [{name: "Send Email Now", functionName: "sendEmail"},

                     {name: "Send Test Email", functionName: "testEmail"},

                     {name: "Create Trigger", functionName: "setupTrigger"},

                     {name: "Delete Trigger", functionName: "deleteTrigger"},

                     {name: "Bounce Report", functionName: "bounce"},

                     {name: "Show Email Quota", functionName: "showQuota"},

                     {name: "Help / About", functionName: "showHelp"}];

  

  mySheet.addMenu("CEOITBOX", menuEntries);

  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

  mySheet.getRange("K1").setValue("Remaining Quota: "+MailApp.getRemainingDailyQuota());

  var authInfo = ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL);

  Logger.log(authInfo.getAuthorizationStatus());

 

}

function getQuota() {

 var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

  mySheet.getRange("K1").setValue("Remaining Quota: "+ MailApp.getRemainingDailyQuota());

}

function showQuota() {

  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

  mySheet.getRange("K1").setValue("Remaining Quota: "+ MailApp.getRemainingDailyQuota());

  Browser.msgBox("Remaining Quota: "+ MailApp.getRemainingDailyQuota());

}

function bounce(){

  var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B Report");

  e.getRange(2,1,e.getLastRow(),e.getLastColumn());

  var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

  var ss="from:(mailer-daemon@google.com OR mailer-daemon@googlemail.com)";

  var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

  var count = 0;

  var threads = GmailApp.search(ss,0,500);  

  for (var t in threads) 

  {

    var message = threads[t].getMessages()[0];

    var i=message.getPlainBody();

    var l=i.match(/Delivery to[\s\S]+?(\S+\@\S+)\s([\s\S]+?)-----/)||i.match(/Your message wasn't delivered to (\S+\@\S+) ([\s\S]+)/);

    var a='=HYPERLINK("URL";"View")'

    if(l)

    {

      var n=l[2].match(/The response.+:\s+(.+)/)||l[2].match(/The error.+:\s+(.+)/)||l[2].match(/Technical details.+:\s+(.+)/)||["",l[2].trim()];

      count++;

      var msgDt=threads[t].getLastMessageDate();

      var reason = n[1].replace(/ (Please|Learn|See).*$/,"");

      var msgSub = threads[t].getFirstMessageSubject();

      e.appendRow([msgDt,l[1],reason,msgSub]);

      threads[t].moveToTrash();

    }

  }

  if ( count != 0) 

  {

    var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

    e.getRange(2,1,e.getLastRow(),e.getLastColumn());

    e.appendRow([toDay, 'Logged these  bounced emails & deleted them: ',count]);

  }

}

function unsubscribe(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B Report");

  e.getRange(2,1,e.getLastRow(),e.getLastColumn());

  var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

  var t = 'subject:unsubscribe';

  var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

  var count = 0;

  var threads = GmailApp.search(t,0,500);  

    Logger.log(ss);

  for (var t in threads) {

    var message = threads[t].getMessages()[0];

    var i=message.getFrom();

    var l=i.split('<')[1];

    var k = l.split('>')[0];

    e.appendRow([threads[t].getLastMessageDate(),k,"Unsubscribe Request from: "+i,message.getSubject()]);

    threads[t].moveToTrash();

//    GmailApp.sendEmail(i, "your unsubscribe request", "We have received your unsubscribe request");  

    count++;  

  }

  if ( count != 0) 

  {

    var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

    e.getRange(2,1,e.getLastRow(),e.getLastColumn());

    e.appendRow([toDay, 'Unsubscribed from this Newsletter: ',count]);

  }

}

function delbounced()

{

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

  var data = sheet.getDataRange().getValues();

  var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

  var count = 0;

  for(var i = 0; i<data.length;i++){

    if(data[i][3] == "Bounced"){ 

      count++;

      var message="Removed "+data[i][1]+" - "+data[i][3];

      sheet.getRange(i+1,5).setValue(message).setFontColor('#ff0000');

      sheet.getRange(i+1,1).setValue("");

      sheet.getRange(i+1,2).setValue("");

      sheet.getRange(i+1,3).setValue("");

      //      sheet.deleteRow(i+1); 

    }

  }

  if ( count != 0) 

  {

    var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

    e.getRange(2,1,e.getLastRow(),e.getLastColumn());

    e.appendRow([toDay, 'Removed from Contacts due to Bounced or Unsubscribed', count]);

  }

  

}

function getTrackingGIF(account, email, subject) {

  var imgURL = "https://ssl.google-analytics.com/collect?"

    + "v=1&t=event"

    + "&tid=" + account

    + "&z="   + Math.round((new Date()).getTime()/1000).toString()

    + "&cid=" + Utilities.getUuid()

    + "&ec=" + encodeURIComponent("Email Open")

    + "&ea=" + encodeURIComponent(subject)

    + "&el=" + encodeURIComponent(email);

  return "<img src='" + imgURL + "' width='1' height='1'/>";

}

function sendEmail() {

  

  var emailSender = Session.getEffectiveUser().getEmail();  

  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

  var myContacts = mySheet.getDataRange();

  var myContact = myContacts.getValues();

  var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

  var newLine = "<br><br>";

  var emailSalutation = template.getRange("B9").getValue();

  var emailUnsub  = emailSender ;

  var emailReplyTo    = template.getRange("B5").getValue(); 

  var emailYourName   = template.getRange("B6").getValue();

  var emailccto = template.getRange("B7").getValue();

  var emailbccto = template.getRange("B8").getValue();

  var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

  var umsg1= "Note: This email was sent to ";

  var umsg2= '. If you do not wish to receive these emails in future <a href="mailto:'+emailUnsub+'?subject=UNSUBSCRIBE&amp;body=Please%20Unsubscribe%20me">Click Here</a>.';

  var count=0;

  var subArray= template.getRange(11, 2, 48).getValues();

  var bodArray= template.getRange(11, 3, 48).getValues(); 

  var emailReport = template.getRange("D3").getValue();

  var analyticsID = template.getRange("D2").getValue();; // Google Analytics ID 

  var emailPattern = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;

  var msgURLold='';

  

  for (i=1; i < myContact.length; i++) {

    var person = myContact[i];

    

    if (emailPattern.test(person[1]) != false && person[1] != "" && person[0] != "" && person[5] >= 1)

    { 

    var emailSub = subArray[person[5]-1];  

    var msgURL = bodArray[person[5]-1];

    if (emailSub != "" && msgURL !="")

    {

      var emailMsg1= emailSalutation + " " + person[0] + ","+newLine ;

      var emailMsg2= newLine+ umsg1 + person[1]+ umsg2;

      if (msgURLold != msgURL){ var msgBody= SitesApp.getPageByUrl(msgURL).getHtmlContent()};

      var emailMsg =emailMsg1+ msgBody+emailMsg2;

      emailMsg += getTrackingGIF(analyticsID, person[1], emailSub);  //Tracking Code

      var advancedArgs = {htmlBody:emailMsg, name:emailYourName, replyTo:emailReplyTo, cc:emailccto, bcc:emailbccto};

      GmailApp.sendEmail(person[1], emailSub, emailMsg , advancedArgs);  

      mySheet.getRange(i+1,5).setValue("Sent Msg#"+person[5]+ " on " +toDay);

      var count = count + 1;

      msgURLold=msgURL;

    }

    }

    else   

    {

      if(emailPattern.test(person[1]) != true & person[1] != "")

      {

        mySheet.getRange(i+1,5).setValue("Invalid Email Address").setFontColor('#ff0000');

        mySheet.getRange(i+1,2).setFontColor('#ff0000');

      }

    }

  }  

/*  catch(e) {    

    var f=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

    f.getRange(2,1,f.getLastRow(),f.getLastColumn());

    f.appendRow([toDay, e.message+' File:'+e.fileName+' Line:'+e.lineNumber,0]); }*/

  

  if ( count != 0) 

  {

    mySheet.getRange("I1").setValue("Actual Emails Sent:"+count);

    var emailSheduled = mySheet.getRange("H1").getValue();

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var emailMsg = emailYourName + ", " +newLine + "Have sent " + count +" messages today." + newLine +"Scheduled to " + emailSheduled + newLine +"<br /><br /><a href=\"" + ss.getUrl() + "\">Open spreadsheet</a><br />"+ "<br /><br />" +"Powered by www.CEOITBOX.com " ;  

    var emailSubject    = count + " Email Newsletters sent today";

    var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

    e.getRange(2,1,e.getLastRow(),e.getLastColumn());

    e.appendRow([toDay, emailSubject+' from this email id: '+emailSender,count]);

    GmailApp.sendEmail(emailReport, emailSubject , emailMsg , {htmlBody:emailMsg}); 

    var firstUse = mySheet.getRange("Z1").getValue();

    if ( firstUse != "1") 

    {

      var emailMsg = emailSalutation + " " + person[0] + "," + newLine + emailMsg+ newLine+ umsg1 + person[1]+ umsg2 ;

      var advancedArgs = {htmlBody:emailMsg, name:emailYourName, replyTo:emailReplyTo, cc:emailccto, bcc:emailbccto};

      var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

      e.getRange(2,1,e.getLastRow(),e.getLastColumn()).clearContent();

      e.appendRow([toDay, emailSubject+' from this email id: '+emailSender,count]);

      GmailApp.sendEmail("info@ceoitbox.com", "Script used - CEOITBOX NL Merge" , emailMsg , advancedArgs );

      mySheet.getRange("Z1").setValue("1").setFontColor('#ffffff');

    }

  }

  else 

  {

    var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

    e.getRange(2,1,e.getLastRow(),e.getLastColumn());

    e.appendRow([toDay, 'No Emails sent from ' + emailSender,0]);

  }

  mySheet.getRange("K1").setValue("Remaining Quota: "+MailApp.getRemainingDailyQuota());

  

  

  SpreadsheetApp.flush();

}

function testEmail() {

  

  // THIS FUNCTION IS USED FOR SENDING TEST EMAILS

  var emailSender = Session.getActiveUser().getEmail(); 

  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

  var myContacts = mySheet.getDataRange();

  var myContact = myContacts.getValues();

  var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

  var newLine = "<br><br>";

  var emailSalutation = template.getRange("B9").getValue();

  var emailUnsub  = emailSender ;

  var emailReplyTo    = template.getRange("B5").getValue(); 

  var emailYourName   = template.getRange("B6").getValue();

  var emailccto = template.getRange("B7").getValue();

  var emailbccto = template.getRange("B8").getValue();

  var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

  var umsg1= "Note: This email was sent to ";

  var umsg2= '. If you do not wish to receive these emails in future <a href="mailto:'+emailUnsub+'?subject=UNSUBSCRIBE&amp;body=Please%20Unsubscribe%20me">Click Here</a>.';

  var count=0;

  var emailReport = template.getRange("D3").getValue();

  var testTemplate = template.getRange("D4").getValue();

  var analyticsID    = template.getRange("D2").getValue();

  var subArray= template.getRange(11, 2, 48).getValues();

  var bodArray= template.getRange(11, 3, 48).getValues(); 

  var emailSub = subArray[testTemplate-1];  

  var msgURL = bodArray[testTemplate-1];

  template.getRange("D5").setValue("");

  

  if (testTemplate != "" && emailReport != "" && emailSub !="" && msgURL !="")

  { 

    var emailMsg1= emailSalutation + " " + emailYourName + ","+ newLine  ;

    var emailMsg2= newLine+ umsg1 + emailReport+ umsg2;   

    var emailMsg =emailMsg1+SitesApp.getPageByUrl(msgURL).getHtmlContent()+emailMsg2;

    var trackGIF=getTrackingGIF(analyticsID, emailReport, emailSub);  //Tracking Code

    emailMsg += trackGIF;  

    var advancedArgs = {htmlBody:emailMsg, name:emailYourName, replyTo:emailReplyTo, cc:emailccto, bcc:emailbccto};

    GmailApp.sendEmail(emailReport, emailSub, emailMsg , advancedArgs);  

    template.getRange("D5").setValue("Sent Test Msg for Template # "+testTemplate);

    var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

    e.getRange(2,1,e.getLastRow(),e.getLastColumn());

    e.appendRow([toDay, "Sent Test Msg for Template # "+testTemplate+' from this email id: '+emailSender,1]);

    

  }

  else {template.getRange("D5").setValue("Nothing sent !");}

}  

function setupTrigger() {

   

  // First - Deletes all triggers in the current project.

  var triggers = ScriptApp.getProjectTriggers();

  for (var i = 0; i < triggers.length; i++) {

    ScriptApp.deleteTrigger(triggers[i]);}

  

  // Second - Setup new trigger in the current project.

  

  var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

  var emailSendTime   = template.getRange("B4").getValue();

  var emailSender=Session.getActiveUser().getEmail();

  ScriptApp.newTrigger('sendEmail').timeBased().everyDays(1).atHour(emailSendTime).create(); 

  ScriptApp.newTrigger('bounce').timeBased().everyDays(1).atHour(22).create(); 

  ScriptApp.newTrigger('unsubscribe').timeBased().everyDays(1).atHour(21).create(); 

  ScriptApp.newTrigger('delbounced').timeBased().everyDays(1).atHour(23).create(); 

  template.getRange("D6").setValue('Trigger has been setup for '+emailSendTime+' Hrs from this email id: '+emailSender);

  Browser.msgBox('Trigger has been setup to send emails automatically at '+emailSendTime+' Hrs from this email id: '+emailSender);

}

function deleteTrigger() {

  // Deletes all triggers in the current project.

  var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

  var triggers = ScriptApp.getProjectTriggers();

  for (var i = 0; i < triggers.length; i++) 

  {

    ScriptApp.deleteTrigger(triggers[i]);

    template.getRange("D6").setValue('No Triggers are active');

  }

  Browser.msgBox('All Triggers have been deleted!');

  

}

SpreadsheetApp.flush();