By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,738 Members | 1,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,738 IT Pros & Developers. It's quick & easy.

Email Addresses - Ignore if null

P: 4
Ok, so i am using this code (see below) to open up a new email message with all my listed clients e-mail addresses in the bcc field.

Pleblem is some of the clients do not have an email address so it comes up with an error.
How do i get Access to ignore these blank fields, where there are no email addresses entered and use the ones that are entered?

Code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command12_Click()
  2.  
  3.  
  4.     Dim cn As ADODB.Connection
  5.     Dim rs As ADODB.Recordset
  6.     Dim strEmail As String
  7.  
  8.     Set cn = CurrentProject.Connection
  9.     Set rs = New ADODB.Recordset
  10.  
  11.     rs.Open "CustomerT", cn
  12.  
  13.     With rs
  14.         Do While Not .EOF
  15.  
  16.             strEmail = strEmail & .Fields("Email") & ";"
  17.             .MoveNext
  18.     Loop
  19.         .Close
  20.     End With
  21.  
  22.  
  23. On Error GoTo Err_Command12_Click
  24.  
  25.     DoCmd.SendObject _
  26.     , _
  27.     , _
  28.     , _
  29.     , _
  30.     , _
  31.     ("" & strEmail), _
  32.     , _
  33.     , _
  34.     True
  35.  
  36. Exit_Command12_Click:
  37.     Exit Sub
  38.  
  39. Err_Command12_Click:
  40.     MsgBox Err.Description
  41.  
  42.     Resume Exit_Command12_Click
  43.  
  44. End Sub
Thanks
Oct 29 '09 #1

✓ answered by ajalwaysus

I have one foot out the door, but here is a quick stab, refine your connection to only those who have email addresses.

Code:
Expand|Select|Wrap|Line Numbers
  1. rs.Open "SELECT * FROM CustomerT WHERE Email Is Not Null", cn
Hope this works.

-AJ

Share this Question
Share on Google+
3 Replies


Expert 100+
P: 266
I have one foot out the door, but here is a quick stab, refine your connection to only those who have email addresses.

Code:
Expand|Select|Wrap|Line Numbers
  1. rs.Open "SELECT * FROM CustomerT WHERE Email Is Not Null", cn
Hope this works.

-AJ
Oct 29 '09 #2

P: 4
Thank you so much. Works perfectly.

You are a Hero!
Cheers
Oct 29 '09 #3

P: 52
Just thought I'd chime in with another possible solution. Would it work if you just ran a query from the table with the criteria "Is Not Null" for the email field and then based the code off the query instead? I had a similar problem and solved it this way.

Have a great day!

Mel
Feb 14 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.