Format output to email problem

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

I am using the code below to populate the message field of Outlook.
Although I have a Crlf command to break each line this is ignored when it
passes to outlook.

Is there a way to format this to do the line return or even put the output
in a Table format.

Thank

Les

Dim stDocName As String
Dim Rpt As String
Rpt = ""

Set rstRecips = CurrentDb.OpenRecordset("tblOrder_Details") 'table Name

If rstRecips.BOF Then 'BOF=no results from tbl
MsgBox "There Are No Orders To Send."
Else

rstRecips.MoveFirst

Do
Prcode = DLookup("strProduct_Code", "tblOrder_Details")
Prdis = DLookup("strProduct_Description", "tblOrder_Details")
PrdPrice = DLookup("curRetail_Price", "tblOrder_Details")
PrdQuan = DLookup("numQuantity", "tblOrder_Details")
PrdTot = DLookup("curTotal", "tblOrder_Details")

Rpt = Rpt & vbCrLf & "" & Prcode & " " & Prdis & " " & " £" & PrdPrice &
" Quanity" & "[ " & PrdQuan & "] " & " £" & PrdTot

rstRecips.MoveNext
Loop Until rstRecips.EOF

End If

rstRecips.Close
Set rstRecips = Nothing

'Send Email with attached doc

stDocName = "rptOrder_Report"
DoCmd.SendObject acReport, stDocName, acFormatRTF, "am email address", ,
, "Request", " " & Rpt & ""
 
Les,

This is strictly anecdotal info and I have no KB article to which I can
refer you; however, I have found that the problem goes away when you use the
ASCII character codes instead of the VB constant. Replace vbCRLF with
Chr(13) & Chr(10) and you should see the problem resolved.


hth,
 
Cheryl Thanks for that. I have now got the spacing.

Can you please take a look at the loop?

It goes through the correct number of times but only showns the first record
on each line. Cant seem to get it to work.

Dim stDocName As String
Dim Rpt As String
Rpt = ""

Set rstRecips = CurrentDb.OpenRecordset("tblOrder_Details") 'table Name


If rstRecips.BOF Then 'BOF=no results from tbl
MsgBox "There Are No Orders To Send."
Else

rstRecips.MoveFirst

Do
Prcode = DLookup("strProduct_Code", "tblOrder_Details")
Prdis = DLookup("strProduct_Description", "tblOrder_Details")
PrdPrice = DLookup("curRetail_Price", "tblOrder_Details")
PrdQuan = DLookup("numQuantity", "tblOrder_Details")
PrdTot = DLookup("curTotal", "tblOrder_Details")

Rpt = Rpt & Chr(10) & Prcode & " " & Prdis & " " & " £" & PrdPrice & "
Quanity" & "[ " & PrdQuan & "] " & " £" & PrdTot & Chr(10)

rstRecips.MoveNext

Debug.Print Rpt
Loop Until rstRecips.EOF


End If


rstRecips.Close
Set rstRecips = Nothing

Thanks

Les


Cheryl Fischer said:
Les,

This is strictly anecdotal info and I have no KB article to which I can
refer you; however, I have found that the problem goes away when you use the
ASCII character codes instead of the VB constant. Replace vbCRLF with
Chr(13) & Chr(10) and you should see the problem resolved.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Les said:
I am using the code below to populate the message field of Outlook.
Although I have a Crlf command to break each line this is ignored when it
passes to outlook.

Is there a way to format this to do the line return or even put the output
in a Table format.

Thank

Les

Dim stDocName As String
Dim Rpt As String
Rpt = ""

Set rstRecips = CurrentDb.OpenRecordset("tblOrder_Details") 'table Name

If rstRecips.BOF Then 'BOF=no results from tbl
MsgBox "There Are No Orders To Send."
Else

rstRecips.MoveFirst

Do
Prcode = DLookup("strProduct_Code", "tblOrder_Details")
Prdis = DLookup("strProduct_Description", "tblOrder_Details")
PrdPrice = DLookup("curRetail_Price", "tblOrder_Details")
PrdQuan = DLookup("numQuantity", "tblOrder_Details")
PrdTot = DLookup("curTotal", "tblOrder_Details")

Rpt = Rpt & vbCrLf & "" & Prcode & " " & Prdis & " " & " £" &
Click to expand...
PrdPrice
&
" Quanity" & "[ " & PrdQuan & "] " & " £" & PrdTot

rstRecips.MoveNext
Loop Until rstRecips.EOF

End If

rstRecips.Close
Set rstRecips = Nothing

'Send Email with attached doc

stDocName = "rptOrder_Report"
DoCmd.SendObject acReport, stDocName, acFormatRTF, "am email
Click to expand...
address",
,
, "Request", " " & Rpt & ""
Click to expand...
Click to expand...
 
Les,

I think it is the DLookUp that is the problem. Also, it is not needed, as
you have created a recordset and can read from it directly without resorting
to DLookUp.

Instead of this:

Prcode = DLookup("strProduct_Code", "tblOrder_Details")
Prdis = DLookup("strProduct_Description", "tblOrder_Details")
PrdPrice = DLookup("curRetail_Price", "tblOrder_Details")
PrdQuan = DLookup("numQuantity", "tblOrder_Details")
PrdTot = DLookup("curTotal", "tblOrder_Details")

Try this:

Prcode = rstRecips!strProduct_Code
Prdis = rstRecips!strProduct_Description
PrdPrice = rstRecips!curRetail_Price
PrdQuan = rstRecips!numQuantity
PrdTot = rstRecips!curTotal

hth,

--

Cheryl Fischer, MVP Microsoft Access



Les said:
Cheryl Thanks for that. I have now got the spacing.

Can you please take a look at the loop?

It goes through the correct number of times but only showns the first record
on each line. Cant seem to get it to work.

Dim stDocName As String
Dim Rpt As String
Rpt = ""

Set rstRecips = CurrentDb.OpenRecordset("tblOrder_Details") 'table Name


If rstRecips.BOF Then 'BOF=no results from tbl
MsgBox "There Are No Orders To Send."
Else

rstRecips.MoveFirst

Do
Prcode = DLookup("strProduct_Code", "tblOrder_Details")
Prdis = DLookup("strProduct_Description", "tblOrder_Details")
PrdPrice = DLookup("curRetail_Price", "tblOrder_Details")
PrdQuan = DLookup("numQuantity", "tblOrder_Details")
PrdTot = DLookup("curTotal", "tblOrder_Details")

Rpt = Rpt & Chr(10) & Prcode & " " & Prdis & " " & " £" & PrdPrice & "
Quanity" & "[ " & PrdQuan & "] " & " £" & PrdTot & Chr(10)

rstRecips.MoveNext

Debug.Print Rpt
Loop Until rstRecips.EOF


End If


rstRecips.Close
Set rstRecips = Nothing

Thanks

Les


Cheryl Fischer said:
Les,

This is strictly anecdotal info and I have no KB article to which I can
refer you; however, I have found that the problem goes away when you use the
ASCII character codes instead of the VB constant. Replace vbCRLF with
Chr(13) & Chr(10) and you should see the problem resolved.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Les said:
I am using the code below to populate the message field of Outlook.
Although I have a Crlf command to break each line this is ignored when it
passes to outlook.

Is there a way to format this to do the line return or even put the output
in a Table format.

Thank

Les

Dim stDocName As String
Dim Rpt As String
Rpt = ""

Set rstRecips = CurrentDb.OpenRecordset("tblOrder_Details") 'table Name

If rstRecips.BOF Then 'BOF=no results from tbl
MsgBox "There Are No Orders To Send."
Else

rstRecips.MoveFirst

Do
Prcode = DLookup("strProduct_Code", "tblOrder_Details")
Prdis = DLookup("strProduct_Description", "tblOrder_Details")
PrdPrice = DLookup("curRetail_Price", "tblOrder_Details")
PrdQuan = DLookup("numQuantity", "tblOrder_Details")
PrdTot = DLookup("curTotal", "tblOrder_Details")

Rpt = Rpt & vbCrLf & "" & Prcode & " " & Prdis & " " & " £" &
Click to expand...
PrdPrice
&
" Quanity" & "[ " & PrdQuan & "] " & " £" & PrdTot

rstRecips.MoveNext
Loop Until rstRecips.EOF

End If

rstRecips.Close
Set rstRecips = Nothing

'Send Email with attached doc

stDocName = "rptOrder_Report"
DoCmd.SendObject acReport, stDocName, acFormatRTF, "am email
Click to expand...
address",
,
, "Request", " " & Rpt & ""
Click to expand...
Click to expand...
Click to expand...
 
Thats great thanks

Les


Cheryl Fischer said:
Les,

I think it is the DLookUp that is the problem. Also, it is not needed, as
you have created a recordset and can read from it directly without resorting
to DLookUp.

Instead of this:

Prcode = DLookup("strProduct_Code", "tblOrder_Details")
Prdis = DLookup("strProduct_Description", "tblOrder_Details")
PrdPrice = DLookup("curRetail_Price", "tblOrder_Details")
PrdQuan = DLookup("numQuantity", "tblOrder_Details")
PrdTot = DLookup("curTotal", "tblOrder_Details")

Try this:

Prcode = rstRecips!strProduct_Code
Prdis = rstRecips!strProduct_Description
PrdPrice = rstRecips!curRetail_Price
PrdQuan = rstRecips!numQuantity
PrdTot = rstRecips!curTotal

hth,

--

Cheryl Fischer, MVP Microsoft Access



Les said:
Cheryl Thanks for that. I have now got the spacing.

Can you please take a look at the loop?

It goes through the correct number of times but only showns the first record
on each line. Cant seem to get it to work.

Dim stDocName As String
Dim Rpt As String
Rpt = ""

Set rstRecips = CurrentDb.OpenRecordset("tblOrder_Details") 'table Name


If rstRecips.BOF Then 'BOF=no results from tbl
MsgBox "There Are No Orders To Send."
Else

rstRecips.MoveFirst

Do
Prcode = DLookup("strProduct_Code", "tblOrder_Details")
Prdis = DLookup("strProduct_Description", "tblOrder_Details")
PrdPrice = DLookup("curRetail_Price", "tblOrder_Details")
PrdQuan = DLookup("numQuantity", "tblOrder_Details")
PrdTot = DLookup("curTotal", "tblOrder_Details")

Rpt = Rpt & Chr(10) & Prcode & " " & Prdis & " " & " £" & PrdPrice & "
Quanity" & "[ " & PrdQuan & "] " & " £" & PrdTot & Chr(10)

rstRecips.MoveNext

Debug.Print Rpt
Loop Until rstRecips.EOF


End If


rstRecips.Close
Set rstRecips = Nothing

Thanks

Les


Cheryl Fischer said:
Les,

This is strictly anecdotal info and I have no KB article to which I can
refer you; however, I have found that the problem goes away when you
Click to expand...
use
the
ASCII character codes instead of the VB constant. Replace vbCRLF with
Chr(13) & Chr(10) and you should see the problem resolved.


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX



I am using the code below to populate the message field of Outlook.
Although I have a Crlf command to break each line this is ignored
Click to expand...
when
it
passes to outlook.

Is there a way to format this to do the line return or even put the output
in a Table format.

Thank

Les

Dim stDocName As String
Dim Rpt As String
Rpt = ""

Set rstRecips = CurrentDb.OpenRecordset("tblOrder_Details") 'table Name

If rstRecips.BOF Then 'BOF=no results from tbl
MsgBox "There Are No Orders To Send."
Else

rstRecips.MoveFirst

Do
Prcode = DLookup("strProduct_Code", "tblOrder_Details")
Prdis = DLookup("strProduct_Description", "tblOrder_Details")
PrdPrice = DLookup("curRetail_Price", "tblOrder_Details")
PrdQuan = DLookup("numQuantity", "tblOrder_Details")
PrdTot = DLookup("curTotal", "tblOrder_Details")

Rpt = Rpt & vbCrLf & "" & Prcode & " " & Prdis & " " & " £" & PrdPrice
&
" Quanity" & "[ " & PrdQuan & "] " & " £" & PrdTot

rstRecips.MoveNext
Loop Until rstRecips.EOF

End If

rstRecips.Close
Set rstRecips = Nothing

'Send Email with attached doc

stDocName = "rptOrder_Report"
DoCmd.SendObject acReport, stDocName, acFormatRTF, "am email address",
,
, "Request", " " & Rpt & ""
Click to expand...
Click to expand...
Click to expand...
 
Back
Top