AEM query results send as email

[newbie34]

Hi guys , 

There is a requirement to send email to users with query results as an attachment from backend . Query result contains pages > 200 . 

My doubt is what will be easier to implement in attachment ,CSV or HTML and how to implement it ? 

Thanks in advance .

[aanchal-sikka]

Hello @newbie34 

It would easier to create an Excel with the details. And send the details as an attachment.

1. Query AEM using QueryBuilder

1. Set Up QueryBuilder API:

   • Access the QueryBuilder API in your AEM Java servlet or Sling model.
   • Build your query parameters to fetch the required content from the JCR repository.

2. Example Code to Fetch Results:

   Map<String, String> queryMap = new HashMap<>();
   queryMap.put("path", "/content/dam");
   queryMap.put("type", "dam:Asset");
   queryMap.put("property", "jcr:content/metadata/dc:title");
   queryMap.put("property.operation", "exists");
   Query query = queryBuilder.createQuery(PredicateGroup.create(queryMap), session);
   SearchResult result = query.getResult();
   

3. Process the Results:

   • Iterate through the SearchResult to extract the desired properties:

   for (Hit hit : result.getHits()) {
       String path = hit.getPath();
       String title = hit.getProperties().get("jcr:content/metadata/dc:title", String.class);
       // Process or store results
   }
   

---

2. Write Results to Excel using Apache POI

1. Set Up Apache POI:

   • Add Apache POI dependencies to your project (in pom.xml if using Maven):

     <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi</artifactId>
         <version>5.2.3</version>
     </dependency>
     <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi-ooxml</artifactId>
         <version>5.2.3</version>
     </dependency>
     

2. Create Excel File:

   • Use Apache POI to generate an Excel workbook and write the query results:

   Workbook workbook = new XSSFWorkbook();
   Sheet sheet = workbook.createSheet("Assets");
   
   // Add header row
   Row headerRow = sheet.createRow(0);
   headerRow.createCell(0).setCellValue("Path");
   headerRow.createCell(1).setCellValue("Title");
   
   // Add data rows
   int rowNum = 1;
   for (Hit hit : result.getHits()) {
       Row row = sheet.createRow(rowNum++);
       row.createCell(0).setCellValue(hit.getPath());
       row.createCell(1).setCellValue(hit.getProperties().get("jcr:content/metadata/dc:title", String.class));
   }
   
   // Write to file
   try (FileOutputStream fileOut = new FileOutputStream("assets.xlsx")) {
       workbook.write(fileOut);
   } catch (IOException e) {
       e.printStackTrace();
   } finally {
       workbook.close();
   }
   

---

3. Send Email Using ACS Commons Email API

1. Configure ACS Commons Email Service:

   • Ensure the Email Service configuration is set up in AEM, with SMTP details and default email templates.

2. Use ACS Commons Email API to Send Emails:

   • Construct an email using the ACS Commons Email Service in a Sling model or servlet:

   @Reference
   private EmailService emailService;
   
   Map<String, String> emailParams = new HashMap<>();
   emailParams.put("to", "recipient@example.com");
   emailParams.put("from", "sender@example.com");
   emailParams.put("subject", "AEM Query Results");
   emailParams.put("body", "The attached Excel file contains the results.");
   
   // Attach the Excel file
   File file = new File("assets.xlsx");
   if (file.exists()) {
       emailParams.put("attachment", file.getAbsolutePath());
   }
   
   EmailService.Config config = emailService.newConfig(emailParams);
   emailService.sendEmail(config);
   

---

[SreenivasBr]

A CSV or Excel report will be better for readability compared to HTML. Displaying HTML on the email or attaching HTML will be little tedious as you might have to fix multiple UI issues and might not be readable.