Building email recipient list for multiple addresses

[Kemidan2014]

I am working on some VBA code for a button to create an Email with a simple message to email multiple people in our organization as a reminder for some action to be taken.

what i have so far works but it is skipping 2 of 6 emails that meet the criteria i am asking for. and i do not understand why.

The goal. Populate the "To" field of my outlook email with email addresses based on matching values
In my form i have a drop down combo box called hotzone. the list values match the TABLE i am pulling emails from which has a column also labelled Hotzone.
basically im asking it "if record in Emaillist table column Hotzone matches form hotzone field then put the email address in the "To" field.

This is still in its building and testing phase. this code is doing what i am asking it to which is when i switch the hotzone field in the form its giving me different emails that match the criteria. but not ALL the emails that match the criteria. did Debug.print and the immediate window matches the To field.

Private Sub Command258_Click()
Dim O As Outlook.Application
Dim M As Outlook.MailItem
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim address As String
Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset("Select * From emaillist")

'Dim msg As String

'msg = "Insert complicated message here"

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With rst
 Do While Not .EOF
  If rst!hotzone = Me.hotzone Then
  address = address & rst!Email & ";"
  rst.MoveNext
  End If
  rst.MoveNext
 Loop
End With
  Debug.Print address
 With M
    .To = address
    .BodyFormat = olFormatHTML
    .HTMLBody = "Test Message" & Me.hotzone
    .Subject = "Attention Out of Spec Condition"
    '.Send
    .Display
    
 End With
Set strto = Nothing
Set M = Nothing
Set O = Nothing

End Sub

 

[Micron]

it is skipping 2 of 6 emails that meet the criteria i am asking for.

Then I'd say that while that appears to be the case it isn't true. Perhaps there is a leading/trailing space in either list. Or there are hidden characters (e.g. rtf or html) that are part of the comparison. Or you are using Option Binary (at the top of your module) and the case of either list value isn't the same. Or something else.

did Debug.print and the immediate window matches the To field.

I'm guessing that only one list is being printed and you're making the comparison against the other source. Perhaps print both values as well as their lengths. Something like:
Debug.Print rst!hotzone & " " & Len(rst!Email) & " " & Me.hotzone & " " & Len(Me.hotzone)

 

[XPS35]

If you find a matching record you perform TWO MoveNext commands.

 

[welshgasman]

If you find a matching record you perform TWO MoveNext commands.

I say the same. You only add if correct hotzone, but you need to move to the next record regardless.
Start learning to walk your code with F8 and breakpoints.

TBH, I would ONLY be looking for records with the correct hotzone in the first place, so no need to test.?
What if you had a 1000 records in there and only 10 were for that hotzone. You have read 1000 records to get 10 email addresses.

I know computers are very fast these days, but that is still very ineffcient.

 

[Kemidan2014]

Micron I thought about your comment about trailing or leading spaces in the values in hotzone field next to each email. copied the value from one of the emails that was filling in to the 2 that were not and I got the same result. Looking at how the table of emails I have is ordered XP and welsh appear to be correct. that My code is moving next 2x if it find a correct value.

TBH, I would ONLY be looking for records with the correct hotzone in the first place, so no need to test.?

Welsh, Before I posted, one of my attempts had a different SQL statement

Set rst = CurrentDb.OpenRecordset("Select * From emaillist")

was originally

Set rst = CurrentDb.OpenRecordset("Select Email From emaillist Where Hotzone = Me.hotzone")

But i received an error about there was too few parameters given in criteria. I am sure it is how i have the statement formatted but decided to simplify the statement to test the result of the code

Also for what ever reason i dont know if i have a setting disabled but F8 NEVER works for me on Access VBA editor, only Excel VBA editor. Can't explain why it just will not.

 

[welshgasman]

You would need to concatenate the hotzone value?

Set rst = CurrentDb.OpenRecordset("Select Email From emaillist Where Hotzone = " & Me.hotzone) 'if numeric

Set rst = CurrentDb.OpenRecordset("Select Email From emaillist Where Hotzone = '" & Me.hotzone & "'") 'if text

What is the sql for EmailList ?

 

[Micron]

F8 NEVER works for me on Access VBA editor

Then customize your toolbar? I've added at least 5 buttons because I use them a lot.

I'm guilty of skimming over the code so I missed the 2 move thing.

 

[Kemidan2014]

Thank you all for your help, below is the edits to my code with combination of each of your input. This work successfully

Now it I can move on to the message format itself

Private Sub Command258_Click()
Dim O As Outlook.Application
Dim M As Outlook.MailItem
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim address As String
Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset("Select Email From emaillist Where Hotzone = '" & Me.hotzone & "'")

'Dim msg As String

'msg = "Insert complicated message here"

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With rst
 Do While Not .EOF
  'If rst!hotzone = Me.hotzone Then
  address = address & rst!Email & ";"
  rst.MoveNext
  'End If
  'rst.MoveNext
 Loop
End With
  Debug.Print address
 With M
    .To = address
    .BodyFormat = olFormatHTML
    .HTMLBody = "Test Message" & Me.hotzone
    .Subject = "Attention Out of Spec Condition"
    '.Send
    .Display
    
 End With
Set strto = Nothing
Set M = Nothing
Set O = Nothing

End Sub

 

[Micron]

With rst isn't really necessary but if you're going to use it, might as well use it all the time. So yes to
.EOF
but might as well be !Email & ";" as well as .MoveNext ? Same with db that you set - might as well use it?
Set db = CurrentDb
Set rst = db.OpenRecordset ...
Or don't create db in the first place.

You're not using Option Explicit, are you? This would raise an error if you were (unless strto is at the module level which you're not showing)
Set strto = Nothing