How to pass information from a table to an email using more than one record

This FAQ assists anyone who wishes to pass information to an email with more than one record from a table using Visual FoxPro 6 (It may work in other versions but I'm not sure) and Microsoft Outlook Express Version 6. The main problem with this particular project was the fact that either the variable string was too long and cut off not showing all the information and also it didn't like the character &

Thanks to craigsboyd, rgbean and aircon for their contributions

Here's how to do it:

Firstly, create a PRG then copy and paste the below

(CREDIT TO: craigsboyd for the suggestion/code and to aircon for the amendment as per thread184-783759 which finally made it work!)

************************
FUNCTION HandleUnsafeChars(sAddressUrl)
***Purpose: Change unsafe chars to escape sequences
************************
#DEFINE ICU_BROWSER_MODE 33554432 && 0x2000000

DECLARE INTEGER InternetCanonicalizeUrl IN wininet ;
STRING sURL, STRING @sBUFFER, INTEGER @nLength, INTEGER nFlags

LOCAL sNewUrl, nResult

* This Section allowed a longer string to be used
nResult = 4096
sNewUrl = Replicate(Chr(0), nResult)

* This solved the problem with & character

sAddressUrl = strtran(sAddressUrl, chr(13), '%0D')
sAddressUrl = strtran(sAddressUrl, chr(10), '%0A')

IF InternetCanonicalizeUrl (sAddressUrl,@sNewUrl, @nResult, ICU_BROWSER_MODE) <> 0
RETURN Left(sNewUrl, nResult)
ELSE
RETURN ""
ENDIF

ENDFUNC &&HandleUnsafeChars

As part of the process, a field is used from a table as shown:

USE ORDERS

STORE "" TO mlpr
SCAN FOR URN=murn
mlpr = mlpr+STR(QTY,3)+"ª"+LEFT(MEDIA,5)+ ;
"ª"+LABEL_ABBR+"ª"+ ;
LEFT(CATALOG_NU,12)+ ;
"ª"+LEFT(ARTIST,16)+"ª"+ ;
LEFT(TITLE,16)+"ª"+ORIGIN+ ;
"ª"+STR(SELL_PRICE,6,2)+"ª"+ ;
INFO+CHR(10)
ENDSCAN

The above, together with other variables are then sent to a form using:

DO FORM EMAILTXT WITH mlpr, mshopname, mcdxmail etc etc

On the EMAILTXT form is a command button. Paste the below in the procedure click event:

nmessage=MESSAGEBOX("Send Email Order Request Now?",4+32+0,"System Message")
IF nmessage=7
RETURN 0
ENDI

mlpr = HandleUnsafeChars(mlpr)

LOCAL cEmail, mconv1, mconv2, mconv3, mconv4, mconv5, mconv6, ;
mconv7, mconv8, mconv9, mconv10, mconv11, mconv12, mconv13, mconv14, ;
mconv15, mconv16 mconsf, mconst

mconv1="Dear Sir / Madam%0d%0a"
mconv2=" %0d%0a"
mconv3="Please supply the items listed below against Order Ref: "+ ;
LTRIM(STR(murn))+" - "+dtoc(mmastdate)+" %0d%0a"
mconv4=" %0d%0a"
mconv5="Order From: "+TRIM(mshopname)
mconv6=" %0d%0a"
mconv7="Email Reply Address (If App): "+TRIM(memailadd)+" %0d%0a"
mconv8=" %0d%0a"
mconv9=mheader+" %0d%0a"
mconv10=" %0d%0a"
mconv11=mlpr
mconv12=" %0d%0a"
mconv13="We are aware that all *back ordered items are "+ ;
"special order for a maximum of 30 days and "+ ;
"CANNOT be cancelled"
mconv14=" %0d%0a"
mconv15=" %0d%0a"
mconv16="Thanking you on behalf of "+TRIM(mshopname)

DECLARE integer ShellExecute IN shell32.dll ;
integer hndWin, string cAction, string cFileName, ;
string cParams, string cDir, integer nShowWin

cEmail = "mailto:"+LOWER(TRIM(mcdxemail))+ ;
"?Subject=Order Request"+ ;
"&Body=" ;
+mconv1+chr(10)+chr(10)+ ;
+mconv2+chr(10)+chr(10)+ ;
+mconv3+chr(10)+chr(10)+ ;
+mconv4+chr(10)+chr(10)+ ;
+mconv5+chr(10)+chr(10)+ ;
+mconv6+chr(10)+chr(10)+ ;
+mconv7+chr(10)+chr(10)+ ;
+mconv8+chr(10)+chr(10)+ ;
+mconv9+chr(10)+chr(10)+ ;
+mconv10+chr(10)+chr(10)+ ;
+mconv11+chr(10)+chr(10)+ ;
+mconv12+chr(10)+chr(10)+ ;
+mconv13+chr(10)+chr(10)+ ;
+mconv14+chr(10)+chr(10)+ ;
+mconv15+chr(10)+chr(10)+ ;
+mconv16+chr(10)+chr(10)
ShellExecute(0,"open",cEMail,"","",1)
CLEAR DLLS ShellExecute

Just for reference the " %0d%0a" forces a carriage return in Outlook, I'm not sure why it does that, but hey, it works!

Once again, thanks to those who contributed in making my life easier, I hope in return, this FAQ helps anyone else interested

Kindest regards and good luck

Lee......