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
else
smtp.Secure = false
end if
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
//recupero commesse
dim stringa_sql as string
Dim ps As MSSQLServerPreparedStatement
Dim ps2 As MSSQLServerPreparedStatement
Dim rs As RecordSet
stringa_sql="Select Elenco_Commesse.Commessa,Elenco_Commesse.Codice_Cliente,Elenco_Commesse.Ragione_Sociale,"
stringa_sql=stringa_sql+"Elenco_Commesse.E_Mail,Elenco_Commesse.Italia_Estero,Elenco_Commesse.Perc_Lavorato,"
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.AddRecipient("utente2@icamonline.eu")
mail.Subject = "prova invio commessa "+rs.IdxField(1).StringValue
testocom = ""
testocom=testocom+rs.IdxField(1).StringValue+"invio di test - Ciro - "+rs.IdxField(7).StringValue
'testocom=ReplaceAll(testocom,"[cognome]",LstComunicazioni.Cell(i,2).Trim)
dim lines() as string
lines.Append testocom
mail.BodyPlainText = Join(lines, EndOfLine)
smtp.Messages.Append(mail)
smtp.SendMail()
'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)"
stringa_sql=stringa_sql+"VALUES "
stringa_sql=stringa_sql+"('"+rs.IdxField(1).StringValue+"',"
stringa_sql=stringa_sql+"'"+rs.IdxField(7).StringValue+"',"
stringa_sql=stringa_sql+"'"+rs.IdxField(1).StringValue+"')"
ps2 = db.Prepare(stringa_sql)
ps2.SQLExecute
'app.SleepCurrentThread(3000)
end if
rs.MoveNext
Wend
rs.Close
smtp.Close
end if
db.Close
Else
MsgBox("Connection error:" + db.ErrorMessage)
End If
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.
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.
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.
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:@]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.
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.
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.