title: Send personalise email from google sheet
body: <p>I'm having difficults trying to write a script to send an email based on the below table</p>
  
  <p>The "agent ldap" is the recipient
  the "TL ldap" is the CC
  and the rest 4 columns are inside the message as a table</p>
  
  <p>I'd like the message be like:</p>
  
  <blockquote>
    <p>Hello, follow your feedback regarding your requests that we have registered:</p>
    
    <blockquote>
      <p>The table with the columns C, D, E and F</p>
    </blockquote>
    
    <p>You can already check it on</p>
  </blockquote>
  
  <p>My main difficult is cause the number of rows may change (no more than five rows), also the status can change of this three ways. I'll copy past the values on this sheet, then send the email.</p>
  
  <p>Is there a way to add a conditional statement for the column F depending the name, to add the color the backgroung? Because I wanna include this column on the table too.</p>
  
  <p>My script is:</p>
  
  <pre><code>function VacationRequest() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var startRow = 2; // First row of data to process
      var numRows = 2; // Number of rows to process
      var dataRange = sheet.getRange(startRow, 1, numRows, 2);
      // Fetch values for each row in the Range.
      var data = dataRange.getValues();
      var emailRecipients = data[0] + "@google.com";
      var CCEmail = data[1] + "@google.com";
      var emailSubject = "Vacation request";
      var emailBody = "Hello, follow your feedback regarding your requests that we have registered: \
      \
      "The table with the columns C, D, E and F"
      \
      You can already check"
  
  
  MailApp.sendEmail({
    to: emailRecipients,
    subject: emailSubject,
    htmlBody: emailBody,
    CC: CCEmail
   });
  
  }
  
  
  
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/AAVZs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AAVZs.png" alt="enter image description here"></a></p>