Importing Emails from Outlook 2007 into Dynamics AX 2009
View in PDF
Download XPO
One of the really good features of Dynamics AX, is the ability to send Emails directly from the application... hence we can email invoices as pdf attachments directly from Dynamics AX..
However, there isnt a automated method of importing Emails from outlook into Dynamics.. one can manually attach emails to a record using the Document Handling functionality.... this functionality is best used in CRM where one can drag a email from Dynamics and drop it in ax (CRM > Encyclopaedia or CRM > Documents).. however the drag and drop (even though its cool) its still manual..
This project basically gives you the ability to import email from outlook in to ax... the code can either be executed on a periodic basis or can be a part of the client start up..
The code here is designed to read emails from the users Inbox and then on a basis of certain parameters, it find out which record in ax the email should be attached to..
The parameters work in 2 ways..
1. Subject Line Prefixes
If you ever worked with a Email integrated ‘Bug tracking System’ then you would know what I am talking about..
Subject line code work the following way..
For example say we create a code for sales.. sales# .. this basically means that if a subject has the word ‘sales#’ then the characters following the code (sales#) signify the sales Id the email needs to be related to.
In this project I added two subject line prefix codes.. one for Sales and the other for Customer... so for example if the subject line has the word ‘cust#’ then the characters following the code specify the customer account number.
2. From Email Address
I added a parameter, so that apart from the above option, one can also search for the record (custTanble or ContactPerson) using the email address of the sender.
Note: you might want to revisit indexes on custTable and Contact person if you like to take this path.
Ohh... in addition to that, if a match is found.. i.e. we find a email that should be attached to a ax record, then after importing the mail into ax, the routine transfer the mail to a folder (specified in the parameters table), hence the mail wont be searched by the routine again.
I dont think I need to say any more.. the code is pretty much self explanatory and were ever I deemed necessary I added comments to explain what I was up to ..
Note: I would like to point out that this is totally untested code, and there are no warranties or guarantees that I or my company provide on the usage of this code... this code was written to show one of the many ways in which ax integrates with Office Systems and was not meant to be used in a production environment.
class mrOutlookImporter
{
mrOutlookImportParameters outLookPram;
docuRef docuRef;
Microsoft.Office.Interop.Outlook.MailItemClass mailItemClass;
}
void getMails()
{
Microsoft.Office.Interop.Outlook._Application outAppl;
Microsoft.Office.Interop.Outlook.MAPIFolder mapiFolder;
Microsoft.Office.Interop.Outlook.NameSpaceClass Nspace;
Microsoft.Office.Interop.Outlook.FoldersClass foldersClass;
Microsoft.Office.Interop.Outlook.ItemsClass itemClass;
Microsoft.Office.Interop.Outlook.MAPIFolder destinationFolder;
//str dd;
str folderName;
str mailSubject;
str fromEmail;
str entryId;
str storeId;
int numOfEmails;
int numOfFolders;
boolean copyToDestination = false;
;
select firstonly outLookPram;
new InteropPermission(InteropKind::ClrInterop).assert();
outAppl = new Microsoft.Office.Interop.Outlook.ApplicationClass();
nSpace = outAppl.GetNamespace('Mapi');
mapiFolder = nSpace.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders::olFolderInbox);
foldersClass = mapiFolder.get_Folders();
numOfFolders = foldersClass.get_Count();
destinationFolder = foldersClass.GetFirst();
while(numOfFolders > 0)
{
folderName = destinationFolder.get_Name();
if(folderName == outLookPram.outlookFolder)
{
copyToDestination = true;
break;
}
destinationFolder = foldersClass.GetNext();
numOfFolders--;
}
if(!copyToDestination)
{
try
{
foldersClass.Add(outLookPram.outlookFolder,Microsoft.Office.Interop.Outlook.OlDefaultFolders::olFolderInbox);
copyToDestination = true;
}
catch(Exception::CLRError)
{
error('cannot create folder');
}
}
itemClass = mapifolder.get_Items();
mailItemClass = itemClass.GetFirst();
numOfEmails = itemClass.get_Count();
while( numOfEmails > 0)
{
try
{
numOfEmails--;
mailSubject = mailItemClass.get_Subject();
fromEmail = mailItemClass.get_SenderEmailAddress();
entryId = mailItemClass.get_EntryID();
storeId = mapifolder.get_StoreID();
if(copyToDestination && this.validateEmail(mailSubject))
{
if(this.importEmail(entryId,storeId))
mailItemClass.Move(destinationFolder);
}
else if(copyToDestination && outLookPram.checkUsingEmailAddress == NoYes::Yes)
{
if(this.findAndSetDocuref('email',fromEmail,mailSubject))
{
if(this.importEmail(entryId,storeId))
mailItemClass.Move(destinationFolder);
}
}
// one might consider moving a mail to a different folder even if a match isnt found.
mailItemClass = itemClass.GetNext(); //
}
catch(Exception::CLRError)
{
error('cannot read email');
continue;
}
}
CodeAccessPermission::revertAssert();
}
boolean validateEmail(str _subject)
{
int firstPos;
int secondPos;
;
//scan sales
if(strScan(_subject,outLookPram.salesPrefix,1,strlen(_subject)))
{
firstPos = strScan(_subject,outLookPram.salesPrefix,1,strlen(_subject));
secondPos = strScan(_subject,'',firstPos,strlen(_subject));
return this.findAndSetDocuref('sales',substr(_subject,firstPos,(secondPos-FirstPos)),_subject);
}
else if(strScan(_subject,outLookPram.custPrefix,1,strlen(_subject)))
{
firstPos = strScan(_subject,outLookPram.custPrefix,1,strlen(_subject));
secondPos = strScan(_subject,'',firstPos,strlen(_subject));
return this.findAndSetDocuref('Cust',substr(_subject,firstPos,(secondPos-FirstPos)),_subject);
}
return false;
}
boolean findAndSetDocuref(str _type,str _num,str _subject)
{
SalesTable salesTable;
CustTable custTable;
ContactPerson contactPerson;
salesId salesId;
custAccount custAccount;
email email;
;
this.initDocuref(_subject);
Switch(_type)
{
Case 'sales' :
{
salesId = _num;
select firstonly recid,ContactPersonId from salesTable where salesTable.SalesId == salesId;
if(salesTable.recid)
{
docuref.RefRecId = salesTable.RecId;
docuRef.RefTableId = tablenum(SalesTable);
docuref.ContactPersonId = salesTable.ContactPersonId;
return true;
}
break;
}
Case 'cust' :
{
custAccount = _num;
select firstonly recid,ContactPersonId from custtable where custTable.AccountNum == custAccount;
if(custTable.RecId)
{
docuref.RefRecId = custTable.RecId;
docuRef.RefTableId = tablenum(custTable);
docuref.ContactPersonId = custTable.ContactPersonId;
return true;
}
break;
}
Case 'email' :
{
email = _num;
select firstonly ContactPersonId,recid from contactPerson
where ( contactPerson.Email == email
|| contactPerson.Email2 == email
|| contactPerson.Email3 == email);
if(contactPerson.ContactPersonId)
{
docuref.RefRecId = contactPerson.RecId;
docuRef.RefTableId = tablenum(contactPerson);
docuref.ContactPersonId = contactPerson.ContactPersonId;
return true;
}
select firstonly RecId,ContactPersonId from custTable where custTable.Email == email;
if(custTable.AccountNum)
{
docuref.RefRecId = custTable.RecId;
docuRef.RefTableId = tablenum(custTable);
docuref.ContactPersonId = custTable.ContactPersonId;
return true;
}
}
}
return false;
}
boolean initDocuref(str _subject)
{
smmDragDropObjectType smmDragDropObjectType = smmDragDropObjectType::InMail;
;
docuRef.ActualCompanyId = curext();
docuref.AuthorId = smmUtility::getCurrentContact();
docuref.Name = _subject;
docuRef.TypeId = DocuType::findDroppedObjectType(smmDragDropObjectType);
}
boolean importEmail(str _entryId,str _storeId)
{
DocuType docuType;
DocuValue docuValue;
NumberSeq numSeq;
str tofilename;
int numOfAttachments;
FilePath archivePath;
int lines = infolog.line();
Query q;
;
if (!_entryId || !_storeId)
{
return checkFailed("@SYS87269");
}
docuType = DocuType::find(docuRef.TypeId);
// Check that the document file location isn't set to "Original location" in the document
if (docuType.FilePlace == DocuFilePlace::NoCopy)
{
// File location can not be set up to Orginal location for the Outlook mail types
return checkFailed("@SYS87271");
}
// Is archive set up to be in a Party (do not save in database)
if (smmDocuments::mustArchiveFiles(docuType))
{
// Get archive path
archivePath = docuType.ArchivePath;
if (archivePath && ! WinAPI::pathExists(archivePath))
{
q = new Query();
q.addDataSource(tablenum(DocuType)).addRange(fieldnum(DocuType,TypeId)).value(queryValue(docuType.TypeId));
return checkFailed(strfmt("@SYS90175",archivePath),'',
SysInfoAction_FormrunQuery::newFormnameControlnameQuery(
formstr(docuType),
identifierstr(Setup_ArchivePath),q));
}
// If no path to set up on the document type the general archive path is used instead
if (! archivePath)
{
archivePath = Docu::archivePath(docuType.DataAreaId);
}
}
else
{
// The Windows path is used because the Outlook mail must be written somewhere on disk as a temp file before it can be saved in the database
archivePath = WinAPI::getWindowsDirectory();
}
// Get a new number from the document numbersequence
numSeq = NumberSeq::newGetNum(DocuParameters::numRefDocuNumber(), true);
// Use next number in numbersequence as filename
docuValue.FileName = smmDocuments::getNonExistingFileName(numSeq, archivePath, 'msg');
// Save mail as the msg type file
docuValue.FileType = 'msg';
// If path doesn't end with to backslash it should be appended
archivePath = Docu::fileCheckPath(archivePath);
docuValue.Path = archivePath;
// Create filename based on the path in the document type table and the filename (number)
tofilename = docuValue.Path + docuValue.FileName + (docuValue.FileType ? ('.' + docuValue.FileType) : '');
// Save the e-mail as a .msg file
try
{
// Call save function on the Outlook mail item
mailItemClass.SaveAs(tofilename,Microsoft.Office.Interop.Outlook.OlDefaultFolders::olFolderInbox);
}
catch
{
Error('Cannot Save Mail as file');
return false;
}
// Check that the file was saved correctly
if (!archivePath || !WinAPI::fileExists(tofilename))
{
// Free the document numbersequence number that was reserved if the file wasn't found
numSeq.abort();
// The file could not be saved. Please check the path in the document type setup.
return checkFailed("@SYS86983");
}
// Create the file reference
docuValue.insert();
// Mark the document numbersequence number is used
numSeq.used();
// Create the document
docuRef.ValueRecId = docuValue.RecId;
docuRef.Notes = '';
docuRef.SmmEMailEntryID = _entryID;
docuRef.SmmEMailStoreID = _storeID;
docuRef.insert();
ttscommit;
// Should the file be stored in the Axapta database?
if (docuType.FilePlace == DocuFilePlace::Database)
{
// Copy the file to the database
docuValue = DocuValue::writeDocuValue(docuRef, tofilename);
// Delete the temp file that was placed in the Windows Party
WinAPI::deleteFile(toFileName);
}
return true;
}