Sending a Word Letter to a Single Access Contact

You may have a Contacts or Customers form in an Access database, and it would be convenient to have a quick way to create a letter to the current contact, using a command button on the form. The sample Word Export database has a form for browsing contacts, frmContacts, shown in Figure 6.15.

If you click the Word button in this form's header, a letter to the selected contact is created, filling Word document properties with data from that record.

FIGURE 6.14

Opening the old Paragraph dialog box from the new Ribbon.

Word Vba Form

FIGURE 6.15

A form for browsing contacts, with a button for creating a Word letter.

S frmContacts

1 Browse Contacts

Contact ID: fl Company Name:

Cascade Coffee Roasters

First Name: Nancy_| Job Title:

Sales Representative

Last Name: [oavoiio ~~| Work Phone:

(206)555-9857

Salutation: Nancy Work Extension:

120

Street Address: 507 - 20th Ave. E. Mobile Phone: Apt. 2A

-1 Fax Mumber:

City: Seattle

Email Name:

(206)555-8888 (206)555-9858 nancydifflanywhere.com

State/Province: WA

1 Mppting natf»:

Posta 1 Cod e: 98122-1829

Country: USA

Elizabeth Brown

Education includes a B.A. in Psychology from State University (1970.) She also completed "The Art of the Cold Call." She's got a good taste for flavored coffees.

1 Record: H « ¡1 of 500 | ► H K j < |Search |

Using Word Field Switches to Format Text Data in DocProperty Fields

When you use Word document properties to merge Access data to Word documents, the values written to the document properties are displayed on the Word document in DocProperty fields. You can use field switches to format the data displayed in the DocProperty field in a variety of ways, which is going to be necessary if you follow my advice and use mostly (if not exclusively) Text document properties. The field switches needed to produce some commonly used formats are listed in the following table.

Raw Access Data

Desired Word Format

Field Code Switches

1 1523.75

$1 1,523.75

DOCPROPERTY ' $###,##0.00

'DollarAmount" \#

2/2/2001

February 2, 2001

DOCPROPERTY ' "MMMM d, yyyy'

'DueDate" \@

282839898

28283-9898

DOCPROPERTY ' '0000"

'ZipCode" \# "00000'-

829887445

829-88-7445

DOCPROPERTY ' '0000"

'SSN" \# "000'-'00'-

150250.50

one hundred fifty thousand two hundred fifty and 50/100

DOCPROPERTY ' DollarText

'DollarAmount" \*

150250.25

ONE HUNDRED FIFTY THOUSAND TWO HUNDRED FIFTY AND 25/100

DOCPROPERTY "DollarAmount" \* DollarText \* Upper

150250.50

one hundred fifty thousand two hundred fifty

DOCPROPERTY ' CardText

'EntryAmount" \*

11/13/2005

Thirteenth

DOCPROPERTY "StartDate" \@ "d" \* OrdText \*FirstCap

11/13/2005

November

DOCPROPERTY ' "MMMM"

'StartDate" \@

You can create PostNet bar codes for U.S. zip codes on an envelope or label by adding a ZipCode DocProperty field to the Word template and applying a ZipCode bookmark to it. Because the WholeAddress field includes the zip code (or postal code, depending on the country), you should make the ZipCode DocProperty field invisible. To do this, select the field, open the Font Dialog by clicking the tiny arrow in the lower right of the Font group on the Word Ribbon, and check the Hidden checkbox. Next, position your cursor above the address block, select Insert O Quick Parts O Field, select the BarCode field, and then the ZipCode bookmark; leave the POSTNET bar code checkbox checked, and click OK to insert the bar code field (see the next figure).

AddressBlock

Advance

Author

AutoNum

AutoNumLgl

AutoNumOut

AutoText

AutoTextList

Bibliography

BidiOutline

Citation

Comments

Compare

CreateDate

Database

Description:

Insert a delivery point bar code

Field properties -Bookmark name

17 POSTNET bar code r Facing ID Mark (FIM): j r* Bar code is US zip code:

Preserve formatting during updates

I Ot= I Cancel

Inserting a U.S. PostNet BarCode field on a Word document.

. P ' When placing DocProperty fields in a template, make sure that the "Preserve format-

■ ting during updates" checkbox is not checked—if it is checked, and the text dis played from a doc property is longer than one word, the first word may have (probably will have, in my experience) a different font or size than the other words.

Application Letter Envelope Format

An envelope with a U.S. PostNet bar code above the address.

I* f f j 'r' r'j. rV See the previous table for a listing of Word field switches used to format values in p-^"1- . UrfsSJi DocProperty fields.

The cmdWord_Click() event procedure first saves information to variables, for use later in the code, then checks that the template is found in the templates folder, sets a Word Application variable, and creates a new Word document based on the template. Next, it sets a reference to the Word CustomDocumentProperties collection of the newly created document and sets each document property to either a variable or the value in a field from the current record. The segment of code that creates a save name for the document (used in most of my export procedures) uses a Do...Loop statement to create a save name for the document containing the merged data, picking up the contact name from a field on the form, adding today's date, in a format that uses dashes to create an acceptable file name:

Private Sub cmdWord_Click()

On Error GoTo ErrorHandler

Dim appWord As Word.Application

Dim strCompanyName As String

Dim strContactName As String

Dim strWholeAddress As String

Dim strJobTitle As String

Dim docs As Word.Documents

Dim doc As Word.Document

Dim strWordTemplate As String

Dim strDocsPath As String

Dim strTemplatePath As String

Dim prps As Object

Dim strShortDate As String

Dim strLongDate As String

Dim strTest As String

Dim strAddress As String

Dim strCountry As String

Dim strSaveName As String

Dim strTestFile As String

Dim intSaveNameFail As Boolean

Dim i As Integer

Dim strSaveNamePath As String

Check for required address information:

strTest = Nz(Me![StreetAddress]) If strTest = "" Then

MsgBox "Can't send letter -- no address!" GoTo ErrorHandlerExit End If strContactName = _

Nz(Me![ContactName]) strCompanyName = _

Nz(Me![CompanyName]) strWordTemplate = "Contact Letter Doc Props.dotx"

strLongDate = Format(Date, "mmmm d, yyyy") strShortDate = Format(Date, "m-d-yyYY") strSaveName = "Letter to " & strContactName strSaveName = strSaveName & " on " & strShortDate _ & ".doc"

strDocsPath = GetContactsDocsPath() Debug.Print "Docs path: " & strDocsPath strTemplatePath = GetContactsTemplatesPath() Debug.Print "Template path: " & strTemplatePath strWordTemplate = strTemplatePath & strWordTemplate Debug.Print "Word template and path: " _ & strWordTemplate

Check for the template in the selected Contact Templates folder, and exit if it is not found:

strTestFile = Nz(Dir(strWordTemplate)) Debug.Print "Test file: " & strTestFile If strTestFile = "" Then MsgBox strWordTemplate _

& " template not found; can't create letter" GoTo ErrorHandlerExit End If

Set the Word Application variable; if Word is not running, the error handler defaults to

CreateObject:

Set appWord = GetObject(Class:="Word.Application")

Set docs = appWord.Documents

Set doc = docs.Add(strWordTemplate)

Set prps = doc.CustomDocumentProperties

Turn off error handler because some of the templates may not have all of the doc properties:

On Error Resume Next prps.Item("NameTitleCompany").Value = _

Nz(Me![NameTitleCompany]) prps.Item("WholeAddress").Value = _

Nz(Me![WholeAddress]) prps.Item("Salutation").Value = _

Nz(Me![Salutation]) prps.Item("TodayDate").Value = strLongDate prps.Item("CompanyName").Value = _

strCompanyName prps.Item("JobTitle").Value = _

Nz(Me![JobTitle]) prps.Item("ZipCode").Value = _

Nz(Me![ZipCode]) prps.Item("ContactName").Value = strContactName

On Error GoTo ErrorHandler

Check for a previously saved letter in the documents folder, and append an incremented number to the save name if one is found:

intSaveNameFail = True Do While intSaveNameFail strSaveNamePath = strDocsPath & strSaveName Debug.Print "Proposed save name and path: " _

& vbCrLf & strSaveNamePath strTestFile = Nz(Dir(strSaveNamePath)) Debug.Print "Test file: " & strTestFile If strTestFile = strSaveName Then

Debug.Print "Save name already used: " _ & strSaveName

Create a new save name with the incremented number:

intSaveNameFail = True strSaveName = "Letter " & CStr(i) & " to " & _

Me![FirstName] & " " & Me![LastName] strSaveName = strSaveName & " on " & strShortDate _ & ".doc"

strSaveNamePath = strDocsPath & strSaveName Debug.Print "New save name and path: " _

Debug.Print "Save name not used: " & strSaveName intSaveNameFail = False End If Loop

With appWord

.Visible = True

.Selection.WholeStory

.Selection.Fields.Update

Debug.Print "Going to save as " & strSaveName .ActiveDocument.SaveAs strSaveNamePath .Activate

.Selection.EndKey Unit:=wdStory End With

ErrorHandlerExit: Exit Sub

ErrorHandler:

'Word is not running; open Word with CreateObject Set appWord = CreateObject(Class:="Word.Application") Resume Next

Else

& "; Description: " & Err.Description Resume ErrorHandlerExit End If

End Sub

Figure 6.16 shows the resulting letter.

When creating Word documents in VBA code, I save the current date to the TodayDate document property in the export code, rather than inserting a date code into the Word template, to ensure that the date on the letter will always be the date the letter was created; a Date field will show the current date (the date the letter is reopened).

If the name has already been used, the code loops back and adds a number to the end of the save name, and keeps trying until an unused number is reached. This technique means that you won't overwrite documents created the same day, but instead will create a series of documents with incrementing numbers.

FIGURE 6.16

A Word letter filled with Access data from a single contact record.

FIGURE 6.16

Blast4traffic

If you don't want to create multiple documents, you can eliminate the Do...Loop statement and overwrite an existing file with the same name, if there is one.

0 0

Responses

  • Lily
    What is the United States Envelope Format?
    6 years ago
  • Calliope
    How to create an address block with docproperty values?
    2 years ago

Post a comment