Results 1 to 3 of 3
  1. #1
    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")
     BodyText = rstEmailBody!email_body
    email_body = BodyText
    objMessage.Subject = rstEmailBody!email_subject
    objMessage.from = ""
    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("") = 2
    'Name or IP of Remote SMTP Server
    objMessage.Configuration.Fields.Item("") = "m*****mail.corp.*****.org" (Masked out for security)
    'Server port (typically 25)
    objMessage.Configuration.Fields.Item("") = 25
    '==End remote SMTP server configuration section==
    Set objMessage = Nothing
    SendNotesMail = True
    End Function
    My Problem is..

    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
    (LIKE *@*.com)

    Let me know if you need more info.

  2. #2
    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 "" with the email address you want to check.
    email = ""
    MsgBox "the result of validation checking: " & myReg.Test(email)
    Boyd Trimmell aka HiTechCoach (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    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.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts