Send Email

[cbrsix]

I have the following code and it is telling me that an item is not found is this collection. this is coming up on the recipient = rst![Business Email Address] line

Private Sub EmailRRCOA_Click()
On Error GoTo SendEmail_Err
    Dim myOlApp As Object
    Dim myNameSpace As Object
    Dim myFolder As Object
    Dim myItem As Object
    Dim myAttachments, myRecipient As Object
    Dim recipient As String
    Dim file_name As String
    Dim mySubject As Object
    Dim dbs As Object
    Dim rst As Object
    Dim strSQL As String
    
    'Select the Query where you want your information to be drawn from
    strSQL = "ClientChangeOfAddress"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveFirst
    While Not rst.EOF
        'This is the email address that you corresponds to your recipient
        recipient = rst![Business Email Address]
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItem(olMailItem)
        Set myAttachments = myItem.Attachments
        Set myRecipient = myItem.Recipients.Add(recipient)
        Set myRecipient = myItem.Recipients.Add("example@example.com")
        myRecipient.Type = olCC
        '"Message Subject String Here"
        myItem.Subject = "Client Change of Address"
        '"Put Message Body Text Here"
        myItem.Body = rst![Rep FName] & "," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Below is an address change we received for your client, " & rst!ClientName & "." & Chr(10) & Chr(13) & "Old Address:" & Chr(10) & "OldAddressHere" & Chr(10) & Chr(13) & "New Address:" & Chr(10) & "NewAddressHere"
        myItem.Display
        rst.MoveNext
    Wend
    DoCmd.Close acForm, "ClientChangeOfAddress" 'Closes the form
    Set myRecipient = Nothing
    Set myAttachments = Nothing
    Set myItem = Nothing
    Set myOlApp = Nothing
    Set rst = Nothing
SendEmail_Exit:
    Exit Sub

here is a copy of the queries recordsource


I can't figure out how to fix it and/or why it's giving me this error.

[June7]

Maybe:

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Otherwise, looks fine to me but can't remember if I have never used an aggregate query as datasource for a recordset. Will the code read any of the fields of the recordset? Try a Debug.Print before the problem line.

[RuralGuy]

I'm going to suggest:

Set rst = dbs.OpenRecordset("ClientChangeOfAddress", dbOpenDynaset)

[cbrsix]

Adding the dbOpenDynaset didn't work. It came back with the same error on the [Business Email Address] line.

[RuralGuy]

Did you try my suggestion?

[cbrsix]

Yes. I tried both.

[RuralGuy]

Care to post your db so we can look at it? Remove any sensitive data of course. Compact and Repair and then ZIP it up before posting.

[cbrsix]

Let me elaborate a little further about this project of mine. I realized when looking at the code to email that it will email to everyone in the query (ClientChangeOfAddress). This is not actually what I want. I have a client address change form and the button (EmailRRCOA) on the form. What I need to happen is to send the email to the Rep for the specific client that is on the current form. I know how to open a report or form based on information using me.dirty (see below) but do not know how to add that aspect to the current code or if it's even possible with the current code. I'm thinking that I won't need the above mentioned query anymore. Any suggestions?

Dim strWhere As String
If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If
If Me.NewRecord Then 'Check these is a record to print
MsgBox "Select a record to print"
Else
strWhere = "ID = " & Me.ID
DoCmd.OpenReport "NewCOAEnvelope", acViewPreview, , strWhere
End If

This is for a button I have on the same form to open a report.

[RuralGuy]

We're trying to get past the error you are having first. You've defined almost everything as Objects where I would have written:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

I do not know if that will make any difference.

[cbrsix]

I tried your suggestion and it didn't make a difference. It still comes back with the same error. I mentioned the second code structure because I didn't know if that would change the thinking on my current email code.

[RuralGuy]

I'm not figuring out what is causing the Collection error. All I can think of now is to play with the actual file. Sorry.