Send a Recordset as Excel file via DoCmd.SendObject

Hello,

Is it even possible to use the Recordset as part of the DoCmd.SendObject method?

I have a recordset that returns some rows. I check that recordset to make sure that the rowcount > 0. If so, I want the rows output to Excel and sent to a user.

doing a docmd.sendobject acSendTable, recordsetname, etc, etc, etc  does not work. Nor does using acSendQuery. Nor does using either of those with the actual SQL string that populates the recordset.

Here's my code....

'// Refresh the form
Form.Refresh

'// Check to see if there is a value in the txtCloseLocationAgentNumber field
'// If not, let the user know and exit this Sub
Dim strAgentNumberClose As String
strAgentNumberClose = txtCloseLocationAgentNumber.Value
If strAgentNumberClose = Null Or strAgentNumberClose = "" Then
    MsgBox "The Agent # field is blank"
    Exit Sub
End If

Dim strSQL As String
strSQL = "SELECT tblU778.DateCreation AS [CREATION DATE]," _
& "tblU778.EffectiveDateOfChange AS [DATE OF CHANGE]," _
& "tblRegion.RO AS REGION," _
& " tblU778.CloseLocationAgentNumber AS [AGENT NUMBER]," _
& "tblRegion.NAME," _
& "tblRegion.LASTNAME AS [AGENT LAST NAME]," _
& "tblRegion.FIRSTNAME AS [AGENT FIRST NAME]," _
& "tblRegion.STATE & tblregion.loc AS [BUILDING CODE]," _
& "tblRegion.AGTTYPE AS [AGENT TYPE]," _
& "tblRegion.AGSTATUS AS [AGENT STATUS]," _
& "tblRegion.ADDRESS AS ADDRESS," _
& "tblRegion.CITY AS CITY," _
& "tblRegion.STATE AS STATE," _
& "tblRegion.ZIP AS ZIP," _
& "tblRegion.PHONE AS [PHONE NUMBER]," _
& "tblRegion.MARKET," _
& "tblRegion.TERR AS TERRITORY," _
& "tblRegion.EMPDT AS [AGENT HIRE DATE]," _
& "tblU778.CloseLocationAgentTermDate AS [TERMINATION DATE], ""A0"" & tblu778.CloseLocationAgentNumber & ""@anywhere.com"" AS EMail," _
& "'' AS [FAX NUMBER]," _
& "'No' AS [NAE ELIGIBILITY]," _
& "'' AS [TELEPHONE PROVIDER]" _
& "FROM tblU778 INNER JOIN tblRegion ON tblU778.CloseLocationAgentNumber = tblRegion.AGTNUM " _
& "WHERE tblU778.U778ID= '" & Me.txtU778ID.Value & "'" _
& "ORDER BY tblU778.DateCreation DESC;"

'// Declare a recordset to get the output of the query
Dim rst As New ADODB.Recordset
Dim lngRecords As Long
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
Do Until rst.EOF
 'Debug.Print "Looping"
 rst.MoveNext
Loop


'// See how many records are in the recordset
lngRecords = rst.RecordCount
MsgBox "There are " & lngRecords & " records"
If lngRecords = 0 Then

>>>THIS IS THE PROBLEM AREA
DoCmd.SendObject acSendTable, strSQL, "MicrosoftExcelBiff8(*.xls)", "john.doe@anywhere.com", , , "Anywhere Update for Agent Number:" & strAgentNumberClose, "Attached is an Anyone file. Any questions, please let us know.", False, ""
chkKetchumNotifiedTerm.Value = True
MsgBox "Ketchum has been notified of the term of Agent Number " & strAgentNumberClose
Form.Requery
Else
    MsgBox "Your query found Zero records! Check to make sure that this Agent # exists in Table1"
    Exit Sub
End If

Any help that you could provide would be greatly appreciated.Start Free Trial