Look at Employee ID, look up email address from table, send email yes/no (1 Viewer)

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
Hello,
Firstly I know this doesn't work as I have taken bits of code from here, there, and everywhere. :D

I was hoping to get the code cleaned up and working for me :)
It needs to look at the form and specific fields to be able to gather the required information and insert into an email.

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()
    Dim txtmessage As String
    Dim iResponse As String
    Dim Msg 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 iResponse
                Case vbYes:
                    ' Check if email address in EmployeeT for PM
                    ' PM Name/ID is from form (TxtProjectManager)
                    Set rs = CurrentDb.OpenRecordset("Select * from EmployeeT_EmailWork")
                    ' PM Name/ID is from form (TxtProjectManager)
                    Msg = "Hello (TxtProjectManager),<p>" & _
                        "Please be advised of date change for Job# (TxtJobNumber), Entry ID (JobID),<p>" & _
                        "Job Reference (TxtReference), New Delivery date (TxtCustomerPreferredDate), Thank you."
                    
                    Set O = New Outlook.Application
                    Set m = O.CreateItem(olMailItem)
                    
                    With m
                        .BodyFormat = olFormatHTML
                        .HTMLBody = Msg
                        .To = "(EmployeeT_EmailWork)"
                        '.CC = ""
                        '.BCC = ""
                        .Subject = "Job# (TxtJobNumber), Entry ID (JobID), Notification of Date Change" & " " & Now()
                        .Display
                        '.Send
                    End With
                    
                    Set m = Nothing
                    Set O = Nothing
            
                Case vbNo:
                    
            End Select

End Function

Thank you.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:00
Joined
Oct 29, 2018
Messages
21,358
Do you know which part, specifically, doesn't work?
 

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
Do you know how to "step through" the code while running it?
Hey, yes I do.
But I know the code is all wrong, I don’t know how to look up fields, tables etc to get the data I need.
Also the if statement to check if the email field is empty.
I know I am asking a lot, it’s my last few hours at work and my brain is fried.
I understand if you and everyone else is busy.
I can wait till next year.

thank you. :)
 

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
Do you know how to "step through" the code while running it?
When I step thru, it skips everything after I click on Yes and goes straight to vbNo., End Select End Function.
 

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
When I step thru, it skips everything after I click on Yes and goes straight to vbNo., End Select End Function.
When I select no, it still moves to vbYes, which has a number "6" wierd. then down to Case vbNo which has "7"
 

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
When I select no, it still moves to vbYes, which has a number "6" wierd. then down to Case vbNo which has "7"
Ok, der... got rid of iresponse and changed to txtmessage... now getting errors.
For selecting the Set rs = CurrentDb.OpenRecordset("Select * from EmployeeT_EmailWork")
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:00
Joined
May 21, 2018
Messages
8,463
Are you trying to send a single email to the current record? If not what is your plan after opening the recordset? The rest of the values seem to come from the form.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:00
Joined
May 21, 2018
Messages
8,463
You seem to want to concatenate values into your strings
"Job# (TxtJobNumber), Entry ID (JobID), Notification of Date Change" & " " & Now()
The Now part is correct, but I assume you want to do the same.
"Job# " & me.TxtJobNumber & ", Entry ID " & me.JobID & ", Notification of Date Change " & Now()
 

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
Are you trying to send a single email to the current record? If not what is your plan after opening the recordset? The rest of the values seem to come from the form.
Thank you,

Yes one single email based on who the project manager is where their email is listed in the EmployeeT
 

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
You seem to want to concatenate values into your strings
"Job# (TxtJobNumber), Entry ID (JobID), Notification of Date Change" & " " & Now()
The Now part is correct, but I assume you want to do the same.
"Job# " & me.TxtJobNumber & ", Entry ID " & me.JobID & ", Notification of Date Change " & Now()
Yes that is correct,
I have a few places where I am drawing on the values of the record.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:00
Joined
May 21, 2018
Messages
8,463
To get the email from employeeID I usually make myself some stand alone functions

Code:
Public Function GetEmail(empID as long) as string
  GetEmail = nz(Dlookup("YourEmailField","EmployeeT_EmailWork","EmployeeIDField = " & empID),"Not Found")
end function

Once you put in your real field and table names you can test in the immediate window if you put this function in a standard module.

debug.print getEmail(123)
where 123 is a valid emp id.

In your code it would probably be
Code:
dim email a string
email = getEmail(me.txtProjectManager)

I assume txtProjectManager is the ID of the person to mail?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:00
Joined
May 21, 2018
Messages
8,463
Can you go to design view and post an image of your EmployeeT_EmailWork? That way can see real names and data types.

fields.jpg
 

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
To get the email from employeeID I usually make myself some stand alone functions

Code:
Public Function GetEmail(empID as long) as string
  GetEmail = nz(Dlookup("YourEmailField","EmployeeT_EmailWork","EmployeeIDField = " & empID),"Not Found")
end function

Once you put in your real field and table names you can test in the immediate window if you put this function in a standard module.

debug.print getEmail(123)
where 123 is a valid emp id.

In your code it would probably be
Code:
dim email a string
email = getEmail(me.txtProjectManager)

I assume txtProjectManager is the ID of the person to mail?
Yes that is correct.

the dlookup part ("YourEmailField", .......
What does this refer to please?
 

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
I am calling this from the forms

Code:
Private Sub TxtCustomerPreferredDate_Change()
    Call BtnUpdate_Click
    Call PmEmail("JobDetailF")
End Sub

or shall i just put the code in the form?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:00
Joined
May 21, 2018
Messages
8,463
Sorry I did not want to see the data for privacy reasons. If that is real data, I think you want to delete it. The fields in design view are fine. I meant "field names" not people names.
 
Last edited:

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
Did not want to see the data. If that is real data, I think you want to delete it. The fields in design view are fine.
1640048258292.png


ProjectManager coming from the jobInfoT.
 

Chief

Registered User.
Local time
Today, 10:00
Joined
Feb 22, 2012
Messages
156
getting error here

1640048413209.png


Code so far with your help :)

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 " & form_name.TxtProjectManager & ",<p>" & _
                        "Please be advised of date change for Job# " & form_name.TxtJobNumber & ", Entry ID " & form_name.JobID & ",<p>" & _
                        "Job Reference " & form_name.TxtReference & ", New Delivery date " & form_name.TxtCustomerPreferredDate & ", Thank you."

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

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

                    Set m = Nothing
                    Set O = Nothing

                Case vbNo:

            End Select

End Function
 

Users who are viewing this thread

Top Bottom