Auto email

[jonny27wakey]

I am in the process of developing an Ideas management database for the company which I work for.

I require an email facility driven by a Submit button from a form on Access, my problem is the email needs to be directed to the departmental manager selected on the form.

Managers details are stored in a table called Departments with following fields
Department - Manager - Email

Also a copy of the email needs to be sent to myself (Scheme Administrator)

I have made several attempts at trying to achieve the above but have been unsuccessful.

I look forward to hearing a solution

Many thanks

John

[mandanarchi]

I'm not entirely sure this is what you mean, but this works for me.
Replace '[e-mail]' with your email address, if you want to BCC instead of CC it, then move it to the next space (between the next , ,)

Private Sub SubmitButton_Click()
On Error GoTo SubmitButton_Err
 
Email= DLookup("[email]", "Departments", "[Department] =Forms![FormName]![Dept]"
 
    If IsNull(Email) = False Then
        DoCmd.SendObject , , , Email,"[e-mail]" , , "Email Subject", "Email Body", True
    Else: MsgBox "Are you sure there's an email address for this manager?.", vbInformation, "Check Email Address"
    End If
 
SubmitButton_Exit:
    Exit Sub
SubmitButton_Err:
    MsgBox Error$
    Resume SubmitButton_Exit
End Sub

Assumptions:

• A textbox called 'Dept' on form contains the value of 'Department' from the table 'Departments'

• The button is called SubmitButton

If you want to lookup the Manager too; make another DLookup with 'Manager' instead of Email.