Email to Current Record Only

[cbrsix]

I have a change of address form with this code to open a report for the current record.

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 "ClientOldAddressLetter", acViewPreview, , strWhere
End If

I have a button that I want to have send an email to a specified person regarding the change. I have the below code to send an email and my question is how do I set it to just send for the current record only, just like the report opens for?

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

        strSql = "ClientChangeOfAddressQRY"
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strSql)
        rst.MoveFirst
        While Not rst.EOF
            recipient = "example@example.com"
            Set myOlApp = CreateObject("Outlook.Application")
            Set myItem = myOlApp.CreateItem(olMailItem)
            Set myAttachment = myItem.Attachments
            Set myRecipient = myItem.Recipients.Add(recipient)
            myItem.CC = "example@example.com"
            myItem.Subject = "Example"
            myItem.Body = "Example"
            myItem.Display
            rst.MoveNext
        Wend
        
    'DoCmd.Close acForm, "ClientChangeOfAddress"
    Set myRecipient = Nothing
    Set myAttachments = Nothing
    Set myItem = Nothing
    Set myOlApp = Nothing
    Set rst = Nothing
    
SendEmail_Exit:
    Exit Sub
SendEmail_Err:
    MsgBox Err.Description
    Resume SendEmail_Exit
End Sub

Can I put the report code in here some here? I can't seem to get it.

[June7]

With that method, would have to save the report as an image (probably PDF) file, then attach the file to the email.

DoCmd.SendObject method will automatically convert the report to PDF and attach.

Is the email address in the record on form?

recipient = Me!fieldname

Need to comment or remove code that is not needed, such as the myItem.CC

[cbrsix]

No. The email is in a query where I bring in the specific rep for the client whose address has changed.

[June7]

If you want to send only one email based on form current record, then don't loop through a recordset, which is what the sample code is designed to do.

How will the procedure get the email address if it's not in the form record? Do you want to do a DLookup in the code?

[cbrsix]

Thanks for the DLookup suggestion, I didn't think of that. So far it is working as I want.