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

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

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

As single mails ?

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

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

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

@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

@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