Create an excel file and send it through e- mail in D365 for Operation/AX 7 using X++

This post is about creating an excel file using X++ and attach it as an e-mail(office 365 SMTP) attachment without saving it in local file location.

First we need to setup the email parameters in AX 7/D365O

Go to System administrator module -> Setup -> Email -> Email parameters form -> SMTP parameters tab .

Fill in the details .

in user name field enter e mail address . For more details click below Microsoft link .

Setup email parameters in AX 7

image1

For example i am going to export customer group table records to excel file and attach it as attachment to an e-mail.

Code:

SysMailerMessageBuilder mailer = new SysMailerMessageBuilder();;
SysMailerSMTP smtp = new SysMailerSMTP();
CustGroup custGrp;
RowNumber row = 1 ;
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
mailer.setSubject(“Customer group report”);
mailer.setFrom(“xxx@yyy.com”);
mailer.setBody(“PFA”);
mailer.addTo(“aaa@bbb.com”);

using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
{
var worksheets = package.get_Workbook().get_Worksheets();
var worksheet = worksheets.Add(“@SYS16445”);
var cells = worksheet.get_Cells();
var cell = cells.get_Item(1,1);
cell.set_Value(“Group Id”);
cell = cells.get_Item(1,2);
cell.set_Value(“Name”);
while select CustGroup, Name from custGrp
{
row++;
cell = cells.get_item(row, 1);
cell.set_Value(custGrp.CustGroup);
cell = cells.get_item(row, 2);
cell.set_Value(custGrp.Name);
}
package.Save();

}
memoryStream.Seek(0, System.IO.SeekOrigin::Begin);

//The below line used to attach excel file to email.
mailer.addAttachment(memoryStream, ‘Customer Group.xlsx’, ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);

try
{
smtp.sendNonInteractive(mailer.getMessage());
}
catch(Exception::CLRError)
{
error(CLRInterop::getLastException().toString());
}

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s