Hi,

Whenever a candidate is approved and not submitted to client by the requirement owner on same date, an e-mail has to be sent to the requirement owner by end of day. I have selected the candidates which are appoved and not submitted to client using SOQL. 

My query is "SELECT Candidate__c,Requirement__c, Requirement_Code__c, Requirement_Owner__c,Req_Owner_Email__c,status1__c FROM Candidate_Mapping__c where  Submitted_to_Client__c =''and status1__c = 'Approved' ". 

No.of rows retrieved is 55 in my sandbox environment. 

I have used text template to send this email to the owners. I am facing a issue like "No of Email Invocation is more than 10" Governor limits.

Code using text template:

public class assigncandidatestatusemailalert
{

public void SendEmail()
{

List<Candidate_Mapping__c> candidatemappinglist = new List<Candidate_Mapping__c>( [SELECT Candidate__c, Id, Req_Owner_Email__c, Requirement__c, Requirement_Owner__c, Requirement_Owner_Email__c, Status1__c,LastModifiedDate FROM Candidate_Mapping__c where Submitted_to_Client__c = '' limit 30]);

system.debug('candidatemappinglist :' + candidatemappinglist );

String CandID, ReqmtID;

if(candidatemappinglist != null)
{
Messaging.SingleEmailMessage mail;

for (Candidate_Mapping__c cm :candidatemappinglist )
{
CandID = cm .Candidate__c;
system.debug('candID:' + CandID);

ReqmtID = cm.Requirement__c;
system.debug('ReqmtID:' + ReqmtID);

Candidate__c Candt = [Select Id, Candidate_Full_Name__c from Candidate__c where Id = :CandID];
Requirement__c Reqmt = [Select Id,Name,Opportunity_Code__c,OwnerId from Requirement__c where id = :ReqmtID];
System.debug('Reqmt:' + Reqmt);
User u = [select Id, Email, Name from User where Id = :Reqmt.OwnerId];
System.debug('user:' + u);

EmailTemplate et = [Select Id, Subject, HtmlValue, Body from EmailTemplate where Name = 'Submitted to Client Alert'];
System.Debug('EmailTemplate Id: ' + et.id);
System.Debug('et : ' + et);
// mail.setSenderDisplayName('Abinaya');

// String htmlBody = et.HtmlValue;
String plainBody = et.Body;

if(u.Name != null)
{
plainBody = plainBody.replace('{!Requirement__c.OwnerFullName}', u.Name);
}

else
{
plainBody = plainBody.replace('{!Requirement__c.OwnerFullName}', ' ');
}

if(Candt.Candidate_Full_Name__c != null)
{
// htmlBody = htmlBody.replace('{!Candidate__c.Candidate_Full_Name__c}', Candt.Candidate_Full_Name__c);
plainBody = plainBody.replace('{!Candidate__c.Candidate_Full_Name__c}', Candt.Candidate_Full_Name__c);
System.Debug('Candt Name:' + Candt.Candidate_Full_Name__c);
}
else
{
//htmlBody = htmlBody.replace('{!Candidate__c.Candidate_Full_Name__c}', ' ');
plainBody = plainBody.replace('{!Candidate__c.Candidate_Full_Name__c}', ' ');
}

if(Candt.Id != null)
{
// htmlBody = htmlBody.replace('{!Candidate__c.Id}', Candt.Id);
plainBody = plainBody.replace('{!Candidate__c.Id}', Candt.Id);
System.Debug('Candt Id:' + Candt.Id);
}
else
{
// htmlBody = htmlBody.replace('{!Candidate__c.Id}', ' ');
plainBody = plainBody.replace('{!Candidate__c.Id}', ' ');
}

if(Reqmt.Name != null )
{
// htmlBody = htmlBody.replace(' {!Requirement__c.Name}', Reqmt.Name);
plainBody = plainBody.replace('{!Requirement__c.Name}', Reqmt.Name);
System.Debug('Reqmt Name:' + Reqmt.Name);
}
else
{
// htmlBody = htmlBody.replace('{!Requirement__c.Name}', ' ');
plainBody = plainBody.replace('{!Requirement__c.Name}', ' ');
}

if(Reqmt.Opportunity_Code__c != null)
{
// htmlBody = htmlBody.replace('{!Requirement__c.Opportunity_Code__c}', Reqmt.Opportunity_Code__c );
plainBody = plainBody.replace('{!Requirement__c.Opportunity_Code__c}', Reqmt.Opportunity_Code__c );
System.Debug('Candt Name:' + Reqmt.Opportunity_Code__c );
}
else
{
// htmlBody = htmlBody.replace('{!Requirement__c.Opportunity_Code__c}', ' ');
plainBody = plainBody.replace('{!Requirement__c.Opportunity_Code__c}', ' ');
}

mail = new Messaging.SingleEmailMessage();

String[] address = new String[]{u.email};
mail.setToAddresses(address);

//System.Debug('HtmlBody:' + htmlBody);

System.Debug('Plain Body:' + plainBody);

// List<User>user = new List<User>([Select Id from User where IsActive = true and ProfileId = '00e40000000wnXc' ]);
// System.Debug('UserID: ' + user.id);

//String subject = et.Subject;
//mail.setTemplateId(et.id);
mail.setPlainTextBody(plainBody);
// mail.setHtmlBody(htmlBody );


String[] bccaddress = new String[]{'abinaya_s@preludesys.com'};

mail.setBccAddresses(bccaddress);

mail.saveAsActivity = false;
// mail.setTargetObjectId(user.Id);

Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

}
}

}

}

So I am trying to send email through visualforce email template. 

For this i have created a class, component and visualforce email template.

Class:

public class SubmittedtoClientalert
{
public final List<Candidate_Mapping__c> candidatemappinglist ;
public final test[ ] candiateassign= new List<test>();
public final List<Candidate_Mapping__c> one = new List<Candidate_Mapping__c>();

public SubmittedtoClientalert()
{
candidatemappinglist = [SELECT Candidate__c,Requirement__c, Requirement_Code__c, Requirement_Owner__c,Req_Owner_Email__c,status1__c FROM Candidate_Mapping__c where Submitted_to_Client__c =''and status1__c = 'Approved' limit 30];

system.debug('candidatemappinglist :' + candidatemappinglist );

String CandID, ReqmtID;

if(candidatemappinglist != null)
{
Messaging.SingleEmailMessage mail;

for (Candidate_Mapping__c cm :candidatemappinglist )
{
test t= new test();
CandID = cm .Candidate__c;
system.debug('candID:' + CandID);
ReqmtID = cm.Requirement__c;
system.debug('ReqmtID:' + ReqmtID);

Candidate__c Candt = [Select Id, Candidate_Full_Name__c from Candidate__c where Id = :CandID];
Requirement__c Reqmt = [Select Id,Name,Opportunity_Code__c,OwnerId from Requirement__c where id = :ReqmtID];
System.debug('Reqmt:' + Reqmt);
User u = [select Id, Email, Name from User where Id = :Reqmt.OwnerId];
System.debug('user:' + u);
one.add(new Candidate_Mapping__c( LR_Comments__c= Candt.Candidate_Full_Name__c ,R_Comments__c= Reqmt.Name,MR_Comments__c= Reqmt.Opportunity_Code__c,status1__c= cm.status1__c));

}
}

}

public List<Candidate_Mapping__c> getcandidatemappinglist()
{
return candidatemappinglist ;
}

public List<Candidate_Mapping__c> getone()
{
return one;
}

public class test
{
public String candidatename { get; set; }
public String requirementname{ get; set; }
public String reqcode { get; set; }
public String status { get; set; }
}
}

Component:

<apex:component controller="SubmittedtoClientalert" access="global">
<apex:dataTable value="{!one}" var="proj" border="2">
<apex:column width="30%" style="align:left">
<apex:facet name="header">Candidate Name</apex:facet>
{!proj.LR_Comments__c}
</apex:column>
<apex:column width="30%" style="align:left">
<apex:facet name="header">Requirement Name</apex:facet>
{!proj.R_Comments__c}
</apex:column>
<apex:column width="40%" style="halign:left">
<apex:facet name="header">Requirement Code</apex:facet>
{!proj.MR_Comments__c}
</apex:column>
</apex:dataTable>
</apex:component>

I am retrieving the output in visualforce template if the query limit is 30. Otherwise too many queries 101 error is throwing.

How to resolve this error?. And also i need to seperate the candidate list based on the requirement owner and send email to the requirement owner with their candidate list.

Kindly advice.