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.
To set up the form letter example:
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.
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.
Define a window that includes a DataWindow control called dw_mail, a MultiLineEdit called mle_body, and a CommandButton or PictureButton:
Assign the DataWindow object d_maillist to the DataWindow control dw_mail.
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()
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 Table 19-5.
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
Write a script for the Close button. All it needs is one command:
Close(Parent)
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
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.
