Getrows array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

trying to send file with email body populated by one field. Get the email
with attachment fine, but can't get the fields to display in the body.

I've tried: the following three and nothing seems to populate?

1.
Dim rs As DAO.Recordset
Dim vArray As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String

Set rs = CurrentDb.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
rs.MoveLast
vArray = rs.GetRows
strMsg = "Detailed summary report for the following docs:" & vbCrLf
strMsg = strMsg & Join(vArray)

strTo = "(e-mail address removed)"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed
Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close

2. Dim db As Database
Dim rs As Recordset
Dim data As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String
'Dim myArray As String
'myArray = Recordset.GetRows(rsDocs)

strMsg = "Detailed summary report for the following docs:" & vbCrLf
Set db = OpenDatabase("f:\users\janetb\listXP.mdb")
Set rs = db.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
Set data = rs.GetRows(0)
strMsg = strMsg & data

strTo = "(e-mail address removed)"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed
Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close
db.Close

3. Dim rs As DAO.Recordset
Dim vArray As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String
Dim intI As Integer, intJ As Integer

Set rs = CurrentDb.OpenRecordset("Select REGULAR-MD from qryFHCxlsRpt")
rs.MoveLast
vArray = rs.GetRows
strMsg = "Detailed summary report for the following docs:" & vbCrLf

' Find upper bound of second dimension.
For intI = 0 To UBound(vArray, 1)
strMsg = strMsg & vArray(intI)
Next intI


strTo = "(e-mail address removed)"
strSubject = "FHC text"
strCC = ""
DoCmd.SendObject acSendReport, "Family Practice Service Detailed
Report", "Snapshot Format", strTo, strCC, , strSubject, strMsg, False

rs.Close


Any help appreciated....
 
janetb said:
trying to send file with email body populated by one field. Get the
email with attachment fine, but can't get the fields to display in
the body.

I've tried: the following three and nothing seems to populate?
Click to expand...
....snip

Try this:

Sub test()

Dim rs As ADODB.Recordset 'not dao.recordset
Dim vArray As Variant
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMsg As String
Dim intI As Integer, intJ As Integer

Set rs = New ADODB.Recordset
rs.Open "Select REGULAR-MD from qryFHCxlsRpt", _
CurrentProject.Connection, _
adOpenForwardOnly, _
adLockReadOnly

vArray = rs.GetRows()
strMsg = "Detailed summary report for the following docs:" & vbCrLf

For intI = 0 To UBound(vArray, 2)
strMsg = strMsg & vArray(0, intI) & vbCrLf
Next intI
rs.Close

...

End Sub


HTH
 
Thanks so much, Jorg (sorry for the type difference)

Tried it but got a error stop on the "Set rs = New ADODB.Recordset" line
saying user-defined type not defined.

Tried adding the following one at a time, but got the same error:
Dim rs As ADO.Recordset
Dim rs As ADODB.Recordset
 
Finally found a combination that worked! Thanks for your reply. Here it is:
strMsg = "Detailed summary report for the following docs: " & vbCrLf

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "qryFHCxlsRpt", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Do While Not rs.EOF
strMsg = strMsg & rs![REGULAR-MD].Value & vbCrLf
rs.MoveNext
Loop
 
Back
Top