Sending massive mails

  1. 2 months ago

    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

  2. Beatrix W

    Jun 17 Pre-Release Testers Europe (Germany)

    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.

  3. Julian S

    Jun 17 Pre-Release Testers, Xojo Pro UK

    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.

  4. and if I put a timer to send an email every 1-2 minutes?

  5. Julian S

    Jun 17 Pre-Release Testers, Xojo Pro UK

    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.

  6. Julian S

    Jun 17 Pre-Release Testers, Xojo Pro UK

    When you say "massive mails", do you mean "large in size" or "lots of them"? (possibly lost in translation)

  7. lots of them, about 100-150 a day (they will be sent overnight.....)

  8. Emile S

    Jun 17 Europe (France, Strasbourg)

    As single mails ?

  9. Julian S

    Jun 17 Pre-Release Testers, Xojo Pro UK

    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.

  10. Emile S

    Jun 17 Europe (France, Strasbourg)

    Did you make tests sending the mails to another of YOUR eMail address ?

  11. Paul B

    Jun 17 Pre-Release Testers, Xojo Pro Europe (UK, Somerset)

    100-150 does not seem excessive, my provider has a limit of 500 per hour.

  12. 100-150 same sender and same 1-2 destination address.

    Each mail has different subject and text inside.......

  13. @Julian S Is there a pattern to the missing emails, are the ones near the end of the list missing? are random ones missing?

    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

  14. Beatrix W

    Jun 17 Pre-Release Testers Europe (Germany)

    You can check the value in the panel of your hosting company. My value is set to 30 per hour.

  15. Julian S

    Jun 17 Pre-Release Testers, Xojo Pro UK

    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?

  16. @Julian S 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?

    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

  17. Julian S

    Jun 17 Pre-Release Testers, Xojo Pro UK
    Edited 2 months ago

    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?

  18. Christian S

    Jun 17 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    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.

  19. Michel B

    Jun 17 Pre-Release Testers, Xojo Pro RubberViews.com

    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.

  20. Scott C

    Jun 17 Pre-Release Testers, Xojo Pro Vancouver, Canada
    Edited 2 months ago

    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 M 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

  21. Newer ›

or Sign Up to reply!