Sending Email by Program Control
by Greg Neid, President Gold River CareHomes Inc.  He has been developing dBASE solutions since 1990.

OUR COMPANY manages retirement homes that are geographically separated.  We had a requirement to communicate critical financial and operational information, on a real time basis, to head office.  Our systems must be cost efficient yet communicate well with head office.  Originally we looked at leased lines, Citrix® and other rather expensive distributed solutions.  The expense was not easily justified.  After some review, we realized that certain transactions, and transaction summaries sent by email might meet our needs.  How might we do this under program control, so that there was no operator intervention?  In organizations with distributed operations automated email can bridge a very important gap and be very cost effective.  Some uses for automated email are:
 
Backup For all business applications, backup is important. But users are notorious for “forgetting” or for not doing it properly.  We also know that off-site backup is critical to surviving serious and catastrophic events.  User often don’t realize the significance and therefore can not be relied upon to provide this function.

The solution is an automatic compression (create .zip) with an automatic email of the compressed file to head office, all outside of the user’s control.

 

Critical
Transaction
Notification
Some transactions are considered critical by definition and are required at head office on an as-processed basis:
  • daily cash posting summaries and bank deposits
  • special order status
  • daily payroll hours accumulation
  • order process notification to customers
System Error Notification Even though we do not like to admit it, systems that we develop have errors in them.  Often we have code to trap inconsistencies or errors -- some more elusive than others.  Often when the error messages are presented, the users do not report them, or report them incorrectly.  Emailing system errors directly to systems manager is the clear answer.

 

Bulk Emails
to Customers
For marketing purposes, it is more and more useful to send emails to customers.  An html formatted email is much more appealing than a traditional text-only format.

 

Attached Documents Backups are sent as attached documents.  Of course any document can be attached.  Reports may be converted to .pdf format or Hotsend or some other encapsulated format and then attached to the email.  This allows the sending of reports in a format that can be read directly out of the email.

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:

 
PROCEDURE ConnectISP
   && start by setting up time
   form.StartConnectTime.value = Time()
   && label operation internally
   form.seeOperation = "seeSmtpConnect"
   && connect to server, giving mail server id and a valid from email address
   form.seeCode = seeSmtpConnect(0, form.server.value, form.from.value, Null)
   && there is a problem with timeout (limited to about 45 seconds) so we try twice, but only if error = -46
   if form.seeCode = -46
      && wait ten seconds before trying again
      sleep 10
      && try to connect one more time
      form.seeCode = seeSmtpConnect(0, form.server.value, form.from.value, Null)
       endif
   && check for See returning error
   if form.seeCode < 0
      form.cSMTPError = "YES"
      && display error
      class::SMTPErrorDisplay()
      && reset error status for next action
      form.seeCode = 0
      && display "x"
      form.ConnectOk.upbitmap = "Resource #28 resource.dll"
   else
      && display check mark
      form.ConnectOk.upbitmap = "Resource #20 resource.dll"
   endif
   && display completed connect time
   form.CompleteConnectTime.value = Time()
   

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).
Send Email

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:

You can get the Marshallsoft demo product, or purchase the real thing from their website (www.marshallsoft.com).  It now comes complete with documentation for dBASE.

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.

To download the author’s email program,  click here
(it’s a 194Kb zipped executable file)


Note: I would like to thank Robert W. Newman, my proof-reader, for the improvements he brought to this text.