Sending massive mails

Hi!

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?

regards
ciro

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?

1 Like

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…)

As single mails ?

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 :slight_smile:

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 :slight_smile:

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