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