Email from access

[S.Dickson]

Hi

I hope some one can help, as i am not very good on Access
I have built a database for placing orders for customers, On the Order
Form i would like a button that would send out a email to say that the
order has been placed, then another button that order has been
despatched.
I have looked on the internet for code and found sum but do not
understand it.
As the order form only has Customer Number im not sure how to code it
to look up customers email address from tblCustomers
If any one could help i would be very greatful i am able to send the
database to you if u want to have a look but i can warn you it not
great.
Thanks
Simon

[Bernard Peek]

In message <11**********************@i40g2000cwc.googlegroups .com>,
S.*******@shos.co.uk writes

Hi

I hope some one can help, as i am not very good on Access
I have built a database for placing orders for customers, On the Order
Form i would like a button that would send out a email to say that the
order has been placed, then another button that order has been
despatched.

We need a little more information. What mail software are you using. Do
you have an Outlook profile on the machine that you want to send the
mail from?

If you aren't using Outlook what mail software do you use?

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[Simon]

I use outlook 2003 abd access 2003

If u thinl you can help please email me at s.*******@shos.co.uk and i
will give u more details and can send u the database ( its not a very
good database)
Thanks so much

Simon

[salad]

S.*******@shos.co.uk wrote:

Hi

I hope some one can help, as i am not very good on Access
I have built a database for placing orders for customers, On the Order
Form i would like a button that would send out a email to say that the
order has been placed, then another button that order has been
despatched.
I have looked on the internet for code and found sum but do not
understand it.
As the order form only has Customer Number im not sure how to code it
to look up customers email address from tblCustomers
If any one could help i would be very greatful i am able to send the
database to you if u want to have a look but i can warn you it not
great.
Thanks
Simon

If you aren't needing to send any other attachments from other
applications then Docmd.SendObject is what you want.

Dim strTo As String
Dim strBody As String
Dim strSubject As String

strTo = Dlookup("EmailAddressFieldName","CustomerTableName ",_
"CustomerID = " & Me.CustomerID)
strBody = "Hello. How Are You?"
strSubject = "Test Subject"
DoCmd.SendObject acSendReport, "YourReport", acFormatTXT, _
strTo, , , strSubect, strBody, True

The above sends an email to whoever strTo is equal to. It sends a
report called YourReport. You would most likely want to send the report
that is filtered to the customer you are sending it to.

DoCmd.SendObject , , acFormatTXT, _
strTo, , , strSubect, strBody, True
will simply send an email to somebody with just subject and body, no
attachment of report or query.

Hopefully this can get you started.

[Simon]

Hi

Thanks very much for helping me the code work
There is still one more thing you might be able to help me with.
I have a subform within my order form that holds what product were
ordered with the order number and the quantity and price.

Is there any way how to get the information with in the subform to
apear with in the body of the email text

As i rather not have an attachment on the email
If you need me to expalin it metter give me an email as
s.*******@shos.co.uk

[Ted]

The strBody mentioned in the code from Salad is where you would put the
information from the sub form.
You want to write the body of the message and retrieve the information
about each item ordered and enter the information into the body of your
email.

Note: The vbCrLf you will see in this code performs a Carriage Return
and Line Feed - like pressing Enter on your keyboard.

You could build it similar to this:

'This first part sets up to allow you to read the details of the items
ordered
Dim CRS1 As ADODB.Recordset
Dim strSQL As String
Set CRS1 = New ADODB.Recordset
strSQL = "SELECT * FROM tblOrderDetails WHERE OrderID =" &
Forms!frmFormName!OrderID

'This part begins to build the body of the email
strBody = "Dear " & Forms!frmFormName!FirstName & ": " & vbCrLf &
vbCrLf
strBody = strBody & "Your order for the following items has been
receieved and has been shipped via "
strBody = strBody & Forms!frmFormName!ShipVia & " on " &
Forms!frmName!ShipDate & "."
strBody = strBody & vbCrLf & vbCrLf

'This part retrieves each item ordered and adds it to the body of the
email
CRS1.Open strSQL, CurrentProject.Connection, adOpenKeySet,
adLockReadOnly
While Not CRS1.EOF
strBody = strBody & CRS1.Fields("Qty") & " " &
CRS1.Fields("Item") & " @ " & CRS1.Fields("Price") & " Total: " & _
CRS1.Fields("Price") * CRS1.Fields("Qty") & vbCrLf
CRS1.MoveNext
Wend
CRS1.Close
Set CRS1 = Nothing

I hope this helps.