Gather fields into a string with recordset (1 Viewer)

[bonekrusher]

Hi All,
I've searched the forum and I cant seem to find a solution to a simple problem. I am trying to gather a bunch of email addressed into a string, to send out a mass email to all my admins. The code below only emails the last email address in the record.

Thanks for the help

Dim rst As DAO.Recordset
Dim strAddress as string

While Not rst.EOF
    With rst
    If rst![admin] = -1 Then
    MsgBox rst![name] & " is an admin"
    strAddress = rst![EMAIL]
      
            With EmailItem
            .Subject = "QA Audit Schedule for " & Date
            .Body = EMailComment
            .To = strAddress
            .Importance = olImportanceHigh 
            .Attachments.Add sFile
            .Send
            End With
    
    
    Else: End If
      End With
rst.MoveNext

Wend

 

[Bodisathva]

Most email operations will accept a list of email addresses delimited by a semicolon (some commas), have you tried concatenating the list of recipients and sending one email?

 

[namliam]

            .To = strAddress

should offcourse have to be

            .To = .To & ";" & strAddress

Good luck

 

[Bodisathva]

neither rain, nor snow, nor dead of night will prevent you from seeing I wasn't awake yet...

Thanks, nam

 

[bonekrusher]

Hmm thanks but now it only emails to the first email record....

anythoughts?

If I could send one email, that would be great.

 

[Bodisathva]

'establish list of addresses
While Not rst.EOF
   If rst![admin] = -1 Then  strAddress = strAddress & "; " & rst![email]
   rst.MoveNext 
Wend  

'remove extraneous semicolon
strAddress = left(strAddress, len(strAddress)-2)

'send the mail          
With EmailItem
   .Subject = "QA Audit Schedule for " & Date
   .Body = EMailComment
   .To = strAddress
   .Importance = olImportanceHigh 
   .Attachments.Add sFile
   .Send
End With

 

[bonekrusher]

Thanks the codes works great. I had to change the following line:

strAddress = left(strAddress, len(strAddress)[COLOR="Red"]-2[/COLOR])

to

strAddress = left(strAddress, len(strAddress))

I've been working on this for 2 days! Thanks so much!