Sending Email with attached Excel file in D365 and Ax 2012 using SysMailerMessageBuilder Framework X++
Moving on from Ax dynamics 2012 to D365, there are multiple frameworks that has been added on by Microsoft to use its basic functionality.In the case of email sending functionality in Dynamics Ax we probably prefer SMTP in 2012 that is to add on the code and bypassing its required parameter.While in D365 we could use SysMailerMessageBuilder framework to perform our work that is sending email to multiple recipient too.
The piece of code firstly has been attached is using SysMailerMessageBuilder framework
*Our objective it to Generate an excel File and attach it to an email and sending it to
multiple recipients
*The file data will be from financial dimension view and it will be distributed into
three worksheets that is D1,D2 and D4_5 according to conditions
*Data of Data Entity of D4 & D5 will be inserted into same worksheet that is D4_5
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.excelworksheet;
class Irc_ExportDataExcelClass extends SysOperationServiceController
{
//Header Titles
public const str description = 'Description';
public const str dimensionValue = 'Dimension Value';
public const str D1 = 'D01Project_Code';
public const str D3 = 'D03Program_Area';
public const str D4 = 'D04Location';
public const str D5 = 'D05Entity_Code';
FINANCIALDIMENSIONVALUEENTITYVIEW financialDimensionGroup, financialDimension;
System.Text.RegularExpressions.Match regExMatch;
public static void main(Args _args)
{
Irc_ExportDataExcelClass exportDataExcelClass = new Irc_ExportDataExcelClass();
exportDataExcelClass.EmailSender(_args);
info('Email successfully sent');
}
public void EmailSender(Args _args)
{
SysMailerMessageBuilder mailer = new SysMailerMessageBuilder();
SysMailerSMTP smtp = new SysMailerSMTP();
var user = xUserInfo::find();
var sysUser = SysUserInfo::find(user.id);
var sender = sysUser.getSendAsEmail(user);
container con = str2con(_args.parm());
Irc_ExportDataExcelClass exporter = new Irc_ExportDataExcelClass();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
var recipient ='';
;
for(int i=1;i<=conLen(con);i++)
{
recipient = conPeek(con,i);
mailer.addTo(recipient);
}
mailer.setSubject("Financial Details");
mailer.setFrom(sender);
mailer.setBody("PFA");
memoryStream = exporter.DataLoaderExcel();
memoryStream.Seek(0, System.IO.SeekOrigin::Begin);
//The below line used to attach excel file to email.
mailer.addAttachment(memoryStream, 'D Codes FCM.xlsx', "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try
{
smtp.sendNonInteractive(mailer.getMessage());
}
catch(Exception::CLRError)
{
error(CLRInterop::getLastException().toString());
}
}
public void ExcelSheetsGenerator(OfficeOpenXml.ExcelRange cell , OfficeOpenXml.ExcelRange cells , int currentRow , str Fdescription ,str Fvalue,int Fval)
{
str string = '';
cell = null;
cell = cells.get_Item(currentRow, 1);
cell.set_Value(Fvalue);
cell = null;
cell = cells.get_Item(currentRow, 2);
string = subStr(Fvalue +' '+ Fdescription,1,29);
string = strLTrim(string);
//according to our client's requirement the string could never have
//any double spaces or
//any special character and any double space
string = System.Text.RegularExpressions.Regex::replace(string,@"[^A-Za-z0-9.\s]", ".");
//to remove extra spacing
string = strLTrim(string);
cell.set_Value(string);
if(Fval == 1)
{
cell = null;
cell = cells.get_Item(currentRow, 3);
cell.set_Value('D4');
}
if(Fval == 2)
{
cell = null;
cell = cells.get_Item(currentRow, 3);
cell.set_Value('D5');
}
}
public str parmName(str sstring)
{
if(sstring==D1)
{
sstring = 'D1';
return sstring;
}
if(sstring==D3)
{
sstring = 'D3';
return sstring;
}
if(sstring ==D4|| sstring==D5)
{
sstring = 'D4_D5';
return sstring;
}
return sstring;
}
public System.IO.MemoryStream DataLoaderExcel()
{
str worksheetName;
Irc_ExportDataExcelClass exporter = new Irc_ExportDataExcelClass();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
using (ExcelPackage package = new ExcelPackage(memoryStream))
{
var worksheets = package.get_Workbook().get_Worksheets();
while select DimensionAttribute from financialDimensionGroup group by financialDimensionGroup.DimensionAttribute
where financialDimensionGroup.DimensionAttribute == D1
|| financialDimensionGroup.DimensionAttribute == D3
|| financialDimensionGroup.DimensionAttribute == D4
|| financialDimensionGroup.DimensionAttribute == D5
{
int currentRow = 1;
int val =0;
worksheetName = exporter.parmName(financialDimensionGroup.DimensionAttribute);
ExcelWorksheet financialDimensionWorksheet;
OfficeOpenXml.ExcelRange cells ;
OfficeOpenXml.ExcelRange cell ;
int sheetsCount = package.Workbook.Worksheets.Count;
if(financialDimensionGroup.DimensionAttribute != D5)
{
financialDimensionWorksheet = worksheets.Add(worksheetName);
cells = financialDimensionWorksheet.get_Cells();
cell = cells.get_Item(currentRow, 1);
System.String value = dimensionValue;
cell.set_Value(value);
cell = null;
value = description;
cell = cells.get_Item(currentRow, 2);
cell.set_Value(value);
//if sheet already created
if(worksheetName == "D4_D5" )
{
cell = null;
value = "D4/5";
cell = cells.get_Item(currentRow, 3);
cell.set_Value(value);
val =1;
}
}
else
{
//financialDimensionWorksheet = workSheets.get_item(3);
int row = financialDimensionWorksheet.Dimension.End.Row;
cells = financialDimensionWorksheet.get_Cells();
cell = cells;
currentRow = row;
val=2;
}
while select Description ,DimensionValue from financialDimension where financialDimension.DimensionAttribute == financialDimensionGroup.DimensionAttribute
{
currentRow++;
exporter.ExcelSheetsGenerator(cell,cells,currentRow,financialDimension.Description ,financialDimension.DimensionValue , val);
}
}
package.Save();
}
return memoryStream;
}
}
Preview :
class TestEmail
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void Main(Args args)
{
// Set these variables.
str sender = 'IRCIntegra@rescue.org';
str recipient = 'shayan.ar007@gmail.com';
str cc = 'shayan.ar007@gmail.com';
str subject = 'TheAxapta_Subject';
str body = 'TheAxapta_Msg';
str fileName = @'C:\Users\Admin1986cca646\Desktop\\D Codes FCM.xlsx';
List toList;
List ccList;
ListEnumerator le;
Set permissionSet;
System.Exception e;
str mailServer;
int mailServerPort;
System.Net.Mail.SmtpClient mailClient;
System.Net.Mail.MailMessage mailMessage;
System.Net.Mail.MailAddress mailFrom;
System.Net.Mail.MailAddress mailTo;
System.Net.Mail.MailAddressCollection mailToCollection;
System.Net.Mail.MailAddressCollection mailCCCollection;
System.Net.Mail.AttachmentCollection mailAttachementCollection;
System.Net.Mail.Attachment mailAttachment;
;
try
{
toList = strSplit(recipient, ';');
ccList = strSplit(cc, ';');
permissionSet = new Set(Types::Class);
permissionSet.add(new InteropPermission(InteropKind::ClrInterop));
permissionSet.add(new FileIOPermission(filename, 'rw'));
CodeAccessPermission::assertMultiple(permissionSet);
mailServer = SysEmaiLParameters::find(false).SMTPRelayServerName;
mailServerPort = SysEmaiLParameters::find(false).SMTPPortNumber;
mailClient = new System.Net.Mail.SmtpClient(mailServer, mailServerPort);
le = toList.getEnumerator();
le.moveNext();
mailFrom = new System.Net.Mail.MailAddress(sender);
mailTo = new System.Net.Mail.MailAddress(strLTrim(strRTrim(le.current())));
mailMessage = new System.Net.Mail.MailMessage(mailFrom, mailTo);
mailToCollection = mailMessage.get_To();
while (le.moveNext())
{
mailToCollection.Add(strLTrim(strRTrim(le.current())));
}
le = ccList.getEnumerator();
mailCCCollection = mailMessage.get_CC();
while (le.moveNext())
{
mailCCCollection.Add(strLTrim(strRTrim(le.current())));
}
mailMessage.set_Priority(System.Net.Mail.MailPriority::High);
mailMessage.set_Subject(subject);
mailMessage.set_Body(body);
mailAttachementCollection = mailMessage.get_Attachments();
mailAttachment = new System.Net.Mail.Attachment(fileName);
mailAttachementCollection.Add(mailAttachment);
mailClient.Send(mailMessage);
mailMessage.Dispose();
CodeAccessPermission::revertAssert();
info("Email sent.");
}
catch (Exception::CLRError)
{
e = ClrInterop::getLastException();
while (e)
{
info(e.get_Message());
e = e.get_InnerException();
}
CodeAccessPermission::revertAssert();
}
}
}
The piece of code firstly has been attached is using SysMailerMessageBuilder framework
Requirements:
*Our objective it to Generate an excel File and attach it to an email and sending it tomultiple recipients
*The file data will be from financial dimension view and it will be distributed into
three worksheets that is D1,D2 and D4_5 according to conditions
*Data of Data Entity of D4 & D5 will be inserted into same worksheet that is D4_5
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.excelworksheet;
class Irc_ExportDataExcelClass extends SysOperationServiceController
{
//Header Titles
public const str description = 'Description';
public const str dimensionValue = 'Dimension Value';
public const str D1 = 'D01Project_Code';
public const str D3 = 'D03Program_Area';
public const str D4 = 'D04Location';
public const str D5 = 'D05Entity_Code';
FINANCIALDIMENSIONVALUEENTITYVIEW financialDimensionGroup, financialDimension;
System.Text.RegularExpressions.Match regExMatch;
public static void main(Args _args)
{
Irc_ExportDataExcelClass exportDataExcelClass = new Irc_ExportDataExcelClass();
exportDataExcelClass.EmailSender(_args);
info('Email successfully sent');
}
public void EmailSender(Args _args)
{
SysMailerMessageBuilder mailer = new SysMailerMessageBuilder();
SysMailerSMTP smtp = new SysMailerSMTP();
var user = xUserInfo::find();
var sysUser = SysUserInfo::find(user.id);
var sender = sysUser.getSendAsEmail(user);
container con = str2con(_args.parm());
Irc_ExportDataExcelClass exporter = new Irc_ExportDataExcelClass();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
var recipient ='';
;
for(int i=1;i<=conLen(con);i++)
{
recipient = conPeek(con,i);
mailer.addTo(recipient);
}
mailer.setSubject("Financial Details");
mailer.setFrom(sender);
mailer.setBody("PFA");
memoryStream = exporter.DataLoaderExcel();
memoryStream.Seek(0, System.IO.SeekOrigin::Begin);
//The below line used to attach excel file to email.
mailer.addAttachment(memoryStream, 'D Codes FCM.xlsx', "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try
{
smtp.sendNonInteractive(mailer.getMessage());
}
catch(Exception::CLRError)
{
error(CLRInterop::getLastException().toString());
}
}
public void ExcelSheetsGenerator(OfficeOpenXml.ExcelRange cell , OfficeOpenXml.ExcelRange cells , int currentRow , str Fdescription ,str Fvalue,int Fval)
{
str string = '';
cell = null;
cell = cells.get_Item(currentRow, 1);
cell.set_Value(Fvalue);
cell = null;
cell = cells.get_Item(currentRow, 2);
string = subStr(Fvalue +' '+ Fdescription,1,29);
string = strLTrim(string);
//according to our client's requirement the string could never have
//any double spaces or
//any special character and any double space
string = System.Text.RegularExpressions.Regex::replace(string,@"[^A-Za-z0-9.\s]", ".");
//to remove extra spacing
string = strLTrim(string);
cell.set_Value(string);
if(Fval == 1)
{
cell = null;
cell = cells.get_Item(currentRow, 3);
cell.set_Value('D4');
}
if(Fval == 2)
{
cell = null;
cell = cells.get_Item(currentRow, 3);
cell.set_Value('D5');
}
}
public str parmName(str sstring)
{
if(sstring==D1)
{
sstring = 'D1';
return sstring;
}
if(sstring==D3)
{
sstring = 'D3';
return sstring;
}
if(sstring ==D4|| sstring==D5)
{
sstring = 'D4_D5';
return sstring;
}
return sstring;
}
public System.IO.MemoryStream DataLoaderExcel()
{
str worksheetName;
Irc_ExportDataExcelClass exporter = new Irc_ExportDataExcelClass();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
using (ExcelPackage package = new ExcelPackage(memoryStream))
{
var worksheets = package.get_Workbook().get_Worksheets();
while select DimensionAttribute from financialDimensionGroup group by financialDimensionGroup.DimensionAttribute
where financialDimensionGroup.DimensionAttribute == D1
|| financialDimensionGroup.DimensionAttribute == D3
|| financialDimensionGroup.DimensionAttribute == D4
|| financialDimensionGroup.DimensionAttribute == D5
{
int currentRow = 1;
int val =0;
worksheetName = exporter.parmName(financialDimensionGroup.DimensionAttribute);
ExcelWorksheet financialDimensionWorksheet;
OfficeOpenXml.ExcelRange cells ;
OfficeOpenXml.ExcelRange cell ;
int sheetsCount = package.Workbook.Worksheets.Count;
if(financialDimensionGroup.DimensionAttribute != D5)
{
financialDimensionWorksheet = worksheets.Add(worksheetName);
cells = financialDimensionWorksheet.get_Cells();
cell = cells.get_Item(currentRow, 1);
System.String value = dimensionValue;
cell.set_Value(value);
cell = null;
value = description;
cell = cells.get_Item(currentRow, 2);
cell.set_Value(value);
//if sheet already created
if(worksheetName == "D4_D5" )
{
cell = null;
value = "D4/5";
cell = cells.get_Item(currentRow, 3);
cell.set_Value(value);
val =1;
}
}
else
{
//financialDimensionWorksheet = workSheets.get_item(3);
int row = financialDimensionWorksheet.Dimension.End.Row;
cells = financialDimensionWorksheet.get_Cells();
cell = cells;
currentRow = row;
val=2;
}
while select Description ,DimensionValue from financialDimension where financialDimension.DimensionAttribute == financialDimensionGroup.DimensionAttribute
{
currentRow++;
exporter.ExcelSheetsGenerator(cell,cells,currentRow,financialDimension.Description ,financialDimension.DimensionValue , val);
}
}
package.Save();
}
return memoryStream;
}
}
Preview :
Below Peice of code could be used in Ax 2012 /D365 In many cased users faces problem in attaching excel file but for sending simple emails without attachment it works perfect.
class TestEmail
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void Main(Args args)
{
// Set these variables.
str sender = 'IRCIntegra@rescue.org';
str recipient = 'shayan.ar007@gmail.com';
str cc = 'shayan.ar007@gmail.com';
str subject = 'TheAxapta_Subject';
str body = 'TheAxapta_Msg';
str fileName = @'C:\Users\Admin1986cca646\Desktop\\D Codes FCM.xlsx';
List toList;
List ccList;
ListEnumerator le;
Set permissionSet;
System.Exception e;
str mailServer;
int mailServerPort;
System.Net.Mail.SmtpClient mailClient;
System.Net.Mail.MailMessage mailMessage;
System.Net.Mail.MailAddress mailFrom;
System.Net.Mail.MailAddress mailTo;
System.Net.Mail.MailAddressCollection mailToCollection;
System.Net.Mail.MailAddressCollection mailCCCollection;
System.Net.Mail.AttachmentCollection mailAttachementCollection;
System.Net.Mail.Attachment mailAttachment;
;
try
{
toList = strSplit(recipient, ';');
ccList = strSplit(cc, ';');
permissionSet = new Set(Types::Class);
permissionSet.add(new InteropPermission(InteropKind::ClrInterop));
permissionSet.add(new FileIOPermission(filename, 'rw'));
CodeAccessPermission::assertMultiple(permissionSet);
mailServer = SysEmaiLParameters::find(false).SMTPRelayServerName;
mailServerPort = SysEmaiLParameters::find(false).SMTPPortNumber;
mailClient = new System.Net.Mail.SmtpClient(mailServer, mailServerPort);
le = toList.getEnumerator();
le.moveNext();
mailFrom = new System.Net.Mail.MailAddress(sender);
mailTo = new System.Net.Mail.MailAddress(strLTrim(strRTrim(le.current())));
mailMessage = new System.Net.Mail.MailMessage(mailFrom, mailTo);
mailToCollection = mailMessage.get_To();
while (le.moveNext())
{
mailToCollection.Add(strLTrim(strRTrim(le.current())));
}
le = ccList.getEnumerator();
mailCCCollection = mailMessage.get_CC();
while (le.moveNext())
{
mailCCCollection.Add(strLTrim(strRTrim(le.current())));
}
mailMessage.set_Priority(System.Net.Mail.MailPriority::High);
mailMessage.set_Subject(subject);
mailMessage.set_Body(body);
mailAttachementCollection = mailMessage.get_Attachments();
mailAttachment = new System.Net.Mail.Attachment(fileName);
mailAttachementCollection.Add(mailAttachment);
mailClient.Send(mailMessage);
mailMessage.Dispose();
CodeAccessPermission::revertAssert();
info("Email sent.");
}
catch (Exception::CLRError)
{
e = ClrInterop::getLastException();
while (e)
{
info(e.get_Message());
e = e.get_InnerException();
}
CodeAccessPermission::revertAssert();
}
}
}
Comments
Post a Comment