Sending Email messages from Access.

[Del]

I have two question on sending email messages from MS Access (Versio
2000, 2002, and 2003). I have a form with a command button to send an
email message, the code behide the button is as follows;
=============== =============== =============== =============== =============== ==

Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = "em**********@d omain.com"
strSubject = "A New Vendor PO Has Been Entered, PO Number " &
Me![PO_Nbr]
strMessageBody = "This is to notify you that a new Vendor PO has
been entered." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "PO Number " &
Me![PO_Nbr] & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "This Email has been
generated from the Vendor Repair PO Database, Please DO NOT reply."

DoCmd.SendObjec t acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False

=============== =============== =============== =============== =============== ===
This code work fine but I would like to expand it in two ways.

First:

The way this code sits now I have to hard code the email address, or
leave
it blank and let the user enter the email address. I don't want to do
either. I want to be able to pull the email address from a table. The
email address would be linked by the "BuyCode" field. The table has
the foloowing columns "BuyerCode" , "BuyerName" , "BuyerEmail " the
form hase a field called "Buyer" which is populated via a drop down by
the user. I want to be able to take the Buyer from the form link it
back to the table, pull the email address for that buyer and insert it
in the code above.
Second:

This database is used to alter the warehouse when a new Vendor Repair
Purchase Order has been entered into our main frame system. The code
above works fine if the Buyer is entering just one Vendor Repair PO.
There are time when a buyer may enter multiple Vendor Repair POs in
the same day. Rather than have the buyer enter each PO then email it
to the warehouse, I have created a form where the buyer can enter any
number of POs. What I would also like to do with the above code is
have is pull all the PO numbers on the "Multi Vendor PO" form and
insert them into the email body. The number of POs could range from 2
to 50 or more.

Thanks in advance for any input on either of these areas.

Regards,
Del

[Pieter Linden]

dt****@yahoo.co m (Del) wrote in message news:<c2******* *************** ****@posting.go ogle.com>...

I have two question on sending email messages from MS Access (Versio
2000, 2002, and 2003). I have a form with a command button to send an
email message, the code behide the button is as follows;
=============== =============== =============== =============== =============== ==

Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = "em**********@d omain.com"
strSubject = "A New Vendor PO Has Been Entered, PO Number " &
Me![PO_Nbr]
strMessageBody = "This is to notify you that a new Vendor PO has
been entered." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "PO Number " &
Me![PO_Nbr] & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "This Email has been
generated from the Vendor Repair PO Database, Please DO NOT reply."

DoCmd.SendObjec t acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False

=============== =============== =============== =============== =============== ===
This code work fine but I would like to expand it in two ways.

First:

The way this code sits now I have to hard code the email address, or
leave
it blank and let the user enter the email address. I don't want to do
either. I want to be able to pull the email address from a table. The
email address would be linked by the "BuyCode" field. The table has
the foloowing columns "BuyerCode" , "BuyerName" , "BuyerEmail " the
form hase a field called "Buyer" which is populated via a drop down by
the user. I want to be able to take the Buyer from the form link it
back to the table, pull the email address for that buyer and insert it
in the code above.

you could use DLookup to grab the e-mail address, or you could include
the e-mail address in the controlsource of your dropdown, and then
just reference the given column... something like

Me.cboBuyer.Col umns(n)

where n is the (n-1)th column... (Column collections(?) are
zero-based) And just use that as your recipient address...

[Pieter Linden]

> Second:

This database is used to alter the warehouse when a new Vendor Repair
Purchase Order has been entered into our main frame system. The code
above works fine if the Buyer is entering just one Vendor Repair PO.
There are time when a buyer may enter multiple Vendor Repair POs in
the same day. Rather than have the buyer enter each PO then email it
to the warehouse, I have created a form where the buyer can enter any
number of POs. What I would also like to do with the above code is
have is pull all the PO numbers on the "Multi Vendor PO" form and
insert them into the email body. The number of POs could range from 2
to 50 or more.

Thanks in advance for any input on either of these areas.

Regards,
Del

use a query to return all the values you want, then loop through them,
concatentating what you want... see www.mvps.org/accessweb Look for
fConcatChild I think that's what you want.