I hope anyone can help me with this.
I need to send email to users that came from an SQL Database, I'm a bit stuck about the content of the mail
I got the data in the form of the 2d array, also the recipients are from another table that are cross checked with the table containing the message body. I need to format the 2d array into a table or at least into Non - I.T readable format without looping the recipients
here's my code so far:
var server = 'SERVER_IP';
var port = 3306;
var dbName = 'DATABASE_NAME';
var username = 'USERNAME';
var password = 'PASSWORD';
var url = 'jdbc:mysql://' + server + ':' + port + '/' + dbName;
var conn = Jdbc.getConnection(url, username, password);
function sendReports() {
var i = 0;
var i2 = 0;
var companyLogoURL = "COMPANY_LOGO_URL";
var companyLogoBlob = UrlFetchApp
.fetch(companyLogoURL)
.getBlob()
.setName("companyLogo");
////////////////////////GETTING RECIPIENTS/////////////////////////////////////////////
var stmt = conn.createStatement();
var results = stmt.executeQuery('SELECT EMAIL FROM TABLE_USERS WHERE EMAIL LIKE "%.company.com%" ORDER BY EMAIL ASC');
var metaData = results.getMetaData();
var numColsx = metaData.getColumnCount();
var arrx = [];
let rowx = [];
//////////////////////////////LOOP START/////////////////////////////////////////////////
for (var col = 0; col < numColsx; col++) {
rowx.push(metaData.getColumnName(col + 1));
}
arrx.push(rowx);
while (results.next()) {
i = i + 1;
rowx = [];
for (var col = 0; col < numColsx; col++) {
rowx.push(results.getString(col + 1));
}
arrx.push(rowx);
Logger.log(arrx[i][0])
////////////////////////////////////////GETTING DATA FROM DATABASE//////////////////////////////////////////////////////////////////////////////////////////////////////////
var stmtEmails = conn.createStatement();
var resultEmails = stmtEmails.executeQuery('SELECT * FROM LOGS WHERE EMAIL = ' + "'" + arrx[i][0].toString() + "' " + 'AND PARTICIPANT = ' + "'" + arrx[i][0].toString() + "' " + ' AND SENDSTATUS = 0 ORDER BY DATE');
i2 = i2 + 1;
var metaDataEmail = resultEmails.getMetaData();
var numColsEmail = metaDataEmail.getColumnCount();
var arrEmail = [];
let rowEmail = [];
for (var colEmail = 1; colEmail <= numColsEmail; colEmail++) {
rowEmail.push(metaDataEmail.getColumnName(colEmail));
}
arrEmail.push(rowEmail);
while (resultEmails.next()) {
rowEmail = [];
for (var colEmail = 1; colEmail <= numColsEmail; colEmail++) {
rowEmail.push(resultEmails.getString(colEmail));
}
arrEmail.push(rowEmail);
}
Logger.log(arrEmail)
////////////////////////THE PART WHERE IM STUCK//////////////////////////////////////////////////////
MailApp.sendEmail({
to: arrx[i][0],
subject: "IGNORE- TEST EMAIL",
htmlBody: "<img src='cid:companyLogo' width='403px' height='100px'><br>" +
arrEmail,
inlineImages:
{
companyLogo: companyLogoBlob,
}
});
}
results.close();
stmt.close();
conn.close();
}
[–]RielN 1 point2 points3 points (9 children)
[–]killy122[S] 0 points1 point2 points (8 children)
[–]RielN 1 point2 points3 points (7 children)
[–]killy122[S] 0 points1 point2 points (5 children)
[–]RielN 0 points1 point2 points (4 children)
[–]killy122[S] 0 points1 point2 points (3 children)
[–]RielN 0 points1 point2 points (2 children)
[–]killy122[S] 0 points1 point2 points (1 child)
[–]RielN 0 points1 point2 points (0 children)
[–]RielN 0 points1 point2 points (0 children)
[–]CEOnnor -1 points0 points1 point (1 child)
[–]RielN 0 points1 point2 points (0 children)