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

C

Chief

Registered User.
Local time
, 22: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.
 
Do you know which part, specifically, doesn't work?
 
theDBguy said:
Do you know how to "step through" the code while running it?
Click to expand...
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. :)
 
theDBguy said:
Do you know how to "step through" the code while running it?
Click to expand...
When I step thru, it skips everything after I click on Yes and goes straight to vbNo., End Select End Function.
 
Chief said:
When I step thru, it skips everything after I click on Yes and goes straight to vbNo., End Select End Function.
Click to expand...
When I select no, it still moves to vbYes, which has a number "6" wierd. then down to Case vbNo which has "7"
 
Chief said:
When I select no, it still moves to vbYes, which has a number "6" wierd. then down to Case vbNo which has "7"
Click to expand...
Ok, der... got rid of iresponse and changed to txtmessage... now getting errors.
For selecting the Set rs = CurrentDb.OpenRecordset("Select * from EmployeeT_EmailWork")
 
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.
 
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()
 
MajP said:
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.
Click to expand...
Thank you,

Yes one single email based on who the project manager is where their email is listed in the EmployeeT
 
MajP said:
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()
Click to expand...
Yes that is correct,
I have a few places where I am drawing on the values of the record.
 
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?
 
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
 
MajP said:
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?
Click to expand...
Yes that is correct.

the dlookup part ("YourEmailField", .......
What does this refer to please?
 
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?
 
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:
MajP said:
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.
Click to expand...
1640048258292.png


ProjectManager coming from the jobInfoT.
 
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
 
You must log in or register to reply here.

Similar threads

A
Solved Problems with code execution following email
Replies
19
Views
628
Pat Hartman
P
A
Solved Auto email error needed if attachment file doesn't exist or is named incorrectly
Replies
3
Views
414
theDBguy
theDBguy
K
Solved Rec.MoveNext not working?
Replies
4
Views
258
kevnaff
K
S
Send pay slips to different employee email ids automatically through outlook
Replies
2
Views
666
Gasman
Gasman
J
Auto Email
Replies
3
Views
810
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom