Create excel file and send through email in d365FO

 Hi guys, today we see how to generate excel file and send that file through email using x++ in D365FO.

using System.IO; using OfficeOpenXml; using OfficeOpenXml.Style; using OfficeOpenXml.Table; class SendEmailProcessSevice extends SysOperationServiceBase { Filename fileName; SysEmailSystemTable sysEmailTable; SysEmailMessagesystemTable messageTable; // sending Email with attachment private void sendEmail(System.IO.MemoryStream attachmentStream) { if (attachmentStream != null) { var user = xUserInfo::find(); Map sysMailers = SysMailerFactory::getMailers(); sysEmailTable = SysEmailSystemTable::find('TestEmail'); select firstonly messageTable where messageTable.EmailId == sysEmailTable.EmailId; str s = date2Str(today(), 321, DateDay::Digits2, DateSeparator::Hyphen, // separator1 DateMonth::Digits2, DateSeparator::Hyphen, // separator2 DateYear::Digits4 ); messageBuilder.setSubject(messageTable.Subject + '-' + s); messageBuilder.setBody(messageTable.Mail); messageBuilder.setFrom(sysEmailTable.SenderAddr); var message = messageBuilder.getMessage(); messageBuilder.addAttachment(attachmentStream,fileName); SysIMailer mailer = sysMailers.lookup('SMTP'); SysMailerSMTP smtp = new SysMailerSMTP(); if (mailer is SysIMailerNonInteractive) { SysIMailerNonInteractive nonInteractiveMailer = mailer; messageSent = nonInteractiveMailer.sendNonInteractive(message); } else if (mailer is SysIMailerInteractive) { SysIMailerInteractive interactiveMailer = mailer; messageSent = interactiveMailer.sendInteractive(message);// messageBuilder.getMessage(); } } else { warning("File is empty."); } } // creating excel file public void fileCreation(MyContract contract) { Query query = new Query(); MemoryStream memoryStream = new MemoryStream(); QueryRun qr; RowNumber row = 1; query.addDataSource(tablenum(Mytable)).addRange(fieldnum(Mytable, Feild1)).value(queryValue(contract.parmtype))); using (var package = new ExcelPackage(memoryStream)) { var worksheets = package.get_Workbook().get_Worksheets(); var worksheet = worksheets.Add('Test Records'); var cells = worksheet.get_Cells(); OfficeOpenXml.ExcelRange cell = cells.get_Item(row, 1); cell.set_Value('Field1'); cell = cells.get_Item(row,2); cell.set_Value('Field2'); cell = cells.get_Item(row,3); cell.set_Value('Field3'); qr = new QueryRun(query); while (qr.next()) { Mytable = qr.get(tableNum(Mytable)); row++; cell = null; cell = cells.get_item(row, 1); cell.set_Value(Mytable.Field1); cell = cells.get_item(row, 2); cell.set_Value(Mytable.Field2); cell = cells.get_item(row, 3); cell.set_Value(Mytable.Field3); } package.save(); file::SendFileToUser(memoryStream,fileName); memoryStream.Seek(0, System.IO.SeekOrigin::Begin); this.sendEmail(memoryStream); } } }

Keep Daxing !!