FUNCky & SMTP E-mail from within dBASE
by John Staub, President, Staub & Associates, Inc.


The author would like to thank David L. Stone, John Fried and Jean-Pierre Martel for their proofreading and assistance in preparing this article.
Why FUNCky?

FUNCky is an excellent set of “third-party” tools for dBASE.  It has so many different and widely varied routines that can be easily included in a dBL app that we consider FUNCky a necessary part of our developer tool set.  We use it for FTP processes, sending single and bulk e-mails, testing for the existence and/or availability of certain drives, provide system information to users, etc., all from within dBL applications.

What is required?

Of course, youíll need a registered copy of FUNCky, available on the Web.. If you are using any of the FUNCky routines in a clientís application, you will need to install FUNCky.dll and FUNCky.TLB on their computers and/or server(s), and register FUNCky.dll on each machine.  These install and registration processes can be included in your installation program, and are especially easy to do using Inno (our preferred install package) or similar packages that allow you to run automated processes during the install.

Our Userís Environment

In this article, we will use a fairly complex user environment as an example.  This particular client has a dBL app that we built and which runs across a LAN and on a Terminal Server,  serving 9 sales locations in 3 different countries.  The application is installed on each workstation on the LAN, and we configured the Terminal Server environment to accommodate multiple users.  They also have a separate e-mail server as part of the domain.

During the building of this application, one requirement was to be able to send e-mails to particular clients, and to be able to send bulk e-mails to a wide variety of clients.  Each client record contains two fields for e-mail addresses, and the application provides for filtering client records in a multitude of methods.  Additionally, we can send an e-mail to either or both e-mail addresses in the client record.  Each sales agent has the ability to filter records based upon whether the client is a “sold” customer, a prospective customer, the make/model of car they are interested in, etc.  This provides each sales agent the ability to “target” their e-mails, especially when sending bulk e-mails.   Additionally, following the successful sending of an e-mail, a client follow-up record is created, documenting the contact with each client.

In the first use of the bulk e-mail send process, the company sent out announcements for the unveiling of a new series automobile.   They were hosting a “coming out” party at each of their sales locations.   While Iím not sure the bulk e-mail played a major role in the overwhelming success of the event, it provided them the means to send an announcement to each prospective customer.  The attendance at this event was significantly greater than at previous events and advertising costs for the event were similarly reduced.

FUNCky SMTP plays a major role in our ability to send e-mails from within our application.

The E-mail Form

  The forms we use are pretty simple and straight-forward.  We include the name of the client, their e-mail address, a CC and BCC field, text, and the ability to add an attachment.  Most of the information for the form is gathered before we ever open the form from values in entryfields on the currently open form.  We call the form in the following manner:
 
 
if not empty(form.cust_entryfield12.value) //first e-mail address field on the client form
  set procedure to one_email_send.wfm additive // one of our e-mail forms
  oEmail = new one_email_sendform()
  oEmail.EmailAddress = ' '
  // check which email address to use if two e-mail addresses exist
  if not empty(form.eCust_Address1.value) AND ;
    not empty(form.eCust_Address2.value)  // both e-mail address fields on the calling form
    set procedure to whichemailaddress.wfm additive
    oWhich = new which_email_addressform()
    oWhich.MDI = false
    oWhich.first_email  = false
    oWhich.second_email = false
    oWhich.both_email   = false
    oWhich.first_email_address  = form.eCust_Address1.value
    oWhich.second_email_address = form.eCust_Address2.value
    // when the form for determining which e-mail address to use opens, it displays
    // both e-mail addresses and pushbuttons for First, Second or Both e-mail addresses
    oWhich.ReadModal()
    if oWhich.first_email
      oEmail.EmailAddress = trim(form.eCust_Address1.value)  // the first e-mail address on the form
    elseif oWhich.second_email
      oEmail.EmailAddress = trim(form.eCust_Address2.value)  // the second e-mail address on the form
    elseif oWhich.both_email
      oEmail.EmailAddress = trim(form.eCust_Address1.value) + ;
      "; " + trim(form.eCust_Address2.value)  // both e-mail addresses separated by a semi-colon
    endif  // which e-mail address to use
    // since we use Boolean values from the which_email_address form, closeout
    // the form now as they are no longer needed
    oWhich.Release()
    oWhich = null
    close procedure which_email_address.wfm
  else   // only one e-mail address exists
    oEmail.EmailAddress = trim(form.eCust_Address1.value)  // first e-mail address on the calling form
  endif  // not empty both e-mail addresses
  oEmail.ClientNumber = form.eClient_Number.value  // clientnumber
  oEmail.MDI = false
  oEmail.ReadModal()
  oEmail.Release()
  oEmail = null
  close procedure one_email_send.wfm
else  // no e-mail address is on record
  MSGBOX("There is not a valid e-mail address for this record","No valid e-mail address")
endif
return
   

Instantiating and opening the form is a pretty simple and straight-forward process.  We check to see if there is an e-mail address for this client record, then we give the user the option of using the first, second or both addresses if two addresses exist. The ClientNumber is a unique record identifier (AutoIncrement field) and is used in the actual e-mail form so we can create a follow-up record for this client if the e-mail is sent.  One thing you may want to note is how we use variables from two different forms to handle specific information and to determine which e-mail address to use.  If only one e-mail address is on record, we just grab that value from the client formís entryfield and use it.  If no e-mail address is in this clientís record, we notify the user and close out the process

In the on_Open() event of the e-mail form, we insert values into several  fields on the e-mail form.  For a variety of reasons, we use disabled entryfields on the actual e-mail form to store the To, From and BCC addresses. Additionally, we create a form.variable to hold the information for any attachments a user may want to include with the line form.attachments = ' '

If the users want to include an attachment, when they click the “Attach” button, the following code is run:
 
 
form.attachments = iif(empty(form.attachments), null, form.attachments + ", ") ;
   + getfile("*.doc;*.xls;*.bmp;*.jpg;*.jpeg;*.gif")
form.editor2.enabled = true
form.editor2.value = ' '
form.editor2.value = form.attachments
return
   

The real FUNCky work starts when the user clicks the Send button:
 
 
set procedure to Funcky.cc additive
local Smtp
Smtp = New FUNCkySmtp()
Smtp.Server = "mymail_server.com" // IP addresses can also be used
Smtp.FromName = form.eAgent_Name.value
Smtp.FromAddress = form.eFrom_Address.value
Smtp.Subject = form.eSubject.value
Smtp.ToName = form.eToName.value
Smtp.ToAddress = form.eTo_Address.value
Smtp.BCC = form.eBCC.value
Smtp.Message = form.editor_message.value
if not empty(form.attachments)
  Smtp.Attachments = form.attachments
endif
if( Smtp.Send(True))  // e-mail sent successfully
  MSGBOX("e-mail Sent","Success")
  // call the routine to create the client followup record
  Class::ClientFollowup()
else  // first attempt failed using the mail server IP address
  Smtp.Server = "myothermailserver.com"  // perhaps use the internal LAN address of the mail server
  if( Smtp.Send(True))
    MSGBOX("e-mail Sent","Success")
    // call the routine to create the followup record
    Class::ClientFollowup()
  else
    MSGBOX("e-mail Send Failed...Status Message is " + Smtp.StatusMessage," ")
    MSGBOX("Error is " + Smtp.Error," ")
    MSGBOX("Error Message is " + Smtp.ErrorMessage)
  endif  // second send attempt
endif    // first send attempt
close procedure Funcky.cc
form.close()  // close the form and get back to business
return
   

In the code example above, we populate several properties of the FUNCky SMTP methods, i.e., Server, the various addresses, and the text message, and we identify any attachments the user may want to include. Additionally, we try sending the e-mail twice, using two different mail server addresses.  Note that when identifying the SMTP server, you can use IP addresses or the actual server name.  If your users are on a LAN, you can use the local IP address of the mail server.  If the send is successful, we notify the user, and call the Class to create the client followup record.  If both sends fail, we notify the users and provide them the error codes and messages.  Fortunately we are blessed with fairly competent users and if they encounter an error message like the above, they write them down and notify our tech support so we can troubleshoot the problem.

If you were in an environment where the IP addresses of the mail servers change, you could store the values for the servers in a configuration table and just grab the values from there.

Bulk E-mail

As mentioned earlier, we also use FUNCky and dBASE to handle sending bulk e-mail.  First we loop through the client records, copying each e-mail address and client name to a temporary table.  Then we open a form used for bulk e-mail which allows the user to type in the text message.  When the user clicks the send button, the following code is run:
 
 


local d,db,r,s,t,x,Smtp
set procedure to Funcky.cc additive
set procedure to message.wfm additive
fMessage = new MessageForm()
fMessage.text = "PCS Information System"  // form text (titlebar)
fMessage.title.text   = " "  // large text at top
fMessage.message.text = "Now sending your e-mails "
fMessage.Open()
fMessage.Mousepointer = 11
Smtp = New FUNCkySmtp()
Smtp.Server      = "myfirstmailserver.com"
Smtp.FromName    = trim(form.eClient_Number.value)
Smtp.FromAddress = trim(form.eFrom_Address.value)
Smtp.Subject     = form.eSubject.value
Smtp.BCC         = trim(_app.AgentEmail)
Smtp.Message     = form.editor_message.value

// now setup the necessary loop and functions to complete the
// address process for all the identified records

d = new database("PCSLOCAL") // BDE Alias used for local, temporary tables
d.emptyTable('bademail.dbf') // empty this table so we can use it

db = new database("PCS")    // BDE Alias used for networked tables

r = new query()
r.database = d
r.sql = 'select * from bulkemail.dbf' // table we stored the names and e-mail addresses in
r.active = true

s = new query()
s.database = d
s.sql = 'select * from bademail.dbf' // used to store failed e-mail information
s.active = true

t = new query()
t.database = db
t.sql = "select * from clientfollowup.dbf" // client followup record table
t.active = true
t.IndexName = "FOLLOWUP" //index based upon the unique client number
r.rowset.first()  // go to the first record in the rowset

// now validate the names and e-mail addresses to
// ensure we do not have any invalid or blank names
do
  if empty(r.rowset.fields["name"].value) and ;
    empty(r.rowset.fields["address"].value)
    r.rowset.delete()
  endif
until not r.rowset.next()

// now get ready to send out the e-mail
r.rowset.first()
do
  // Set the rest of the message properties based upon
  // data in the local table
  Smtp.ToName = trim(r.rowset.fields["name"].value)
  Smtp.ToAddress = trim(r.rowset.fields["address"].value)
  if not(Smtp.Send())    // if the e-mail did not get sent
    // change the e-mail server IP and try again
    Smtp.Server = "mymailserver.com"
    if not (Smtp.Send()) // failed again - dump the records into
                         // a table for analysis
      s.rowset.BeginAppend()
      s.rowset.fields["name"].value = trim(r.rowset.fields["name"].value)
      s.rowset.fields["address"].value = trim(r.rowset.fields["address"].value)
      s.rowset.Save()
    else  // e-mail was sent - create a clientfollowup record
      Class::ClientFollowup()
    endif // second send attempt
  else // e-mail was sent first time - create a followup record
    Class::ClientFollowup()
  endif  // first send attempt
until not r.rowset.next()

/*
now check for the existence of bad e-mails
if there are records, show a report so the user
can fix the problem
*/
if s.rowset.first()
  do preview.wfm with "bademail.rep" // preview.wfm is available in the dUFLP
endif
// end of bad e-mails check code
t.active  := false
s.active  := false
r.active  := false
d.active  := false
db.active := false
fMessage.mousePointer = 0
fMessage.Close()
close procedure message.wfm
close procedure Funcky.cc
// close the form and return to the calling program
form.close()
return

   

While much of the above code is specific to our form for sending bulk e-mail, I believe it demonstrates an approach that you can easily adapt to your circumstances.

This particular client also sends a variety of “form” e-mails to customers throughout the process of building and delivering the customersí cars.  These e-mails are built and sent using a .cc file we developed specifically for this purpose.   While we use the same .cfm e-mail form as in the above examples, the populating of the various fields, and especially the formatting the e-mail text is handled through our .cc file.

Sending e-mails using either of the above examples is extremely fast.

Get FUNCky?

FUNCky provides a wide variety of tools for the dBASE user.  The help file is complete and includes well-documented examples for a variety of programming languages.  Much of the above code was “derived” (okay, “borrowed”) directly from the FUNCky examples.  Technical support is excellent and available from the FUNCky web site at http://www.FUNCky.com. There is a new FUNCky demo for dBASE available at (watch for word wrap)
http://www.funcky.com/downloadfile.asp?Filename=FUNCkyVDB.Exe&Section=Demos&Version=VDB

We use FUNCky for a variety of purposes and have found it to be very easy to integrate into a dBL app.  Perhaps itís time for you to get FUNCky.


For more information on  Staub & Associates, Inc., please visit  http://www.staubassociates.com
For information on hosting your dBASE web applications or other web sites, please contact wrightd_dBulletin_@dcs-fl.com (take out ď_dBulletin_Ē).
This article is Copyright © — 2001, Staub & Associates, Inc — All Rights Reserved.