Christopher Greaves

416-621-9348 cgreaves@chrisgreaves.com
Home Services Products

Envelope Data Preparation

I have a little macro (naturally!) in my word processor that will obtain address data from a text file and create a series of envelopes - one for each address.

We are going to create a Command Button that delivers such a text file. This will be somewhat similar to taking a snapshot of a record, but here we will make use of a limited set of fields. Specifically we would like our address to contain the contact name and title, the corporate name, the street address, city, province, country and postal code.

We have based the following procedure on our own "Client" table of data, but the field names should be evident, and you should find it quite easy to adapt this to your own ends.

Here is a procedure, quite closely geared to this particular database, not quiet as general-purpose as previous procedures. It takes as parameters as user form (the traditional "Me"), a character string to separate data that flows on the same line (typically just a space character), and a character string that separates lines.

Public Function strFormAddress(frm As Form, strSpace As String, strDelim As String) As String
Dim strResult As String
strResult = ""
strResult = strResult & frm.Controls("SALN") & strSpace
strResult = strResult & frm.Controls("GIVEN") & strSpace
strResult = strResult & frm.Controls("SURN") & strDelim
strResult = strResult & frm.Controls("TITLE") & strDelim
strResult = strResult & frm.Controls("BUSINESS") & strDelim
strResult = strResult & frm.Controls("Address1") & strDelim
strResult = strResult & frm.Controls("ADDRESS2") & strDelim
strResult = strResult & frm.Controls("CITY") & strSpace
strResult = strResult & frm.Controls("State") & strSpace
strResult = strResult & frm.Controls("COUNTRY") & strDelim
strResult = strResult & frm.Controls("POSTCODE") & strDelim
strFormAddress = strResult
End Function

Here is the Click event for our new Command Button.

We are using a space character to separate fields that are to appear on the same line, and we are using a new-line character (ASCII code "011") to separate one line from the next.

Private Sub cmd_Address_Click()
Dim strRecord As String
strRecord = strFormAddress(Me, " ", Chr$(11))
Call strPrintFile("Address.DOC", strRecord)
End Sub

When you click on the Address Command Button, a small word-like document will appear with contents formatted like this:

Mr. Helmut Ratz
Manager
CIT - Careers In Transition
500 Alden Rd.
Unit 204
Markham Ontario CANADA L3R 5H5

Toronto and Mississauga, Sunday, December 05, 2010 3:19 PM