R
Rain
I am trying to email the results of a query in the body of an email. I can't
get this code to give me the results. Any help is appreciated. Thanks
Private Function CreateMessageBody() As String
CreateMessageBody = ""
Dim cnnSamples As ADODB.Connection
Dim dbDHL As Database
Dim myRecordSet As ADODB.Recordset
Set cnnSamples = CurrentProject.Connection
Dim strDocName As String
strDocName = "qryHowManySamples"
Dim Waybill As String
Waybill = ""
Dim Sender As String
Sender = ""
Dim Receiver As String
Receiver = ""
'SQL query to retrieve data from DHL Main Data table.
Dim strSQLResult As String
strSQLResult = "SELECT [Waybill Number], Sender, Receiver, [Received
Date], [Expr1]"
strSQLResult = strSQLResult & " FROM " & strDocName & ""
strSQLResult = strSQLResult & " WHERE ((([RECEIVED DATE]) = #" &
Date & "#) And (([# Of Samples]) Is Null))"
Debug.Print strSQLResult
'Set record set of SQL query.
Set myRecordSet = New ADODB.Recordset
myRecordSet.Open strSQLResult, cnnSamples, adOpenForwardOnly,
adLockReadOnly
myRecordSet.MoveFirst
strResult = ""
'Loop through all records.
Do While myRecordSet.EOF = False
strResult = strSQLResult & vbCrLf & cnnSamples
myRecordSet.MoveNext
Loop
'Create the message for the body of email.
CreateMessageBody = "# " & Waybill & " from " & Sender & " to " &
Receiver & " # of samples:" & vbCrLf
myRecordSet.Close
cnnSamples.Close
Set myRecordSet = Nothing
End Function
get this code to give me the results. Any help is appreciated. Thanks
Private Function CreateMessageBody() As String
CreateMessageBody = ""
Dim cnnSamples As ADODB.Connection
Dim dbDHL As Database
Dim myRecordSet As ADODB.Recordset
Set cnnSamples = CurrentProject.Connection
Dim strDocName As String
strDocName = "qryHowManySamples"
Dim Waybill As String
Waybill = ""
Dim Sender As String
Sender = ""
Dim Receiver As String
Receiver = ""
'SQL query to retrieve data from DHL Main Data table.
Dim strSQLResult As String
strSQLResult = "SELECT [Waybill Number], Sender, Receiver, [Received
Date], [Expr1]"
strSQLResult = strSQLResult & " FROM " & strDocName & ""
strSQLResult = strSQLResult & " WHERE ((([RECEIVED DATE]) = #" &
Date & "#) And (([# Of Samples]) Is Null))"
Debug.Print strSQLResult
'Set record set of SQL query.
Set myRecordSet = New ADODB.Recordset
myRecordSet.Open strSQLResult, cnnSamples, adOpenForwardOnly,
adLockReadOnly
myRecordSet.MoveFirst
strResult = ""
'Loop through all records.
Do While myRecordSet.EOF = False
strResult = strSQLResult & vbCrLf & cnnSamples
myRecordSet.MoveNext
Loop
'Create the message for the body of email.
CreateMessageBody = "# " & Waybill & " from " & Sender & " to " &
Receiver & " # of samples:" & vbCrLf
myRecordSet.Close
cnnSamples.Close
Set myRecordSet = Nothing
End Function