We all know how to create reports and dashboards, but the challenge arises when we need to send reports to email as an excel attachment. Salesforce doesn’t provide us with standard inbuilt functionality to do so. I too faced the same challenge and built a solution for this. We have to follow certain steps to achieve this approach.

1. Create a report
2. Create a Visualforce Component
3. Create a Visualforce Email Template

1. Create a report

Creating a report or we can take any existing report which is already present in the Org. And make sure that the report preview format is the tabular format.

2. Create a Visualforce Component

Next, we need to create a visualforce component to get the report data and to display as a report. Here we are getting the report Id from the Email Template.

Visualforce Component: ReportsToEmail

<apex:component controller="ReportsToEmailController" access="global">
    <apex:attribute name="ReportId" description="Report ID" type="Id" assignTo="{!rptId}"/>
    <apex:outputPanel>         
        <table style="width: 100%;">
            <thead>                 
                <apex:repeat value="{!ReportResult.reportMetadata.detailColumns}" var="colName"> 

                    <!-- reportMetadata is a class where it contains metadata of a report.
						DetailColumns is a method of ReportMetadata class, it returns the API names (Columns Names) 
							for the fields that contain detailed data-->

                    <th><apex:outputText value="{!ReportResult.reportExtendedMetadata.detailColumnInfo[colName].label}"/></th>

                    <!-- reportExtendedMetadata is class where it contains Report extended metadata and
							it provides data type and label information.
						detailColumnInfo is a method of reportExtendedMetadata class, it returns map of columns names 
							and its label to Display as Header -->

                </apex:repeat> 
            </thead> 
            <tbody> 
                <apex:repeat value="{!ReportResult.factMap['T!T'].rows}" var="row" rows="999"> 
                    <!-- Here we will get entire data of each row and T refers to the Row -->
                    <tr> <apex:repeat value="{!row.dataCells}" var="cell">
                        <!-- Here we will get data of each cell and displayed -->
                        <td><apex:outputText value="{!cell.label}"/></td>
                        </apex:repeat> </tr> 
                </apex:repeat>
            </tbody>
        </table>
    </apex:outputPanel> 
</apex:component>

Apex Controller: ReportsToEmailController

public class ReportsToEmailController {
    public Id rptId { get; set; } // Here we will get the report Id from the VF Component
    private transient Reports.ReportResults results; // It will hold the entire data of a report

    /*********************
     // Method Name : getReportResult
     // Description : Here we will get the data of a report and send to the VF Component
    /********************/

    public Reports.ReportResults getReportResult() {
        // Here it will run the report with that report ID and adding the report data into results
        results = Reports.ReportManager.runReport(rptId, true);
        return results;
    }
}

3. Create a Visualforce Email Template

After creating that we need to edit the template and add the visualforce component as an attachment.

<messaging:emailTemplate subject="Report" recipientType="User" >
    <messaging:plainTextEmailBody >
        Hi {!recipient.FirstName}
        Please find the below attachments. 

    </messaging:plainTextEmailBody>
    <messaging:attachment filename="Account Report.xls">
        <!-- Here we can add multiple Reports -->
        <c:ReportsToEmail ReportId="xxxxxxxxxxxxxxxxxxxx"/>
        <c:ReportsToEmail ReportId="xxxxxxxxxxxxxxxxxxxx"/>
    </messaging:attachment>
</messaging:emailTemplate>

“ <c:ReportsToEmail ReportId=”xxxxxxxxxxxxxxxxxx”/> ” Here I have called the VF component and we need to give the Report ID. In filename I am giving the name of the file and the type of the attachment(.xls), it automatically attaches the file to an email.

This Email Template can be used in Email Alerts or in apex class and from there you can send the reports to Email.

Here I am using a checkbox field to send email, using workflow whenever this checkbox field is true I am sending the reports to the email using email alert and then I am unchecking the checkbox using field update.