sending data into an email body - outlook

[webisti]

Hi



I have a query where I collect data for different vendors and their purchase orders, products deadlines etc.
Is it possible via access to generate a report and via outlook send in the email body a message where is showing the purchase order date of delivery and priority?
or can that be done via a form?¨

Any help would be appreciated.

[orange]

There are samples at
http://www.blueclaw-db.com/access_em...nd_outlook.htm

[webisti]

Still was not what I was expecting..
the send mail function is not a problem
the question is how to insert in the body of the mail the query results.
and nicely format it.

[webisti]

smth like

1. Dim MyDB As DAO.Database
2. # Dim rst As DAO.Recordset
3. #
4. # Set MyDB = CurrentDb
5. # Set rst = MyDB.OpenRecordset("Orders Qry", dbOpenForwardOnly)
6. #
7. # With rst
8. # Do While Not .EOF
9. # Mailbody = Mailbody & ![OrderID] & " | " & ![CustomerID] & " | " & ![ShippedDate] & vbCrLf
10. # .MoveNext
11. # Loop
12. # End With
13. #
14. # rst.Close
15. # Set rst = Nothing
16. #
17. # oMail.Body = Mailbody

[orange]

If your intent is to send a standard paragraph or 2, and have some fields specific to the "customer" within that paragraph, then ageneral approach could be

- create the text for the body, including unique place markers for your custom data
-read a record from the recordset (hopefully it has email addr, name etc and the custom values)
- replace the placemarkers with values from the recordset
-send the email via Outlook
-go to the next record in the recordset

[webisti]

I am trying this code
but it gives me an error
Can smb see and check what is the wrong thing:-)

Private Sub Label94_Click()
'*** error trapping - execution goes to bottom on error
On Error GoTo Err_Vendors
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim Subjectline As String
Dim strInfo As String
Dim Body As String
Dim qry_bk As Recordset
Dim rsCriteria As Recordset
Dim rec As Recordset
Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("SELECT * FROM tbl_vendorusers WHERE [email] = True", dbOpenDynaset)
rsCriteria.MoveFirst
Do Until rsCriteria.EOF
'*** create the Select query based on
' the first record in the Criteria table, if match in vendor table and the active shipment email vendor data for appointment
strSQL = "SELECT * FROM qry_bk0 WHERE "
strSQL = strSQL & "[Origin Facility Name] = '" & rsCriteria![Param] & "'"
'*** delete the previous query & find
db.QueryDefs.Delete "qry_bk"
Set qdf = db.CreateQueryDef("qry_bk", strSQL)
Set rec = db.OpenRecordset("qry_bk", dbOpenDynaset)
'*** checks to see if query returned any records, if true then email is generated, if false moves to next record

If rec.RecordCount <> 0 Then
Do Until rec.EOF
'Loop through the records and build your strInfo variable based on all the records
strInfo = strInfo & "Shipment ID: " & rec![Shipment ID] & " Lead PO: " & rec![PO] & " Pickup Date: " & rec![zz] & "" _
& " Appointment Window: Pallets: " & Chr(13)
rec.MoveNext
Loop
'Build your Body and Subject only once - shouldn't need to be done within each loop unless you wish to send an indivdual e-mail per record
Body = rsCriteria![FirstName] & "," & Chr(13) & Chr(13) & "I have pickups for the Pro#(s) below. " & Chr(13) & Chr(13) _
& Chr(13) & strInfo & Chr(10) & Chr(13) & Chr(10) _
& Chr(13) & Chr(10) & Chr(13) & "Also, can you please advise on how many pallets are there for the Shipment # as soon as possible? Even an estimate of whether or not it's 1/4, 1/2, 3/4 or Full would be great" _
& Chr(13) & "Pallet counts or estimations of the trailer space the freight will take up are very important and helpful. By having this information, we can coordinate pickups better. " _
& "Better coordination of pickup can mean less missed and will save on delays of the product." _
& Chr(13) & Chr(13) & "Thank you for your cooperation as this is a team effort" _
& Chr(13) & "RDC#xxxx CA" & Chr(13) & "Yvonne " & Chr(13) & "Phone xxx-xxx-xxxx" & Chr(13) & "Yvonne_email@email.com.com"
Subjectline = "DC Backhauls " & " " & rsCriteria![vendor] & " Pickup date: " & rec![zz] & " Shipment ID: " & rec![Shipment ID]
MsgBox "Code has read the body"
DoCmd.SendObject acSendNoObject, "", "", rsCriteria![User], "", "", [Subjectline], [Body], False, ""
MsgBox "Code made it below send object line"
Else
rsCriteria!emailed = False
MsgBox "rscriteria = false"
MsgBox strInfo

End If
rec.CLOSE
Loop
rsCriteria.CLOSE
MsgBox Err.Number
Exit_Vendors:
Exit Sub
Err_Vendors: '*** if there is an error, execution goes here
'*** if the error is the table or query missing (3265)
' then skip the delete line and resume on the next line
' Error 2501 notifies you that the SendObject action
' has been cancelled.
MsgBox rec.RecordCount
MsgBox "code is in the Err Vendor Area"

If Err.Number = 3265 Or Err.Number = 2501 Then
Resume Next
Else
'*** write out the error and exit the sub
MsgBox strInfo
MsgBox Err.Number
MsgBox "Code is in the error area"
MsgBox Err.Description
Resume Exit_Vendors
End If
End Sub

[orange]

A couple of things, and I haven't gone through your code in detail.

You might explicitly
Dim db As DAO.Database and each of your Recordsets as DAO.REcordset.

What is the exact error message? What line is the issue?
Put a breakpoint in your code and step through it. Check the values of sooome of your variables.