Emailing query results 1

[Peps]

Hi, does anyone know if it is at all possible to email the results of a query in the actual body text of a message by using the SendObject cmd?

Peps

 

[jksmi]

Hi, I'm pretty sure that the sendobject command will only send your query as an attachment - but you can write an email using outlook as an ole server. To do this you would need to use your query as a recordset and faq68-4272, by LeighMoore, will give you a good idea how to automate outlook...

HTH, Jamie
FAQ219-2884

 

[Peps]

Thanks Jamie, although I havent go round to doing anything yet, I think I can get around the problem. I'm gonna create a text box that will acccomodate all my entry's. I'm gonna write a small routine that will loop through my query and add each entry found into the stated text box.

The query is'nt gonna hold a great amount of data, so I reckon the process wont take too long.

I'm open to any better ideas if anyones got one.

Peps

 

[jksmi]

Hi, thinking about it I'm wrong - you could cheat a little!

Build up your query as a string (loop through the recordset or use your text box) and call the following...

DoCmd.SendObject , , , "some.ones@email.address", , , "your subject", sBodyText

to get the query into text from a form and mail it on a button click evet would go something like

Private Sub cmdSend_Click
Dim r As DAO.Recordset
Dim sBodyText As String

Set r = Me.RecordsetClone

sBodyText = "Field 1" & vbTab & "Field 2" & vbCrLf

While Not r.EOF
    sBodyText = sBodyText & r.Fields(0) & vbTab & r.Fields(1) & vbTab & vbCrLf
    r.MoveNext
Wend

r.Close

DoCmd.SendObject , , , "some.ones@email.address", , , "your subject", sBodyText

End Sub

HTH, Jamie
FAQ219-2884

 

[Peps]

Thanks Jamie, I havent got round to doing anything yet. Thanks for the input. Peps

 

[Peps]

Jaime, if your still there?

I can't get the the routine to work? To be honest I'm not familiar with working with DAO commands. The results are comming from a query called CREW_SELECTION, the fields are few: [Id],[Name] & [Nationality].

This is the code that dosent work:

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Crew_Selection", dbOpen)

'Dim sBodyText As String

Set r = Me.RecordsetClone

sBodyText = "Field 1" & vbTab & "Field 2" & vbCrLf

While Not r.EOF
sBodyText = sBodyText & r.Fields(0) & vbTab & r.Fields(1) & vbTab & vbCrLf
r.MoveNext
Wend

r.Close

I created a textbox called sBodyText so's I could see the results. While I understand the code setup, I'm not sure if the rst dbs.OpenRecordset cmd line is correct. Neither am I sure about the "Field 1" and "Field 2". I've tried changing the code but I don't get anything. No errors, nothing. I created a routine a longtime ago but this opened a form from where I got the data from, this time I'd like to keep it clean.

Any help appreciated.
Peps

 

[jksmi]

Hi, the recordset clone bit was just if you were calling the function from a form that had your query set as its source, so you can ignore that if you like... copy the following into a public module and run it (F5), in the immediate window (CTRL+G) you should see your table. To see how it works step through the function (F8)

Sub testBuildBody()
Dim rst As DAO.Recordset
Dim sBodyText As String

[COLOR=green]' open the query named Crew_selection as a recordset[/color]
Set rst = CurrentDb.OpenRecordset("Crew_Selection", dbOpenSnapshot)

[COLOR=green]' set some headers[/color]
sBodyText = "ID" & vbTab & "NAME" & "NATIONALITY" & vbCrLf

[COLOR=green]' loop through the recordset, adding each row as a new line (vbcrlf adds a new line feed)[/color]
While Not rst.EOF    [COLOR=green]'.eof is end of file[/color]
    sBodyText = sBodyText & rst.Fields("ID") & vbTab & rst.Fields("Name") & vbTab & rst.Fields("NATIONALITY") & vbCrLf
    rst.MoveNext
Wend

[COLOR=green]' print to the immediate window[/color]
Debug.Print sBodyText

rst.Close    [COLOR=green]' close recordset[/color]

[COLOR=green]' rest of code to send goes here[/color]

End Sub

HTH, Jamie
FAQ219-2884

 

[Peps]

Hi Jaime, Thanks for that post. After making a very small modification, I've got exactly what I was looking for.

Cheers mate.
Peps