INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

COM and Automation

How to pass information from a table to an email using more than one record by keepingbusy
Posted: 26 Feb 04 (Edited 12 Feb 05)

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

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close