Send Continuous Form Records By Email 1

[DomDom3]

Hi there,

I have a continuous subform containing a click button that when pressed, uses the DoCmd.SendObject object to send the records from the subform using Outlook.

The message text contains the records in the format
line 1 :field1
line 2 :field2
line 3 :field3

The problem I have is that because the messagetext is a string listing the field names, the email appears with only the first record. I cannot work out how to loop through the records of the subform so all records appear.

I presume I need to use "Do While not rstSubForm.EOF" but how can I get that into a string???
-----------

str = "Resource needs have changed or are new. Please review the data in the Integration Database." & vbCrLf _
& "Where there is a change, data is available in the SOCR to understand why the change has occurred." & vbCrLf _
& vbCrLf _
& vbCrLf _
& "Week Resources required = " & field1 & vbCrLf _
& "Week Commencing = " & field2 & vbCrLf _
& "Planned Heads = " & field3 & vbCrLf _
& "Forecasted Heads = " & field4 & vbCrLf _
& "Competency Centre = " & field5 & vbCrLf _
& "-------------------------------" & vbCrLf
DoCmd.SendObject , , acFormatRTF, "" & [Forms]![WP Detail Subform]. & "@xxxx.com", , , _
"IntDB v2.2 Project: " & [Forms]![WP Detail Subform].[Project Title] & " MVID: " & [Forms]![WP Detail Subform].[cmbSelectPWP] & " WPID: " _
& [Forms]![WP Detail Subform].[WPID] & "", str , True, False

A ny help would be appreciated.

Thanks

 

[PHV]

Why not simply send a report ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DomDom3]

I would like the details to be in the message text of the email and not as an attachment.

Can a report be pasted into messagetext??

 

[PHV]

Ah, OK.
A starting point:
Dim rs As DAO.Recordset
Set rs = Me.Recordset
str = "Resource needs have changed or are new. Please review the data in the Integration Database." & vbCrLf _
& "Where there is a change, data is available in the SOCR to understand why the change has occurred." & vbCrLf _
& vbCrLf
With rs
.MoveFirst
While Not .EOF
str = str & vbCrLf & "Week Resources required = " & field1 & vbCrLf _
& "Week Commencing = " & field2 & vbCrLf _
& "Planned Heads = " & field3 & vbCrLf _
& "Forecasted Heads = " & field4 & vbCrLf _
& "Competency Centre = " & field5 & vbCrLf _
& "-------------------------------" & vbCrLf
.MoveNext
Wend
End With
DoCmd.SendObject ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DomDom3]

PHV

Thanks for your reply. Your suggestion gets me part of the way there.

The code counts the number of records in the recordset, but then duplicates the first record equal to the record count.

Hopefully I can adjust the code so it loops through the records.

Thanks

 

[Remou]

You ask "Can a report be pasted into messagetext??". It can, after a fashion:

http://www.tek-tips.com/viewthread.cfm?qid=1376963&page=1