Thread: Access CDO VB to send emails
07-13-10, 17:52 #1Registered User
- Join Date
- May 2009
Unanswered: Access CDO VB to send emails
I have a database that we use to send out mass emails to customers. The database has a table that has the email addresses and names to go along with them. I then have some VB that goes through and pairs the info together and sends it to the code below:
Option Compare Database Public Function SendNotesMail(Recipient, name) Set rstEmailBody = CurrentDb.OpenRecordset("SELECT * FROM options") Set objMessage = CreateObject("CDO.Message") rstEmailBody.MoveFirst BodyText = rstEmailBody!email_body email_body = BodyText objMessage.Subject = rstEmailBody!email_subject objMessage.from = "Customer_Support@email.com" objMessage.To = Recipient objMessage.TextBody = BodyText '==This section provides the configuration information for the remote SMTP server. '==Normally you will only change the server name or IP. objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Name or IP of Remote SMTP Server objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "m*****mail.corp.*****.org" (Masked out for security) 'Server port (typically 25) objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 objMessage.Configuration.Fields.Update '==End remote SMTP server configuration section== objMessage.Send Set objMessage = Nothing SendNotesMail = True End Function
1) We use Lotus Notes.
2) It seems that when an invalid email address comes along (for whatever reason, either not formatted, or not reconized by the internal address book)
the data base just stops. So. If 500 emails need to be sent and on #50 it doesnt like the email address, it just stops. No error. Doesnt continue, just stops. So i have to go in and check the email address I BCC everything to to see where it stopped, delete the next one in the table and kick it off again.
DOes anyone know why this would happen or how I can prevent it?
Ive gone as far as to run a query first to look for invalid emails and remove them
Let me know if you need more info.
07-13-10, 19:36 #2Registered User
- Join Date
- May 2010
IMHO, the simple way to prevent this would be to not allow invalid email address to be entered. I would to validate the email address format at data entry.
Email validation with Regular expression
Dim myReg As RegExp Dim email As String Set myReg = New RegExp myReg.IgnoreCase = True myReg.Pattern = "^[\w-\.]+@\w+\.\w+$" 'replace "firstname.lastname@example.org" with the email address you want to check. email = "email@example.com" MsgBox "the result of validation checking: " & myReg.Test(email)
07-14-10, 10:56 #3Registered User
Provided Answers: 2
- Join Date
- Sep 2006
- Surrey, UK
It is possible to write Lotus Script within a Notes database that will send out merged E-mails. This would probably save you a lot of time.10% of magic is knowing something that no-one else does. The rest is misdirection.