Writing Data to Text Files

If your code iterates through a recordset, doing (or not doing) some action for each record, a text file is a handy way to document which records have been processed, or perhaps just to document records that were skipped because of missing information. You can write informational data to a text file using three methods: the legacy VB statements (Open FileName For Input/Output As #n); components of the FileSystemObject object model (the TextStream object in particular); or components of the ADO object model (the Stream object in particular).

The sample Select Contacts for Email form (frmEMailMerge), shown in Figure 9.10, has a multi-select listbox for selecting contacts to receive an email, textboxes for entering the message subject and body, and an option group for selecting the method of creating a text file containing information about the skipped records.

A form with options to create a text file with information about skipped records using three different methods.

51 frmEMailMerge

FH Select Contacts for EMail

Kaiser, Adrian

[email protected]

Kuntz, Noble

[email protected]

Laughlin, Vicki

Allied Industries, Ltd.

Marx, Sean

AVANCO Specialties, Ltd.

Matthews, Cynthia

[email protected]

Advanced

Moore, Alfred

a 1 f red m @ wi dgetcor p. com

ALT Shipping

Morrison, Carl

[email protected] widgetcorp.com

AA Shipping, Ltd.

Morrison, Theodore

[email protected]

m

Murray, Elizabeth

[email protected]

m Ackerman Industries, Ltd.

Murray, Kirk

[email protected]

Pruitt, Muriel

Blumenthal Management, Ltd.

Richardson, Brett

[email protected]

American Center, Inc.

Smith, Noble

AAA, Inc.

Thompson, Kelky

kel [email protected]îdgetcorp.com

ADG Management, inc.

W il kins, Lester

[email protected] wi dgetcor p.c om

Advantage Center, Limited

-

Summer 2005 Update

This message is to remind you that the Summer 2006 update of our software is now available for download.)

I Create Emails ] } Clear All Selections ]

The complete cmdCreateEMails_Click event procedure is listed next; the code first checks that the message subject and body text have been entered on the form and sets a reference to the Outlook Application object, deletes the old text file, if it exists, then sets up a Select Case statement to work with text files differently, according to which option was selected in the Text Type option group:

Private Sub cmdCreateEMails_Click()

On Error GoTo ErrorHandler

Dim appOutlook As Outlook.Application

Dim fso As Scripting.FileSystemObject

Dim msg As Outlook.MailItem

Dim strBody As String

Dim strEMailRecipient

Dim strSubject As String

Dim strTo As String

Dim varItem As Variant

Dim strTest As String

Dim lngContactID As Long

Dim strFullName As String

Dim strText As String

Dim strCompanyName As String

Dim strDocsPath As String

Dim strFile As String

Dim blnSomeSkipped As Boolean Dim intTextType As Integer Dim strTitle As String Dim strPrompt As String Dim txt As Scripting.TextStream Dim tstr As ADODB.Stream

Set 1st = Me![lstSelectContacts] intTextType = Nz(Me![fraTextType].Value, 2) strDocsPath = GetCustomDocsPath()

Check that at least one contact has been selected.

If lst.ItemsSelected.Count = 0 Then strTitle = "No contact selected"

strPrompt = "Please select at least one contact" MsgBox prompt:=strPrompt, Buttons:=vbExclamation

+ vbOKOnly, title:=strTitle lst.SetFocus GoTo ErrorHandlerExit End If

Test for required message fields.

strSubject = Nz(Me![txtMessageSubject].Value) If strSubject = "" Then strTitle = "No subject entered"

strPrompt = "Please enter a subject"

MsgBox prompt:=strPrompt, Buttons:=vbExclamation

+ vbOKOnly, title:=strTitle Me![txtMessageSubject].SetFocus GoTo ErrorHandlerExit End If strBody = Nz(Me![txtMessageBody].Value) If strBody = "" Then strTitle = "No message body entered" strPrompt = "Please enter the message body" MsgBox prompt:=strPrompt, Buttons:=vbExclamation

+ vbOKOnly, title:=strTitle Me![txtMessageBody].SetFocus GoTo ErrorHandlerExit End If

Checks passed; proceed to create a message using the selected text output method.

Set appOutlook = GetObject(, "Outlook.Application") strFile = strDocsPath & "Skipped Records.txt" Debug.Print "Text file: " & strFile

On Error Resume Next

Delete existing file, if there is one. Kill strFile On Error GoTo ErrorHandler Select Case intTextType Case 1

Set tstr = New ADODB.Stream tstr.Open strText = "Information on progress creating " _

& "Outlook mail messages" tstr.WriteText Data:=strText, Options:=adWriteLine tstr.WriteText Data:=vbCrLf & vbCrLf blnSomeSkipped = False

For Each varltem In lst.ItemsSelected

Get the Contact ID and name for reference.

lngContactID = Nz(lst.Column(0, varltem)) Debug.Print "Contact ID: " & lngContactID strFullName = Nz(lst.Column(1, varItem))

Check for email address.

strEMailRecipient = Nz(lst.Column(2, varItem)) strTest = strEMailRecipient Debug.Print "Email address: " & strTest If strTest = "" Then blnSomeSkipped = True strText = "Contact No. " & lngContactID _ & " (" & strFullName _ & ") skipped; no email address" tstr.WriteText Data:=vbCrLf tstr.WriteText Data:=strText, _

Options:=adWriteLine GoTo NextContactADO End If

Check for company name.

strCompanyName = Nz(lst.Column(3, varItem))

strTest = strCompanyName

Debug.Print "Company name: " & strTest

If strTest = "" Then blnSomeSkipped = True strText = "Contact No. " & IngContactID _ & " (" & strFullName _ & ") skipped; no company name" tstr.WriteText Data:=vbCrLf tstr.WriteText Data:=strText, _

Options:=adWriteLine GoTo NextContactADO End If

Has required info; create new mail message and send to contact.

Set msg = appOutlook.Createltem(olMailltem) With msg

.To = strEMailRecipient .Subject = strSubject .Body = strBody .Send End With

NextContactADO:

Next varltem

If blnSomeSkipped = True Then

Write final line and save text file.

strText = "End of File" tstr.WriteText Data:=vbCrLf tstr.WriteText Data:=strText tstr.SaveToFile FileName:=strFile, _ Options:=adSaveCreateNotExist

End If Case 2

Set fso = CreateObject("Scripting.FileSystemObject") Set txt = fso.CreateTextFile(FileName:=strFile, _

overwrite:=True) strText = "Information on progress creating " _

& "Outlook mail messages" txt.WriteLine Text:=strText txt.WriteBlankLines Lines:=2

blnSomeSkipped = False

For Each varItem In lst.ItemsSelected

Get Contact ID and name for reference.

IngContactID = Nz(lst.Column(0, varltem)) Debug.Print "Contact ID: " & IngContactID strFullName = Nz(lst.Column(1, varltem))

Check for email address.

strEMailRecipient = Nz(lst.Column(2, varltem)) strTest = strEMailRecipient Debug.Print "Email address: " & strTest If strTest = "" Then blnSomeSkipped = True strText = "Contact No. " & lngContactID _ & " (" & strFullName _ & ") skipped; no email address" txt.WriteBlankLines Lines:=1 txt.WriteLine Text:=strText GoTo NextContactFSO End If

Check for company name.

strCompanyName = Nz(lst.Column(3, varItem)) strTest = strCompanyName Debug.Print "Company name: " & strTest If strTest = "" Then blnSomeSkipped = True strText = "Contact No. " & lngContactID _ & " (" & strFullName _ & ") skipped; no company name" txt.WriteBlankLines Lines:=1 txt.WriteLine Text:=strText GoTo NextContactFSO End If

Has required info; create new mail message and send to contact.

Set msg = appOutlook.CreateItem(olMailItem) With msg

.To = strEMailRecipient .Subject = strSubject .Body = strBody .Send End With

NextContactFSO:

Next varItem

Write final line.

strText = "End of File" txt.WriteBlankLines Lines:=1 txt.WriteLine Text:=strText

Case 3

Open text file for writing information about export progress.

Open strFile For Output As #1

strText = "Information on progress creating " _

& "Outlook mail messages" Print #1, strText Print #1, Print #1, blnSomeSkipped = False

For Each varltem In lst.ItemsSelected

Get Contact ID and name for reference.

lngContactID = Nz(lst.Column(0, varltem)) Debug.Print "Contact ID: " & lngContactID strFullName = Nz(lst.Column(1, varItem))

Check for email address.

strEMailRecipient = Nz(lst.Column(2, varItem)) strTest = strEMailRecipient Debug.Print "Email address: " & strTest If strTest = "" Then blnSomeSkipped = True strText = "Contact No. " & lngContactID _ & " (" & strFullName _ & ") skipped; no email address" Print #1, Print #1, strText GoTo NextContactVB End If

Check for company name.

strCompanyName = Nz(lst.Column(3, varItem))

strTest = strCompanyName

Debug.Print "Company name: " & strTest

If strTest = "" Then blnSomeSkipped = True strText = "Contact No. " & lngContactID _ & " (" & strFullName _ & ") skipped; no company name" Print #1, Print #1, strText GoTo NextContactVB End If

Has required info; create new mail message and send to contact.

Set msg = appOutlook.Createltem(olMailltem) With msg

.To = strEMailRecipient .Subject = strSubject .Body = strBody .Send End With

NextContactVB:

Next varItem

If blnSomeSkipped = True Then

Write final line and close text file.

strText = "End of file" Print #1, Print #1, strText Close #1 End If

End Select

Open text file in Notepad.

Shell "Notepad " & strFile

ErrorHandlerExit: Exit Sub

ErrorHandler:

Outlook is not running; open Outlook with CreateObject.

If Err.Number = 429 Then

Set appOutlook = CreateObject("Outlook.Application") Resume Next ElseIf Err.Number = 55 Then

File is already open; close it.

Close #1 Resume Else

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

End Sub

Figure 9.11 shows a typical text file created by the cmdCreateEMails_Click event procedure code (the text file is the same regardless of the method used to create it).

FIGURE 9.11

A text file with information on skipped records.

1 Skipped Records.txt - Notepad

1 <=, 1 ED L£3-4

File Edit Format View Help

1 |Information on progress creating Outlook man! messages

Contact No. 440 (Armstrong, Harriette) skipped; no email address contact no. 493 (Burton, Nicholas) skipped; no company name Contact No. 476 (Carver, Peter) skipped; no email address End of File

Figure 9.12 shows one of the email messages created by the previous code.

As is so often the case with Access, you have a choice of several techniques to use when working with text files in VBA code. Any of the three methods discussed in the next sections can create a text file and write to it; which method you use depends on such factors as your familiarity with the technique, or the need for extra references in the database to support the code. I generally use the FileSystemObject method, partly because I usually have a reference set to the Scripting Runtime library for other purposes and partly because its syntax is the most intuitive. If your database has a reference to the ADO library, but not the Scripting Runtime library, you can use the ADO method to avoid the need for setting an extra reference; if you don't have a reference set to either the ADO or Scripting Runtime libraries, you can use the VB method to avoid setting an extra reference.

An email message created from code.

Cta ri

j. > v Test of ADO email

■l^l-s-l^ad'

H :

jcalrbri |l2 - ¡ A' A H •= - != * ||

m w

I s-Ji m-

r 1

Paste ^

B I u ■ A - s- ;=- *=-

Address Check Book Names

Follow a Up *

Spelling

has not been sent.

. «„ 1 1

Subject Test of ADO email

Just a test

0 0

Post a comment