VBA Loops

[mbit-uk]

I am trying to get my database to perform a loop until it reaches the end of the table, picking up all clients email addresses. So far I have -

'-- Looks up email address from TblUsers
If comboGroup.Value = "clients" Then

varTo = DLookup("[email_address]", "clients")

End If

At the minute, it just adds one record (The last one). I believe this is because there is no loop adding them in.

Thanks.

[Norie]

Could you not just use a query?:eek:

Perhaps an update query? Or a query using parameter(s) criteria?

[CreganTur]

Welcome to the forum- it's always good to see new members.

What exactly are you trying to do? What do you want to do with these e-mail addresses? Add them into a new table, or something else?

More input...more INPUT! ;)

[mbit-uk]

I am trying to get it to send an email to everyone on the table. I have it importing one address in to outlook, but I need it to do it for everybody in the table.

Hope this helps.

Matt

[CreganTur]

I'm guessing that you have to send each e-mail individually, correct?

Are these e-mails being sent by Outlook? If so, do you get the "outside application is trying to take over Outlook" message?

Please post the existing code you are using you generate your e-mails; I'll use it to build a loop for you and I will fully explinan it to help you understand what's going on. Also, list your table's name and the e-mail field's name if your code doesn't show it.

When you post code be sure to wrap it with VBA tags (click the green VBA button)- this will format the code according to VBIDE, which will make it easy to read.

[mbit-uk]

Thanks.

My code is as follows -
Private Sub cmdSend_Click()
Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stWho As String '-- Reference to tblUsers
Dim errLoop As Error

'-- Looks up email address from TblUsers
If comboGroup.Value = "clients" Then

varTo = DLookup("[email_address]", "clients")

End If

If comboGroup.Value = "commercial" Then

varTo = DLookup("[Email_address]", "commercial")

End If

If comboGroup.Value = "import" Then

varTo = DLookup("[Email_address]", "import")

End If

stSubject = txtSubject

stText = txtMessage

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1
End Sub

I have three tables. Commercial, Clients and Import. Each one contains user info such as email addresses. These clients need to be emailed in bulk. The email needs a subject and some content in plain text.

Thanks for your help.

[CreganTur]

I made some changes to your original code, and I also setup the loops that will gather all of the e-mail addresses from the specified table. I commented the code so you'll hopefully understand what's going on, but if you have questions let me know.

Basically I gather the e-mail addresses from the table into a DAO Recordset. Then I use a loop to pull each e-mail address into the strTo variable, which is a string variable that will contain your list of e-mail addresses.

Dim strTo As String '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim db As DAO.Database '<<<Create DAO Connection
Dim rst As DAO.Recordset '<<<Create DAO Recordset

Set db = CurrentDb '<<<Create a connection to this database
strTo = "" '<<<Explicitly make this an empty string variable

'-- Looks up email address from tables
If comboGroup.Value = "clients" Then
'create a DAO recordset that pulls all e-mail addresses from table clients
Set rst = db.OpenRecordset("SELECT email_address FROM clients")
'move to the first record in the recordset
rst.MoveFirst
Do Until rst.EOF
'add each e-mail address to the strTo variable
'correctly delimit e-mail addresses with semicolon
strTo = strTo & "; " & rst.Fields(0).Value
rst.MoveNext '<<<Move to the next record
Loop '<<<Encases loop; all statements between Do and Loop keywords will repeat
End If

If comboGroup.Value = "commercial" Then
Set rst = db.OpenRecordset("SELECT Email_address FROM commercial")
rst.MoveFirst
Do Until rst.EOF
strTo = strTo & "; " & rst.Fields(0).Value
rst.MoveNext
Loop
End If

If comboGroup.Value = "import" Then
Set rst = db.OpenRecordset("SELECT Email_Address FROM import")
rst.MoveFirst
Do Until rst.EOF
strTo = strTo & "; " & rst.Fields(0).Value
rst.MoveNext
Loop
End If

stSubject = txtSubject
stText = txtMessage
'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, strTo, , , stSubject, stText, -1

This code is untested and written from memory; no warranty provided; may require adjustments.

[mbit-uk]

THanks, this is a HUGE help. Will this work if the client does not have an email address? I only ask because when I run it on a table that has only 7 email addresses in out of about 20 records, it comes up with the message 'Unknown Message Recipient(s), the message was not sent'.

Many thanks.

[CreganTur]

THanks, this is a HUGE help. Will this work if the client does not have an email address? I only ask because when I run it on a table that has only 7 email addresses in out of about 20 records, it comes up with the message 'Unknown Message Recipient(s), the message was not sent'.

Many thanks.

I did not put in any error-checking for the provided VBA. You'll need to add some in so that when the loop comes across a null-valued e-mail it will skip it and not add it into the collection of e-mail addresses.

Take a stab at it, I'm sure that you can figure it out with what I've already given you. If not, then let me know and I'll get back to you on Monday:thumb

[mbit-uk]

I have come up with the following but it does not seem to work. I get the error 'Type Mismatch'.

If comboGroup.Value = "commercial" Then
Set rst = db.OpenRecordset("SELECT Email_address FROM commercial")
rst.MoveFirst
Do Until rst.EOF
If rst.EOF = "" Then
rst.MoveNext
Else

strTo = strTo & "; " & rst.Fields(0).Value
rst.MoveNext
End If

Loop
End If

Thanks :-)

[CreganTur]

I have come up with the following but it does not seem to work. I get the error 'Type Mismatch'.

which line of code is highlighted when you debug the error?

This line of code is meaningless:
If rst.EOF = "" Then

You need to check for the value of the field: If rst.Fields(0).Value = "" Then
Also, I can't rememeber if you're going to need to check for an empty string or a null value with DAO recordsets.

[mbit-uk]

I replaced the line you suggested and after running and pressing debug, the following line is highlighted yellow -

DoCmd.SendObject , , acFormatTXT, strTo, , , stSubject, stText, -1

Thanks, Matt