Email Problem

[racer25]

Hi,

I have a problem with some code that I have copied & edited to suit my needs. Our company uses this code to email hundreds of statments of account to our customers. The problem is that when it hits an invalid email address such john_smith#gmail.com the error messafge appears and I have to figure where it had got to via the sent box. I then delete the records before that one tidy up that record (if known) and then start the run again with a reduced list.

What I would ideally like it to do is append each successful record to a table "Success" and the error records to a "Fail" table.

I have shown the code below and I am hoping that someone can help me with this problem.

Thanks in advance.

Private Sub Command1_Click()
Dim notesdb As Object
Dim notesdoc As Object
Dim notesrtf As Object
Dim notessession As Object
Dim strSupportEMail As String
Dim avarattach(3) As Variant
Dim rs As DAO.Recordset
Dim sql As String
Dim strPath As String
Dim x As String

x = 0

strPath = "C:\Temp\"
sql = "SELECT DISTINCT StatementTable.Terms, StatementTable.StateFile, StatementTable.Email, StatementTable.Salutation, StatementTable.SHD_ENDDATE FROM StatementTable;"
Set rs = CurrentDb().OpenRecordset(sql, dbOpenSnapshot)
Do While Not rs.EOF
    StateFile = rs!StateFile
    Email = rs!Email
    Salutation = rs!Salutation
    StateMonth = rs!SHD_ENDDATE
    Terms = rs!Terms
    
x = x + 1
    
'make new mail message
Set notessession = CreateObject("Notes.Notessession")
Set notesdb = notessession.GetDatabase("", "")
Call notesdb.OPENMAIL
Set notesdoc = notesdb.CreateDocument
Call notesdoc.replaceitemvalue("Sendto", Email)
Call notesdoc.replaceitemvalue("Subject", "Statement of Account " & MonthName(Month(StateMonth)) & " " & Year(StateMonth))
Set notesrtf = notesdoc.CreateRichTextItem("body")


Call notesrtf.AppendText("Dear " & Salutation & ",")
Call notesrtf.AddNewLine(2)
Call notesrtf.AppendText("Please find attached your statement for " & MonthName(Month(StateMonth)) & " " & Year(StateMonth) & ".")
Call notesrtf.AddNewLine(2)
Call notesrtf.AppendText("The statement for the attached period where payments are recorded by Aon constitute a receipt and should be retained carefully.")
Call notesrtf.AddNewLine(2)
Call notesrtf.AppendText("The File - " & Terms & ".pdf" & " - contains additional supporting information and Terms & Conditions along with Bank Information.")
Call notesrtf.AddNewLine(2)



strSupportEMail = "someemail@email.com"
notesdoc.SaveMessageOnSend = True
Call notesdoc.Send(False) 'Send the email!

Set notessession = Nothing
    rs.MoveNext

Pause (3) 'for a three second pause
Loop

MsgBox x & " Statement Emailed"

Set rs = Nothing
End Sub

 

[namliam]

Why dont you (double) check before triggering this code that atleast the email addresses look like valid email addresses??

Like "*@*.*" should be a valid email adress, anything "NOT" like that is invalid and should not be mailed...

I see you are using LOTUS NOTES?? Kewl !

Not that many companies using Lotus now a days...

 

[racer25]

I do limited checking but there a multitude of ways that this can fall over

e.g @gmail..com

There are also internal mail address's which do not contain @

Hence the reason for try to create a proper and safe method of capturing these.

Notes isn't bad apart from its complete unwillingness to talk to MS.

 

[namliam]

Internal addresses can also be spelled out to their complete address including the "@Company.com" part, no problem... or make an additional field/check box to identify internal addresses which are send regardless.
But I would "simply" add the @ part without problems... This would result in bounced emails if a user doesnt excist (anymore)

Any error, once known can be "trapped" by any function or query statement. Probably a function is most logical which returns a True or False in your query.

Trap the most obvious/known ones to start with.... and add as you find new ones.

 

[racer25]

Hi,

A quick test on the internal mails while I can use the @company.com our NAB automatically checks these anyway and fails the script if the person has left.

As I said in the first post I copied the code and edited to suit, I kind of know whats its doing but my knowledge is limited in comparison.

Maybe I am going about this the wrong way, but I was thinking that within this code if there was someway of writing to a table a couple of fields from the StatementTable into a table called Success, an example field being CLI_CLIENTNUMBER.

Likewise if there is an error then a similar query to the above but going to a fail table.

In the perfect world ..... I would like to run a tidy up of the email addresses and then use the above as kind of an audit trail.

Like I said before my knowledge is quite limited, but am eager to learn.

Thanks for your time it is appreciated.

R

 

[namliam]

My experience with Lotus is limited (to say the least) but I gather you want something like "send OK" or something...

OK Lets go...
Take your StatementTable and add a field, could be a true/false field or a date/time field.
I would go for date/time field, call it SendDateTime.
Now add it to the query obviously... otherwize we cannot use it.

Now before moving on to the next record (rs.movenext) add:
rs.edit
rs!SendDateTime = now()
rs.update
to write the timestamp into the table.

Now there are 2 options depending on your code, which I dont know 100% how it works either.
1) The first record without a date/time will be your error mail
2) The last record with a date will be the error record.

You can also extend your query with
Where SendDateTime is null
This will limit the email addresses to ones not yet send without you having to go in and manually remove any addresses that have allready been send.

I hope this is what you are looking for.

 

[gemma-the-husky]

where in the code does your error message appear

can you not trap it, and just ignore the suspect email, and report all the dodgy email addresses later?