Email macro works, but...

[saccharine]

I need the To: box to be automatically filled with the email address from a certain query.

I have a query named "qryemail" that has records for individuals that have gone past a certain date, each record has an email address in a field called "customeremailaddress". I want a sendobject email that I have named "mcroemail" to pull all of those email address from "qryemail" and place them in the To: box of the email created.

Can someone help me?

I have added the file.

 

[KenHigg]

You can do this pretty easy with the sendobject method. Something like:

DoCmd.SendObject acSendTable, "Employees", acFormatXLS, _
    "Nancy Davolio; Andrew Fuller", "Joan Weber", , _
    "Current Spreadsheet of Employees", , False

Think this would work?

 

[saccharine]

How do I fit that into my current setup?

 

[KenHigg]

Hum... Have you written any VBA code before? If so, VBA help lays it out pretty good.

???

Edit: In short, in the command button on click event, go from something like:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

    Dim stDocName As String

    stDocName = "mcroemail"
    DoCmd.RunMacro stDocName

Exit_Command17_Click:
    Exit Sub

Err_Command17_Click:
    MsgBox Err.Description
    Resume Exit_Command17_Click
    
End Sub

To something like:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

    Dim strCCName As String
    Dim strTitle As String
    Dim strMessage As String

    strCCName = "joel.williams@barksdale.af.mil"
    strTitle = "90 Day Mark"
    strMessage = "You have reached your 90 day mark for followup from deployment."

    DoCmd.SendObject , , , Me.CustomerEMailAddress, strCCName, , strTitle, strMessage, False
    
Exit_Command17_Click:
    Exit Sub

Err_Command17_Click:
    MsgBox Err.Description
    Resume Exit_Command17_Click
    
End Sub

 

[saccharine]

Awesome Ken!!! Thank you!!!! One last question. It only sends the email to the individual who's record I have opened at the time. Is it possible to set it to send an email to everyone in that has come up in that query?

 

[KenHigg]

Hum... I think so. You would need to build the list by looping through the recordset...

 

[saccharine]

Looping through the record set?

 

[KenHigg]

I think this will work, I haven't tested it

    Dim strTo As String
    Dim strCCName As String
    Dim strTitle As String
    Dim strMessage As String

    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("myQueryName")

    Do While Not rs.EOF
        strTo = rs!CustomerEMailAddress & "; "
        rs.MoveNext
    Loop
       
    rs.Close
           
    strCCName = "joel.williams@barksdale.af.mil"
    strTitle = "90 Day Mark"
    strMessage = "You have reached your 90 day mark for followup from deployment."

    DoCmd.SendObject , , , strTo, strCCName, , strTitle, strMessage, False

Of course you need to change the 'myQueryName' to a query that returns the addresses you need...