send email

  • Thread starter Thread starter kms
  • Start date Start date
K

kms

Let's hope I can explain this clearly.

I have a form with a submit button on it. I would like for this button to
send an email to the employee's supervisor notifying them that there is a
document that needs to be approved. I have a table with all employee names,
email addresses and supervisor's name.

I can't seem to figure out the best way to program the button to send the
email to the supervisor without the user having to type the email address in
automatically. The user's name is in the table that is the record source for
the form.

I'm not familiar with coding so the command button was created with the
macro builder. Any help would be greatly appreciated.
 
Hello "kms"

I'm no expert (I ask more questions here than I answer!), but...

Use the SendObject macro action, and for the email address argument either
use Dlookup (e.g. Dlookup([companies]![supervisor_email_address],
[companies],[companyID]=[Forms]![frm_myform]![companyID]
or, if the email address happens to be on the current form, just get it
from there: [Forms]![frm_myform]![supervisor_email_address]

Don't know if it will work, but worth a try!

HTH
Leslie Isaacs (NVA)
 
Thanks leslie but it didn't work the address that was entered into Outlooks
to field was the d lookup function. Any other ideas. I'll keep trying.

Leslie Isaacs said:
Hello "kms"

I'm no expert (I ask more questions here than I answer!), but...

Use the SendObject macro action, and for the email address argument either
use Dlookup (e.g. Dlookup([companies]![supervisor_email_address],
[companies],[companyID]=[Forms]![frm_myform]![companyID]
or, if the email address happens to be on the current form, just get it
from there: [Forms]![frm_myform]![supervisor_email_address]

Don't know if it will work, but worth a try!

HTH
Leslie Isaacs (NVA)



kms said:
Let's hope I can explain this clearly.

I have a form with a submit button on it. I would like for this button to
send an email to the employee's supervisor notifying them that there is a
document that needs to be approved. I have a table with all employee names,
email addresses and supervisor's name.

I can't seem to figure out the best way to program the button to send the
email to the supervisor without the user having to type the email address in
automatically. The user's name is in the table that is the record source for
the form.

I'm not familiar with coding so the command button was created with the
macro builder. Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
Kms,

Try the syntax of the To argument like this...

=DLookup("[supervisor_email_address]","companies","[companyID]=" &
[companyID])

Like Les's answer, this too is a guess, as you haven't really given us
enough information to go on. We would really need to know where we are
supposed to find the email address of the supervisor, and how this is
related to the data that the form is based on.

--
Steve Schapel, Microsoft Access MVP


kms said:
Thanks leslie but it didn't work the address that was entered into
Outlooks
to field was the d lookup function. Any other ideas. I'll keep trying.

Leslie Isaacs said:
Hello "kms"

I'm no expert (I ask more questions here than I answer!), but...

Use the SendObject macro action, and for the email address argument
either
use Dlookup (e.g. Dlookup([companies]![supervisor_email_address],
[companies],[companyID]=[Forms]![frm_myform]![companyID]
or, if the email address happens to be on the current form, just get it
from there: [Forms]![frm_myform]![supervisor_email_address]

Don't know if it will work, but worth a try!

HTH
Leslie Isaacs (NVA)
Click to expand...
Click to expand...
 
I have a table that has all of our employees names, email addresses, user id
and supervisors names. (The supervisors are included in this table as
employees as well.)

I have a PO form, a control on the form automaically populates who the form
was created by the default value current user. I have a submit button that
is suppose to email the creater's supervisor to approve the po. I have 2
problems. First I'm trying to figure out how to get the email addresses of
the creater's supervisor from the employee table, I've played around with
queries to pull the data from the employee table but I haven't quite figured
that out yet. The second thing is I need to know what syntax to include in
the "To:" section of the send object command. This is the code I'm using thus
far, please note I'm not even sure that this will even pull the correct email
address.


Private Sub Command299_Click()
On Error GoTo SUBMIT_PO1_Err

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "PO FORM PRINT TEST", acViewPreview, "", "[PO
#]=[Forms]![PO FORM TEST]![PO #]", acNormal
DoCmd.SendObject acReport, "PO FORM PRINT TEST", "PDFFormat(*.pdf)",
"DLOOKUP([Employee_test]![Email_Address],[Employee test],[First Name]&'
'&[Last Name]=[Forms]![PO_FORM_TEST]![MANAGER])", "", "", "PURCHASE ORDER",
"", True, ""
DoCmd.Close acReport, "PO FORM PRINT TEST"
DoCmd.Close acForm, "PO FORM TEST"
DoCmd.OpenForm "PURCHASE ORDER MENU", acNormal, "", "", acEdit, acNormal


SUBMIT_PO1_Exit:
Exit Sub

SUBMIT_PO1_Err:
MsgBox Error$
Resume SUBMIT_PO1_Exit


Steve Schapel said:
Kms,

Try the syntax of the To argument like this...

=DLookup("[supervisor_email_address]","companies","[companyID]=" &
[companyID])

Like Les's answer, this too is a guess, as you haven't really given us
enough information to go on. We would really need to know where we are
supposed to find the email address of the supervisor, and how this is
related to the data that the form is based on.

--
Steve Schapel, Microsoft Access MVP


kms said:
Thanks leslie but it didn't work the address that was entered into
Outlooks
to field was the d lookup function. Any other ideas. I'll keep trying.

Leslie Isaacs said:
Hello "kms"

I'm no expert (I ask more questions here than I answer!), but...

Use the SendObject macro action, and for the email address argument
either
use Dlookup (e.g. Dlookup([companies]![supervisor_email_address],
[companies],[companyID]=[Forms]![frm_myform]![companyID]
or, if the email address happens to be on the current form, just get it
from there: [Forms]![frm_myform]![supervisor_email_address]

Don't know if it will work, but worth a try!

HTH
Leslie Isaacs (NVA)
Click to expand...
Click to expand...
Click to expand...
 
Kms,

Ok, well first of all this is a VBA procedure, which is quite a different
creature than an Access macro. So I'm glad we clarified that.

Secondly, you did not seem to see the way I used the quotes in the DLookup
function I showed you. Each of the 3 arguments of the DLookuo need to be
separately delimited with ""s.

And also the DLookup expression is missing quite a few spaces where there
need to be spaces.

But anyway since you have this in VBA, it will probably be better to assign
the email address, via the DLookup, to a String variable first, and then put
the variable into the SendObject code.

--
Steve Schapel, Microsoft Access MVP


kms said:
I have a table that has all of our employees names, email addresses, user
id
and supervisors names. (The supervisors are included in this table as
employees as well.)

I have a PO form, a control on the form automaically populates who the
form
was created by the default value current user. I have a submit button
that
is suppose to email the creater's supervisor to approve the po. I have 2
problems. First I'm trying to figure out how to get the email addresses
of
the creater's supervisor from the employee table, I've played around with
queries to pull the data from the employee table but I haven't quite
figured
that out yet. The second thing is I need to know what syntax to include in
the "To:" section of the send object command. This is the code I'm using
thus
far, please note I'm not even sure that this will even pull the correct
email
address.


Private Sub Command299_Click()
On Error GoTo SUBMIT_PO1_Err

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "PO FORM PRINT TEST", acViewPreview, "", "[PO
#]=[Forms]![PO FORM TEST]![PO #]", acNormal
DoCmd.SendObject acReport, "PO FORM PRINT TEST", "PDFFormat(*.pdf)",
"DLOOKUP([Employee_test]![Email_Address],[Employee test],[First Name]&'
'&[Last Name]=[Forms]![PO_FORM_TEST]![MANAGER])", "", "", "PURCHASE
ORDER",
"", True, ""
DoCmd.Close acReport, "PO FORM PRINT TEST"
DoCmd.Close acForm, "PO FORM TEST"
DoCmd.OpenForm "PURCHASE ORDER MENU", acNormal, "", "", acEdit,
acNormal


SUBMIT_PO1_Exit:
Exit Sub

SUBMIT_PO1_Err:
MsgBox Error$
Resume SUBMIT_PO1_Exit


Steve Schapel said:
Kms,

Try the syntax of the To argument like this...

=DLookup("[supervisor_email_address]","companies","[companyID]=" &
[companyID])

Like Les's answer, this too is a guess, as you haven't really given us
enough information to go on. We would really need to know where we are
supposed to find the email address of the supervisor, and how this is
related to the data that the form is based on.

--
Steve Schapel, Microsoft Access MVP


kms said:
Thanks leslie but it didn't work the address that was entered into
Outlooks
to field was the d lookup function. Any other ideas. I'll keep
trying.

:

Hello "kms"

I'm no expert (I ask more questions here than I answer!), but...

Use the SendObject macro action, and for the email address argument
either
use Dlookup (e.g. Dlookup([companies]![supervisor_email_address],
[companies],[companyID]=[Forms]![frm_myform]![companyID]
or, if the email address happens to be on the current form, just get
it
from there: [Forms]![frm_myform]![supervisor_email_address]

Don't know if it will work, but worth a try!

HTH
Leslie Isaacs (NVA)
Click to expand...
Click to expand...
Click to expand...
 
Steve,
Even though its in VBA let me tell ya I'm quite clueless about coding. I
actually had it in a macro first and then converted it to VBA. So what do
you mean by assigning it as a "String variable first"?

Steve Schapel said:
Kms,

Ok, well first of all this is a VBA procedure, which is quite a different
creature than an Access macro. So I'm glad we clarified that.

Secondly, you did not seem to see the way I used the quotes in the DLookup
function I showed you. Each of the 3 arguments of the DLookuo need to be
separately delimited with ""s.

And also the DLookup expression is missing quite a few spaces where there
need to be spaces.

But anyway since you have this in VBA, it will probably be better to assign
the email address, via the DLookup, to a String variable first, and then put
the variable into the SendObject code.

--
Steve Schapel, Microsoft Access MVP


kms said:
I have a table that has all of our employees names, email addresses, user
id
and supervisors names. (The supervisors are included in this table as
employees as well.)

I have a PO form, a control on the form automaically populates who the
form
was created by the default value current user. I have a submit button
that
is suppose to email the creater's supervisor to approve the po. I have 2
problems. First I'm trying to figure out how to get the email addresses
of
the creater's supervisor from the employee table, I've played around with
queries to pull the data from the employee table but I haven't quite
figured
that out yet. The second thing is I need to know what syntax to include in
the "To:" section of the send object command. This is the code I'm using
thus
far, please note I'm not even sure that this will even pull the correct
email
address.


Private Sub Command299_Click()
On Error GoTo SUBMIT_PO1_Err

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "PO FORM PRINT TEST", acViewPreview, "", "[PO
#]=[Forms]![PO FORM TEST]![PO #]", acNormal
DoCmd.SendObject acReport, "PO FORM PRINT TEST", "PDFFormat(*.pdf)",
"DLOOKUP([Employee_test]![Email_Address],[Employee test],[First Name]&'
'&[Last Name]=[Forms]![PO_FORM_TEST]![MANAGER])", "", "", "PURCHASE
ORDER",
"", True, ""
DoCmd.Close acReport, "PO FORM PRINT TEST"
DoCmd.Close acForm, "PO FORM TEST"
DoCmd.OpenForm "PURCHASE ORDER MENU", acNormal, "", "", acEdit,
acNormal


SUBMIT_PO1_Exit:
Exit Sub

SUBMIT_PO1_Err:
MsgBox Error$
Resume SUBMIT_PO1_Exit


Steve Schapel said:
Kms,

Try the syntax of the To argument like this...

=DLookup("[supervisor_email_address]","companies","[companyID]=" &
[companyID])

Like Les's answer, this too is a guess, as you haven't really given us
enough information to go on. We would really need to know where we are
supposed to find the email address of the supervisor, and how this is
related to the data that the form is based on.

--
Steve Schapel, Microsoft Access MVP


Thanks leslie but it didn't work the address that was entered into
Outlooks
to field was the d lookup function. Any other ideas. I'll keep
trying.

:

Hello "kms"

I'm no expert (I ask more questions here than I answer!), but...

Use the SendObject macro action, and for the email address argument
either
use Dlookup (e.g. Dlookup([companies]![supervisor_email_address],
[companies],[companyID]=[Forms]![frm_myform]![companyID]
or, if the email address happens to be on the current form, just get
it
from there: [Forms]![frm_myform]![supervisor_email_address]

Don't know if it will work, but worth a try!

HTH
Leslie Isaacs (NVA)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top