E-mail a recordset 1

[PJFry]

I want to e-mail a recordset as an excel file. Here is what I have right now:

Sub Mailrs()

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim rs As New ADODB.Recordset
rs.ActiveConnection = cnn1

rs.Open "SELECT * FROM tContract"

End Sub

I know how to e-mail objects like queries, but I have never tried it with a recordset. Thoughts?

Thanks in advance,
PJ

 

[Remou]

Use the recordset to build a query and email the query.

 

[PJFry]

That is where I am stuck. I have never converted a recordset into anything like a query.

 

[AlexCuse]

PJ - How are you planning to do this? Do you want to send the query result as text?

I have a vbScript function that sends the recordset as an HTML table within an email. I don't want to post the whole thing right now, because its' vbScript and its' setup to work with SQL server and send the email using SMTP. So it might not be relevant, and the whole thing is VERY long.

If this is something you would be interested in, I can see if I can hack the 'TableString' function into VBA (It might just be a copy/paste job)

If you want to look on your own, check out this thread:
thread183-1336319
See my post dated: 20 Feb 07 11:36, in particular the function within that script called 'TableString(strQuery)'

I hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Also, at the end of the function you will need to add this:

'clean up
set oConn = Nothing
set rstResult = Nothing

I was kind of winging that, and I forgot to add these before I posted. Rest assured this code did not make it into production like that though

Good Luck!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[lordfidan]

Alternatively you can use

strsql="select * from tcontract"
Set qdf = CurrentDb.CreateQueryDef("My_Query", strSQL)
Set qdf = Nothing
docmd.SendObject acSendQuery,"My_Query",acformatxls,"My Recipient"
docmd.deleteobject acquery, "My_query"

 

[PJFry]

LF,

Perfect! Now that I look at how it is done it makes perfect sense. Thanks!

PJ