Sub Form data into outlook

[Ade F]

I have returned to this good ole faithful forum in the hope that somone could assist in my technical stumbling block. It has been years since my last visit but upon returning I still see the pro names with massive reply counts. My hat's off to those who are doing this as a profession and still have time for people such as myself.

Having cobbled together the code below from other sources I have managed to get an email on click output to outlook.

The DB has two tables

tblMain_Bird
tblBird_Detail

In the bird detail I'm planning on recording various types of birds and numbers

tblMain_Bird - Main tbl


tblBird_Detail
BirdID - Autonumber
BirdDescription - Text
BirdNumber - Number

The recorded output would end up ebing

Bird Description - Quantity

My question is this, the information below prints into the tblMain_Bird information great but the sub form that records bird description and quantity has continuous forms. How would I go about printing into the email body the tclbird recorded detail?. Is there a way of looping through the sub forma and printing it out to the stText? (or something similar via another var).

This is a little confusing and any help would be greatly appreciated. Essentially this will allow me to record the birds via the main / sub form and easily email the data to my recipient on a weekly basis. I'm sure this example could be used for a multitude of applications so I'm hoping this may help others should a suitable solution be found. If an example file exists in a better format then please do say I'm not proud to put this to one side and take advice from a new avenue.

Regards

Ade

---------------------------------------------

Private Sub cmdMailTicket_Click()
On Error GoTo Err_cmdMailTicket_Click

Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stTicketID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Combo of names to assign ticket to
stWho = Me.cboAssignee
stWhere = "tblMain_Bird.strUserID = " & "'" & stWho & "'"
'-- Looks up email address from TblUsers
varTo = DLookup("[strEMail]", "tblMain_Bird", stWhere)

stSubject = "Bird Survey"

stTicketID = "123456"

RecDate = Me.txtDateReceived
'-- Helpdesk employee who assigns ticket
'strHelpDesk = Me.cboReceivedBy.Column(1)

stText = "Here are the bird findings for this month." & Chr$(13) & Chr$(13) & _
"The total number of birds spotted this month is " & stTicketID & Chr$(13) & Chr$(13) & _
"Best Regards."

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next


Exit_cmdMailTicket_Click:
Exit Sub

Err_cmdMailTicket_Click:
MsgBox Err.Description
Resume Exit_cmdMailTicket_Click

End Sub

 

[DavidAtWork]

one question first: does tblMain_Bird hold types of birds and tblBird_Detail record the details of individual birds of which there a many of each type? If not and the realtionship between tblBird_Detail and tblMain_Bird is 1 to 1, I'd question why the need for 2 tables.
Anyway assuming the former, you probably need to open a recordset of tblBird_Detail WHERE tblBird_Detail.bird_id = tblMain_Bird.bird_id (which will be the bird in your main form)
Use the fields collection to get the record's values and assign the values to your stText. Use VbCrLf to start a new line and keep concatenating stText until you've looped through all the records. This will build up your email body text (stText).
Something like: stText = stText & VbCrLf & rs.Fields(0) & " " & rs.Fields(1) etc
David

 

[Ade F]

Cheers David,

I should have called it tblMain. It contains a date and notes/memo box for observations. I'm not duplicating data the bird details/names are only in the sub form.

Thanks you for the feedback anyway.

I may just appoach this in a different manner and convert the report to pdf via a click for ease.

As much as I like reports I also still like the speed and ease of access of plain old text. There are no attachments etc for people to junk in their email filters etc.

I will give this more thought. At the moment this is purely a drawing board exercise I'm trying to get to grips with the pricipals of getting the data out and into the outlook body text.