Sending Bulk Email - WAS Impact

[azz211]

Okay. I have been searching through all of the threads on sending bulk email. Bulk to me is in the area of 5,000 to 10,000 emails a day. I understand there are 3rd party bulk email applications or smtp relay approaches like SendGrid. What I am not understanding is how to hand off the email process to one of these third party apps in a way that will not kill the WAS.

Here is the path that I have started down. First I have created an email queue table such that when a user sends an email or emails it is actually inserted into the queue table waiting to be handed off to another process. Currently, I have a field call WorkData that accepts an xbasic script that contains all of the information necessary to send the email (SMTP, password, username, SMTP port, from, etc.). This is all working fine. Now what I am having trouble wrapping my head around is how to hand off the work to another process without killing the WAS.

I was thinking or perhaps hoping that one of these third party apps would have a way to basically set up some type of cron job to go in get the emails out of the queue table then start sending them off but that doesn't seem to be the case. Of course the third party app would have to read xbasic which it won't.

Then I am thinking what about a second A5 app whose only function is to run a script from a cron job (every 60-minutes) where it processes the email queue and sends emails using an SMTP relay like SendGrid thereby freeing up the WAS on the main app. Basically what its coming down to is that some process still has to get the emails to a third party bulk email application and I am not sure of the best way to do this or even if I am on the correct path. Does it make sense to have a dedicated WAS that just processes emails using a relay? Or am I completely missing something about the best way to accomplish this task? An insight would be much appreciated.

[Steve Wood]

Re: Sending Bulk Email - WAS Impact

Not an email expert and some of this is off the cuff but -- I send 600 emails seven days a week around 11PM using a CRON that kicks off the process. It fires each email, and if email_send2() returns failed, it tries up to 5 more times before giving up. It sends all of them in pretty short order, although I have not timed it lately. They are all text based emails, but pretty large.

If you sent the email job to a 3rd party process, it would not kill the WAS because the WAS would not be doing anything but sending the email payload to the processor. Although, I dont know if you mean you sent 5-10000 all at once, or in small amounts during the day. My guess is you identify a 3rd party processor and then ask them how you send your email request to them. The email body would need to go from your server to their server for processing, I don't see how any 3rd party email company (if that is what you meant) is going to run the email from your server. You could also install a dedicated email package like Mail Enable on your server, but that seems redundant if you can just send them from Alpha.

If your emails are in a queue table, you could (as you suggest) fire up a second instance of the WAS and let it be the one that sends the email, targeting the database on the "other" WAS. I put that in quotes, because database tables are really accessible from any WAS instance.

[cptutrecht]

Re: Sending Bulk Email - WAS Impact

Below our procedure for sending email through the was at a user defined timeslot. All messages are in an table messages to sent. Messages can be html or plain messages, That needs the provision for special characters. No doubt this code can be optimized. But at it is running flawless at customers production side.

function StartMailProcess as c (e as p)

'connection and variables

Dim cn as SQl::Connection
Dim args as SQL::Arguments
Dim sqlCommand as c
dim rs as sql::ResultSet
dim pm as p

dim strvar as c
dim ss as C
dim res as C
dim ts as C
ss = "�" + crlf() + "�" + crlf() + "�" + crlf() + "€" + crlf() + "�" + crlf()
ss = ss + "�" + crlf() + "�" + crlf() + "�" + crlf() + "�" + crlf() + "�" + crlf()
ss = ss + "�" + crlf() + "�" + crlf() + "�" + crlf() + "�" + crlf()
ss = ss + "�" + crlf() + "�" + crlf() + "�" + crlf() + "�" + crlf()
ss = ss + "�" + crlf() + "�" + crlf() + "�" + crlf() + "�" + crlf()
ss = ss + "�" + crlf() + "�" + crlf() + "�" + crlf() + "�" + crlf()

res = "é" + crlf() + "á" + crlf() + "´" + crlf() + "€" + crlf() + "Ç" + crlf()
res = res + "Ä" + crlf() + "È" + crlf() + "É" + crlf()+ "Ë" + crlf() + "ç" + crlf()
res = res + "Ï" + crlf() + "Ö" + crlf() + "Ü" + crlf()+ "ß" + crlf()
res = res + "à" + crlf() + "â" + crlf() + "ã" + crlf()+ "ä" + crlf()
res = res + "è" + crlf() + "ê" + crlf() + "ë" + crlf()+ "ï" + crlf()
res = res + "ö" + crlf() + "ü" + crlf() + "ø" + crlf()+ "ú" + crlf()

dim MailProcessActive as c
dim MailProcessInterval as N

flag = cn.open(Session.__protected__ConnectionString)


'create record in sysinportal if not already present

sqlCommand = "Select * from sysinportal"

flag = cn.execute(sqlCommand)
rs = cn.ResultSet

flag = rs.NextRow()

if flag then

'Set status mailproces active unless stop command is given, and get interval

MailProcessActive = rs.data("SYSINPORTALMAILENACTIEF")

if isnull(mailprocessactive) then
MailProcessActive = "X"

sqlCommand = "Update sysinportal SET SysinPortalMailenActief = 'X',DatumTijdMutatie = now(),MutatieDoor = 'Cptportal StopMailProcess'"

cn.execute(sqlCommand)

else
if MailProcessActive == "X" then

'do nothing if the mailprocess is already active

StartMailProcess = "alert('Mailproces is al actief');javascript:location.reload(true);"


else

'if status mailprocess is "S" (to be stopped) set to null

MailProcessActive = ""

sqlCommand = "Update sysinportal SET SysinPortalMailenActief = null,DatumTijdMutatie = now(),MutatieDoor = 'Cptportal StartMailProcess'"

cn.execute(sqlCommand)

StartMailProcess = "alert('Mailproces wordt gestopt');javascript:location.reload(true);"



end if


end if

MailProcessInterval = int(rs.data("SYSINPORTALMAILENWACHTTIJD"))


else

'Should not happen: in the initialize event a record is created if sysinportal has no records


end if

while MailProcessActive == "X"


sqlCommand = "Select * from berichtenmail where berichtenmail_verzonden IS NULL"

flag_SendMail = cn.execute(sqlCommand)



rs = cn.ResultSet


flag_SendMail = rs.NextRow()

while flag_SendMail

if isnull(rs.data("BERICHTENMAIL_HTML_MESSAG")) then

'Fill email variables
pm.From = alltrim(str(rs.data("BERICHTENMAIL_FROM")))
pm.From_Alias = alltrim(str(rs.data("BERICHTENMAIL_FROM_ALIAS")))
pm.To = alltrim(str(rs.data("BERICHTENMAIL_TO")))
pm.CC = alltrim(str(rs.data("BERICHTENMAIL_CC")))
pm.BCC = alltrim(str(rs.data("BERICHTENMAIL_BCC")))
pm.Subject = alltrim(str(rs.data("BERICHTENMAIL_SUBJECT")))
pm.Message = str(rs.data("BERICHTENMAIL_MESSAGE"))
'pm.Html_Message = e._currentRowDataNew.BERICHTENMAIL_HTML_MESSAG
pm.Attachments = alltrim(str(rs.data("BERICHTENMAIL_ATTACHMENTS")))
pm.Xmailer = alltrim(str(""))
'pm.Lrelated = rs.data("BERICHTENMAIL_LRELATED")
'pm.Date = alltrim(str(rs.data("BERICHTENMAIL_DATE")))
pm.Failure = alltrim(str(rs.data("BERICHTENMAIL_FAILURE")))
pm.Headers = alltrim(str(""))
pm.Success = alltrim(str(rs.data("BERICHTENMAIL_SUCCESS")))
'pm.ReturnReceipt = alltrim(str(rs.data("BERICHTENMAIL_RETURNRECEI")))


'sent the mail
flag = email_send2(pm)

if flag then

'Update status mail
sqlCommand = "UPDATE berichtenmail SET BerichtenMail_Verzonden = 'X', mutatiedoor = 'Cptportal SendAllEmail',datumtijdmutatie = now() WHERE BerichtenMail_Id =" + rs.data("BERICHTENMAIL_ID")
cn.execute( sqlCommand)

else
'do not update mailstatus if sending email was not succesfull

end if

're-initialize email pointer variable

pm.From = ""
pm.From_Alias = ""
pm.To = ""
pm.CC = ""
pm.BCC = ""
pm.Subject = ""
pm.Message = ""
'pm.Html_Message = ""
pm.Attachments = ""
pm.Xmailer = ""
'pm.Lrelated = ""
'pm.Date = ""
pm.Failure = ""
pm.Headers = ""
pm.Success = ""
'pm.ReturnReceipt = ""

'goto next email record to mail
flag_SendMail = rs.nextRow()

else

pm.From = alltrim(str(rs.data("BERICHTENMAIL_FROM")))
pm.From_Alias = alltrim(str(rs.data("BERICHTENMAIL_FROM_ALIAS")))
pm.To = alltrim(str(rs.data("BERICHTENMAIL_TO")))
pm.CC = alltrim(str(rs.data("BERICHTENMAIL_CC")))
pm.BCC = alltrim(str(rs.data("BERICHTENMAIL_BCC")))
pm.Subject = alltrim(str(rs.data("BERICHTENMAIL_SUBJECT")))
'pm.Message = rs.data("BERICHTENMAIL_MESSAGE")
strvar = rs.data("BERICHTENMAIL_HTML_MESSAG")
strvar = stritran_multi(strvar, ss, res)
pm.Html_message = strvar
'pm.Html_Message = rs.data("BERICHTENMAIL_HTML_MESSAG")
pm.Attachments = alltrim(str(rs.data("BERICHTENMAIL_ATTACHMENTS")))
pm.Xmailer = alltrim(str(""))
'pm.Lrelated = rs.data("BERICHTENMAIL_LRELATED")
'pm.Date = alltrim(str(rs.data("BERICHTENMAIL_DATE")))
pm.Failure = alltrim(str(rs.data("BERICHTENMAIL_FAILURE")))
pm.Headers = alltrim(str(""))
pm.Success = alltrim(str(rs.data("BERICHTENMAIL_SUCCESS")))
'pm.ReturnReceipt = alltrim(str(rs.data("BERICHTENMAIL_RETURNRECEI")))

'sent the mail

flag = email_send2(pm)

if flag then

'Update status mail
sqlCommand = "UPDATE berichtenmail SET BerichtenMail_Verzonden = 'X', mutatiedoor = 'Cptportal StartMailProcess',datumtijdmutatie = now() WHERE BerichtenMail_Id =" + rs.data("BERICHTENMAIL_ID")
cn.execute(sqlCommand)

else
'do not update mailstatus if sending email was not succesfull


end if

're-initialize email pointer variable

pm.From = ""
pm.From_Alias = ""
pm.To = ""
pm.CC = ""
pm.BCC = ""
pm.Subject = ""
pm.Message = ""
'pm.Html_Message= ""
pm.Attachments = ""
pm.Xmailer = ""
'pm.Lrelated = ""
'pm.Date = ""
pm.Failure = ""
pm.Headers = ""
pm.Success = ""
'pm.ReturnReceipt = ""

'goto next email record to mail

flag_SendMail = rs.nextRow()
end if




end while

'Update status email cycle
sqlCommand = "Update sysinportal SET SysinPortalLaatsteKeerGemaild = now(),DatumTijdMutatie = now(),MutatieDoor = 'Cptportal StartMailProcess'"
cn.execute(sqlCommand)


'Wait time between cycles
sleep(MailProcessInterval)

'check / change Active status and cycle interval
sqlCommand = "Select * from sysinportal"
cn.execute(sqlCommand)
rs = cn.ResultSet
flag = rs.NextRow()
MailProcessActive = rs.data("SYSINPORTALMAILENACTIEF")
MailProcessInterval = int(rs.data("SYSINPORTALMAILENWACHTTIJD"))

'if status mailprocess is "S" (to be stopped) set to null

if MailProcessActive == "S" then

MailProcessActive = ""

sqlCommand = "Update sysinportal SET SysinPortalMailenActief = null,DatumTijdMutatie = now(),MutatieDoor = 'Cptportal StartMailProcess'"

flag = cn.execute(sqlCommand)
if flag then

StartMailProcess = "alert('Mailproces gestopt');javascript:location.reload(true);"

end if
else
'do nothing if the mailprocess is already active


end if


end while

cn.close()

end function

[Mike Reed]

Re: Sending Bulk Email - WAS Impact

I have an account that I have that sends 1500 to 1800 emails at a crack, all of which is done within the WAS. We have no negative problems with the WAS. The only delay we have is with the mail server. It would take an hour sometimes to run the full count, but that had a 1.2MB file attachment. Normal emails (with out large attachments) take 10 -15 minutes. Here is a confirmation message from the last run:

Your message for the *** communities has been processed. There were 1839 emails sent.

Message processing began at 06:56:50 and ended at 07:09:06

[azz211]

Re: Sending Bulk Email - WAS Impact

Thanks everyone for the replies. Much appreciated. Ger - I have a script similar to the one that you posted. So its good to get some confirmation that I am moving in the right direction with the processing portion.

Steve - thanks for your explanation. Where I was getting confused was between actually sending an email and making a request via a third party to send an email where the work of actually sending the email is off loaded to separate server.

Mike - its good to see you can send that volume of email using the WAS. This may mean we can use a hybird approach of using the WAS for certain low volume emails and sending the heavier work to a 3rd party.

Thanks again.