Thursday, April 12, 2018

D365 X++ code to send mail with excel attachment

using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
using System.Net;
using Microsoft.Dynamics.ApplicationPlatform.Services.Instrumentation;
using Microsoft.DynamicsOnline.Infrastructure.Components.SharedServiceUnitStorage;
using Microsoft.Dynamics.ApplicationPlatform.Environment;
using Microsoft.Dynamics.AX.Framework.FileManagement;
class BB_ExcelEmail
{
 
    #define.CurrentVersion(1)
    #localmacro.CurrentList

    #endmacro

    public void run()
    {
        #AviFiles
        SysOperationProgress progress1 = new SysOperationProgress();
 
        int             row=1;
        int             datediff;
        CustAccount     invoiceAccount;
        int             startrow;
        int             col;
        int             i;
        boolean         goin = false;
     
        SysMailerMessageBuilder builder = new SysMailerMessageBuilder();
     
        COM                 Crange;
        COM                 CBorders;
        COM                 CBorder;
        Amount              TotalSales;
        Qty                 TotalQty;
        Amount              IYesterday, Itoday, IOpen;
        Amount              SYesterday, Stoday, SOpen;
        Amount              EYesterday, Etoday, EOpen;
        Amount              invoiceAmount;
        System.IO.StreamReader reader= null;
        System.String contentString;
        System.Net.WebClient webClient;
        DocuFileSaveResult saveResult =DocuFileSaveResult::construct();
         
        System.IO.Stream workbookStream = new System.IO.MemoryStream();
           
        System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
        using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
        {
            var sheet = package.get_Workbook().get_Worksheets();
            var worksheet = sheet.Add("First sheet");
            var cells = worksheet.get_Cells();
            OfficeOpenXml.Style.ExcelStyle style = cells.get_Item(row,1).get_style();
            OfficeOpenXml.Style.ExcelFont font = style.Font;
            OfficeOpenXml.Style.ExcelColor color = font.Color;
 
            VAR cell = Cells.get_item(row,1);cell.set_value("Exel email test");
            VAR cellStyle = Cells.get_item(row, 1).get_style();
            cellStyle.get_Font().set_Bold(true);
            color.SetColor(System.Drawing.Color::FromArgb(170,0,0));
            font.Size = 20;
            row++;
         
            cell = Cells.get_item(row,1);cell.set_Value("Entry Date");
            cellStyle = Cells.get_item(row, 1).get_style();
            style = cells.style;font  = style.font;color = font.color;
            color.SetColor(System.Drawing.Color::FromArgb(190,0,0));
            cell = Cells.get_item(row,2);cell.set_Value("Sales ID");
            cellStyle = Cells.get_item(row, 2).get_style();
            style = cells.style;font  = style.font;color = font.color;
            color.SetColor(System.Drawing.Color::FromArgb(190,0,0));
            cell = Cells.get_item(row,3);cell.set_Value("Bill To");
            cellStyle = Cells.get_item(row, 3).get_style();
            style = cells.style;font  = style.font;color = font.color;
            color.SetColor(System.Drawing.Color::FromArgb(190,0,0));
            cell = Cells.get_item(row,4);cell.set_Value("End Customer");
            row++;
            package.save();
            downloadUrl = File::SendFileToTempStore(memoryStream, "ExcelEmail.xlsx");
            webClient = new System.Net.WebClient();
            System.IO.Stream        stream = new System.IO.MemoryStream(webClient.DownloadData(downloadUrl));
       

            builder.setFrom(SysEmailParameters::find().SMTPUserName);
            builder.addTo("test@test.com");
            builder.addAttachment(stream,"ExcelEmail.xlsx");
                   
        }
   
        SysMailerFactory::getNonInteractiveMailer().sendNonInteractive(builder.getMessage());
    }

    public static void main(args args)
    {
        BB_ExcelEmail BB_ExcelEmail = new BB_ExcelEmail ();
        ;
             
            BB_ExcelEmail ();
    }

}