Export and email excel through batch in D365 FO

Hi, If you are looking for a code to export excel through batch and email the excel as an attachment to your vendors.

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;


class VendorReportingFileExport extends RunBaseBatch implements BatchRetryable
{
    // Packed variables
    TransDate       transDate;
    QueryRun        queryRun;

    #define.CurrentVersion(1)
    #localmacro.CurrentList
        transDate
    #endmacro
    

    /// <summary>
    ///    Indicates whether the class is shown in the list of <c>Journal</c> types.
    /// </summary>
    /// <returns>
    ///    true if the class is shown in the list of <c>Journal</c> types; otherwise, false.
    /// </returns>
    /// <remarks>
    ///    A class that can be used in a batch journal is a class where the same parameters can be used
    ///    repeatedly. The dialog box can be shown and the parameters can be changed, but parameters of some
    ///    classes might build on data that is only valid for a short time. Running a class two times with the
    ///    same parameters is not always possible. If the <c>RunBaseBatch.canGoBatch</c> method is false, this
    ///    method will not have any effect.
    /// </remarks>
    public boolean canGoBatchJournal()
    {
        return true;
    }

    /// <summary>
    ///    Returns a class that contains the methods that are described by the <c>RunBaseDialogable</c>
    ///    interface.
    /// </summary>
    /// <returns>
    ///    A class that contains the methods that are described by the <c>RunBaseDialogable</c> interface.
    /// </returns>
    /// <remarks>
    ///    A dialog box can be either built by using the <c>Dialog</c> class or by using a class that is
    ///    created in the Application Object Tree (AOT).
    /// </remarks>
    public Object dialog()
    {
        DialogRunbase       dialog = super();
        #resAppl
        ;

        return dialog;
    }

    public void dialogPostRun(DialogRunbase dialog)
    {
        ;
        super(dialog);
    }

    public boolean init()
    {
        return true;
    }

    public boolean showQueryValues()
    {
        return true;
    }

    protected void new()
    {
        super();
    }

    public container pack()
    {
        return [#CurrentVersion,#CurrentList,queryrun.pack()];
    }

    public void exportFileStandard()
    {
        VendorReporting         vendReporting; 
        queryRun        qrRunLoc = this.queryRun();

        qrRunLoc.query().allowCrossCompany(true);
        while (qrRunLoc.next())
        {
            MemoryStream    memoryStream = new MemoryStream();
            using(var package = new ExcelPackage(memoryStream))
            {
                var worksheets = package.get_Workbook().get_Worksheets();
                var worksheet = worksheets.Add("Inventory");
                var cells = worksheet.get_Cells();
                var currentRow=1 ;

                /*-------HEADER PART -------*/

                var cell = cells.get_Item(currentRow,1);
                cell.set_Value("
                tributor Name");
                cell = null;
                cell = cells.get_Item(currentRow,2);
                cell.set_Value("ABCtributor Country");
                cell = null;
                cell = cells.get_Item(currentRow,3);
                cell.set_Value("Date");
                cell = null;
                cell = cells.get_Item(currentRow,4);
                cell.set_Value("Manufacture part nr");
                cell = null;
                cell = cells.get_Item(currentRow,5);
                cell.set_Value("ABCtributor Part Number");
                cell = null;
                cell = cells.get_Item(currentRow,6);
                cell.set_Value("Product Description");
                cell = null;
                cell = cells.get_Item(currentRow,7);
                cell.set_Value("Total Units in Stock");
                cell = null;
                cell = cells.get_Item(currentRow,8);
                cell.set_Value("Available Units (Free Stock)");
                cell = null;
                cell = cells.get_Item(currentRow,9);
                cell.set_Value("ABCtributor Unit Cost (SEK)");
                cell = null;
                cell = cells.get_Item(currentRow,10);
                cell.set_Value("Total Stock Value (SEK)");
                cell = null;
                cell = cells.get_Item(currentRow,11);
                cell.set_Value("Open order Qty");
                cell = null;
                /*-------HEADER PART END-------*/

                var worksheet2 = worksheets.Add("Sales out");
                vendReporting = qrRunLoc.get(tableNum(VendorReporting));

                InventTable     inventTable;
                InventQty totalUnitsStock;
                InventQtyReservPhysical physicallyReserved;
            
                while select inventTable
                    where inventTable.PrimaryVendorId == vendReporting.VendorId && 
                    inventTable.DataAreaId == 'XYZ'
                {
                    totalUnitsStock = 0;
                    physicallyReserved = 0;

                    currentRow += 1;
                    cell = cells.get_Item(currentRow, 1);
                    cell.set_Value(CompanyInfo::findDataArea(curExt()).Name);
                    cell= null;
                    cell= cells.get_Item(currentRow, 2);
                    cell.set_Value(CompanyInfo::findDataArea(curExt()).postalAddress().CountryRegionId);
                    cell= null;
                    cell= cells.get_Item(currentRow, 3);
                    cell.set_Value(date2Str(systemDateGet(),321,2,2,2,2,4));
                    cell= null;
                    cell= cells.get_Item(currentRow, 4);
                    cell.set_Value(InventItemBarcode::findItemId(inventTable.ItemId,false,false, SysAppUtilities_ABC::barcodeSetupMPN).itemBarCode);
                    cell= null;
                    cell= cells.get_Item(currentRow, 5);
                    cell.set_Value(inventTable.ItemId);
                    cell= null;
                    cell= cells.get_Item(currentRow, 6);
                    cell.set_Value(inventTable.itemName());
                    cell= null;
                    cell= cells.get_Item(currentRow, 7);

                    totalUnitsStock = this.onHandInventory(inventTable.itemid, SysAppUtilities_ABC::main, SysAppUtilities_ABC::standard);
                    totalUnitsStock = totalUnitsStock + this.onHandInventory(inventTable.itemid, SysAppUtilities_ABC::high, SysAppUtilities_ABC::standard);

                    cell.set_Value(totalUnitsStock);
                    cell= null;
                    cell= cells.get_Item(currentRow, 8);

                    physicallyReserved = this.availPhysical(inventTable.itemid, SysAppUtilities_ABC::main, SysAppUtilities_ABC::standard);
                    physicallyReserved = physicallyReserved + this.availPhysical(inventTable.itemid, SysAppUtilities_ABC::high, SysAppUtilities_ABC::standard);

                    cell.set_Value(physicallyReserved);
                    cell= null;
                    cell= cells.get_Item(currentRow, 9);
                    cell.set_Value(InventTable.ABCtITBaseCost_ABC);
                    cell= null;
                    cell= cells.get_Item(currentRow, 10);
                    cell.set_Value(totalUnitsStock * InventTable.ABCtITBaseCost_ABC);
                    cell= null;
                    cell= cells.get_Item(currentRow, 11);
                    cell.set_Value(this.getOpenOrderQty(inventTable.ItemId));
                }

                
                cells = worksheet2.get_Cells();
                currentRow=1 ;

                /*-------HEADER PART -------*/

                cell = cells.get_Item(currentRow,1);
                cell.set_Value("ABCtributor Name");
                cell = null;
                cell = cells.get_Item(currentRow,2);
                cell.set_Value("ABCtributor Country");
                cell = null;
                cell = cells.get_Item(currentRow,3);
                cell.set_Value("Month/Week/Date");
                cell = null;
                cell = cells.get_Item(currentRow,4);
                cell.set_Value("Customer Account Number");
                cell = null;
                cell = cells.get_Item(currentRow,5);
                cell.set_Value("Customer Name");
                cell = null;
                cell = cells.get_Item(currentRow,6);
                cell.set_Value("Customer Address");
                cell = null;
                cell = cells.get_Item(currentRow,7);
                cell.set_Value("Customer Address 2");
                cell = null;
                cell = cells.get_Item(currentRow,8);
                cell.set_Value("Customer City");
                cell = null;
                cell = cells.get_Item(currentRow,9);
                cell.set_Value("Customer Postal Code");
                cell = null;
                cell = cells.get_Item(currentRow,10);
                cell.set_Value("Customer VAT/NIF Number");
                cell = null;
                cell = cells.get_Item(currentRow,11);
                cell.set_Value("Customer Country");
                cell = null;
                cell = cells.get_Item(currentRow,12);
                cell.set_Value("Invoice date");
                cell = null;
                cell = cells.get_Item(currentRow,13);
                cell.set_Value("ABCtributor Part Code");
                cell = null;
                cell = cells.get_Item(currentRow,14);
                cell.set_Value("Vendor Part Code");
                cell = null;
                cell = cells.get_Item(currentRow,15);
                cell.set_Value("Product description");
                cell = null;
                cell = cells.get_Item(currentRow,16);
                cell.set_Value("Quantity Sold");
                cell = null;
                cell = cells.get_Item(currentRow,17);
                cell.set_Value("ABCtribution Unit Sell Price");
                cell = null;
                cell = cells.get_Item(currentRow,18);
                cell.set_Value("ABCtribution Cost Price");
                cell = null;
                cell = cells.get_Item(currentRow,19);
                cell.set_Value("Currency");
                cell = null;
                    /*-------HEADER PART END-------*/

                TransDate fromDate, toDate, prevDate;
                TransDate currentDate = DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone());
                switch (vendReporting.Schedule)
                {
                    case VendorReportingExpSchedule_ABC::Daily :
                        fromDate = currentDate - 1;
                        toDate = currentDate - 1;
                        break;
                    case VendorReportingExpSchedule_ABC::Weekly :
                        PreferredLocale preferredLocale = (new xSession()).PreferredLocale();
                        prevDate = HcmDateTimeUtil::calculateDateWithOffset(PeriodUnit::Day,7,false, currentDate);
                        toDate   = DateTimeUtil::getEndOfWeekDate(preferredLocale, prevDate);
                        fromDate = DateTimeUtil::getStartOfWeekDate(preferredLocale, prevDate);
                        break;
                    case VendorReportingExpSchedule_ABC::Monthly :
                        prevDate = prevMth(currentDate);
                        fromDate = DateStartMth(prevDate);
                        toDate = endmth(prevDate);
                        break;
                }

                CustInvoiceTrans    custInvoiceTrans;
                container conCompanies = ['ABC', 'CDE', 'XYZ', 'LMN'];

                while select crosscompany : conCompanies custInvoiceTrans
                    where custInvoiceTrans.InvoiceDate >= fromDate &&
                            custInvoiceTrans.InvoiceDate <= toDate
                {
                    InventTable inventTableLoc = InventTable::find(custInvoiceTrans.ItemId);
                    if (inventTableLoc.PrimaryVendorId == vendReporting.VendorId)
                    {
                        changecompany (custInvoiceTrans.DataAreaId)
                        {
                            InventTable inventTableIC = InventTable::find(custInvoiceTrans.ItemId);
                            CustTable   custTable = CustTable::find(SalesTable::find(custInvoiceTrans.SalesId).CustAccount);
                            currentRow += 1;
                            cell = cells.get_Item(currentRow,1);
                            cell.set_Value(CompanyInfo::findDataArea(curExt()).Name);
                            cell = null;
                            cell = cells.get_Item(currentRow,2);
                            cell.set_Value(CompanyInfo::findDataArea(curExt()).postalAddress().CountryRegionId);
                            cell = null;
                            cell = cells.get_Item(currentRow,3);
                            cell.set_Value(this.getMonthWeekDate(vendReporting.schedule, prevDate));
                            cell = null;
                            cell = cells.get_Item(currentRow,4);
                            cell.set_Value(custTable.AccountNum);
                            cell = null;
                            cell = cells.get_Item(currentRow,5);
                            cell.set_Value(custTable.name());
                            cell = null;
                            cell = cells.get_Item(currentRow,6);
                            cell.set_Value(custTable.postalAddress().Street);
                            cell = null;
                            cell = cells.get_Item(currentRow,7);
                            cell.set_Value(custTable.postalAddress().BuildingCompliment);
                            cell = null;
                            cell = cells.get_Item(currentRow,8);
                            cell.set_Value(custTable.postalAddress().City);
                            cell = null;
                            cell = cells.get_Item(currentRow,9);
                            cell.set_Value(custTable.postalAddress().ZipCode);
                            cell = null;
                            cell = cells.get_Item(currentRow,10);
                            cell.set_Value(custTable.VATNum);
                            cell = null;
                            cell = cells.get_Item(currentRow,11);
                            cell.set_Value(custTable.countryRegionId());
                            cell = null;
                            cell = cells.get_Item(currentRow,12);
                            cell.set_Value(date2Str(CustInvoiceTrans.InvoiceDate,321,2,2,2,2,4));
                            cell = null;
                            cell = cells.get_Item(currentRow,13);
                            cell.set_Value(CustInvoiceTrans.ItemId);
                            cell = null;
                            cell = cells.get_Item(currentRow,14);
                            cell.set_Value(InventItemBarcode::findItemId(inventTableIC.ItemId,false,false, SysAppUtilities_ABC::barcodeSetupMPN).itemBarCode);
                            cell = null;
                            cell = cells.get_Item(currentRow,15);
                            cell.set_Value(inventTableIC.itemName());
                            cell = null;
                            cell = cells.get_Item(currentRow,16);
                            cell.set_Value(custInvoiceTrans.Qty);
                            cell = null;
                            cell = cells.get_Item(currentRow,17);
                            cell.set_Value(custInvoiceTrans.LineAmount);
                          
                            if(Ledger::accountingCurrency(CompanyInfo::current()) != custInvoiceTrans.CurrencyCode)
                            {
                                cell = null;
                                cell = cells.get_Item(currentRow,18);
                                cell.set_Value(this.exchangeRate(Ledger::accountingCurrency(CompanyInfo::current()), custInvoiceTrans.CurrencyCode, inventTableIC.ABCtITICCost_ABC, custInvoiceTrans.InvoiceDate));
                            }
                            else
                            {
                                cell = null;
                                cell = cells.get_Item(currentRow,18);
                                cell.set_Value(inventTableIC.ABCtITICCost_ABC);
                            }
                            
                            cell = null;
                            cell = cells.get_Item(currentRow,19);
                            cell.set_Value(custInvoiceTrans.CurrencyCode);
                            cell = null;
                        }
                    }
                }
                package.Save();
                memoryStream.Seek(0,System.IO.SeekOrigin::Begin);

                str fileNameVal = "ABC_"+ VendTable::find(vendReporting.VendorId).name() + "_" + date2Str(DateStartMth(fromDate),321,2,2,2,2,2) + "_" + date2Str(endMth(toDate),321,2,2,2,2,2) + ".xlsx";

                VendorReportingFileExport::sendEmail( fileNameVal,
                                                                "Inventory and Sales out ",
                                                                "Inventory and Sales out details",
                                                                memoryStream,
                                                                SysEmailParameters::find().SMTPUserName,
                                                                vendReporting.Email);
                

            }
        }
    }

    public static void sendEmail(str _fileName,
                                 str _subject,
                                 str _body,
                                 System.IO.MemoryStream _memoryStream,
                                 Email _fromEmail,
                                 Email _toEmail)
    {
        SysMailerMessageBuilder mailer = new SysMailerMessageBuilder();
        SysMailerSMTP           smtp = new SysMailerSMTP();
        try
        {
            mailer.setSubject(_subject);

            mailer.setFrom(_fromEmail);

            mailer.setBody(_body);

            mailer.addTo(_toEmail);

            mailer.addAttachment(_memoryStream, _fileName);

            smtp.sendNonInteractive(mailer.getMessage());

            Info("@SYS58551");
        }
        catch (Exception::CLRError)
        {
            System.Exception ex = ClrInterop::getLastException();
            if (ex != null)
            {
                ex = ex.get_InnerException();
                if (ex != null)
                {
                    error(ex.ToString());
                }
            }
        }
        catch (Exception::Error)
        {
            Error("Error sending email");
        }
    }

    public Qty getOpenOrderQty(ItemId       _itemid)
    {
        SalesLine   salesLine;

        select sum(SalesQty) from salesLine
            where SalesLine.itemid == _itemid   && SalesLine.salesStatus != SalesStatus::Invoiced;


        return salesLine.SalesQty;
    }

    public Amount exchangeRate (CurrencyCode _fromCurrency, CurrencyCode _toCurrency, Amount _amount, TransDate  _transDate)
    {
        Amount  exchRate;

        if(_amount == 0 )
        {
            return 0;
        }
        else
        {
            CurrencyExchangeHelper currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), _transDate);
            exchRate =  currencyExchangeHelper.calculateCurrencyToCurrency(_fromCurrency, _toCurrency, _amount, true);
        }

        return exchRate;
    }

    public str  getMonthWeekDate(VendorReportingExpSchedule_ABC _schedule, TransDate _transDate)
    {
        str     ret;
        switch  (_schedule)
        {
            case VendorReportingExpSchedule_ABC::Daily:
                    ret = date2Str(_transDate,321,2,2,2,2,4);
                break;

             case VendorReportingExpSchedule_ABC::Monthly:
                    ret = int2Str(mthOfYr(_transDate));
                 break;

             case VendorReportingExpSchedule_ABC::Weekly:
                    ret = int2Str(wkOfYr(_transDate));
                 break;
        }

        return ret;
    }

    public InventQty onHandInventory(ItemId _itemId, InventLocationId _inventLocationId, InventSiteId _inventSiteId)
    {
        InventOnhand    inventOnhand;
        InventDim       inventDimLoc;
        inventDimLoc.InventLocationId = _inventLocationId;
        inventDimLoc.InventSiteId     = _inventSiteId;

        InventDim       inventDim = inventDim::findOrCreate(inventDimLoc);
        InventDimParm   inventDimParm;

        inventDimParm.initFromInventDim(inventDim);
        inventOnhand = InventOnhand::newParameters(_itemId, inventDim, inventDimParm);
        return inventOnhand.physicalInvent();
    }

    public InventQtyReservPhysical availPhysical(ItemId _itemId, InventLocationId _inventLocationId, InventSiteId _inventSiteId)
    {
        InventOnhand    inventOnhand;
        InventDim       inventDimLoc;
        inventDimLoc.InventLocationId = _inventLocationId;
        inventDimLoc.InventSiteId     = _inventSiteId;

        InventDim       inventDim = inventDim::findOrCreate(inventDimLoc);
        InventDimParm   inventDimParm;

        inventDimParm.initFromInventDim(inventDim);
        inventOnhand = InventOnhand::newParameters(_itemId, inventDim, inventDimParm);
        return inventOnhand.availPhysical();
    }

    /// <summary>
    ///    Contains the code that does the actual job of the class.
    /// </summary>
    public void run()
    {
        #OCCRetryCount

        try
        {
            ttsbegin;

            this.exportFileStandard();

            ttscommit;
        }
        catch (Exception::Deadlock)
        {
            retry;
        }
        catch (Exception::UpdateConflict)
        {
            if (appl.ttsLevel() == 0)
            {
                if (xSession::currentRetryCount() >= #RetryNum)
                {
                    throw Exception::UpdateConflictNotRecovered;
                }
                else
                {
                    retry;
                }
            }
            else
            {
                throw Exception::UpdateConflict;
            }
        }
    }

    public boolean runsImpersonated()
    {
        return true;
    }

    public QueryRun queryRun()
    {
        return queryRun;
    }

    public boolean unpack(container _packedClass)
    {
        Integer     version = conPeek(_packedClass,1);
        container   packedQueryRun;
 
        switch (version)
        {
            case #CurrentVersion:
                [version, #CurrentList, packedQueryRun] = _packedClass;
                queryRun = new QueryRun(packedQueryRun);
                break;
            default:
                return false;
        }
 
        return true;
    }

    static VendorReportingFileExport_ABC construct()
    {
        return new VendorReportingFileExport();
    }

    // Here goes a description of the class
    static ClassDescription description()
    {
        return "Vendor reporting excel export ";
    }

    static void main(Args args)
    {
        VendorReportingFileExport    VendorReportingFileExport;
        ;
        VendorReportingFileExport = VendorReportingFileExport::construct();

        if (VendorReportingFileExport.prompt())
            VendorReportingFileExport.runOperation();
    }

    [Hookable(false)]
    public final boolean isRetryable()
    {
        return true;
    }

    protected boolean canRunInNewSession()
    {
        return false;
    }

    void initQuery()
    {
        Query   query = new Query(queryStr(VendorReportingQuery_ABC));
        
        queryrun = new QueryRun(query);
    }

    public void initParmDefault()
    {
        super();
 
        this.initQuery();
    }

}