omitting blank fields in recordset loop

[surfstu]

hi all,

i have a table called volunteer, with a field called E-mail Address, i have been pulling my hair out trying to figure a way of emailing everyone and have finally got the code working.....BUT it gets stuck when it reaches a record with no E-mail Address entry. I'd like to know how to ignore blank entries and move onto the next record. I tried an 'if null then else' statement (in the red section) but i must have got it wrong, it did not like the 'with' statement... please please help, all i want to do is allow the procedure to run uninterrupted even if it reaches a blank entry,
code below, cheers stuart

Option Compare Database
Option Explicit
Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Volunteer")
MyRS.MoveFirst
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![E-mail Address]
With objOutlookMsg
' Add the To recipients to the e-mail message.

Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)

objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText

.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next

.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

 

[James Dudden]

Try something like this:
If not isnull(MyRS![E-mail Address]) then
TheAddress = MyRS![E-mail Address]
With objOutlookMsg
' Add the To recipients to the e-mail message.

Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo
End if

 

[boblarson]

As you can't assign a null to a string variable, it isn't likely that you are hitting a null (or else you would have received an INVALID USE OF NULL error and it would have highlighted this row:

TheAddress = MyRS![E-mail Address]


as the offending line before it got to the other.

You should also deal with EMPTY STRINGS ("") so this would do it:

If Len(MyRS![E-mail Address] & "")<> 0 Then

... and then the rest of the code

 

[pbaldy]

I would avoid them completely with this:

Set MyRS = MyDB.OpenRecordset("SELECT * FROM Volunteer WHERE [E-mail Address] Is Not Null")

 

[boblarson]

I would avoid them completely with this:

Set MyRS = MyDB.OpenRecordset("SELECT * FROM Volunteer WHERE [E-mail Address] Is Not Null")

A stellar idea.
<banging head on wall and muttering "I should have thought of that, I should have thought of that">

 

[pbaldy]

I've done the same thing. You get so focused on fixing the problem at hand you don't step back and see other ways to go.

 

[surfstu]

thanks guys, that last line worked a treat!!!
cheers

 

[pbaldy]

Happy to help.

 

[darbid]

I wanted to add my kindergarten expierenced 2 cents worth.

To avoid these problems I use and thought the NZ function was the soltuion?

This appears to be an Access only function.

I recently tried to use NZ in a VB6 project and realised it does not exist. A little research in google and I found that people have actually struggled to agree on a similar function that does everything that NZ does.

There are many ways to skin a cat and even more opinions on how NOT to do it. I have found that what gets me out of trouble most of the time is a combination of

if LEN(NZ(variable/or txtcontrol/field etc)) > 0 then

end if

once again just my 2 cents worth.