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:
http://www.awareim.com/forum/viewtopic.php?f=4&t=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:

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

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 [email protected] 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 [email protected] 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:

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

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?