Longwinded mail system

Mark HHP

So I am relooking at my mail system as it's majorly cumbersome.

At the moment, I have one catch-all mail account. All of the consultants and clients mail gets copied through this account. So if a consultant sends a mail, copy goes through. If they receive mail from client, copy goes through.

I've set a rule on the IncomingMail notification for the following:

FIND Contact WHERE IncomingEmail.FromAddress CONTAINS Contact.EmailAddress
CREATE ReceivedEmail WITH ReceivedEmail.Contact=ThisContact,
ReceivedEmail.Subject=IncomingEmail.Subject,
ReceivedEmail.Message=IncomingEmail.Message,
ReceivedEmail.SentOn=IncomingEmail.SentDate,
ReceivedEmail.ToAddress=IncomingEmail.ToAddress,
ReceivedEmail.FromAddress=IncomingEmail.FromAddress,
ReceivedEmail.Type='Incoming',
ReceivedEmail.CC=IncomingEmail.CC,
ReceivedEmail.BCC=IncomingEmail.BCC
INSERT ReceivedEmail IN Contact.Emails

Firstly, is this the best way? And if so, could I then reverse for outgoing?

FIND Contact WHERE IncomingEmail.ToAddress CONTAINS Contact.EmailAddress
CREATE ReceivedEmail WITH ReceivedEmail.Contact=ThisContact,
ReceivedEmail.Subject=IncomingEmail.Subject,
ReceivedEmail.Message=IncomingEmail.Message,
ReceivedEmail.SentOn=IncomingEmail.SentDate,
ReceivedEmail.ToAddress=IncomingEmail.ToAddress,
ReceivedEmail.FromAddress=IncomingEmail.FromAddress,
ReceivedEmail.Type='Outgoing',
ReceivedEmail.CC=IncomingEmail.CC,
ReceivedEmail.BCC=IncomingEmail.BCC
INSERT ReceivedEmail IN Contact.Emails

I really don't know if I'm going about this the right way.

hpl123

Mark,
This seems to be the best working solution at the moment and email handling for multiple tenants in Aware IM could be a lot better.

Regarding using catchall for incoming email (multiple tenants), what is your experience using this solution? Does it work reliably? What volume do you get? etc. I have thought of using it but not dared thinking this type of solution might not be reliable enough for a live production system (SaaS app). Thanks

Mark HHP

Hi Henrik,

In my first pass, I left in the attachments in the rule, and I think it's saved all of the attachments now. The database for mails is massive, almost 10Gb! Trying to view any mails slows the system to a crawl, sometimes shutting it down completely.

hpl123

Thanks for the info Mark,
Not sure your problem is due to the catchall email though? Many users use email extensively and don´t run into these problems. Try looking at optimizing your DB tables to see if that works:
https://forum.awareim.com/d/7368>

Mark HHP

I'm experiencing a problem in that if I send email from my gmail (the "Client") to my work account (the "Contact") and cc the catchall account, that gets pulled into the system. But if I email from Contact to Client, I receive the mail in my gmail but it doesn't pull into system?

The code I'm using is:

Then for outgoing:

FIND Contact WHERE IncomingEmail.ToAddress CONTAINS Contact.EmailAddress
CREATE ReceivedEmail WITH ReceivedEmail.Contact=ThisContact,
ReceivedEmail.Subject=IncomingEmail.Subject,
ReceivedEmail.Message=IncomingEmail.Message,
ReceivedEmail.SentOn=IncomingEmail.SentDate,
ReceivedEmail.ToAddress=IncomingEmail.ToAddress,
ReceivedEmail.FromAddress=IncomingEmail.FromAddress,
ReceivedEmail.Type='Outgoing',
ReceivedEmail.CC=IncomingEmail.CC,
ReceivedEmail.BCC=IncomingEmail.BCC
INSERT ReceivedEmail IN Contact.Emails

Mark HHP

Anyone have any ideas on this? It's driving me mad. The 'Incoming' works fine but the 'Outgoing' doesn't. The mails are definitely hitting server but from there, they just disappear.

RentProperty

Hi Mark, I'll post a summery of how we handle email a.s.a.p. It's quite comprehensive so might not be today but will do as soon as i get a moment.

Mark HHP

You, sir, are a gentleman and a scholar.

I might owe you a beer or 5 if this works!

RentProperty

Our Approach to email handling email in one of our Multi Client SaaS products.

Ok, so to start off with... We have the following Objects

Company (Top Level)
ApplicationUsers
EmailAddress
ParsedEmailAddress
OutgoingEmail
ReceivedEmail

Relationships Are As Follows:

Company --0< ApplicationUsers
Company --0< EmailAddresses
Company --0< OutgoingEmails
Company --0< ReceivedEmails

ReceivedEmail |--<0 ParsedEmailAddresses

So Basically, we setup rules on ApplicationUser that register a new email address
and link it to the company of that administrator. If he chooses an email that
already belongs to another company, we report error and he must choose another.
Thus an ApplicationUser can Choose any emailaddress already linked to His company,
or if he selects a new one not already xisting and being used by another company then
we register that emailaddress and link it to his company.

We also have a regular expression on the ApplicationUser.EmailAddress Field making
sure that He can ONLY select an email address for the domain that we have chosen.
For example '@mysaasproduct.net'. So the email a customer select might be something
like mycompany123@mysaasproduct.net and all emails he sends from the system will then
have that as the FROM address in the outgoing email.

Ok so for sending emails it is then very simple... Wherever you do a ENTER NEW OutgoingEmail
you just do a WITH OutgoingEmail.FromAddress=LoggedInApplicationUser.EmailAddress.

Then, same as you we have setup a Catchall email box for our domain. So all email
replies will then go to that email box which gets polled from AwareIM.

So when a new Email Notification Comes In We Create a ReceivedEmail Record as you would normally.
However we have an attribute on ReceivedEmail Called 'StringToParse'. So Whenever a new
Received Email Is Registered We Do:

// If ReceivedEmail IS NEW Then 
// ReceivedEmail.StringToParse=ReceivedEmail.ToAddress+' '+ReceivedEmail.CC+' '+ReceivedEmail.BCC

Then we have another Rule:

// If ReceivedEmail IS NEW  AND ReceivedEmail.StringToParse WAS CHANGED Then 
// ParseEmailsFromReceivedMail

'ParseEmailsFromReceivedMail' is a Custom Process Which my brother wrote for us in Java which basically
takes the StringToParse Attribute, and creates ParsedEmailAdress Objects for each email address in that
string and then links those ParsedEmailAddresses to the ReceivedEmail.

Then on the ParsedEmailAddress We have the Following Rules to see if any of the parsed emails already
Exisit in the system as EmailAddresses Registered by users. It there is then we find the company that
Email Address Belongs to and make the link:

// If ParsedEmailAddresses IS NEW  AND EXISTS EmailAddress WHERE 
(EmailAddress.EmailAddress=ParsedEmailAddresses.EmailAddress) Then 
// FIND ReceivedEmail WHERE ParsedEmailAddresses.ReceivedEmail=ReceivedEmail 
// FIND EmailAddress WHERE EmailAddress.EmailAddress=ParsedEmailAddresses.EmailAddress 
// FIND Company WHERE EmailAddress IN Company.EmailAddresses 
// INSERT ReceivedEmail IN Company.ReceivedEmail

And that is that. 🙂

I Must say though that we do have hick ups here and there with the email Poller That stops so we have to
monitor that on a daily basis, but I have set it up now as a Process to connect to email Account and not
on system Settings. This way if it does stop I can manually disconnect and reconnect without bringing
the server down.

Also I find that if someone Sends email to the CatchAll@mysaasproduct.net box with a lot of attachments
AwareIM does not process that and it gets left on the server. So ideally you should tell your clients
to tell their clients not to send 10 attachments in 1 Email. 🙂 Or Support should maybe investigate
and optimise this process a bit further.

We Use Rackspace for the hosting of our Catchall email account.

Hope that helps and can maybe inspire some Aware Users who are struggling with the emails. I know it took
us Darn long to get it working the way it does now. But I'm pretty happy with how it works at the end of the day.

The only thing I would recommend from day one is to do all your Attachments for outgoing and incoming in a
File System and not the Data Base. I am currently working on a process to Export all the existing attachments
and then switch over as I can see those tables are growing VERY quickly so one would definitely not want to
store all the attachments as blobs if you are going to have a lot of incoming and outgoing emails.

Kindest Regards & I Hope this helps some peeps!

Hein

Mark HHP

Ok, that is quite in depth, thank you!

The problem is, our system is a lot simpler, yet seems a lot harder. The way our system is set up is this:

We have Consultants and Contacts (the clients) and a ReceivedEmail BO.
All mail in and out gets BCC'd to our catchall account. In testing, my Gmail = Contact and my work email = Consultant. So if I email from my Gmail and bcc the account, a ReceivedEmail gets created with ReceivedEmail.Direction set to 'Incoming'

This code:

That works a treat. It creates the ReceivedEmail object and inserts it into the Contacts emails.

Now, theoretically, I should be able to reverse it for 'Outgoing' (bearing in mind, no mail gets sent from our system. All mail is handled on Consultants Outlooks, these are just copies)

BUT YET, if I use the following code, nothing happens.

FIND Contact WHERE IncomingEmail.ToAddress CONTAINS Contact.EmailAddress
CREATE ReceivedEmail WITH ReceivedEmail.Contact=ThisContact,
ReceivedEmail.Subject=IncomingEmail.Subject,
ReceivedEmail.Message=IncomingEmail.Message,
ReceivedEmail.SentOn=IncomingEmail.SentDate,
ReceivedEmail.ToAddress=IncomingEmail.ToAddress,
ReceivedEmail.FromAddress=IncomingEmail.FromAddress,
ReceivedEmail.Type='Outgoing',
ReceivedEmail.CC=IncomingEmail.CC,
ReceivedEmail.BCC=IncomingEmail.BCC
INSERT ReceivedEmail IN Contact.Emails

I don't understand why or where it's going wrong. I am rebuilding the system now so maybe it's a good time to look at the way I do it but I figured this should work for just splitting mail into 'Incoming' and 'Outgoing'?

Also, I made the attachment mistake 🙁 Our email database is huge and I have no idea how to fix it. For reference, I am a frontend guy mostly. I put my hand up to learn the system...

RentProperty

Hi Mark

Shouldn't that last part be:

FIND Contact WHERE IncomingEmail.FromAddress CONTAINS Contact.EmailAddress
CREATE ReceivedEmail WITH ReceivedEmail.Contact=ThisContact,
ReceivedEmail.Subject=IncomingEmail.Subject,
ReceivedEmail.Message=IncomingEmail.Message,
ReceivedEmail.SentOn=IncomingEmail.SentDate,
ReceivedEmail.ToAddress=IncomingEmail.ToAddress,
ReceivedEmail.FromAddress=IncomingEmail.FromAddress,
ReceivedEmail.Type='Outgoing',
ReceivedEmail.CC=IncomingEmail.CC,
ReceivedEmail.BCC=IncomingEmail.BCC
INSERT ReceivedEmail IN Contact.Emails

Because if you are Sending the email from your gmail then the ContactEmail will be the one in the FROM address as it is being sent from and not received to that address? Not sure but maybe give that a shot. 🙂

Regards

Hein

Mark HHP

Hi Hein,

I thought that might've been my problem but that's the same code as the first one that works. I just don't understand it. It's definitely coming in with a to address and from address. From Gmail works fine.

OK, FFFFFFUUUUUUUUUUU

I just sent from Gmail to Gmail (to me from me) and it created 2. One for incoming and one for outgoing. Which is right, I guess (will have to look at that duplication)

Which means, it's a problem on our internal mail. Thanks for your time helping with this ridiculous problem.

Thanks,
Mark

RentProperty

Eish... No Problem man.

Hope you get it sorted.

Kind regards

Hein

Mark HHP

And suddenly, it works. I dunno...

PS: If you come right with exporting and clearing the attachments, please let me know. Mine desperately needs a clean. I'm running on MySQL using phpmyadmin. But I'm not proficient enough with database stuff to go dropping tables etc.

Mark HHP

OK guys. I'm about to commit harakiri if this continues.

Is is right that I'm running both rules through the IncomingEmail notification? I thought perhaps the order was important, like running the one meant it stopped before running the other, but reversing the order of the rules made no difference.

It's definitely a problem with the "outgoing" mail. Unless my email is doing something to the address to render it unreadable but that doesn't seem likely.

Does anyone else use mail the same way as me, one catchall account that gets split by crm into sent and received messages? Or have a better way to do this?

Mark HHP

Ok, I think I've figured out WHY I'm not receiving them but I can't figure out how to fix it.

SO:

If I email from gmail (Contact) to my work account (Consultant) it works. I receive an email on server and CRM produces a mail marked 'Incoming'.

If I email from work account to gmail, it doesn't work. (Using Macs default Mail program)

BUT if I email from another work account to gmail, the CRM produces a mail marked 'Outgoing'.

This leads me to believe that because my gmail account is saved on my work machine, it is adding other stuff to the address, which is confusing the CRM. When I emailed from the other account, the ToAddress showed as xxx@gmail.com, no extra names or anything. I thought that using FIND Contact WHERE IncomingEmail.ToAddress CONTAINS Contact.EmailAddress would be sufficient but if the mail program is amending the mail addresses then that's going to cause problems as the consultants have the contacts saved.

Any ideas?

ninap

hi - not that i want to dig this up but I have been working for 3 days on the email system..

try this;

get the properties of that email including the header and other relevant information and look for the sender name, receiver name in the details which are hidden by Email clients.

many bits there can give you clues to what maybe happening.

Mark HHP

Hi,

So where would you do that? And what would you do with the result?

Jaymer

Hein/Mark HHP wrote
FIND Contact WHERE IncomingEmail.FromAddress CONTAINS Contact.EmailAddress
CREATE ReceivedEmail WITH ReceivedEmail.Contact=ThisContact,
ReceivedEmail.Subject=IncomingEmail.Subject,
ReceivedEmail.Message=IncomingEmail.Message,
ReceivedEmail.SentOn=IncomingEmail.SentDate,
ReceivedEmail.ToAddress=IncomingEmail.ToAddress,
ReceivedEmail.FromAddress=IncomingEmail.FromAddress,
ReceivedEmail.Type='Incoming',
ReceivedEmail.CC=IncomingEmail.CC,
ReceivedEmail.BCC=IncomingEmail.BCC
INSERT ReceivedEmail IN Contact.Emails

guys
aside from the intricacies you were having in this thread, about gmail acct, etc., HOW do you handle attachments?
In the above code, you didn't reference attachments.
If I'm storing it in the file system, then I can init the Path (which the Document attrib uses) to put it in the FS.
But HOW do I get it out of the email and into the FS?
IMPORT/EXPORT Document commands?
Could someone explain a little how you have done this?
thx