-
Notifications
You must be signed in to change notification settings - Fork 1
/
BulkMailing.gs
65 lines (47 loc) · 2.59 KB
/
BulkMailing.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
/*FOR CHECKING WHETHER THE MAILS IS SENT OR NO IF NOT SENT THE BELOW
CREATED VARIABLE VALUE WILL BE PLACED THAT WILL BE USEFUL TO AVOID MAIL DUPLICATION */
var SENT_MAILS = 'SENT_ALREADY';
function sendEmail() {
/* THE BELOW VARIABLE SS IS ASSIGNED WITH THE ACCESS OF THE SPREADSHEET
WHICH WE ARE WORKING NOW */
var ss = SpreadsheetApp.getActiveSpreadsheet()
/* VARIABLE SHEET1 & SHEET2 HAVING THE ACCESS OF Sheet1 & Sheet2 THAT IS SUBPART OF THE SPREADSHEET */
var sheet1=ss.getSheetByName('Sheet1');
var sheet2=ss.getSheetByName('Sheet2');
/*THE 'n' VARIABLE IS ASSIGNED WITH THE COUNT OF NO OF ROW IS USED
THAT WILL BE ABLE TO SEND MAILS TO ALL THE E-MAIL I'D WE MENTIONED */
var n=sheet1.getLastRow();
/*AS SUBJECT IS MENTIONED IN THE SECOND SUB-PART OF SPREEDSHEET AND IN FIRST COLUMN OF SECOND ROW ,IN GET_RANGE WE MENTIONED (2,1)
*/
var subject = sheet2.getRange(2,1).getValue();
/*AS FILE LINK IS MENTIONED IN THE SECOND SUB-PART OF SPREEDSHEET AND IN SECOND COLUMN OF SECOND ROW ,IN GET_RANGE WE MENTIONED (2,1)
*/
var FILEADDRESS1 = sheet2.getRange(2,2).getValue();
var FILEADDRESS2 = sheet2.getRange(2,3).getValue();
/* THE BELOW MENTIONED FOR LOOP IS USED TO SEND THE MAILS TO ALL THE EMAILS
WITH OUT EXCEPTIONS */
for (var i = 2; i < n+1 ; i++ ) {
/* THE CHECK VARIBLE WILL GET THE VALUE OF THE VALUE OF THE FIRST SUB-SPREEDSHEET AND IN THE THIRD ROW ,IN GET_RANGE WE MENTIONED (i,3)*/
var check=sheet1.getRange(i,3).getValue();
/*IF THE CHECK GOT 'SENT_ALREADY' AS VALUE THEN THE MAIL IS ALREADY SENT TO THE MAILS TO THAT EMAIL THE LOOP WILL BE MOVED TO NEXT ROW
IF IT IS NOT ASSIGNED WITH VALUE THEN THE MAIL WILL BE SENT AND THE ROW VALUE WILL BE UPDATED AS 'SENT_ALREADY' SO IT WILL AVOID THE DUPLICTION IN FUTURE*/
if(check != 'SENT_ALREADY'){
/*AS NAME IS MENTIONED IN THE FIRST SUB-PART OF SPREEDSHEET AND IN SECOND ROW , IN GET_RANGE WE MENTIONED (i,2)
*/
var NAME =sheet1.getRange(i,2).getValue();
/* THIS IS THE BODY OF THE MAIL*/
var message = `Hi ${NAME},
---> enter your content here..
${FILEADDRESS1}
${FILEADDRESS2}
`;
/* THE EMAILADDRESS WILL BE ASSIGNED WITH THE ACCESS OF THE MAIL ADDRESS WE MENTIONED IN SPREEDSHEET SUB-PART-1 */
var emailAddress = sheet1.getRange(i,1).getValue();
MailApp.sendEmail(emailAddress,subject,message);
/*IF THE MAIL IS SENT TO THE EMAIL I'D THEN THE ROW VALUE WILL BE UPDATED AS 'SENT_ALREADY' SO IT WILL AVOID THE DUPLICTION IN NEXT TIME EXECUTION OF THE CODE*/
sheet1.getRange(i,3).setValue('SENT_ALREADY');
}
/*THIS IS USED TO TERMINATE THE ACCESS OF THE SPREADSHEET */
SpreadsheetApp.flush();
}
};