asked on

Send email on afterupdate event of a combobox

I'm trying to write code to send an email (preview first) when a combobox has been updated. I can't get it to work.

I have this code in the afterupdate event of the combobox:

SendEmailOutlook Me.txtEmail, "New Client Alert", "You have been assigned a new client named " & Me.txtFirstName & " " & Me.txtLastName & ".", False, False

Open in new window

And I have this code in a module:

Public Sub SendEmailOutlook(strTo As String, strSubject As String, strBody As String, _
    strFile As String, Optional bFileAttach As Boolean = False, Optional bPreview As Boolean = False)
    
    On Error GoTo SendEmailOutlookErr
    
    Dim strEmail As String
    Dim strMsg As String
    Dim oLook As Object
    Dim oMail As Object
    
    Set oLook = CreateObject("Outlook.Application")
    Set oMail = oLook.createitem(0)
    With oMail
        '.ReplyRecipients.Add "ben@accessdatabasetutorial.com"
        .to = strTo
        '.body = sBody
        .htmlbody = strBody
        .Subject = strSubject
        If strFile <> "" Then
            .Attachments.Add (strFile)
        End If
        If bFileAttach = True Then
            'THIS IS WHERE YOU CODE YOUR FORM TO ATTACH FILE(S)...
            '.Attachments.Add (CurrentProject.Path & "XYZ.XXX")
        End If
        If bPreview = True Then
            .display
        Else
            .Send
        End If
    End With
    
    If bPreview = False Then
        Set oMail = Nothing
        Set oLook = Nothing
    End If
    Exit Sub
    
SendEmailOutlookErrExit:
        Exit Sub
    
SendEmailOutlookErr:
        MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
        Resume SendEmailOutlookErrExit
End Sub

Open in new window

Can someone tell me what I'm doing wrong?

Jim Dettman (EE MVE)

What happens?

Jim.

SteveL13

ASKER

I get this:

Cannot find this file. Verify the path and file name are correct.

I'm not even send a file. ???

PatHartman

What is in strFile? What is the point of bFileAttach if you attach a file prior to checking the Boolean?

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

PS, I would not send the email from the control level event. I would use the form's AfterUpdate event. That way you know the change has actually been committed. If you send it in the control's AfterUpdate event, the record save could still be cancelled and the update won't happen. To use the Form's AfterUpdate event, you will need a global variable or preferably a TempVar. Set the TempVar to False in the Form's Current event. In the control's AfterUpdate event, set the TempVar to True. In the Form's AfterUpdate event, check the TempVar and send the email if necessary. Then reset the TempVar to False.