Access VBA Conditional Loop (1 Viewer)

[mickelb]

I am trying to get a conditional loop working, but can't seem to get the condition statement in the correct place to work for each record.

I want to loop through a table and send an email to each record unless I have checked an EXCLUDE field in the form. So somewhere I need

"If EXCLUDE = "Yes" then goto the next record" or similar so that it misses out any record set EXCLUDE = Yes on each loop until EOF.

Where and what statements?

Thanks.

Private Sub Command11_Click()
Dim address As String
Dim Name As String
Dim EXCLUDE As String
Dim db As Database
      Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("MYTABLE", dbOpenTable)
 Me.Refresh
    rst.MoveFirst
    Do While Not rst.EOF
 
        Name = Trim(rst!Name)
        address = Trim(rst!Email)
      
    Dim mail    As CDO.Message
    Dim config  As CDO.Configuration
    
    Set mail = CreateObject("CDO.Message")
    Set config = CreateObject("CDO.Configuration")
    
       config.Fields(cdoSMTPAuthenticate).Value = cdoBasic
    config.Fields(cdoSendUserName).Value = "MYUSERNAME"
    config.Fields(cdoSendPassword).Value = "MYPASSWORD"
    config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
    config.Fields(cdoSMTPServer).Value = "MYSMTP"
    config.Fields(cdoSMTPUseSSL).Value = True
    config.Fields(cdoSMTPServerPort).Value = MYPORT
    config.Fields.Update

    Set mail.Configuration = config

        With mail
        .To = [address]

        .From = "MY FROM"
        .subject = "MY SUBJECT"

.HTMLBody = "Dear " & [Name] & "," _
& "<br>" _
& "<br>" _
html = html & "</tr>"
        .Send
        
    End With
    rst.Edit
    rst!EMAIL_DATE = (Now())
    rst.Update
    Me.Refresh
    Set config = Nothing
    Set mail = Nothing
            rst.MoveNext
            Loop

End Sub

 

[Rene vK]

Maybe you can create your recordset with a SQL query with a WHERE clause.

SQLstring = "SELECT * FROM MyTable WHERE EXCLUDE <> 'Yes' "

 

[mickelb]

Sorry, I should have been clearer. The first record set will be based on a query such as you suggest but I need the user to be able to exclude a record from an email blast by checking Exclude in the form in the event that an email address returns an error and then resume. That way they get a record of email addresses that need to be cleaned.

 

[Uncle Gizmo]

A similar question was asked, but not solved.

Find the bad e-mail address

Hello. I use the following code to automatically send e-mails from a database using the smtp method: Public Function SendsmtpEmail(txtRecipient As String, txtSender As String, txtsubject As String, txtbody As String) As String On Error GoTo errorhandler Dim mail Dim sName As String Set mail...

www.access-programmers.co.uk

Might give you a starting point for further Googling...

 

[Rene vK]

Sorry, I should have been clearer. The first record set will be based on a query such as you suggest but I need the user to be able to exclude a record from an email blast by checking Exclude in the form in the event that an email address returns an error and then resume. That way they get a record of email addresses that need to be cleaned.

Mickel,
I am trying to see your sequence...,
1. you open a form with your recordset
2. you scan the emailadresses in your records
3. if wrong you check the record to be excluded.
4. you start the loop for sending the emails

If I am correct, you can run through your records and create a new recordset and send your emails.
(checking in a record is an immediate result.)
otherwise you have to catch the error while sending the mail and log the adress in a temp table..

Sorry if I am totally missing the point

 

[mickelb]

@Uncle Gizmo, afraid I've done days of Googling and not come up with an answer.

As that thread said, if the SMTP server rejects an email the address is returned in the error dialogue. That error can be handled and the offending email skipped plus marked to be excluded. It is then that I need the email blast to resume from the next email based on EXCLUDE now being Yes.

I can get the IF code to work in one position, before the Do While Not EOF, if the first record is EXCLUDE = Yes, but any later ones are ignored. Or if I place the IF after Do While Not EOF I get a without Do error.

 

[mickelb]

@Rene vK ; yes.

1. The form is loaded with any filtering query on the table
2. If the user sees any errors in the records they can set as many records to EXCLUDE = Yes as needed.
3. The user clicks the "SEND EMAILS" button
3. The procedure should then run but ignore any EXCLUDE = Yes records wherever they are in the list.

As above I can get it to work if the first record is set to EXCLUDE = Yes but it then ignores any others or, depending on where I have tried the IF statement, I get an error - typically without a Do error.

I would get the procedure to handle bad emails.

 

[cheekybuddha]

Hi,

Use the form's Recordset instead of opening the table again:

Private Sub Command11_Click()
  Dim address As String
  Dim strName As String     ' Name is a reserved word - save yourself the headache and don't use it as a variable name'
'  Dim EXCLUDE As Boolean
  Dim mail    As CDO.Message
  Dim config  As CDO.Configuration
  ' Dim db As Database
  ' Dim rst As DAO.Recordset
' Set up the mail object outside the loop - it only needs to be done once, thus more efficient
  Set mail = CreateObject("CDO.Message")
  Set config = CreateObject("CDO.Configuration")
  With config
    .Fields(cdoSMTPAuthenticate).Value = cdoBasic
    .Fields(cdoSendUserName).Value = "MYUSERNAME"
    .Fields(cdoSendPassword).Value = "MYPASSWORD"
    .Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
    .Fields(cdoSMTPServer).Value = "MYSMTP"
    .Fields(cdoSMTPUseSSL).Value = True
    .Fields(cdoSMTPServerPort).Value = MYPORT
    .Fields.Update
  End With
  Set mail.Configuration = config

  With Me.RecordsetClone
    .MoveFirst
    Do While Not .EOF
      If !Exclude = False Then
        strName = Trim(!Name)
        address = Trim(!Email)
        With mail
          .To = [address]
          .From = "MY FROM"
          .subject = "MY SUBJECT"
          .HTMLBody = "Dear " & [Name] & "," _
                    & "<br>" _
                    & "<br>" _
  '        html = html & "</tr>"     ' This line is doing nothing. Make sure you decalre the html variable
          .Send
        End With
        .Edit
        !EMAIL_DATE = (Now())
        .Update
      End If
      .MoveNext
    Loop   
    Me.Refresh
  End With
  Set config = Nothing
  Set mail = Nothing
End Sub

I'd also suggest naming your command button something more meaningful.

And, more importantly, adding Option Explicit to the top of every code module.

hth,

d

 

[arnelgp]

what is EXCLUDE field, Yes/No field or Short Text field?

Private Sub Command11_Click()
Dim address As String
Dim Name As String
Dim EXCLUDE As String
Dim mail    As CDO.Message
Dim config  As CDO.Configuration
'Dim db As Database
      Dim rst As DAO.Recordset

'Set db = CurrentDb
'Set rst = db.OpenRecordset("MYTABLE", dbOpenTable)
Set rst = Me.RecordsetClone
'Me.Refresh

rst.MoveFirst
Do While Not rst.EOF
    ' if EXCLUDE is Yes/No field, uncomment this line and comment the next line
    ' IF ((rst!EXCLUDE)=False)
    If (rst!EXCLUDE & "") <> "Yes" Then
        Set mail = CreateObject("CDO.Message")
        Set config = CreateObject("CDO.Configuration")

        config.Fields(cdoSMTPAuthenticate).Value = cdoBasic
        config.Fields(cdoSendUserName).Value = "MYUSERNAME"
        config.Fields(cdoSendPassword).Value = "MYPASSWORD"
        config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
        config.Fields(cdoSMTPServer).Value = "MYSMTP"
        config.Fields(cdoSMTPUseSSL).Value = True
        config.Fields(cdoSMTPServerPort).Value = MYPORT
        config.Fields.Update

        Set mail.Configuration = config

        Name = Trim(rst!Name)
        address = Trim(rst!Email)
        With mail
            .To = rst![address]

            .From = "MY FROM"
            .subject = "MY SUBJECT"

            .HTMLBody = "Dear " & [Name] & "," _
            & "<br>" _
            & "<br>" _
            html = html & "</tr>"
            .Send

        End With
        rst.Edit
        rst!EMAIL_DATE = (Now())
        rst.Update
        'Me.Refresh
    End If
    Set config = Nothing
    Set mail = Nothing
    rst.MoveNext
Loop

End Sub

is the recordsource of the Form is table "MYTABLE"?
if yes then use the Form's recordset.
only make sure that all the Fields in Command1_Click event is present in the Form:

 

[mickelb]

@cheekybuddha: many thanks. A bit of modifying and that works. The code is very rough and ready at the moment, but I will take on board your
other advice. I certainly wouldn't use Name for a field and would name the Command button with something more meaningful.

Thanks also to all the other contributors. You all Rock!

 

[cheekybuddha]

You're welcome!

I'm pleased we all could help.

 

[mickelb]

I am always intrigued by the different approaches to a solution, some looking complicated and others simple. I guess it depends on how you were taught or learned VBA - or perhaps lessons learned.

 

[Micron]

I guess it depends on how you were taught or learned VBA

If all you have is a hammer, pretty soon everything looks like a nail.
I think that sometimes it's driven by your own way of looking at things, which for some, is always to see the trees and not the forest, if you get my drift. I tend to over complicate things at times, and it is by the simpler approaches of others that you learn to simplify things.

 

[namliam]

KISS wtf