Need Help with Code to Email from Form

**MGFoster** · Nov 12 '05, 03:17 PM

Re: Need Help with Code to Email from Form

-----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/AwUBP4r3LIechKq OuFEgEQImNQCfdi YZ016v8h9UTnT7R LkD6ov7174An06V
PIPy/+g6D/TKrSfAe9jPhwqS
=JzP/
-----END PGP SIGNATURE-----

jdph40@yahoo.co m wrote:
[color=blue]
> 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.Item sSelected
> strSQL = strSQL & Me!EmpList.Item Data(varSelecte d) & ","
> Next varSelected
> If strSQL <> "" Then
> strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
> Len(strSQL) - 1) & ")"
> DoCmd.OpenForm "frmVacationWee ks", acViewNormal, , strSQL
>
> I have a textbox on the frmVacationWeek s 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[/color]

**Pieter Linden** · Nov 12 '05, 03:17 PM

Re: Need Help with Code to Email from Form

jdph40@yahoo.co m (jdph40@yahoo.c om) wrote in message news:<7580afc1. 0310130757.480b dfad@posting.go ogle.com>...[color=blue]
> 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.Item sSelected
> strSQL = strSQL & Me!EmpList.Item Data(varSelecte d) & ","
> Next varSelected
> If strSQL <> "" Then
> strSQL = "[Employees].[EmployeeNumber] IN (" & Left(strSQL,
> Len(strSQL) - 1) & ")"
> DoCmd.OpenForm "frmVacationWee ks", acViewNormal, , strSQL
>
> I have a textbox on the frmVacationWeek s 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[/color]

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@yahoo.com** · Nov 12 '05, 03:19 PM

Re: Need Help with Code to Email from Form

pietlinden@hotm ail.com (Pieter Linden) wrote in message news:<bf31e41b. 0310131255.b092 e8c@posting.goo gle.com>...[color=blue]
>
> 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.[/color]

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** · Nov 12 '05, 03:19 PM

Re: Need Help with Code to Email from Form

jdph40@yahoo.co m (jdph40@yahoo.c om) wrote in message news:<7580afc1. 0310140534.7845 2d38@posting.go ogle.com>...[color=blue]
> pietlinden@hotm ail.com (Pieter Linden) wrote in message news:<bf31e41b. 0310131255.b092 e8c@posting.goo gle.com>...[color=green]
> >
> > 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.[/color]
>
> 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[/color]

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.SendObjec t 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@yahoo.com** · Nov 12 '05, 03:20 PM

Re: Need Help with Code to Email from Form

jdph40@yahoo.co m (jdph40@yahoo.c om) wrote in message news:<7580afc1. 0310140534.7845 2d38@posting.go ogle.com>...[color=blue]
> pietlinden@hotm ail.com (Pieter Linden) wrote in message news:<bf31e41b. 0310131255.b092 e8c@posting.goo gle.com>...[color=green]
> >
> > 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.[/color]
>
> 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[/color]

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.SendObjec t 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

Need Help with Code to Email from Form

Need Help with Code to Email from Form

Comment

Comment

Comment

Comment

Comment