Look at Employee ID, look up email address from table, send email yes/no

[MajP]

You can test this by dropping into a standard module. Then you can call it from the immediate window or another function
debug.print GetEmail(1)

should return the email for employeeID 1 if I got the table and field names correct.

Public Function GetEmail(empID As Long) As String
  If DCount("*", "EmployeeT", "employeeID = " & empID) = 0 Then
    MsgBox "No such employeeID found"
  Else
    GetEmail = Nz(DLookup("EmailWork", "EmployeeT", "EmployeeID = " & empID), "No Email Listed")
  End If
End Function

 

[MajP]

If your code exists in the form module, you can refer to any field simply by its name
"Hello " & TxtProjectManager & ",<p>" &
You can get any property to include controls and fields by using intellisense.
Me refers to properties of the object. So if you type "Me." intellisense comes up. So often you see "Me." before the field or control name.
If you are referencing something on another form you have to reference that form
forms("theFormName").controlName

You are passing in a form name. You only would need to do that if this code is not on that forms code. If that is the case a lot of things have to change. Is this code in the Form's code.

 

[Chief]

I think i have fixed one of the errors, getting error on this now.

Updated Code...

' Ask if to send email to Project Manager, Yes: Look up PM and get email.
' Check for email first, if empty open PM contact record to enter email.
Public Function PmEmail(form_name As String)
    Dim txtmessage As String
    Dim Msg As String
    Dim email As String
    Dim rs As Recordset
    Dim O As Outlook.Application
    Dim m As Outlook.MailItem

        txtmessage = MsgBox("Do you wish to notify Project Manager of date change?", vbYesNo, "Email Project Manager")
            Select Case txtmessage
                Case vbYes:
                    ' Check if email address in EmployeeT for PM
'                    Set rs = CurrentDb.OpenRecordset("Select * from EmployeeT_EmailWork")
                    Msg = "Hello " & Forms(form_name).TxtProjectManager & ",<p>" & _
                        "Please be advised of date change for Job# " & Forms(form_name).TxtJobNumber & ", Entry ID " & Forms(form_name).JobID & ",<p>" & _
                        "Job Reference " & Forms(form_name).TxtReference & ", New Delivery date " & Forms(form_name).TxtCustomerPreferredDate & ", Thank you."

                    Set O = New Outlook.Application
                    Set m = O.CreateItem(olMailItem)

                    With m
                        .BodyFormat = olFormatHTML
                        .HTMLBody = Msg
                        .To = GetEmail(Forms!form_name.TxtProjectManager)
                        '.CC = ""
                        '.BCC = ""
                        .Subject = "Job# " & Forms(form_name).TxtJobNumber & ", Entry ID " & Forms(form_name).JobID & ", Notification of Date Change " & Now()
                        .Display
                        '.Send
                    End With

                    Set m = Nothing
                    Set O = Nothing

                Case vbNo:

            End Select

End Function

 

[Chief]

If your code exists in the form module, you can refer to any field simply by its name
"Hello " & TxtProjectManager & ",<p>" &
You can get any property to include controls and fields by using intellisense.
Me refers to properties of the object. So if you type "Me." intellisense comes up. So often you see "Me." before the field or control name.
If you are referencing something on another form you have to reference that form
forms("theFormName").controlName

You are passing in a form name. You only would need to do that if this code is not on that forms code. If that is the case a lot of things have to change. Is this code in the Form's code.

Thank you!
I think nearly there, the .To = is this where I call the getemail function?
If so I dont know how.

thanks.

 

[MajP]

This works for me assuming the txtboxes referenced are on the form and this code is in the forms module.
I do not see the need to pass in a form name. This code is not generic to be used ofor other forms it appears specific to one form with all of the listed textboxes. Unless another form has all of these same fields and textboxes.

Public Function PmEmail()
    'This code appears to be in the form's module so passing a form name in makes no sense.  It is not a generic code to work with other forms
    
    Dim txtmessage As String
    Dim Msg As String
    Dim email As String
    Dim rs As Recordset
    Dim O As Outlook.Application
    Dim m As Outlook.MailItem

        If MsgBox("Do you wish to notify Project Manager of date change?", vbYesNo, "Email Project Manager") = vbYes Then
                    ' Check if email address in EmployeeT for PM
'                    Set rs = CurrentDb.OpenRecordset("Select * from EmployeeT_EmailWork")
                    Msg = "Hello " & Me.txtProjectManager & ",<p>" & _
                        "Please be advised of date change for Job# " & Me.txtJobNumber & ", Entry ID " & Me.jobID & ",<p>" & _
                        "Job Reference " & Me.txtReference & ", New Delivery date " & Me.TxtCustomerPreferredDate & ", Thank you."

                    Set O = New Outlook.Application
                    Set m = O.CreateItem(olMailItem)

                    With m
                        .BodyFormat = olFormatHTML
                        .HTMLBody = Msg
                        .To = GetEmail(Me.txtProjectManager)
                        '.CC = ""
                        '.BCC = ""
                        .Subject = "Job# " & Me.txtJobNumber & ", Entry ID " & Me.jobID & ", Notification of Date Change " & Now()
                        .Display
                        '.Send
                    End With

                    Set m = Nothing
                    Set O = Nothing
       End If

End Function

 

[Chief]

This works for me assuming the txtboxes referenced are on the form and this code is in the forms module.
I do not see the need to pass in a form name. This code is not generic to be used ofor other forms it appears specific to one form with all of the listed textboxes. Unless another form has all of these same fields and textboxes.

Public Function PmEmail()
    'This code appears to be in the form's module so passing a form name in makes no sense.  It is not a generic code to work with other forms
   
    Dim txtmessage As String
    Dim Msg As String
    Dim email As String
    Dim rs As Recordset
    Dim O As Outlook.Application
    Dim m As Outlook.MailItem

        If MsgBox("Do you wish to notify Project Manager of date change?", vbYesNo, "Email Project Manager") = vbYes Then
                    ' Check if email address in EmployeeT for PM
'                    Set rs = CurrentDb.OpenRecordset("Select * from EmployeeT_EmailWork")
                    Msg = "Hello " & Me.txtProjectManager & ",<p>" & _
                        "Please be advised of date change for Job# " & Me.txtJobNumber & ", Entry ID " & Me.jobID & ",<p>" & _
                        "Job Reference " & Me.txtReference & ", New Delivery date " & Me.TxtCustomerPreferredDate & ", Thank you."

                    Set O = New Outlook.Application
                    Set m = O.CreateItem(olMailItem)

                    With m
                        .BodyFormat = olFormatHTML
                        .HTMLBody = Msg
                        .To = GetEmail(Me.txtProjectManager)
                        '.CC = ""
                        '.BCC = ""
                        .Subject = "Job# " & Me.txtJobNumber & ", Entry ID " & Me.jobID & ", Notification of Date Change " & Now()
                        .Display
                        '.Send
                    End With

                    Set m = Nothing
                    Set O = Nothing
       End If

End Function

Awesome Mate!
Thanks legend, I’ll make changes and report back.
Thanks so much.

 

[Chief]

This works for me assuming the txtboxes referenced are on the form and this code is in the forms module.
I do not see the need to pass in a form name. This code is not generic to be used ofor other forms it appears specific to one form with all of the listed textboxes. Unless another form has all of these same fields and textboxes.

Public Function PmEmail()
    'This code appears to be in the form's module so passing a form name in makes no sense.  It is not a generic code to work with other forms
   
    Dim txtmessage As String
    Dim Msg As String
    Dim email As String
    Dim rs As Recordset
    Dim O As Outlook.Application
    Dim m As Outlook.MailItem

        If MsgBox("Do you wish to notify Project Manager of date change?", vbYesNo, "Email Project Manager") = vbYes Then
                    ' Check if email address in EmployeeT for PM
'                    Set rs = CurrentDb.OpenRecordset("Select * from EmployeeT_EmailWork")
                    Msg = "Hello " & Me.txtProjectManager & ",<p>" & _
                        "Please be advised of date change for Job# " & Me.txtJobNumber & ", Entry ID " & Me.jobID & ",<p>" & _
                        "Job Reference " & Me.txtReference & ", New Delivery date " & Me.TxtCustomerPreferredDate & ", Thank you."

                    Set O = New Outlook.Application
                    Set m = O.CreateItem(olMailItem)

                    With m
                        .BodyFormat = olFormatHTML
                        .HTMLBody = Msg
                        .To = GetEmail(Me.txtProjectManager)
                        '.CC = ""
                        '.BCC = ""
                        .Subject = "Job# " & Me.txtJobNumber & ", Entry ID " & Me.jobID & ", Notification of Date Change " & Now()
                        .Display
                        '.Send
                    End With

                    Set m = Nothing
                    Set O = Nothing
       End If

End Function

Happy New Year....
Just updated code that you did for me, thanks again.

Seems to be working, exception the project manager when "Hello (Should be PM first name) is saying "Hello 6"
which is obviously the Employee ID.