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.
transactions are considered critical by definition and are required at
head office on an as-processed basis:
|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.
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.
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:
|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.
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.
with Seexx.dll, example:
to tell Seexx.dll that the message is html code.
to operate Seexx.dll in a mode that allows the program to continue to
execute while see works in "background"
with seeStatistics to get many kinds of statistical data from the
for demo system, as assign by Marshallsoft otherwise
to attach to Seexx.dll driver. see_Key_Code is the confidential
license number used to identify that the user has authorized software.
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.|
release DLL &cSeeDLL
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:
form.StartConnectTime.value = Time()
form.seeOperation = "seeSmtpConnect"
form.seeCode = seeSmtpConnect(0, form.server.value, form.from.value, Null)
&& start by setting up time
&& label operation internally
&& connect to server, giving mail server id and a valid from email address
|if form.seeCode = -46||&& there is a problem with timeout (limited to about 45 seconds) so we try twice, but only if error = -46|
form.seeCode = seeSmtpConnect(0, form.server.value,
wait ten seconds before trying again
&& try to connect one more time
form.seeCode < 0
form.cSMTPError = "YES"
form.seeCode = 0
form.ConnectOk.upbitmap = "Resource #28 resource.dll"
form.ConnectOk.upbitmap = "Resource #20 resource.dll"
for see returning error
&& display error
|form.CompleteConnectTime.value = Time()||&& display completed connect 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:
the main program from which everything can be run
the program that wraps the see16.dll and actually sends the email
Marshallsoft demo dll
Visual dBASE support files
the file containing the list of recipients for the email
the file containing a formatted html message to be sent via email
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:
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.
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",
address of receiver. Must be in the form of "Name<email@example.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<firstname.lastname@example.org>". 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<email@example.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 ""|
In order to process automatic emails, you will require the following:
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.
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)