Application Techniques
Using OLE in an Application

To 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:

   • name1 -- for the name in the return address

   • name2 -- for the name in the salutation

   • address1 -- for the street, city, state, and zip in the return address

   • body -- for the body of the letter

   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:

   id

   first_name

   last_name

   street

   city

   state

   zip

   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:

   

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 a SQL 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:

   • Creates the OLEObject variable

   • Connects to the server (word.application)

   • For each row in the DataWindow object, generates a letter

     To do so, it uses VBA statements to perform the tasks in the following table.

     VBA statements	Task
     open	Opens the document with the bookmarks
     goto and typetext	Extracts the name and address information from a row in the DataWindow object and inserts it into the appropriate places in the letter
     goto and typetext	Inserts the text the user types in mle_body into the letter
     printout	Prints the letter
     close	Closes the letter document without saving it

     
     

   • Disconnects from the server

   • Destroys the OLEObject variable

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

   Close(Parent)