I’ve a sql table and I’ve to send an email to all the entries in that table (of course, I’ve email addesses in it!)
I’ve made a method like this (I read the records one by one, I send the mail and then put in another table - log - the info that I’ve send the mail):
Dim db As New MSSQLServerDatabase
db.Host ="192.168.1.111" // or just the IP if using the default instance
db.DatabaseName = "SalCommessa"
db.UserName = "sa" // or "Domain\\UserID for trusted domain accounts
db.Password = "mypwd"
If db.Connect Then
dim smtp as new SMTPSecureSocket
smtp.Address = Icam_SAL_Commessa.smtp_host
if Icam_SAL_Commessa.smtp_secure="True" then
smtp.Secure = true
smtp.Secure = false
smtp.Port = Icam_SAL_Commessa.smtp_port.Val
smtp.ConnectionType = 1
smtp.Username = Icam_SAL_Commessa.smtp_account
smtp.Password = Icam_SAL_Commessa.smtp_password
dim stringa_sql as string
Dim ps As MSSQLServerPreparedStatement
Dim ps2 As MSSQLServerPreparedStatement
Dim rs As RecordSet
stringa_sql=stringa_sql+"Elenco_Commesse.Stato,Elenco_Commesse.Data_Consegna,isnull(Log_Invio.Data_Invio,'NO') as Data_Invio"
stringa_sql=stringa_sql+" from Elenco_Commesse "
stringa_sql=stringa_sql+" left outer join Log_Invio on Elenco_Commesse.Commessa=Log_Invio.Commessa and "
stringa_sql=stringa_sql+" Elenco_Commesse.Stato=Log_Invio.Stato "
stringa_sql=stringa_sql+" order by Commessa "
ps = db.Prepare(stringa_sql)
rs = ps.SQLSelect
if rs <> Nil Then
While Not rs.EOF
//invio mail solo se per questo stato non esiste gi
if rs.IdxField(9).StringValue = "NO" then
Dim mail As New EmailMessage
Dim testocom as string
mail.FromAddress = Icam_SAL_Commessa.smtp_account
mail.Subject = "prova invio commessa "+rs.IdxField(1).StringValue
testocom = ""
testocom=testocom+rs.IdxField(1).StringValue+"invio di test - Ciro - "+rs.IdxField(7).StringValue
dim lines() as string
mail.BodyPlainText = Join(lines, EndOfLine)
'msgbox("inviata mail commessa "+rs.IdxField(1).StringValue)
'System.DebugLog("inviata mail commessa "+rs.IdxField(1).StringValue)
'//aggiorniamo il log
stringa_sql="INSERT INTO Log_Invio (Commessa,Stato,Data_Invio)"
ps2 = db.Prepare(stringa_sql)
MsgBox("Connection error:" + db.ErrorMessage)
the problem is that sometimes the mails arrive, sometimes I think they are not send.
Any idea or another way to do this?
Sending more than a few mails with a normal SMTP connection is prohibited by your hosting company. Either you send only a couple of mails at a time. Or you send your mails with Amazon, Mandrill, SendGrid and the like.
Your domain (I assume its icamonline.eu) isn’t blacklisted which is good. Make sure you set up an SPF record for your domain (you don’t have one) as lots of mail servers will check that first and bounce anything without one.
and if I put a timer to send an email every 1-2 minutes?
If your hosting company has a throughput limit per hour then that will solve that issue, but if its related to SPF then sending them slowly won’t make any difference.
When you say “massive mails”, do you mean “large in size” or “lots of them”? (possibly lost in translation)
lots of them, about 100-150 a day (they will be sent overnight…)
If you are rate limited by your host, you’ll usually get an error back when trying to send them, its very odd that they’d accept them and then bin them, they would normally drop the connection to save themselves bandwidth of you actually submitting the data to them.
Is there a pattern to the missing emails, are the ones near the end of the list missing? are random ones missing?
If you are rate limited it’ll be the ones near the end, if its an SPF issue it will be a random selection.
Did you make tests sending the mails to another of YOUR eMail address ?
100-150 does not seem excessive, my provider has a limit of 500 per hour.
100-150 same sender and same 1-2 destination address.
Each mail has different subject and text inside…
no there is not a pattern, sometimes I receive all the mails, sometimes none of them…
the first time I always get the mail, from the second try sometimes yes, others not
You can check the value in the panel of your hosting company. My value is set to 30 per hour.
I hate diagnosing email issues as there are so many factors that could get in the way
I see that your from address is the same as the account info, Is the from address valid? do you get any bounce messages back to it? Are you able to send an email to that “from” address from outside the company (e.g. via gmail) and read the message on that from account?
[quote=441696:@JulianS]I hate diagnosing email issues as there are so many factors that could get in the way
I see that your from address is the same as the account info, Is the from address valid? do you get any bounce messages back to it? Are you able to send an email to that “from” address from outside the company (e.g. via gmail) and read the message on that from account?[/quote]
yes the addresses are correct, using a manual send works. I think is am hosting limitation, I’ll try to check it with Aruba - my hosting company
Having never sent an email with xojo (just reading up on it now), if you don’t handle the ServerError event how do you know that you aren’t getting errors back during the send?
Please review MassEmailer example coming with MBS Xojo CURL Plugin.
it sends a thousand emails easily and delays them optionally to start an email every 5 seconds only to add some delay.
Ciro, why reinvent the wheel ?
Sending mass email is much easily done with a specialized service, such as mailchimp.com, which will not only send mail without risk for your email account, but also manage unsubscribe.
Sending mass emails is certainly an ongoing problem. And not that I’m trying to change the topic away from this important discussion, or discourage anyone from using Xojo as their solution tool.
But I hope it might be helpful to point out that Microsoft SQL Server (which @Ciro Marciano appears to be using) has it’s own send mail functions, a.k.a., Database Mail.
The key benefits are:
- A configurable retry queue, like a real SMTP server
- Very reliable (I’ve been using this feature for years, although not for marketing purposes)
- Logging tables to report the success or failure of each email send attempt
- Features to resend previously undeliverable email.
See The sp_send_dbmail Procedure and Check the Status of Emails sent with Database Mail
You may have to get your hands dirty with writing a couple of Stored Procedures to manage this (many examples can be found by googling), but then I think you can call the Procedures from Xojo or even put the Send on a schedule using SQL Jobs.
I hope that helps.
Edited: To add extra link