Continuous Form into an email

[Griff389]

I've got a form with a cotinuous form as a subform.

I've got an e-mail button which creates an e-mail based on details from the form.

How can I go about adding in the details of the subform into my e-mail. Obviously it would only contain records relevant to the main form.

The code for my e-mail is:

Dim strBody As String
Dim olApp As Object
Set olApp = CreateObject("Outlook.Application")
    
If MsgBox("Do you wish to e-mail this report to someone?", vbYesNo + vbQuestion, "E-mail?") = vbYes Then
            strBody = "CANFORD SERVICE REPORT - QUICK E-MAIL" & Chr(13)
            strBody = strBody & Chr(13)
            strBody = strBody & "Return No: " & ReturnNo & Chr(13)
            strBody = strBody & "Company Name: " & CompanyName & Chr(13)
            strBody = strBody & "Received From: " & NameOfContact & Chr(13)
            strBody = strBody & "Date Received: " & DateReceived & Chr(13)
            strBody = strBody & "Telephone No: " & TelephoneNo & Chr(13)
            strBody = strBody & "New Sales Reference: " & NewSalesReference & Chr(13)
            strBody = strBody & "Current Location: " & CurrentLocation & Chr(13)
            strBody = strBody & Chr(13)
            strBody = strBody & "Customers Comments: " & Chr(13) & CustomersComments & Chr(13)
            strBody = strBody & Chr(13)
            strBody = strBody & "Action Taken: " & Chr(13) & ActionTaken & Chr(13)
            
            Set olMail = olApp.CreateItem(olMailItem)
            olMail.Subject = "Canford Service Report - " & ReturnNo & ", " & ReceivedFrom
            olMail.Body = strBody
            olMail.Display
        
        End If

Thanks
Griff

 

[OnTheFly]

I would do it by using the recordset clone property of the subform and looping through the records for the values. The code would look something like this:

Dim rstInfo As DAO.Recordset

Set rstInfo = Me.SubFormControlName.Form.RecordsetClone
If rstInfo.EOF=False Then
rstInfo.MoveFirst
Do Until rstInfo.EOF
strBody = strBody & "Field 1=" & rstInfo!Field1Name & vbCRLF
strBody = strBody & "Field 2=" & rstInfo!Filed2Name & vbCRLF
rstInfo.MoveNext
Loop
End If
rstInfo.Close
Set rstInfo=Nothing

If you are using Access 2000 and have not added the DAO reference yet you will need to do this first for this to work.


Hope this helps.

OnTheFly

 

[Griff389]

Thanks,

I've got this on a button, and it only works the first time you press the button. If you move to another record in the database and press it, the results in the e-mail are blank. Or evne if you just press it a second time for that matter.
If you close the form, then open it up and press it, (or navigate to the record you want and then press the button) it works, but only once.

Any ideas?
(It's Access 2002 incidently).

Regards

Griff

 

[Griff389]

This works now, I changed the 'if' statement to:

If rstInfo.RecordCount > 0 Then

It was falling over by checking for EOF, which it was after the first run. This way it doesn't check for that, and also solved a prolem where it fell over if there weren't any records in the subform.

Thanks again.
Regards

Griff

As a side query, is there any way to edit an old post on here, I can't see any edit button/link anywhere.