Example: generating form letters using OLE

This example takes names and addresses from a DataWindow object and letter body from a MultiLineEdit and creates and prints letters in Microsoft Word using VBA scripting.

StepsTo set up the form letter example:

  1. Create a Word document called CONTACT.DOC with four bookmarks and save the file in your PowerBuilder directory.

    These are the bookmarks:

    The letter should have the following content:

    Multimedia Promotions, Inc.
    1234 Technology Drive
    Westboro, Massachusetts
    January 12, 2003
    
    [bookmark name1]
    [bookmark address1]
    
    Dear [bookmark name2]:
    [bookmark body]
    
    Sincerely,
    Harry Mogul
    President
    

    You could enhance the letter with a company and a signature logo. The important items are the names and placement of the bookmarks.

  2. In PowerBuilder, define a DataWindow object called d_maillist that has the following columns:

    You can turn on Prompt for Criteria in the DataWindow object so the user can specify the customers who will receive the letters.

  3. Define a window that includes a DataWindow control called dw_mail, a MultiLineEdit called mle_body, and a CommandButton or PictureButton:

    The sample screen shows a  Mailing list in the DataWindow control, an area labeled Letter Body as the Multi line Edit, and two Command Buttons, Close and Generate Letters.
  4. Assign the DataWindow object d_maillist to the DataWindow control dw_mail.

  5. Write a script for the window’s Open event that connects to the database and retrieves data for the DataWindow object. The following code connects to an Adaptive Server Anywhere database. (When the window is part of a larger application, the connection is typically done by the application Open script.)

    /**************************************************
    Set up the Transaction object from the INI file
    **************************************************/
    SQLCA.DBMS=ProfileString("myapp.ini", &
       "Database", "DBMS", " ")
    
    SQLCA.DbParm=ProfileString("myapp.ini", &
       "Database", "DbParm", " ")
    /**************************************************
    Connect to the database and test whether the 
    connect succeeded
    **************************************************/
    CONNECT USING SQLCA;
    IF SQLCA.SQLCode <> 0 THEN
       MessageBox("Connect Failed", "Cannot connect" &
          + "to database. " + SQLCA.SQLErrText)
       RETURN
    END IF
    /**************************************************
    Set the Transaction object for the DataWindow control and retrieve data
    **************************************************/
    dw_mail.SetTransObject(SQLCA)
    dw_mail.Retrieve()
    
  6. Write the script for the Generate Letters button (the script is shown below).

    The script does all the work, performing the following tasks:

  7. Write a script for the Close button. All it needs is one command:

    Close(Parent)
    

Script for generating form letters

The following script generates and prints the form letters:

OLEObject contact_ltr
integer result, n
string ls_name, ls_addr
/***************************************************
Allocate memory for the OLEObject variable
***************************************************/
contact_ltr = CREATE oleObject
/***************************************************
Connect to the server and check for errors
***************************************************/
result = &
   contact_ltr.ConnectToNewObject("word.application")
IF result <> 0 THEN
   DESTROY contact_ltr
   MessageBox("OLE Error", &
      "Unable to connect to Microsoft Word. " &
      + "Code: " &
      + String(result))
      RETURN
END IF
/***************************************************
For each row in the DataWindow, send customer
data to Word and print a letter
***************************************************/
FOR n = 1 to dw_mail.RowCount()
/************************************************
   Open the document that has been prepared with
   bookmarks
************************************************/
   contact_ltr.documents.open("c:\pbdocs\contact.doc")
/************************************************
   Build a string of the first and last name and
   insert it into Word at the name1 and name2
   bookmarks
************************************************/
   ls_name = dw_mail.GetItemString(n, "first_name")&
   + " " + dw_mail.GetItemString(n, "last_name")
   contact_ltr.Selection.goto("name1")
   contact_ltr.Selection.typetext(ls_name)
   contact_ltr.Selection.goto("name2")
   contact_ltr.Selection.typetext(ls_name)
/************************************************
   Build a string of the address and insert it into
   Word at the address1 bookmark
************************************************/
   ls_addr = dw_mail.GetItemString(n, "street") &
      + "~r~n" &
      + dw_mail.GetItemString(n, "city") &
      + ", " &
      + dw_mail.GetItemString(n, "state") &
      + " " &
      + dw_mail.GetItemString(n, "zip")
   contact_ltr.Selection.goto("address1")
   contact_ltr.Selection.typetext(ls_addr)
/************************************************
   Insert the letter text at the body bookmark
***********************************************/
   contact_ltr.Selection.goto("body")
   contact_ltr.Selection.typetext(mle_body.Text)
/************************************************
   Print the letter
************************************************/
   contact_ltr.Application.printout()
/************************************************
   Close the document without saving
************************************************/
   contact_ltr.Documents.close
   contact_ltr.quit()
NEXT
/***************************************************
Disconnect from the server and release the memory for the OLEObject variable
***************************************************/
contact_ltr.DisconnectObject()
DESTROY contact_ltr

Running the example

To run the example, write a script for the Application object that opens the window or use the Run/Preview button on the PowerBar.

When the application opens the window, the user can specify retrieval criteria to select the customers who will receive letters. After entering text in the MultiLineEdit for the letter body, the user can click on the Generate Letters button to print letters for the listed customers.