record exclusions within code (1 Viewer)

[tfaiers]

Hello everyone, hope someone can help me.

I'm using the following code to extract e-mails from my database, compile them into a string and use that in outlook (not spam) the only problem is that I need to exclude entries that are blank.

The original bit of code had this in the compile line (stTO &lt&gt "", ";", "") instead of (stTO <> "", ";", "") but never worked, kept bugging out.

What would be the best way to exclude blank fields???

Dim rst As DAO.Recordset
Dim stSQL As String
Dim stTO As String
stSQL = "clients"

Set rst = CurrentDb.OpenRecordset(stSQL)

With rst
.MoveFirst
Do While Not .EOF
stTO = stTO & IIf(stTO <> "", ";", "") & .Fields("E-Mail Address")

.MoveNext
Loop
End With

DoCmd.SendObject acSendNoObject, , , , , stTO, "Message Header", , True

End Sub


Cheers Everyone

 

[Travis]

1. IIf(stTO <> "", ";", "") does not check for blank, What it does is check to see if stTOT already has a value and adds the ; as a seperator between the email addresses.

Try this:

Dim rst As DAO.Recordset 
Dim stSQL As String 
Dim stTO As String 
stSQL = "clients" 

Set rst = CurrentDb.OpenRecordset(stSQL) 

With rst 
    .MoveFirst 
    Do While Not .EOF 
      If .Fields("E-MailAddress")<>"" Then
        stTO = stTO & IIf(stTO <> "", ";", "") & .Fields("E-Mail Address") 
      End If
      .MoveNext 
    Loop 
End With 

DoCmd.SendObject acSendNoObject, , , , , stTO, "Message Header", , True 

End Sub

 

[Travis]

1. IIf(stTO <> "", ";", "") does not check for blank, What it does is check to see if stTOT already has a value and adds the ; as a seperator between the email messages.

Try this:

Dim rst As DAO.Recordset 
Dim stSQL As String 
Dim stTO As String 
stSQL = "clients" 

Set rst = CurrentDb.OpenRecordset(stSQL) 

With rst 
    .MoveFirst 
    Do While Not .EOF 
      If .Fields("E-MailAddress")<>"" Then
        stTO = stTO & IIf(stTO <> "", ";", "") & .Fields("E-Mail Address") 
      End If
      .MoveNext 
    Loop 
End With 

DoCmd.SendObject acSendNoObject, , , , , stTO, "Message Header", , True 

End Sub

 

[tfaiers]

Many thanx Travis,

It certainly did work, but I guess u knew it would!

I guess I just didn't look at the problem straight, and so much of this code and others within the program kinda scrambles your brain, especially if the solution is obvious!

Cheers

Tony