E-Mail-Archivierung in Sage CRM mit dem E-Mail-Manager

Wie schnell die Zeit vergeht...
Dieser Beitrag wurde vor über 5 Jahren veröffentlicht und enthält evtl. Informationen, die nicht mehr oder nur noch teilweise gültig sind.

Wie im vorletzten Beitrag erwähnt, lässt sich der in CRM integrierte E-Mail-Manager wunderbar dazu benutzen, um ein- und ausgehende E-Mails automatisch zu archivieren. Wie so etwas funktionieren kann, soll der nachfolgende Artikel etwas genauer beleuchten.

Das Szenario:

Sie wollen bei Bedarf ein- und ausgehende E-Mails in ein separates (Exchange-)Postfach kopieren oder verschieben und die E-Mails in diesem Postfach dann automatisch mittels dem CRM-E-Mail-Manager in Sage CRM archivieren? Die Zuordnung zu Leads oder Tickets, sowie zu Personen, Firmen und Konten soll natürlich automatisch durchgeführt werden?

Dann brauchen Sie hierfür lediglich drei Dinge:

1.) Der E-Mail-Manager-Dienst auf dem CRM-Server muss aktiviert sein
2.) Ein Postfach, welches Ihre abzulegenden E-Mails aufnimmt
3.) Ein E-Mail-Manager-Skript, welches die Ablage in CRM durchführt

Nun, die Punkte 1.) + 2.) dürften nicht schwierig zu bewerkstelligen sein. Punkt 3.) ist da schon etwas interessanter.
CRM liefert im Standard ein Skript mit (“communication.js”), welches durch geringfügige Anpassung bzw. Erweiterung das geschilderte Szenario Realität werden lässt.

Wenn Sie nun im Verzeichnis Scripts Ihrer CRM-Installation (also z. B. C:\Programme\Sage\CRM\Services\CustomPages\Scripts) eine neue Datei mit der Endung .js anlegen (in meinem Beispiel ArchiveEmails.js) und die folgenden 25kB Text hineinkopieren, ist die erste Hürde bereits genommen (ich hoffe inständig, dass sich kein Tippfehler eingeschlichen hat ;)):

//************************************************************************************************************************
// Email Manager Communications script.
// When this script is run from the email manager 6 objects are passed in to the scripts namespace.
// They are 
// 1. UserQuery - eWareQuery object that contains the details of the user who sent the email...should there be one. 
// 2. PersonQuery - eWareQuery object that contains the person details of the sender of the email
// 3. CompanyQuery - eWareQuery object that contains the company details of the sender of the email
// 4. eware - eWare object itself...logged on with admin user
// 5. MsgHandler - The log function (To catch errors) is only used from this object
// 6. eMail - This is the interface to the email itself
// *NOTE* for user (the senders) security to apply you must create your own eware object and logon as that user using their
//        credentials.
//*************************************************************************************************************************
//
// This is the Communications script. 
// All the functionality is called from the BeforeMainAction function
//
//
//
//

MsgHandler.Debug=true;
MsgHandler.Log("Start");
var comm;

var PersInfo = 0;
//assigned user
var AssignedUser=0;
var AssignedChannel=0;
//priority
var prLow=0;
var prNormal=1;
var prHigh=2;

var sBlank="";

var commid;
var caseid;

commid=0;
caseid=0;
oppoid=0;

//Comm variables
var CommAction;

var pers_array = new Array();
var perscomp_array = new Array();
var persAcc_array = new Array();

var comp_array = new Array();
var compAcc_array = new Array();

var Acc_array = new Array();

var iLeadID = 0;

var EmailInAddress;

function SetCommAction()
{
  if (eMail.Recipients.Count == 1)
  {
    var i=0;
    var singleaddress = eMail.Recipients.Items(0).Address;
    var senderaddress = eMail.SenderAddress;
    MsgHandler.Log("Sender: " + eMail.SenderAddress);
    // check if the email address is the address in here
    MsgHandler.Log("singleaddress=" + singleaddress);
    SQL = "SELECT Count(user_userid) as UserCount from users where user_emailaddress=UPPER(RTRIM(N'" + singleaddress + "')) ";
    recQueryE = eWare.CreateQueryObj(SQL);
    recQueryE.SelectSQL();
    while(!recQueryE.eof)
    {
	i=recQueryE.FieldValue("UserCount");
        recQueryE.NextRecord();
    }
    MsgHandler.Log("iCount is: " +i);
    if(i==1)
    {
      CommAction ="EmailIn";
    }
    else
    {
      CommAction ="EmailOut";
    }
  }   
  else
  {
    CommAction ="EmailOut";
  }
}

function BuildIdArrays()
{
  //get the people from the recipient list
  for (i = 0; i < (eMail.Recipients.Count); i++)
  {
    GetDetails(eMail.Recipients.Items(i).Address);
  }
  for (i = 0; i < (eMail.CC.Count); i++)
  {
    GetDetails(eMail.CC.Items(i).Address);
  }
  for (i = 0; i < (eMail.BCC.Count); i++)
  {
    GetDetails(eMail.BCC.Items(i).Address);
  }
}

function FixStringForSQL(myvalue)
{
  var r, re;
  var mystring;
  mystring = myvalue + "";
  re = /\'/g;             //Create regular expression pattern.
  mystring  = mystring.replace(re, "''");    //Replace "'" with "''".
  return(mystring);
}


//BMcA 09/02/09 If the Email isn't associated with a person or company, See if it is related to an Account.

function GetDetails(emailaddress)
{
  var bIdFound, bUseAccounts;

  emailaddress = FixStringForSQL(emailaddress);


  if (DoAccountsExist() =="Y")
  {
    bUseAccounts = true;
  }
  else
  {
    bUseAccounts = false;
  }

  MsgHandler.Log("Get Details: Start. Accounts are supported by this CRM Install: " + bUseAccounts);



  if (emailaddress != "")
  {
    MsgHandler.Log("emailaddress is " + emailaddress);

    //ab v7.1 normalisation - simplify code here, do one select from vSearchListEmail view
    //this contains all email addresses - then you can tell if they are person/company or account by the ids
    //populate arrays as before
    
    SQL = "SELECT DISTINCT Pers_Personid, Acc_AccountId, Comp_CompanyId, Emai_EmailAddress FROM vEmailCompanyAndPerson WHERE " + 
          "UPPER(RTRIM(Emai_EmailAddress)) = UPPER(RTRIM(N'" + emailaddress + "')) ";

   
    recQuery = eWare.CreateQueryObj(SQL);
    MsgHandler.Log("Select all matching email addresses " + SQL);
    recQuery.SelectSQL();

    if (recQuery.EOF)
    {       
      // get the lead info
      SQL = "SELECT Lead_LeadID FROM Lead WHERE UPPER(RTRIM(Lead_PersonEMail)) = UPPER(RTRIM(N'" +
            emailaddress + "')) AND Lead_Deleted IS NULL";

      recQuery = eWare.CreateQueryObj(SQL);
      MsgHandler.Log("Email account doesn't belong to any person/company/account. Check the leads table: " + SQL);
      recQuery.SelectSQL();

      if (recQuery.EOF)
      {
        //if (UserQuery.EOF) 
        //SendFailureReply();
      }
      else
      {
        iLeadID = recQuery("Lead_LeadID");
      }
    }
    else
    {
      while (!recQuery.EOF)
      {
        bIdFound = false;
        //All emails for company/presona and account are now in this query so check each record to see what entity it is
        if (recQuery("Pers_PersonId") !=null)
        {
          //Person email
          for (x = 0; x < pers_array.length; x++)
          {
            if (pers_array[x] == recQuery("Pers_PersonId"))
              bIdFound = true;
          } 
          if (bIdFound == false)
          {
            pers_array[pers_array.length] = recQuery("Pers_PersonId");
            perscomp_array[perscomp_array.length] = recQuery("Comp_CompanyId");
            persAcc_array[persAcc_array.length] = recQuery("Acc_AccountId");
          }
        }
        else if (recQuery("Acc_AccountId") != null)
        {
          //ACCOUNT email
          for (x = 0; x < Acc_array.length; x++)
          {
            if (Acc_array[x] == recQuery("Acc_AccountId"))
              bIdFound = true;
          }
          if (bIdFound == false)
          {
            Acc_array[Acc_array.length] = recQuery("Acc_AccountId");
          }
        }
        else 
        {
          //COMPANY EMAIL - SAVE ACCOUNT DETAILS TOO for some unknown reason.
          for (x = 0; x < comp_array.length; x++)
          {
            if (comp_array[x] == recQuery("Comp_CompanyId"))
            {
              //This company is in vEmail more than once.. we only want one record for it.
              bIdFound = true;
              if (compAcc_array[x]==null)
              {
                //But double check that the account info is ok
                if (recQuery("Acc_AccountId") != null)
                {
                  //if not use this vEmail record value instead..
                  compAcc_array[x] = recQuery("Acc_AccountId");
                } 
              }
            }
          }
          if (bIdFound == false)
          {
            comp_array[comp_array.length] = recQuery("Comp_CompanyId");
            compAcc_array[compAcc_array.length] = recQuery("Acc_AccountId");
          }

        }

        recQuery.NextRecord();
      }
      if (pers_array.length > 0)    
      {
        MsgHandler.Log("Email is related to People: " + pers_array.length);
//        var ss = '';
//        for (x = 0; x < pers_array.length; x++) {ss+=pers_array[x] +','}
//        MsgHandler.Log("personids: "+ss);

      }
      if (comp_array.length > 0)
      { 
        MsgHandler.Log("Email is related to Company: " + comp_array.length); 
//        var ss = '';
//        for (x = 0; x < comp_array.length; x++) {ss+=comp_array[x] +','}
//        MsgHandler.Log("companyids: "+ss);
      }
      if (Acc_array.length > 0)
      {
        MsgHandler.Log("Email is related to Account: " + Acc_array.length); 
//        var ss = '';
//        for (x = 0; x < Acc_array.length; x++) {ss+=Acc_array[x] +','}
//        MsgHandler.Log("accountids: "+ss);
      }
    }
  }
  MsgHandler.Log("Get Details: Finished");
}



function CheckForMultipleUsers()
{
  Usercount = 0;

  while (!UserQuery.EOF)
  {
    Usercount = Usercount + 1;
    UserQuery.NextRecord();
  }

  if (Usercount > 1)
  {
    MsgHandler.Log("***********************************************************************");
    MsgHandler.Log("Potential Problem: There are multiple Users with the same email address");
    MsgHandler.Log("***********************************************************************");
  }
  UserQuery.SelectSQL();
}

// Use this function to alter any data or to do any custom work before the main action gets called.
function BeforeMainAction()
{
  MsgHandler.Log("Before Main Action : Start");
  //CheckForMultipleUsers();
  //if (!UserQuery.EOF)
  //{
    SetCommAction();
    if (CommAction == "EmailIn") 
    {
      MsgHandler.Log("Its an emailin type");
      //EmailInAddress = ParseForEmailAddress(eMail.Body);
      var EmailInAdress = eMail.SenderAddress;
      MsgHandler.Log("--------> EmailInAddress is " + eMail.SenderAddress);
      GetDetails(eMail.SenderAddress);
    }
    else
    {
      MsgHandler.Log("Its an emailOut type");
      BuildIdArrays();
    }
    CreateComm();
  //}
  //else
  //{
  //  MsgHandler.Log("No Users exist for this from email address");
  //}
}

// Use this function to alter any data or to do any custom work like create a log record or send an email for example.
function AfterMainAction()
{

}

// Useful functions
function Defined(Arg)
{
  return (Arg+""!="undefined");
}

// Takes a javascript date object as its parameter
function getsqldatetimefromdate(dtdate)
{
  var d = dtdate.getDate() + "";
  var m = (dtdate.getMonth() + 1) + "";
  if (d.length == 1)
    d = "0" + d;
  if (m.length == 1)
    m = "0" + m;
  return m + "/" + d + "/" + dtdate.getFullYear() + " " + dtdate.getHours() + ":" + dtdate.getMinutes() + ":01";
}

function replaceInlineImages(emailBody) {
  var regularExpressionPattern = /src="cid:\w+?\.\w+?@.+?"/g;
  var embeddedImages = emailBody.match(regularExpressionPattern);
  if (embeddedImages != null) {
    for (i = 0; i < embeddedImages.length; i++) {
      var imageName = embeddedImages[i].replace(/src="cid:/,'src="').replace(/@.+?"/,'"');
      emailBody = emailBody.replace(embeddedImages[i],imageName);
    }
  }
  return emailBody;
}

// Create a communication
function CreateComm()
{
  if (Defined(comm))
    return;
  MsgHandler.Log("About to create a communication");
  var ToList, CCList, BCCList;
  //communication
  comm = eWare.CreateRecord("communication");
  comm("Comm_Action") = CommAction;
  comm("Comm_Type") = "email";
  comm("Comm_Status") = "Complete"
  //priority
  if (eMail.Priority == prLow)
  {
    comm("Comm_Priority") = "Low";
  }
  else
  if (eMail.Priority == prNormal)
  {
    comm("Comm_Priority") = "Normal";
  }
  else
  if (eMail.Priority == prHigh)
  {
    comm("Comm_Priority") = "High";
  }
  // comm_datetime
  commdate = new Date(eMail.DeliveryTime); 
  comm("Comm_datetime") = commdate.getVarDate(); 
  comm("Comm_Subject") = eMail.Subject;
  comm("Comm_Email") = replaceInlineImages(eMail.Body);
  if (iLeadID != 0)
    comm("Comm_LeadID") = iLeadID;

  if ((CommAction == "EmailIn") & (eMail.SenderAddress != ""))
  { 
    comm("comm_from") = "<" + eMail.SenderAddress + "> ";
  }
  else
  { 
    if (eMail.SenderName != "")
    {
      comm("comm_from") = "\"" + eMail.SenderName + "\" " + "<" + eMail.SenderAddress + "> "; 
    }
    else
    {
      comm("comm_from") = eMail.SenderAddress;
    }
  }
  ToList = "";
  CCList = "";
  BCCList = "";

  var singleaddress;
  var useraddress = MsgHandler.EmailAddress; 

  if ((CommAction == "EmailIn") & (eMail.SenderAddress != ""))
  { 
    comm("comm_to") = "\"" + eMail.Recipients.Items(0).Name + "\" " + "<" + eMail.Recipients.Items(0).Address + "> ";
  }
  else
  { 
    comm("comm_to") = GetMailList(eMail.Recipients); 
  }
  comm("comm_cc") = GetMailList(eMail.CC); 
  comm("comm_bcc") = GetMailList(eMail.BCC); 
  comm("comm_replyto") = eMail.Header("Reply-To");
  comm("Comm_IsHtml") = "Y";
  SetUserTerr(comm);
  comm.SaveChanges(); 

  bRecCreated = false;

  // comm_links
  // person info

  if (pers_array.length >= 1)
  {
    MsgHandler.Log("Person Info");
    numrecips = pers_array.length;
    i = 0;
    while (i < numrecips)
    {
      commlink = eWare.CreateRecord("comm_link");
      commlink("CmLi_Comm_CommunicationId") = comm.RecordId;
      if (!UserQuery.EOF)
        commlink("CmLi_Comm_UserId") = UserQuery("User_UserId");
      commlink("CmLi_Comm_PersonId") = pers_array[i];
      MsgHandler.Log("Pers ID:  " + pers_array[i]);

      //if There isn't a relationship from the email table, look up the person_link table. 
      if ((perscomp_array[i] == "") || (perscomp_array[i] == null))
      {
         MsgHandler.Log("Comp ID is either null or blank:  " + perscomp_array[i]);
         commlink("CmLi_Comm_CompanyId") = GetRelatedCompany(pers_array[i]);
      }
      else
      {
         MsgHandler.Log("Comp ID is:  " + perscomp_array[i]);
         commlink("CmLi_Comm_CompanyId") = perscomp_array[i];
      } 


      //if There isn't a relationship from the email table, look up the person table. 
      if ((persAcc_array[i] == "") || (persAcc_array[i] == null))
      {
         MsgHandler.Log("Account ID is either null or blank:  " + persAcc_array[i]);
         commlink("CmLi_Comm_AccountId") = GetRelatedAccount(pers_array[i], 1);
      }
      else
      {
         MsgHandler.Log("Account ID :  " + persAcc_array[i]);
         commlink("CmLi_Comm_AccountId") = persAcc_array[i];
      } 

      commlink.SaveChanges();
      bRecCreated = true;
      i = i + 1;
    }
  }

  // company info
  if (comp_array.length >= 1)
  {
    MsgHandler.Log("Company Info");
    numcomps = comp_array.length;
    i = 0;
    while (i < numcomps)
    {
      commlink = eWare.CreateRecord("comm_link");
      commlink("CmLi_Comm_CommunicationId") = comm.RecordId;
      if (!UserQuery.EOF) 
        commlink("CmLi_Comm_UserId") = UserQuery("User_UserId");
      commlink("CmLi_Comm_CompanyId") = comp_array[i];
      commlink("CmLi_Comm_AccountId") = compAcc_array[i];
      commlink.SaveChanges();
      bRecCreated=true;
      i = i + 1;
    }
  }


  // Account info
  if (Acc_array.length >= 1)
  {
    MsgHandler.Log("Account Info");
    numAccs = Acc_array.length;
    i = 0;
    while (i < numAccs)
    {
      commlink = eWare.CreateRecord("comm_link");
      commlink("CmLi_Comm_CommunicationId") = comm.RecordId;
      if (!UserQuery.EOF) 
        commlink("CmLi_Comm_UserId") = UserQuery("User_UserId");
      commlink("CmLi_Comm_AccountId") = Acc_array[i];
      commlink("CmLi_Comm_CompanyId") = GetRelatedAccount(Acc_array[i], 2);
      commlink.SaveChanges();
      bRecCreated=true;
      i = i + 1;
    }
  }



  if ((bRecCreated == false) & (!(UserQuery.EOF)))
  {
    //user info
    commlink = eWare.CreateRecord("comm_link");
    commlink("CmLi_Comm_CommunicationId") = comm.RecordId;
    commlink("CmLi_Comm_UserId") = UserQuery("User_UserId");
    commlink.SaveChanges();
    bRecCreated=true;
  } else if (bRecCreated == false) {
      MsgHandler.Log("Communication is not linked. Sender not found in CRM!");
  }
  MsgHandler.Log("Communication created");

  var Attachments = eMail.Attachments;

  var subfolder = new String(comm.RecordId);
  subfolder = "Comm" + subfolder;

  var Userdir;
  if (!(UserQuery.EOF))
    Userdir = UserQuery("User_firstname") + " " + UserQuery("User_lastname") + "\\" + (subfolder);
  else
    Userdir = eMail.SenderName + "\\" + (subfolder);

  var libdir = Attachments.LibraryPath + "\\" + Userdir;

  var AttItem;
  var bHasAttachments;
  bHasAttachments = false;

  // library records...attachments
  for (i = 0; i < Attachments.Count; i++)
  {
    // saving the attachment here
    AttItem = Attachments.Items(i);
    if (AttItem.Name != "_alt.0")
    {
      // getting a new name for it
      NewName = MsgHandler.GetUniqueFileName(libdir, AttItem.Name);
      // saving to the library... 
      AttItem.SaveAs(NewName, libdir);
      librec = eWare.CreateRecord("library");
      librec("libr_FileName") = NewName;
      librec("libr_CommunicationId") = comm.RecordId;
      if (!(UserQuery.EOF))
        librec("libr_Userid") = UserQuery("User_UserId");
      librec("libr_type") = "EmailAttachment";
      librec("libr_status") = "Complete";
      librec("Libr_FilePath") = Userdir;
      librec.SaveChanges();
      bHasAttachments = true;
    }
  }

MsgHandler.Log("Case tracker start");
  var mycase
  var BIsValid
    // Try to get the case number
  mycase = ParseForCaseRef(eMail.Subject);
  // Check if the email subject contains a case reference number
    if (mycase!="") 
    {
      // If it does then check if it is a valid case reference
      if (IsValidCase(mycase))
      {
        bIsValid = true;
      }
      else
      {
        bIsValid = false;      
      }
    }
    else
    {
      bIsValid = false;      
    } 
   MsgHandler.Log("Case tracker status: " + bIsValid);
   if (bIsValid)
   {
       caseid = CaseQuery("Case_CaseId");
       if(PersInfo == 1)
       {
       	CasePersonId = CaseQuery("Case_PrimaryPersonId");
       	commlink("CmLi_Comm_PersonId") = CasePersonId;
       }
       CaseCompanyId = CaseQuery("Case_PrimaryCompanyId");
       CaseAccountId = CaseQuery("Case_PrimaryAccountId");
       AssignedUser = CaseQuery("Case_AssignedUserId");
       AssignedChannel = CaseQuery("Case_ChannelId");
       territoryid = CaseQuery("Case_SecTerr");
       commlink("CmLi_Comm_AccountId") = CaseAccountId;
       commlink("CmLi_Comm_CompanyId") = CaseCompanyId;
       commlink.SaveChanges();
       comm("comm_caseid")=caseid;
       var Betreff = eMail.Subject;
       var ReplaceString = ParseForCaseRef(Betreff);
       var NeuerBetreff = Betreff.replace(ReplaceString, "");
       comm("Comm_Subject") = NeuerBetreff;
       comm.SaveChanges();
   }

  if (bHasAttachments == true)
  {
    comm("Comm_HasAttachments") = "Y";
    comm.SaveChanges();
  }
  //SendConfirmationReply();
}



function IsValidCase(value)
{
  CaseSQL = "SELECT * FROM vCases WHERE Case_ReferenceID = '" + value + "'";
  CaseQuery = eWare.CreateQueryObj(CaseSQL);
  CaseQuery.SelectSQL(); // open the query
  if (CaseQuery.EOF)
  {
    return(false);
  }
  else
  {
    return(true);
  }
}

function ParseForCaseRef(value)
{
  var r, re, singlestr; //Declare variables.
  // Create my test string
  var s = value;
  // Create regular expression pattern. Used for getting the case reference
  re = /[\d]+-[a-z_0-9]{1,20}/i; 
  re.Global = true;
  re.IgnoreCase = true;
  re.MultiLine = true;

  singlestr = s.match(re);
  if (singlestr == null)
  {
    singlestr = "";
  }
  return(singlestr); // Return the first case ref
}


function SetTerr(comm)
{
  // reset the queries.
  PersonQuery.SelectSql();
  CompanyQuery.SelectSql();
  if (!PersonQuery.EOF)
  {
    if (PersonQuery("pers_secterr") != null)
      comm("comm_secterr") = PersonQuery("pers_secterr");
  }
  else 
  if (!CompanyQuery.EOF)
  {
    if (CompanyQuery("comp_secterr") != null)
      comm("comm_secterr") = CompanyQuery("comp_secterr");
  }
}

function SetUserTerr(comm)
{
  if (!UserQuery.EOF)
  {
    if (UserQuery("User_PrimaryTerritory") != null)
      comm("comm_secterr") = UserQuery("User_PrimaryTerritory");
  }
}

// This function finds the first email address and returns the email address.
function ParseForEmailAddress(value)
{
  var r, re, singlestr; //Declare variables.
  //create my test string
  var s = value;

  //Create regular expression pattern. used for testing whether a string is a valid email address
  re = /[a-z][a-z_0-9_\-\'\.]+@[a-z_0-9_\-_'\.]+\.[a-z]{2,6}[^0-9]{1}/i; 
  re.Global = true;
  re.IgnoreCase = true;
  re.MultiLine = true;
  singlestr = s.match(re);
  if ((singlestr!="") && (singlestr!=null)) {
    singlestr = "a"+singlestr; // convert to string
    singlestr = singlestr.substr(1, singlestr.length-2); //get email address only
  }
  return(singlestr);                   //Return the first email address that we find.
}

function SuffixFileName(Name, Ext)
{
  var newname = Name.substr(0, 4);  //Get substring
  var ndt = new Date();
  newname += ndt.getHours() + "." + ndt.getMinutes() + " " + ndt.getDay() + "-" +
    (ndt.getMonth() + 1) + "-" + ndt.getYear() + " " + ndt.getHours() +":" +
    ndt.getMinutes() + ":" + ndt.getSeconds() + "." + ndt.getMilliseconds() + Ext;
  return(newname);
}

// This function expects either "eMail.Recipients" or "eMail.CC" or "eMail.BCC" as a parameter.
// it returns a string containing the email addresses and friendly names of the respective list.
function GetMailList(mylist)
{
  var ToList;
  ToList="";
  for (i = 0; i < (mylist.Count); i++)
  {
    if (i != 0)
      ToList = ToList + "; ";
    if (mylist.Items(i).Name != "")
      ToList = ToList + "\"" + mylist.Items(i).Name + "\" <" + mylist.Items(i).Address + "> ";
    else
      ToList = ToList + mylist.Items(i).Address;
  }
  return(ToList); 
}

function prepareConfirmationMail()
{
  var sReplyName = eMail.SenderName;
  var sReplyAddress = eMail.SenderAddress;

  eMail.Clear();
  eMail.Recipients.AddAddress(sReplyAddress, sReplyName);

  // Add in the system notify address
  SQL = "SELECT Parm_Value FROM Custom_SysParams WHERE LOWER(RTRIM(Parm_Name)) = N'notifyemailaddress'";
  recQuery = eWare.CreateQueryObj(SQL);
  MsgHandler.Log(SQL);
  recQuery.SelectSQL();
  if (!recQuery.EOF)
  {
    eMail.SenderAddress = recQuery("Parm_Value");
  }
  if (eMail.SenderAddress == "")
  {
    MsgHandler.Log("Notify E-mail Address is not set.");
  }
    
  SQL = "SELECT Parm_Value FROM Custom_SysParams WHERE LOWER(RTRIM(Parm_Name)) = N'notifyemailname'";
  recQuery = eWare.CreateQueryObj(SQL);
  MsgHandler.Log(SQL);
  recQuery.SelectSQL();
  if (!recQuery.EOF)
  {
    eMail.SenderName = recQuery("Parm_Value");
  }
  if (eMail.SenderAddress == "")
  {
    MsgHandler.Log("Notify E-mail Name is not set.");
  }
}

function SendConfirmationReply()
{
  if (MsgHandler.FeedbackOnSuccess)
  {
    var sSubject = eMail.Subject.substring(0, 39);
    prepareConfirmationMail();
    eMail.Subject = "Email filed -- " + sSubject;
    eMail.Body = ".";
    if (!(eMail.SenderAddress == "") && !(eMail.Recipients.Count == 0))
      eMail.Send();
  }
}

function SendFailureReply()
{
  if (MsgHandler.FeedbackOnFailure)
  {
    var sSubject = eMail.Subject.substring(0, 39);
    prepareConfirmationMail();
    eMail.Subject = "Filing failed -- " + sSubject;
    eMail.Body = "There was no matching recipients to file under";
    if (!(eMail.SenderAddress == "") && !(eMail.Recipients.Count == 0))
      eMail.Send();
  }
}

function GetRelatedCompany(PersonID)
{
    
    MsgHandler.Log("Look for a relationship between a Person and a company.");
    SQL = "SELECT DISTINCT peli_CompanyID from person_link WHERE peli_personID = "+PersonID; 
    recQuery = eWare.CreateQueryObj(SQL);
    recQuery.SelectSQL();

    if (!recQuery.EOF)
    {
       MsgHandler.Log("Company Found!");
       return(recQuery("peli_CompanyID"));   
    }

}


function DoAccountsExist()
{
    MsgHandler.Log("Check to see if Accounts exist for this CRM installation");

    SQL = "SELECT Parm_Value FROM Custom_SysParams WHERE LOWER(RTRIM(Parm_Name)) = N'useaccounts'";
    recQuery = eWare.CreateQueryObj(SQL);
    recQuery.SelectSQL();

    if (!recQuery.EOF)
    {
        str = recQuery("Parm_Value");
    }
    return (str);

}

//BMcA 

function GetRelatedAccount(ID, x)
{
  if (x == 1)
  {
    SQL = "SELECT DISTINCT pers_AccountID from person WHERE pers_personID = "+ID; 
    MsgHandler.Log("Look for Relationship between Person and Account: " + SQL);

    recQuery = eWare.CreateQueryObj(SQL);
    recQuery.SelectSQL();

    if (!recQuery.EOF)
    {
        str = recQuery("pers_AccountID"); 
        MsgHandler.Log("Account: " + str);

    } else {
        str = null;
    }
    return (str);

  }
 
  

  if (x == 2)
  {

    SQL = "SELECT DISTINCT Acc_CompanyID from vAccount WHERE Acc_AccountID = "+ID; 
    MsgHandler.Log("Look for a relationship between a Company and an account "+ SQL);

    recQuery = eWare.CreateQueryObj(SQL);
    recQuery.SelectSQL();
    if (!recQuery.EOF)
    {
        str = recQuery("Acc_CompanyID");   
    } else {
        str = null;
    }
    return (str);
  }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

//************************************************************************************************************************

// Email Manager Communications script.

// When this script is run from the email manager 6 objects are passed in to the scripts namespace.

// They are

// 1. UserQuery - eWareQuery object that contains the details of the user who sent the email...should there be one.

// 2. PersonQuery - eWareQuery object that contains the person details of the sender of the email

// 3. CompanyQuery - eWareQuery object that contains the company details of the sender of the email

// 4. eware - eWare object itself...logged on with admin user

// 5. MsgHandler - The log function (To catch errors) is only used from this object

// 6. eMail - This is the interface to the email itself

// *NOTE* for user (the senders) security to apply you must create your own eware object and logon as that user using their

// credentials.

//*************************************************************************************************************************

// This is the Communications script.

// All the functionality is called from the BeforeMainAction function

MsgHandler.Debug=true;

MsgHandler.Log("Start");

var comm;

var PersInfo = 0;

//assigned user

var AssignedUser=0;

var AssignedChannel=0;

//priority

var prLow=0;

var prNormal=1;

var prHigh=2;

var sBlank="";

var commid;

var caseid;

commid=0;

caseid=0;

oppoid=0;

//Comm variables

var CommAction;

var pers_array = new Array();

var perscomp_array = new Array();

var persAcc_array = new Array();

var comp_array = new Array();

var compAcc_array = new Array();

var Acc_array = new Array();

var iLeadID = 0;

var EmailInAddress;

function SetCommAction()

{

if (eMail.Recipients.Count == 1)

{

var i=0;

var singleaddress = eMail.Recipients.Items(0).Address;

var senderaddress = eMail.SenderAddress;

MsgHandler.Log("Sender: " + eMail.SenderAddress);

// check if the email address is the address in here

MsgHandler.Log("singleaddress=" + singleaddress);

SQL = "SELECT Count(user_userid) as UserCount from users where user_emailaddress=UPPER(RTRIM(N'" + singleaddress + "')) ";

recQueryE = eWare.CreateQueryObj(SQL);

recQueryE.SelectSQL();

while(!recQueryE.eof)

{

i=recQueryE.FieldValue("UserCount");

recQueryE.NextRecord();

}

MsgHandler.Log("iCount is: " +i);

if(i==1)

{

CommAction ="EmailIn";

}

else

{

CommAction ="EmailOut";

}

else

{

CommAction ="EmailOut";

}

function BuildIdArrays()

{

//get the people from the recipient list

for (i = 0; i < (eMail.Recipients.Count); i++)

{

GetDetails(eMail.Recipients.Items(i).Address);

}

for (i = 0; i < (eMail.CC.Count); i++)

{

GetDetails(eMail.CC.Items(i).Address);

}

for (i = 0; i < (eMail.BCC.Count); i++)

{

GetDetails(eMail.BCC.Items(i).Address);

}

function FixStringForSQL(myvalue)

{

var r, re;

var mystring;

mystring = myvalue + "";

re = /\'/g; //Create regular expression pattern.

mystring = mystring.replace(re, "''"); //Replace "'" with "''".

return(mystring);

}

//BMcA 09/02/09 If the Email isn't associated with a person or company, See if it is related to an Account.

function GetDetails(emailaddress)

{

var bIdFound, bUseAccounts;

emailaddress = FixStringForSQL(emailaddress);

if (DoAccountsExist() =="Y")

{

bUseAccounts = true;

}

else

{

bUseAccounts = false;

}

MsgHandler.Log("Get Details: Start. Accounts are supported by this CRM Install: " + bUseAccounts);

if (emailaddress != "")

{

MsgHandler.Log("emailaddress is " + emailaddress);

//ab v7.1 normalisation - simplify code here, do one select from vSearchListEmail view

//this contains all email addresses - then you can tell if they are person/company or account by the ids

//populate arrays as before

SQL = "SELECT DISTINCT Pers_Personid, Acc_AccountId, Comp_CompanyId, Emai_EmailAddress FROM vEmailCompanyAndPerson WHERE " +

"UPPER(RTRIM(Emai_EmailAddress)) = UPPER(RTRIM(N'" + emailaddress + "')) ";

recQuery = eWare.CreateQueryObj(SQL);

MsgHandler.Log("Select all matching email addresses " + SQL);

recQuery.SelectSQL();

if (recQuery.EOF)

{

// get the lead info

SQL = "SELECT Lead_LeadID FROM Lead WHERE UPPER(RTRIM(Lead_PersonEMail)) = UPPER(RTRIM(N'" +

emailaddress + "')) AND Lead_Deleted IS NULL";

recQuery = eWare.CreateQueryObj(SQL);

MsgHandler.Log("Email account doesn't belong to any person/company/account. Check the leads table: " + SQL);

recQuery.SelectSQL();

if (recQuery.EOF)

{

//if (UserQuery.EOF)

//SendFailureReply();

}

else

{

iLeadID = recQuery("Lead_LeadID");

}

else

{

while (!recQuery.EOF)

{

bIdFound = false;

//All emails for company/presona and account are now in this query so check each record to see what entity it is

if (recQuery("Pers_PersonId") !=null)

{

//Person email

for (x = 0; x < pers_array.length; x++)

{

if (pers_array[x] == recQuery("Pers_PersonId"))

bIdFound = true;

}

if (bIdFound == false)

{

pers_array[pers_array.length] = recQuery("Pers_PersonId");

perscomp_array[perscomp_array.length] = recQuery("Comp_CompanyId");

persAcc_array[persAcc_array.length] = recQuery("Acc_AccountId");

}

else if (recQuery("Acc_AccountId") != null)

{

//ACCOUNT email

for (x = 0; x < Acc_array.length; x++)

{

if (Acc_array[x] == recQuery("Acc_AccountId"))

bIdFound = true;

}

if (bIdFound == false)

{

Acc_array[Acc_array.length] = recQuery("Acc_AccountId");

}

else

{

//COMPANY EMAIL - SAVE ACCOUNT DETAILS TOO for some unknown reason.

for (x = 0; x < comp_array.length; x++)

{

if (comp_array[x] == recQuery("Comp_CompanyId"))

{

//This company is in vEmail more than once.. we only want one record for it.

bIdFound = true;

if (compAcc_array[x]==null)

{

//But double check that the account info is ok

if (recQuery("Acc_AccountId") != null)

{

//if not use this vEmail record value instead..

compAcc_array[x] = recQuery("Acc_AccountId");

}

if (bIdFound == false)

{

comp_array[comp_array.length] = recQuery("Comp_CompanyId");

compAcc_array[compAcc_array.length] = recQuery("Acc_AccountId");

}

recQuery.NextRecord();

}

if (pers_array.length > 0)

{

MsgHandler.Log("Email is related to People: " + pers_array.length);

// var ss = '';

// for (x = 0; x < pers_array.length; x++) {ss+=pers_array[x] +','}

// MsgHandler.Log("personids: "+ss);

}

if (comp_array.length > 0)

{

MsgHandler.Log("Email is related to Company: " + comp_array.length);

// var ss = '';

// for (x = 0; x < comp_array.length; x++) {ss+=comp_array[x] +','}

// MsgHandler.Log("companyids: "+ss);

}

if (Acc_array.length > 0)

{

MsgHandler.Log("Email is related to Account: " + Acc_array.length);

// var ss = '';

// for (x = 0; x < Acc_array.length; x++) {ss+=Acc_array[x] +','}

// MsgHandler.Log("accountids: "+ss);

}

MsgHandler.Log("Get Details: Finished");

}

function CheckForMultipleUsers()

{

Usercount = 0;

while (!UserQuery.EOF)

{

Usercount = Usercount + 1;

UserQuery.NextRecord();

}

if (Usercount > 1)

{

MsgHandler.Log("***********************************************************************");

MsgHandler.Log("Potential Problem: There are multiple Users with the same email address");

MsgHandler.Log("***********************************************************************");

}

UserQuery.SelectSQL();

}

// Use this function to alter any data or to do any custom work before the main action gets called.

function BeforeMainAction()

{

MsgHandler.Log("Before Main Action : Start");

//CheckForMultipleUsers();

//if (!UserQuery.EOF)

//{

SetCommAction();

if (CommAction == "EmailIn")

{

MsgHandler.Log("Its an emailin type");

//EmailInAddress = ParseForEmailAddress(eMail.Body);

var EmailInAdress = eMail.SenderAddress;

MsgHandler.Log("--------> EmailInAddress is " + eMail.SenderAddress);

GetDetails(eMail.SenderAddress);

}

else

{

MsgHandler.Log("Its an emailOut type");

BuildIdArrays();

}

CreateComm();

//}

//else

//{

// MsgHandler.Log("No Users exist for this from email address");

//}

}

// Use this function to alter any data or to do any custom work like create a log record or send an email for example.

function AfterMainAction()

{

}

// Useful functions

function Defined(Arg)

{

return (Arg+""!="undefined");

}

// Takes a javascript date object as its parameter

function getsqldatetimefromdate(dtdate)

{

var d = dtdate.getDate() + "";

var m = (dtdate.getMonth() + 1) + "";

if (d.length == 1)

d = "0" + d;

if (m.length == 1)

m = "0" + m;

return m + "/" + d + "/" + dtdate.getFullYear() + " " + dtdate.getHours() + ":" + dtdate.getMinutes() + ":01";

}

function replaceInlineImages(emailBody) {

var regularExpressionPattern = /src="cid:\w+?\.\w+?@.+?"/g;

var embeddedImages = emailBody.match(regularExpressionPattern);

if (embeddedImages != null) {

for (i = 0; i < embeddedImages.length; i++) {

var imageName = embeddedImages[i].replace(/src="cid:/,'src="').replace(/@.+?"/,'"');

emailBody = emailBody.replace(embeddedImages[i],imageName);

}

return emailBody;

}

// Create a communication

function CreateComm()

{

if (Defined(comm))

return;

MsgHandler.Log("About to create a communication");

var ToList, CCList, BCCList;

//communication

comm = eWare.CreateRecord("communication");

comm("Comm_Action") = CommAction;

comm("Comm_Type") = "email";

comm("Comm_Status") = "Complete"

//priority

if (eMail.Priority == prLow)

{

comm("Comm_Priority") = "Low";

}

else

if (eMail.Priority == prNormal)

{

comm("Comm_Priority") = "Normal";

}

else

if (eMail.Priority == prHigh)

{

comm("Comm_Priority") = "High";

}

// comm_datetime

commdate = new Date(eMail.DeliveryTime);

comm("Comm_datetime") = commdate.getVarDate();

comm("Comm_Subject") = eMail.Subject;

comm("Comm_Email") = replaceInlineImages(eMail.Body);

if (iLeadID != 0)

comm("Comm_LeadID") = iLeadID;

if ((CommAction == "EmailIn") & (eMail.SenderAddress != ""))

{

comm("comm_from") = "<" + eMail.SenderAddress + "> ";

}

else

{

if (eMail.SenderName != "")

{

comm("comm_from") = "\"" + eMail.SenderName + "\" " + "<" + eMail.SenderAddress + "> ";

}

else

{

comm("comm_from") = eMail.SenderAddress;

}

ToList = "";

CCList = "";

BCCList = "";

var singleaddress;

var useraddress = MsgHandler.EmailAddress;

if ((CommAction == "EmailIn") & (eMail.SenderAddress != ""))

{

comm("comm_to") = "\"" + eMail.Recipients.Items(0).Name + "\" " + "<" + eMail.Recipients.Items(0).Address + "> ";

}

else

{

comm("comm_to") = GetMailList(eMail.Recipients);

}

comm("comm_cc") = GetMailList(eMail.CC);

comm("comm_bcc") = GetMailList(eMail.BCC);

comm("comm_replyto") = eMail.Header("Reply-To");

comm("Comm_IsHtml") = "Y";

SetUserTerr(comm);

comm.SaveChanges();

bRecCreated = false;

// comm_links

// person info

if (pers_array.length >= 1)

{

MsgHandler.Log("Person Info");

numrecips = pers_array.length;

i = 0;

while (i < numrecips)

{

commlink = eWare.CreateRecord("comm_link");

commlink("CmLi_Comm_CommunicationId") = comm.RecordId;

if (!UserQuery.EOF)

commlink("CmLi_Comm_UserId") = UserQuery("User_UserId");

commlink("CmLi_Comm_PersonId") = pers_array[i];

MsgHandler.Log("Pers ID: " + pers_array[i]);

//if There isn't a relationship from the email table, look up the person_link table.

if ((perscomp_array[i] == "") || (perscomp_array[i] == null))

{

MsgHandler.Log("Comp ID is either null or blank: " + perscomp_array[i]);

commlink("CmLi_Comm_CompanyId") = GetRelatedCompany(pers_array[i]);

}

else

{

MsgHandler.Log("Comp ID is: " + perscomp_array[i]);

commlink("CmLi_Comm_CompanyId") = perscomp_array[i];

}

//if There isn't a relationship from the email table, look up the person table.

if ((persAcc_array[i] == "") || (persAcc_array[i] == null))

{

MsgHandler.Log("Account ID is either null or blank: " + persAcc_array[i]);

commlink("CmLi_Comm_AccountId") = GetRelatedAccount(pers_array[i], 1);

}

else

{

MsgHandler.Log("Account ID : " + persAcc_array[i]);

commlink("CmLi_Comm_AccountId") = persAcc_array[i];

}

commlink.SaveChanges();

bRecCreated = true;

i = i + 1;

}

// company info

if (comp_array.length >= 1)

{

MsgHandler.Log("Company Info");

numcomps = comp_array.length;

i = 0;

while (i < numcomps)

{

commlink = eWare.CreateRecord("comm_link");

commlink("CmLi_Comm_CommunicationId") = comm.RecordId;

if (!UserQuery.EOF)

commlink("CmLi_Comm_UserId") = UserQuery("User_UserId");

commlink("CmLi_Comm_CompanyId") = comp_array[i];

commlink("CmLi_Comm_AccountId") = compAcc_array[i];

commlink.SaveChanges();

bRecCreated=true;

i = i + 1;

}

// Account info

if (Acc_array.length >= 1)

{

MsgHandler.Log("Account Info");

numAccs = Acc_array.length;

i = 0;

while (i < numAccs)

{

commlink = eWare.CreateRecord("comm_link");

commlink("CmLi_Comm_CommunicationId") = comm.RecordId;

if (!UserQuery.EOF)

commlink("CmLi_Comm_UserId") = UserQuery("User_UserId");

commlink("CmLi_Comm_AccountId") = Acc_array[i];

commlink("CmLi_Comm_CompanyId") = GetRelatedAccount(Acc_array[i], 2);

commlink.SaveChanges();

bRecCreated=true;

i = i + 1;

}

if ((bRecCreated == false) & (!(UserQuery.EOF)))

{

//user info

commlink = eWare.CreateRecord("comm_link");

commlink("CmLi_Comm_CommunicationId") = comm.RecordId;

commlink("CmLi_Comm_UserId") = UserQuery("User_UserId");

commlink.SaveChanges();

bRecCreated=true;

} else if (bRecCreated == false) {

MsgHandler.Log("Communication is not linked. Sender not found in CRM!");

}

MsgHandler.Log("Communication created");

var Attachments = eMail.Attachments;

var subfolder = new String(comm.RecordId);

subfolder = "Comm" + subfolder;

var Userdir;

if (!(UserQuery.EOF))

Userdir = UserQuery("User_firstname") + " " + UserQuery("User_lastname") + "\\" + (subfolder);

else

Userdir = eMail.SenderName + "\\" + (subfolder);

var libdir = Attachments.LibraryPath + "\\" + Userdir;

var AttItem;

var bHasAttachments;

bHasAttachments = false;

// library records...attachments

for (i = 0; i < Attachments.Count; i++)

{

// saving the attachment here

AttItem = Attachments.Items(i);

if (AttItem.Name != "_alt.0")

{

// getting a new name for it

NewName = MsgHandler.GetUniqueFileName(libdir, AttItem.Name);

// saving to the library...

AttItem.SaveAs(NewName, libdir);

librec = eWare.CreateRecord("library");

librec("libr_FileName") = NewName;

librec("libr_CommunicationId") = comm.RecordId;

if (!(UserQuery.EOF))

librec("libr_Userid") = UserQuery("User_UserId");

librec("libr_type") = "EmailAttachment";

librec("libr_status") = "Complete";

librec("Libr_FilePath") = Userdir;

librec.SaveChanges();

bHasAttachments = true;

}

MsgHandler.Log("Case tracker start");

var mycase

var BIsValid

// Try to get the case number

mycase = ParseForCaseRef(eMail.Subject);

// Check if the email subject contains a case reference number

if (mycase!="")

{

// If it does then check if it is a valid case reference

if (IsValidCase(mycase))

{

bIsValid = true;

}

else

{

bIsValid = false;

}

else

{

bIsValid = false;

}

MsgHandler.Log("Case tracker status: " + bIsValid);

if (bIsValid)

{

caseid = CaseQuery("Case_CaseId");

if(PersInfo == 1)

{

CasePersonId = CaseQuery("Case_PrimaryPersonId");

commlink("CmLi_Comm_PersonId") = CasePersonId;

}

CaseCompanyId = CaseQuery("Case_PrimaryCompanyId");

CaseAccountId = CaseQuery("Case_PrimaryAccountId");

AssignedUser = CaseQuery("Case_AssignedUserId");

AssignedChannel = CaseQuery("Case_ChannelId");

territoryid = CaseQuery("Case_SecTerr");

commlink("CmLi_Comm_AccountId") = CaseAccountId;

commlink("CmLi_Comm_CompanyId") = CaseCompanyId;

commlink.SaveChanges();

comm("comm_caseid")=caseid;

var Betreff = eMail.Subject;

var ReplaceString = ParseForCaseRef(Betreff);

var NeuerBetreff = Betreff.replace(ReplaceString, "");

comm("Comm_Subject") = NeuerBetreff;

comm.SaveChanges();

}

if (bHasAttachments == true)

{

comm("Comm_HasAttachments") = "Y";

comm.SaveChanges();

}

//SendConfirmationReply();

}

function IsValidCase(value)

{

CaseSQL = "SELECT * FROM vCases WHERE Case_ReferenceID = '" + value + "'";

CaseQuery = eWare.CreateQueryObj(CaseSQL);

CaseQuery.SelectSQL(); // open the query

if (CaseQuery.EOF)

{

return(false);

}

else

{

return(true);

}

function ParseForCaseRef(value)

{

var r, re, singlestr; //Declare variables.

// Create my test string

var s = value;

// Create regular expression pattern. Used for getting the case reference

re = /[\d]+-[a-z_0-9]{1,20}/i;

re.Global = true;

re.IgnoreCase = true;

re.MultiLine = true;

singlestr = s.match(re);

if (singlestr == null)

{

singlestr = "";

}

return(singlestr); // Return the first case ref

}

function SetTerr(comm)

{

// reset the queries.

PersonQuery.SelectSql();

CompanyQuery.SelectSql();

if (!PersonQuery.EOF)

{

if (PersonQuery("pers_secterr") != null)

comm("comm_secterr") = PersonQuery("pers_secterr");

}

else

if (!CompanyQuery.EOF)

{

if (CompanyQuery("comp_secterr") != null)

comm("comm_secterr") = CompanyQuery("comp_secterr");

}

function SetUserTerr(comm)

{

if (!UserQuery.EOF)

{

if (UserQuery("User_PrimaryTerritory") != null)

comm("comm_secterr") = UserQuery("User_PrimaryTerritory");

}

// This function finds the first email address and returns the email address.

function ParseForEmailAddress(value)

{

var r, re, singlestr; //Declare variables.

//create my test string

var s = value;

//Create regular expression pattern. used for testing whether a string is a valid email address

re = /[a-z][a-z_0-9_\-\'\.]+@[a-z_0-9_\-_'\.]+\.[a-z]{2,6}[^0-9]{1}/i;

re.Global = true;

re.IgnoreCase = true;

re.MultiLine = true;

singlestr = s.match(re);

if ((singlestr!="") && (singlestr!=null)) {

singlestr = "a"+singlestr; // convert to string

singlestr = singlestr.substr(1, singlestr.length-2); //get email address only

}

return(singlestr); //Return the first email address that we find.

}

function SuffixFileName(Name, Ext)

{

var newname = Name.substr(0, 4); //Get substring

var ndt = new Date();

newname += ndt.getHours() + "." + ndt.getMinutes() + " " + ndt.getDay() + "-" +

(ndt.getMonth() + 1) + "-" + ndt.getYear() + " " + ndt.getHours() +":" +

ndt.getMinutes() + ":" + ndt.getSeconds() + "." + ndt.getMilliseconds() + Ext;

return(newname);

}

// This function expects either "eMail.Recipients" or "eMail.CC" or "eMail.BCC" as a parameter.

// it returns a string containing the email addresses and friendly names of the respective list.

function GetMailList(mylist)

{

var ToList;

ToList="";

for (i = 0; i < (mylist.Count); i++)

{

if (i != 0)

ToList = ToList + "; ";

if (mylist.Items(i).Name != "")

ToList = ToList + "\"" + mylist.Items(i).Name + "\" <" + mylist.Items(i).Address + "> ";

else

ToList = ToList + mylist.Items(i).Address;

}

return(ToList);

}

function prepareConfirmationMail()

{

var sReplyName = eMail.SenderName;

var sReplyAddress = eMail.SenderAddress;

eMail.Clear();

eMail.Recipients.AddAddress(sReplyAddress, sReplyName);

// Add in the system notify address

SQL = "SELECT Parm_Value FROM Custom_SysParams WHERE LOWER(RTRIM(Parm_Name)) = N'notifyemailaddress'";

recQuery = eWare.CreateQueryObj(SQL);

MsgHandler.Log(SQL);

recQuery.SelectSQL();

if (!recQuery.EOF)

{

eMail.SenderAddress = recQuery("Parm_Value");

}

if (eMail.SenderAddress == "")

{

MsgHandler.Log("Notify E-mail Address is not set.");

}

SQL = "SELECT Parm_Value FROM Custom_SysParams WHERE LOWER(RTRIM(Parm_Name)) = N'notifyemailname'";

recQuery = eWare.CreateQueryObj(SQL);

MsgHandler.Log(SQL);

recQuery.SelectSQL();

if (!recQuery.EOF)

{

eMail.SenderName = recQuery("Parm_Value");

}

if (eMail.SenderAddress == "")

{

MsgHandler.Log("Notify E-mail Name is not set.");

}

function SendConfirmationReply()

{

if (MsgHandler.FeedbackOnSuccess)

{

var sSubject = eMail.Subject.substring(0, 39);

prepareConfirmationMail();

eMail.Subject = "Email filed -- " + sSubject;

eMail.Body = ".";

if (!(eMail.SenderAddress == "") && !(eMail.Recipients.Count == 0))

eMail.Send();

}

function SendFailureReply()

{

if (MsgHandler.FeedbackOnFailure)

{

var sSubject = eMail.Subject.substring(0, 39);

prepareConfirmationMail();

eMail.Subject = "Filing failed -- " + sSubject;

eMail.Body = "There was no matching recipients to file under";

if (!(eMail.SenderAddress == "") && !(eMail.Recipients.Count == 0))

eMail.Send();

}

function GetRelatedCompany(PersonID)

{

MsgHandler.Log("Look for a relationship between a Person and a company.");

SQL = "SELECT DISTINCT peli_CompanyID from person_link WHERE peli_personID = "+PersonID;

recQuery = eWare.CreateQueryObj(SQL);

recQuery.SelectSQL();

if (!recQuery.EOF)

{

MsgHandler.Log("Company Found!");

return(recQuery("peli_CompanyID"));

}

function DoAccountsExist()

{

MsgHandler.Log("Check to see if Accounts exist for this CRM installation");

SQL = "SELECT Parm_Value FROM Custom_SysParams WHERE LOWER(RTRIM(Parm_Name)) = N'useaccounts'";

recQuery = eWare.CreateQueryObj(SQL);

recQuery.SelectSQL();

if (!recQuery.EOF)

{

str = recQuery("Parm_Value");

}

return (str);

}

//BMcA

function GetRelatedAccount(ID, x)

{

if (x == 1)

{

SQL = "SELECT DISTINCT pers_AccountID from person WHERE pers_personID = "+ID;

MsgHandler.Log("Look for Relationship between Person and Account: " + SQL);

recQuery = eWare.CreateQueryObj(SQL);

recQuery.SelectSQL();

if (!recQuery.EOF)

{

str = recQuery("pers_AccountID");

MsgHandler.Log("Account: " + str);

} else {

str = null;

}

return (str);

}

if (x == 2)

{

SQL = "SELECT DISTINCT Acc_CompanyID from vAccount WHERE Acc_AccountID = "+ID;

MsgHandler.Log("Look for a relationship between a Company and an account "+ SQL);

recQuery = eWare.CreateQueryObj(SQL);

recQuery.SelectSQL();

if (!recQuery.EOF)

{

str = recQuery("Acc_CompanyID");

} else {

str = null;

}

return (str);

}

Werbung

Nun muss man dem E-Mail-Manager lediglich noch mitteilen, welches Postfach er zu überwachen hat, und welches Skript für die Verarbeitung der dort vorhandenen E-Mails genutzt werden soll (Administration -> E-Mail und Dokumente -> E-Mail-Verwaltungsserveroptionen -> Neu).
Das verdeutlicht der folgende Screenshot:

Wichtig ist hier u. a., dass bei “Vorlage” das soeben angelegte Skript ausgewählt wird. Den Hinweis in meinem vorletzten Blog-Beitrag sollte man evtl. auch noch beachten.

Sofern nun der E-Mail-Manager-Dienst aktiviert ist und E-Mails im überwachten Postfach vorhanden sind, dürften die ersten Nachrichten in CRM eintrudeln.
Ach so: Unter Administration -> E-Mail und Dokumente -> Erweiterte E-Mail-Verwaltungsserveroptionen lässt sich übrigens noch der Abfrageintervall definieren.

Einen hab ich noch:
Bei Nutzung von Outlook 2013 kann man sich für das Kopieren / Verschieben der E-Mails in das überwachte Postfach auch noch einen sog. QuickStep anlegen. Damit kann man mit nur einem Klick die E-Mails in CRM archivieren lassen.
Hier ein Bildchen:

Wenn das mal nicht komfortabel ist!
Getestet habe ich das Ganze übrigens mit Sage CRM 7.2a.1 und 7.2b, dürfte aber durchaus auch mit den Vorgängerversionen funktionieren.

Viel Spaß beim “Ablegen”!

E-Mail-Archivierung in Sage CRM mit dem E-Mail-Manager

Sie haben das letzte Wort! Antworten abbrechen

Mehr in CRM

Sage CRM E-Mail-Manager: Skriptverarbeitung startet nicht

Fallstricke und Fehler beim Anlegen eines neuen Kontos oder Postfaches im Sage CRM E-Mail-Verwaltungsserver