Sending email from table or query

[ptrifile]

I use the following code to send an email to alert a certain group of people. What i would like to do is substitute the cc1 = email addresses to a table or query that lists email addresses instead of manually manipulating the code every time someone moves in or out of a position.

I have a table called TblEmail that holds the email addresses but i cannot seem to figure out how to change the code to do it. I have successfully tried dlookup but of course that only returns the first record in the table. the current code is as follows:

    If Not Me.FraudRisk.Value And Not Me.Update.Value Then
    
            Select Case Me.PotentialLoss.Value
            
                Case Is < 1000.01
            
                cc1 = "email address 1; email address 2"
  DoCmd.SendObject acReport, "Rptprocedureexceptionreport", "pdf", to1, cc1, bcc1, "Procedure Exception Notification - Issue - " & "Branch " & Me.Branch & "/ " & Me.Branch2 & "/ " & Me.Branch3 & "/ " & Me.Branch4 & "/ " & Me.Branch5 & " - Loss/Potential Loss $" & Me.PotentialLoss & " ", "The attached summary is to notify you of a procedure exception that occurred at your branch and is being communicated to you for quality and training purposes." & vbCrLf & vbCrLf & "If you have any questions or concerns please feel free to contact me directly to discuss the matter further. As always, Retail Operations is here to assist whenever needed." & vbCrLf & vbCrLf & "Thank you for your time and attention to this matter.", True, """"
            
                Case Is <= 10000
                
                   cc1 = "email address 1; email address 2; email address 3"
                    DoCmd.SendObject acReport, "Rptprocedureexceptionreport", "pdf", to1, cc1, bcc1, "Procedure Exception Summary - Issue - " & "Branch " & Me.Branch & "/ " & Me.Branch2 & "/ " & Me.Branch3 & "/ " & Me.Branch4 & "/ " & Me.Branch5 & " - Loss/Potential Loss $" & Me.PotentialLoss & " ", "Attached is a procedure exception situation that Retail Operations - Research was made aware of regarding __________, resulting in a loss/potential loss of $_______." & vbCrLf & vbCrLf & "If you have any questions or concerns regarding the attached document, please contact me directly at (716) 625-_____." & vbCrLf & vbCrLf & "Thank you,", True, """"
            
                Case Else
            
            
                    cc1 = "email address 1; email address 2; email address 3; email address 4"
                    DoCmd.SendObject acReport, "Rptprocedureexceptionreport", "pdf", to1, cc1, bcc1, "Procedure Exception Summary - Issue - " & "Branch " & Me.Branch & "/ " & Me.Branch2 & "/ " & Me.Branch3 & "/ " & Me.Branch4 & "/ " & Me.Branch5 & " - Loss/Potential Loss $" & Me.PotentialLoss & " ", "Attached is a procedure exception situation that Retail Operations - Research was made aware of regarding __________, resulting in a loss/potential loss of $_______." & vbCrLf & vbCrLf & "If you have any questions or concerns regarding the attached document, please contact me directly at (716) 625-_____." & vbCrLf & vbCrLf & "Thank you,", True, """"
            
            End Select
        
           
    End If

so i am looking to replace the "cc1 = the email addresses" with the results of a query or entire table.

Hope this makes some sort of sense.

thanks for any help!

Paul

 

[Andrzejek]

How about something like this pseudo-code:

[blue]
Dim rst as Recordset

rst.Open "Select E_Mail, Person From TblEmail"
[/blue]
If Not Me.FraudRisk.Value And Not Me.Update.Value Then

Select Case Me.PotentialLoss.Value
    Case Is < 1000.01[blue]
        With rst
            .Filter = "Whatever you need to filter on"
            If .RecordCount > 0 Then
                For iCount = 1 To .RecordCount
                    If iCount = 1 Then
                        cc1 = !E_Mail.Value
                    Else
                        cc1 = cc1 ": " & !E_Mail.Value
                    End If
                    .MoveNext
                Next iCount
            End If
        End With[/blue]
        DoCmd.SendObject acReport, ...
    Case Is <= 10000

    Case Else

End Select
End If
rst.Close

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.