Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    29

    Unanswered: Access CDO VB to send emails

    Hello,

    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:

    Code:
    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
    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
    Posts
    601
    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
    Code:
     Dim myReg As RegExp
    
    Dim email As String
    
    Set myReg = New RegExp
    
    myReg.IgnoreCase = True
    
    myReg.Pattern = "^[\w-\.]+@\w+\.\w+$"
    
    'replace "myemail@hotmail.com" with the email address you want to check.
    
    email = "myemail@hotmail.com"
    
    MsgBox "the result of validation checking: " & myReg.Test(email)
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (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
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    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.

Posting Permissions

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