0

I have a requirement where in I created a Report in Salesforce to get all Orders group by Owner, Account Geo field & orders that are closed this quarter.` I have prepared the report but I need to send this report in Email body(like table having columns & rows with report data). Is it possible to do in Apex. I don't want to send the reports as attachment it should be directly sent in Email Body. I don't need attachment in excel format because client is worried of sending the attachment in Email due to Security reasons. Here is my report which I created.

Order Report

What are the workarounds for this?

EDIT

I some how managed to get the table in Email body by using below query in apex.

Public static string tableEmailBody(){ 
        List<Period> periodLst = [Select EndDate,StartDate From Period Where type = 'Quarter' and StartDate = THIS_FISCAL_QUARTER];
        Date currentFQStartDate = periodLst[0].StartDate;
        Date currentFQEndDate = periodLst[0].EndDate;
        String htmlBody;
        htmlBody = '<table border="1" style="border-collapse: collapse"><caption>Report for Orders Closed Current Quarter</caption><tr><th>Owner Name</th><th>Account Geo</th><th>New Recurring Amount</th><th>New One Time Amount</th><th>Record Count</th></tr>';
        for(AggregateResult clsOrd : [SELECT SBQQ__Quote__r.Owner.Name ownerName,SBQQ__Quote__r.SBQQ__Account__r.Geo__c accountGeo ,SUM(SBQQ__Quote__r.New_Recurring_Amount_formula__c) recurringAmount,
                                      SUM(SBQQ__Quote__r.New_One_Time_Amount_rollup__c) oneTimeAmount,COUNT(OrderNumber) ordNo FROM Order where (Opportunity_Closed_Date__c >=:currentFQStartDate) AND (Opportunity_Closed_Date__c <=:currentFQEndDate)
                                      GROUP BY SBQQ__Quote__r.Owner.Name,SBQQ__Quote__r.SBQQ__Account__r.Geo__c]){
                                          
                                          htmlBody += '<tr><td>' + clsOrd.get('ownerName') + '</td><td>' + clsOrd.get('accountGeo') + '</td></tr>';
                                          htmlBody += '<tr><td>' + clsOrd.get('recurringAmount') + '</td><td>' + clsOrd.get('oneTimeAmount') + '</td></tr>';
                                          htmlBody += '<tr><td>' + clsOrd.get('ordNo') + '</td><td>'; 
                                      }
        htmlBody += '</table>';
        return htmlBody; 
    } 

But since the query is grouped by Owner name & account geo I am getting table in below format in email. Column 3,4,5 values are getting populated in owner & account geo column, but they should be at 3,4,5 column respt. What went wrong here.? Please help!! Email body

3
  • Have you tried this? - forcetalks.com/salesforce-topic/…
    – Ysr Shk
    Commented Nov 4, 2020 at 10:18
  • As I said I don't want to send it as attachment. Commented Nov 4, 2020 at 10:20
  • ApexPages.PageReference report = new ApexPages.PageReference( '/' + reportId[i] + '?excel=1'); // or csv=1 This is key part I was hoping you check. with this you can try to print the data in email.
    – Ysr Shk
    Commented Nov 4, 2020 at 10:22

1 Answer 1

0

My bad the <tr> tags were not properly formatted. Here is the working code to get report data in Email body.

Public static string tableEmailBody(){ 
        List<Period> periodLst = [Select EndDate,StartDate From Period Where type = 'Quarter' and StartDate = THIS_FISCAL_QUARTER];
        Date currentFQStartDate = periodLst[0].StartDate;
        Date currentFQEndDate = periodLst[0].EndDate;
        String htmlBody;
        htmlBody = '<table border="1" style="border-collapse: collapse"><caption>Report for Orders Closed Current Quarter</caption><tr><th>Owner Name</th><th>Account Geo</th><th>New Recurring Amount</th><th>New One Time Amount</th><th>Record Count</th></tr>';
        for(AggregateResult clsOrd : [SELECT SBQQ__Quote__r.Owner.Name ownerName,SBQQ__Quote__r.SBQQ__Account__r.Geo__c accountGeo ,SUM(SBQQ__Quote__r.New_Recurring_Amount_formula__c) recurringAmount,
                                      SUM(SBQQ__Quote__r.New_One_Time_Amount_rollup__c) oneTimeAmount,COUNT(OrderNumber) ordNo FROM Order where (Opportunity_Closed_Date__c >=:currentFQStartDate) AND (Opportunity_Closed_Date__c <=:currentFQEndDate)
                                      GROUP BY SBQQ__Quote__r.Owner.Name,SBQQ__Quote__r.SBQQ__Account__r.Geo__c ORDER BY SBQQ__Quote__r.Owner.Name]){
                                          
                                          htmlBody += '<tr><td>' + clsOrd.get('ownerName') + '</td><td>' + clsOrd.get('accountGeo') + '</td>';
                                          htmlBody += '<td>' + clsOrd.get('recurringAmount') + '</td><td>' + clsOrd.get('oneTimeAmount') + '</td>';
                                          htmlBody += '<td>' + clsOrd.get('ordNo') + '</td></tr>'; 
                                      }
        htmlBody += '</table>';
        return htmlBody; 
    } 

Report Data

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .