Once we realized the power of email we found its use limited mostly by our imagination.

Choosing an Email System

We knew that we required an integrated email function.  Some time ago, I began to work with OLE and Outlook 98.  There were also some custom classes created for dBASE that seemed like they would work — but, I soon learned that Microsoft has many versions of email support structure in Windows and they are not all the same, nor are they all compatible.  We were looking for an SMTP (simple mail transfer protocol) handler that we could control and would work the same at all locations.

Over the years of using ActiveX (not compatible with 5.6/7) and VBX (compatible with 5.6/7) and .dll's (dynamic link library), I gained a preference for using .dll's as third party software, if possible.  Although VBX, ActiveX and .dll's work the same in development, my experience is that .dll's deploy to the user much easier.

The Marshallsoft product was a .dll and seemed to fit all of our needs. In addition Marshallsoft has since become a “dBASE Partner”, further validating their ability.  Today it comes “wrapped” for dBASE although when I bought it, it did not.

Was it smooth sailing?  No, all software products require a learning curve -- but the curve wasn't too steep.  The main difficulty that I experienced was defining the extern's.  The extern statement defines the parameters that will be passed to the .dll and received back from the .dll.  The Marshallsoft product is called See and the .dll is either See16.dll for dBASE 5.6/7 or See32.dll for dBASE 7.x.  I will refer to it as Seexx.dll.

If you decide to use Seexx.dll, be very careful with clong (long integer) and cint (short integer, maximum value 32,767 — 15 bits for integer value and one bit for the sign of the integer).  Because Seexx.dll passes back negative numbers when indicating an error; the sign can be missed if clong is used when cint is appropriate.  The original documentation that came with my copy of the software from Marshallsoft was not correct on all these definitions.

Wrapping SEE

There are certain functions that are used to interface with any .dll.  The extern defines what and how data is passed to and from the .dll.  The only parameters types that are passed back and forth to Seexx.dll are:
 

cint	numeric	16 bit signed integer
clong	numeric	32 bit signed integer
cstring	character string	string data

The extern definitions for Seexx.dll are:
 

see Function	Comment	Used in Sample Application
extern cint seeAttach(clong,clong) see16.dll	attach to and start thread for see16.dll	yes
extern cint seeClose(clong) see16.dll	used to close see16.dll	yes
extern cint seeDebug(clong,cint,cstring,cint) see16.dll
extern cint seeDecodeBuffer(cstring,cstring,cint) see16.dll
extern cint seeDeleteEmail(clong,clong) see16.dll
extern cint seeDriver(clong) see16.dll	used to test status of message to determine if completed or not.  This feature allows program to display status of message as it is being sent.	yes
extern cint seeEncodeBuffer(cstring,cstring,cint) see16.dll
extern cint seeErrorText(clong,clong,cstring,clong) see16.dll
extern cint seeExtractText(cstring,cstring,cstring,cint) see16.dll
extern cint seeGetEmailCount(clong) see16.dll
extern cint seeGetEmailFile(clong,clong,cstring,cstring,cstring) see16.dll
extern cint seeGetEmailLines(clong,clong,clong,cstring,clong) see16.dll
extern cint seeGetEmailSize(clong,clong) see16.dll	get the size of the email.  Email attachments are expanded by approximately 40% before sending	yes
extern cint seeGetEmailUID(clong,clong,cstring,clong) see16.dll
extern clong seeIntegerParam(cint,cint,clong) see16.dll	used to send control parameters to see16.dll.	yes
extern cint seePop3Connect(clong,cstring,cstring,cstring) see16.dll
extern cint seeRelease() see16.dll	release the see16.dll	yes
extern cint seeSendEmail(clong,cstring,cstring,cstring,cstring,cstring,cstring) see16.dll	send the email	yes
extern cint seeSmtpConnect(clong,cstring,cstring,cstring) see16.dll	connect to an ISP	yes
extern clong seeStatistics(clong,clong) see16.dll	get statistics from see16.dll	yes
extern cint seeStringParam(clong,clong,cstring) see16.dll
extern cint seeVerifyFormat(cstring) see16.dll
extern cint seeVerifyUser(clong,cstring) see16.dll

#define statements are used to define values the are used to control Seexx.dll.
 

Definition	Value	Description
#define SEE_MIN_RESPONSE_WAIT	1	The control codes used to set up line timing.  In all communications systems timing parameters are required, usually for determining wait times.  That is waiting until the required response doesn't happen, then setting an error code. Used with Seexx.dll, example: seeIntegerParam(0,see_min_response_wait,30000) wait 30,000 milliseconds before checking
#define SEE_MAX_RESPONSE_WAIT	2
#define SEE_CONNECT_WAIT	3
#define SEE_DISABLE_MIME	4
#define SEE_MIN_LINE_WAIT	5
#define SEE_MAX_LINE_WAIT	6
#define SEE_QUOTED_PRINTABLE	8	Used to tell Seexx.dll that the message is html code.  seeIntegerParam(0,SEE_QUOTED_PRINTABLE, 2)
#define SEE_AUTO_DRIVER_CALL	9	Used to operate Seexx.dll in a mode that allows the program to continue to execute while see works in "background"  seeIntegerParam(0,SEE_AUTO_DRIVER_CALL,0)
#define SEE_LOG_FILE	20
#define SEE_GET_ERROR_TEXT	1
#define SEE_GET_COUNTER	2
#define SEE_GET_RESPONSE	3
#define SEE_GET_SOCK_ERROR	4
#define SEE_GET_MESSAGE_BYTES_READ	10	Used with seeStatistics to get many kinds of statistical data from the Seexx.dll form.seeVersion.value = seeStatistics(0,SEE_GET_VERSION) form.seeBuild.value = seeStatistics(0, SEE_GET_BUILD)
#define SEE_GET_ATTACH_BYTES_READ	11
#define SEE_GET_TOTAL_BYTES_READ	12
#define SEE_GET_MESSAGE_BYTES_SENT	13
#define SEE_GET_ATTACH_BYTES_SENT	14
#define SEE_GET_TOTAL_BYTES_SENT	15
#define SEE_GET_VERSION	16
#define SEE_GET_MSG_COUNT	17
#define SEE_GET_MSG_SIZE	18
#define SEE_GET_BUFFER_COUNT	19
#define SEE_GET_CONNECT_STATUS	20
#define SEE_GET_REGISTRATION	21
#define SEE_GET_ATTACH_COUNT	22
#define SEE_GET_LAST_RESPONSE	23
#define SEE_GET_VERIFY_STATUS	24
#define SEE_GET_SERVER_IP	25
#define SEE_GET_BUILD	26
#define SEE_KEY_CODE	0 for demo system, as assign by Marshallsoft otherwise	Used to attach to Seexx.dll driver.  see_Key_Code is the confidential license number used to identify that the user has authorized software. SeeAttach(1,see_Key_Code)

Some Code Samples

Developing a complete automated email system required the use of very few Seexx.dll functions.  The only functions that were used are:
 

Step Number	Seexx.dll Process	Comment
1.	seeAttach(1,see_Key_Code)	First things first -- attach to Seexx.dll giving it your authorization to continue.
2.	seeStatistics(0,SEE_GET_VERSION)	Get the version information, make sure you are working with the correct code.
3.	seeIntegerParam(0,see_min_response_wait, form.esee_min_response_wait.value)	Set up timing parameters for your system.  All the defaults were OK for me, except maximum connect time, which I extended to 90,000 milliseconds.  It turns out that the 16-bit Winsock doesn't use this parameter and so I forced it to wait by issuing a second connect command (see the code sample attached).
4.	seeSmtpConnect(0, form.server.value,form.from.value,Null)	Connect to your ISP.  This takes time.  There could be modem dialing involved plus the execution of the actual "handshake" (passwords etc.).  This can take more time than expected. See the note on item 3 above.
5.	seeSendEmail(0, cSendList, form.copy.value, Null, form.Subject.value, form.Message.value, form.attachment.value)	Finally, send the email.  All parameters must have been formatted.  In the sample code, BCC (blind carbon copy address) is not used. Null is in its place.
6.	seeDriver(0)	In the example code, the seeDriver command is used to check the status of the seeSendEmail.  It is part of a loop that checks for either errors, or completion of the send at which time it exits the loop.  While in the loop, the program checks the sending status of the email and reports it to the user.
7.	seeStatistics(0, SEE_GET_TOTAL_BYTES_SENT)	Used in the "loop" (see item 6) to determine and report the percentage of the email sent. Check if the bytes sent are more than the user would expect.  The sample program adds 10% to the message text and 40% to the attachment to estimate the actual sent data size.
8.	seeClose(0)	Close Seexx.dll
9.	SeeRelease( ) release DLL &cSeeDLL	Release Seexx.dll

Here is a sample of code for connecting to your ISP using Seexx.dll.  The confusing part  is that it tries to connect twice.  The connection should only take a few seconds if the modem is already connected.  Our internal systems use 3COM lan-modem boxes.  >From Windows point of view, we are connecting through a lan, but in reality, many times the 3COM lan-modem box must dial the ISP, then connect.  This can take more than 45 seconds and a time-out can occur.

The 16-bit version of Winsock does not accept the delay parameters sent by See16.dll.  Winsock is supposed to wait 60 seconds.  In my tests I could never find it waiting longer than 45 seconds.  The connections to most of our ISPs took a few seconds if the line was already up, but if disconnected, it took between 35 and 60 seconds including dialing etc., often too long (if you have ever had problems with Outlook not connecting properly, it may be the same problem).  Anyway, the solution is:

• try to connect,
• if unsuccessful and error = -46 (-46: Cannot find SMTP/POP3 server.)
• sleep 10 seconds,
• try to connect again.

The Sample System

The sample system is available at the end of this article.  Download "Sample Email.zip" into a new folder and expand it.  The files that it will contain are:
 

1-2.	start_email.wfm exit.mnu	the main program from which everything can be run
3.	emailsnd.prg	the program that wraps the see16.dll and actually sends the email
4.	see16.dll	Marshallsoft demo dll
5-8.	bitmaps.dll gralert.bmp emailsup.prg email.h	Visual dBASE support files
9.	elist.dbf	the file containing the list of recipients for the email
10-11.	htmlrep.dbf/dbt	the file containing a formatted html message to be sent via email
12.	profile.dbf	parameter file for email system
13.	Word Document.doc	A word document that is used as an attachment to the email.

Now run start_email.wfm and see how it works.  Before pressing the Send Email button, be sure to give it valid data:

1. Valid send addresses in the Auto-distribution section, otherwise it will not work.
2. Also be sure there are no blank email addresses.  Many email servers will fail to send anything when they come upon a blank email address.
3. Valid SMTP address (example: "smtp.compuserve.com") and
4. Valid email sending address (example "myname@compuserve.com").

Don't do this until you have set the parameters on the 2nd and 3rd pages of this form!

Then, press the “SEND EMAIL” button to send an email.

Auto-Distribution Email Addresses

This is the elist file.  It contains the names and email address of each recipient of email transactions.

Timing and SMTP Parameters

The top section contains timing parameters which should work as is. The lower section requires that you enter an SMTP address and a valid email sending address.

EmailSnd.prg

The main program for sending the email and attachments is included.  As with all programs, the actual sending of data is easy; most of the code is for handling errors and reporting to the user.

EmailSnd.prg is written in the form of a .prg and accepts parameters as follows:

do emailsnd with "to", "copy", "bcc", "subject", "message", "attachment", "html"
 

Parameter	Meaning
"to"	email address of receiver.  Must be in the form of "Name<name@domain.com>".  Can contain multiple addresses, separated by commas.  This field cannot be empty. if "to" = "elist", an array aNames is filled from elist.dbf and all addresses in elist are processed
"copy"	email address of copy receiver.  Must be in the form of "Name<name@domain.com>".  Can contain multiple addresses, separated by commas.  If empty chr(0).
"bcc"	email address of blind copy receiver.  Must be in the form of "Name<name@domain.com>".  Can contain multiple addresses, separated by commas.  If empty chr(0).
"subject"	email subject line
"message"	email message text.  If the text is in html format, then last parameter must be "html"
"attachment"	file name and location of attachment
"html"	if document is an html document then "html", otherwise ""

System Requirements

In order to process automatic emails, you will require the following:

• dBASE 5.6/5.7 or 7.x
• Marshallsoft  Email Libraries for Windows (SEE4DB) (www.marshallsoft.com).  The library required is see16.dll for dBASE 5.6/5.7 and see32.dll for 7.x
• Windows 95/98
• Access to the internet via LAN, modem etc.
• An internet service provider.

Note:

The attached sample program does not cause your computer modem to be dialed automatically. You may find it necessary to dial manually or you may need to set the parameters in your browser.  The Iinternet Explorer 5 example hereunder configures it for automatic dialing if there is no existing internet connection.

Good Luck

Automated email is one of the most useful tools that our company has developed in dBASE.  Our head office receives automated sales reports, backup information and cash reports.  The email subsystem is a success and the demand for new email reports is increasing.

Since overcoming the difficulties in connection timing (see sample code above), it has been extremely reliable.  Marshallsoft now has it packaged for dBASE 5.6/5.7 and 7.x.  dBASE version 7.5 has an email class wrapping the Marshallsoft product, which has not been publicly released at the time of this writing.

Program controlled email is here to stay — learn it and use it.