Trying to create an email with form data.

[simonmarkjones]

Hi I'm having trouble writing to an excel spreadsheet.
I have the following code

Dim strList As String
With Me.trainingplan.Form.RecordsetClone
.MoveFirst
While Not .EOF
strList = strList & vbCrLf & Nz(.Fields("Course"))
.MoveNext
Wend
End With

This loops to store all the values in Course on a Continuous Form.

i Now want to email this data. I tried to put this into an email as
text but it didnt take the vbCrLf and ended up as one big long line of
text. I tried an email as html as well using <BR>/ But that didnt work
either.

So now i'm trying to put this data into an excel spreadsheet first.

I'm using the following...

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strList,
"C:\Courses.xls"

i thought that i'd be able to write out the variable. But this doesnt
work.

Please someone help!
Many thanks in advance.

[Brian Wilson]

<si************@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Hi I'm having trouble writing to an excel spreadsheet.
I have the following code

Dim strList As String
With Me.trainingplan.Form.RecordsetClone
.MoveFirst
While Not .EOF
strList = strList & vbCrLf & Nz(.Fields("Course"))
.MoveNext
Wend
End With

This loops to store all the values in Course on a Continuous Form.

i Now want to email this data. I tried to put this into an email as
text but it didnt take the vbCrLf and ended up as one big long line of
text. I tried an email as html as well using <BR>/ But that didnt work
either.

So now i'm trying to put this data into an excel spreadsheet first.

I'm using the following...

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strList,
"C:\Courses.xls"

i thought that i'd be able to write out the variable. But this doesnt
work.

Please someone help!
Many thanks in advance.

By all means use an Excel spreadsheet if you prefer, but not as a
second-rate alternative. I can't see why the vbCrLf does not give you new
lines. Probably because you don't say how you are generating the e-mail or
what e-mail client you have. The simplest (and most limited) way to send an
e-mail is DoCmd.SendObject and here I have made a simple list. On my
machine with Outlook XP this generates an e-mail with three separate lines
in it.
Why not post your code?

Private Sub cmdEmail_Click()

On Error GoTo Err_Handler

Dim strList As String

strList = "One" & vbCrLf & "Two" & vbCrLf & "Three"

DoCmd.SendObject , , , , , , , strList

Exit_Handler:
Exit Sub

Err_Handler:

Select Case Err.Number

Case 2501 'E-mail cancelled - ignore this

Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number

End Select

Resume Exit_Handler

End Sub

[simonmarkjones]

Hi I'm getting there now.

I've created a query called Query Training Plan. For the field "Name"
in the criteria section i have [Enter Staff Name]

Now when i click a button on my form i call the code below. The only
problem i now have is i dont want to enter the Staff Name. I want to
just pass this to the query from the current record being viewed.
here's my code.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

sAddedtext = sAddedtext & "&Subject=" & "Training Plan"
sAddedtext = sAddedtext & "&Body=" & strList
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query
Training Plan", "C:\Test.xls"
sAddedtext = sAddedtext & "Attach=" & Chr$(34) & "C:\test.xls" &
Chr$(34)
Debug.Print strList

stext = "mailto:" & stext

If Len(sAddedtext) <> 0 Then
Mid$(sAddedtext, 1, 1) = "?"
End If

stext = stext & sAddedtext
'MsgBox stext

' launch default e-mail program
If Len(stext) Then
Call rubbish(hwnd, "open", stext, vbNullString,
vbNullString, 1)
End If
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub

[simonmarkjones]

Email client is outlook.

The none excel method looked like this...
Dim strList As String
With Me.trainingplan.Form.RecordsetClone
.MoveFirst
While Not .EOF
strList = strList & vbCrLf & Nz(.Fields("Course"))
.MoveNext
Wend
End With

sAddedtext = sAddedtext & "&Subject=" & "Training Plan"
sAddedtext = sAddedtext & "&Body=" & strList

stext = "mailto:" & stext

[Brian Wilson]

<si************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Email client is outlook.

The none excel method looked like this...
Dim strList As String
With Me.trainingplan.Form.RecordsetClone
.MoveFirst
While Not .EOF
strList = strList & vbCrLf & Nz(.Fields("Course"))
.MoveNext
Wend
End With

sAddedtext = sAddedtext & "&Subject=" & "Training Plan"
sAddedtext = sAddedtext & "&Body=" & strList

stext = "mailto:" & stext

Sorry for the delay - suddenly called away.
I know how you build up the list, but how do you generate the e-mail? In my
earlier example, I show one method using DoCmd.SendObject, but I guess you
are doing something different.
strList = "One" & vbCrLf & "Two" & vbCrLf & "Three"
DoCmd.SendObject , , , , , , , strList

With the Excel method, you can change the design of the query, so instead of
prompting you for the Staff Name parameter it reads it from the form. You
do this by changing the criteria
from [Enter the Staff Name]
to Forms![My Form]![Staff Name]
or whatever your form is called.

[Brian Wilson]

<si************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Email client is outlook.

The none excel method looked like this...
Dim strList As String
With Me.trainingplan.Form.RecordsetClone
.MoveFirst
While Not .EOF
strList = strList & vbCrLf & Nz(.Fields("Course"))
.MoveNext
Wend
End With

sAddedtext = sAddedtext & "&Subject=" & "Training Plan"
sAddedtext = sAddedtext & "&Body=" & strList

stext = "mailto:" & stext

Would you consider the DoCmd.SendObject method of sending an e-mail instead
of Call rubbish(hwnd, "open", stext, vbNullString, vbNullString, 1) as per
previous example.

Private Sub cmdEmail_Click()

On Error GoTo Err_Handler

Dim strList As String

strList = "One" & vbCrLf & "Two" & vbCrLf & "Three"

DoCmd.SendObject , , , , , , , strList

Exit_Handler:
Exit Sub

Err_Handler:

Select Case Err.Number

Case 2501 'E-mail cancelled - ignore this

Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number

End Select

Resume Exit_Handler

End Sub
With the Excel method, you can change the design of the query, so instead of
prompting you for the Staff Name parameter it reads it from the form. You
do this by changing the criteria
from [Enter the Staff Name]
to Forms![My Form]![Staff Name]
or whatever your form is called.