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:
|
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:
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:
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 167Kb file)