Need Help with Code to Email from Form

[jdph40]

I have a form in Access 97 on which I have an unbound list box filled
with our employees names from tblEmployees. When you select an
employee's name and click a button, the following code is run and
another form opens with only that employee's data:

For Each varSelected In Me!EmpList.ItemsSelected
strSQL = strSQL & Me!EmpList.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL

I have a textbox on the frmVacationWeeks called txtEmail. I need to
be able to click a button on this form that will automatically email a
report called rptApprovedVac only to the employee whose email address
is in txtEmail. I will appreciate any help you can give me on this.

Thanks,
JD

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the SendObject method of the DoCmd object - see the help topic
SendObject.

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4r3LIechKqOuFEgEQImNQCfdiYZ016v8h9UTnT7RLkD6o v7174An06V
PIPy/+g6D/TKrSfAe9jPhwqS
=JzP/
-----END PGP SIGNATURE-----

jd****@yahoo.com wrote:

I have a form in Access 97 on which I have an unbound list box filled
with our employees names from tblEmployees. When you select an
employee's name and click a button, the following code is run and
another form opens with only that employee's data:

For Each varSelected In Me!EmpList.ItemsSelected
strSQL = strSQL & Me!EmpList.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL

I have a textbox on the frmVacationWeeks called txtEmail. I need to
be able to click a button on this form that will automatically email a
report called rptApprovedVac only to the employee whose email address
is in txtEmail. I will appreciate any help you can give me on this.

Thanks,
JD

[Pieter Linden]

jd****@yahoo.com (jd****@yahoo.com) wrote in message news:<75**************************@posting.google. com>...

I have a form in Access 97 on which I have an unbound list box filled
with our employees names from tblEmployees. When you select an
employee's name and click a button, the following code is run and
another form opens with only that employee's data:

For Each varSelected In Me!EmpList.ItemsSelected
strSQL = strSQL & Me!EmpList.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
Len(strSQL) - 1) & ")"
DoCmd.OpenForm "frmVacationWeeks", acViewNormal, , strSQL

I have a textbox on the frmVacationWeeks called txtEmail. I need to
be able to click a button on this form that will automatically email a
report called rptApprovedVac only to the employee whose email address
is in txtEmail. I will appreciate any help you can give me on this.

Thanks,
JD

Either use the SendObject command or automate Outlook. (If you're
only mailing to one person at a time, why are you using a listbox?)
You could just loop through the ItemsSelected collection of the
listbox and inside the loop use SendObject command. Nothing doing.

[jdph40]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf*************************@posting.google.c om>...

Either use the SendObject command or automate Outlook. (If you're
only mailing to one person at a time, why are you using a listbox?)
You could just loop through the ItemsSelected collection of the
listbox and inside the loop use SendObject command. Nothing doing.

I developed a Vacation database and I am using the listbox on a form
that has several options - when an employee's name is selected in the
listbox, one can view and print reports with only that employee's
data, and open a form on which the employee can post dates he wants to
take off from work. On another form, after an employee's name is
selected in the listbox, the supervisor can approve or reject dates
the employee has requested to take off. It is on this form that I
need a button I can have the supervisors click to automatically email
the employee whose name appears on the form. I used SendObject, but
when I used this, I had to manually type the employee's name in the
"To" line of message box. My boss wants this automated - he doesn't
want the supervisors to have to type the employee's name. Any
suggestions?

Thanks,
JD

[Pieter Linden]

jd****@yahoo.com (jd****@yahoo.com) wrote in message news:<75**************************@posting.google. com>...

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf*************************@posting.google.c om>...

Either use the SendObject command or automate Outlook. (If you're
only mailing to one person at a time, why are you using a listbox?)
You could just loop through the ItemsSelected collection of the
listbox and inside the loop use SendObject command. Nothing doing.

I developed a Vacation database and I am using the listbox on a form
that has several options - when an employee's name is selected in the
listbox, one can view and print reports with only that employee's
data, and open a form on which the employee can post dates he wants to
take off from work. On another form, after an employee's name is
selected in the listbox, the supervisor can approve or reject dates
the employee has requested to take off. It is on this form that I
need a button I can have the supervisors click to automatically email
the employee whose name appears on the form. I used SendObject, but
when I used this, I had to manually type the employee's name in the
"To" line of message box. My boss wants this automated - he doesn't
want the supervisors to have to type the employee's name. Any
suggestions?

Thanks,
JD

Say you have a combobox or a listbox with two columns. One shows the
employee's name, and the other shows the employee's e-mail address.
Then you could just pass the proper column value to the SendObject
function, and you should be ready to go. Something kinda like this...
without all the message box rubbish (just to check that I can see all
this stuff...)

Private Sub Command4_Click()
Dim strName As String
Dim strEMail As String

strName = Me.List2.Column(0)
strEMail = Me.List2.Column(1)

MsgBox "Name: " & strName & vbCrLf & "E-Mail:" & strEMail,
vbOKOnly
DoCmd.SendObject acSendReport, "In House for Pickup",
acFormatHTML, strEMail, , , "See attached Report", "Like my report?"
End Sub

Column(0) is visible (width is non-zero)
Column(1) is not visible (width=0)

Then you just grab the e-mail addresses from a query or table...

SELECT FName & " " & LName As StaffName, EMailAddress
FROM Staff
ORDER BY FName, LName;

or whatever you want...

HTH,
Pieter

[jdph40]

jd****@yahoo.com (jd****@yahoo.com) wrote in message news:<75**************************@posting.google. com>...

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf*************************@posting.google.c om>...

Either use the SendObject command or automate Outlook. (If you're
only mailing to one person at a time, why are you using a listbox?)
You could just loop through the ItemsSelected collection of the
listbox and inside the loop use SendObject command. Nothing doing.

I developed a Vacation database and I am using the listbox on a form
that has several options - when an employee's name is selected in the
listbox, one can view and print reports with only that employee's
data, and open a form on which the employee can post dates he wants to
take off from work. On another form, after an employee's name is
selected in the listbox, the supervisor can approve or reject dates
the employee has requested to take off. It is on this form that I
need a button I can have the supervisors click to automatically email
the employee whose name appears on the form. I used SendObject, but
when I used this, I had to manually type the employee's name in the
"To" line of message box. My boss wants this automated - he doesn't
want the supervisors to have to type the employee's name. Any
suggestions?

Thanks,
JD

Addendum to above post: I modified my code in the OnClick event of the
button on my form to the following (thanks to someone in this NG):
DoCmd.SendObject acSendReport, "rptReportName", "Snapshot Format",
Me.txtEMail, , , "Subject", "Message Body." This keeps me from having
to type in the employee's name - I just have to click Send when the
message box pops up, but I still need to automate this so it happens
in the background and the user needs to do nothing but click the
button. Thanks in advance for any tips.

JD