🔧 Create and Send Excel via Email in D365FO Using X++ Without Saving Locally

 When working with batch processes or customer reports in Dynamics 365 Finance and Operations, there are situations where you want to generate Excel files in-memory and send them via email—all without saving anything locally.

Let’s dive into how you can achieve this using X++ and SMTP (Office365).

---

✅ Final Output

• Excel file created dynamically

• Memory stream used to avoid physical storage

• Email sent with Excel as attachment via SMTP

---

🧾 X++ Code – Generate Excel & Send via Email

xpp
public void sendExcelAttachmentThroughEmail()
{
    System.IO.MemoryStream        excelStream = new System.IO.MemoryStream();
    SysMailerMessageBuilder       emailBuilder = new SysMailerMessageBuilder();
    SysMailerSMTP                 smtpClient = new SysMailerSMTP();
    RowNumber                     lineIndex = 1;
    MyLogTableBuffer              logBuffer;

    str mailSubject = strFmt("Batch Status - %1", systemDateGet());
    str fileName    = strFmt("BatchStatus_%1.xlsx", systemDateGet());

    // Compose email details
    emailBuilder.setSubject(mailSubject);
    emailBuilder.setFrom(SysUserInfo::find().Email);
    emailBuilder.setTo("example@yourdomain.com");
    emailBuilder.setBody("Hello,\n\nPlease find attached the batch status report.\n\nRegards,\nD365 System");

    // Excel generation using EPPlus
    using (var package = new OfficeOpenXml.ExcelPackage(excelStream))
    {
        var workbook = package.get_Workbook();
        var worksheet = workbook.get_Worksheets().Add("Batch Status");
        var cells = worksheet.get_Cells();

        // Header Row
        cells.get_Item(1,1).set_Value("Register ID");
        cells.get_Item(1,2).set_Value("Check #");
        cells.get_Item(1,3).set_Value("Agreement");
        cells.get_Item(1,4).set_Value("Customer");
        cells.get_Item(1,5).set_Value("Status");
        cells.get_Item(1,6).set_Value("Remarks");

        // Data Rows
        while select logBuffer
        {
            lineIndex++;

            cells.get_Item(lineIndex, 1).set_Value(logBuffer.MyRegisterId);
            cells.get_Item(lineIndex, 2).set_Value(logBuffer.CheckRef);
            cells.get_Item(lineIndex, 3).set_Value(logBuffer.AgreementCode);
            cells.get_Item(lineIndex, 4).set_Value(logBuffer.CustomerId);

            str statusLabel = logBuffer.Status == MyLogStatus::Error ? "Error" :
                              logBuffer.Status == MyLogStatus::Success ? "Posted" : "Unknown";

            cells.get_Item(lineIndex, 5).set_Value(statusLabel);
            cells.get_Item(lineIndex, 6).set_Value(logBuffer.Message);
        }

        package.Save(); // Save to memory stream
    }

    // Prepare stream and send
    excelStream.Seek(0, System.IO.SeekOrigin::Begin);
    emailBuilder.addAttachment(excelStream, fileName, 
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    try
    {
        smtpClient.sendNonInteractive(emailBuilder.getMessage());
        info("Email sent successfully with Excel attachment.");
    }
    catch (Exception::CLRError)
    {
        error(CLRInterop::getLastException().ToString());
    }
}

✅ Notes

• Make sure to reference EPPlus DLL in your AOT project.

• Replace MyLogTableBuffer, MyLogStatus, and other placeholder names with your actual table/enums.

• You can configure SMTP in System administration > Email parameters.

Output: 

Mail screenshot:

Excel screenshot:

---