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......
