Expert One-on-One Microsoft Access Application Development
If you don’t have WinFax installed, when you open the sample database, you will see the error message shown in Figure 14.10, and the large button on the main menu will have the caption #Error, as shown in Figure 14.11.
If you open the References dialog from the Tools menu in the Visual Basic window, you will see the reference marked as “MISSING”, as shown in Figure 14.12. If you uncheck this reference, you will be able to select and use the Export Contacts to Outlook form for testing text file creation, but of course you won’t be able to send faxes.
Chapter 13, Working with Excel, covered exporting Access data to two different text formats—comma-delimited (.csv) and plain text (.txt). However, this is not the only way you can export to a text file from Access. Fossilized in Access VBA there are some antique DOS methods that you can use to create and write to text files. I like to use these methods to write information about exports, in particular to record any problems that result in Access records being skipped. Several of the earlier chapters used the Write # method to write information about skipped records to a text file. In this chapter, I expand the technique to write a line to a text file about every record selected for export, to give the user a complete record of the export, including both the records that were processed and those that were skipped.
Export Contacts to Outlook
The Export Contacts to Outlook form uses this technique. This form (shown in Figure 14.13) has a command button for selecting an Outlook folder (see Chapter 12, Working with Outlook, for an explanation of how this button works), a listbox for selecting contacts to export, and a command button to do the export (the command button is only enabled after selecting an Outlook folder).
When you click the Create Contact(s) button, the Click event procedure that follows runs. It is substantially similar to the procedure for exporting data to Outlook contacts on the Export to Outlook (Listbox) form in Chapter 12. The portions of the code related to creating and writing to the text file are commented in the code listed below.
Private Sub cmdCreateContacts_Click() On Error GoTo ErrorHandler
Declare Outlook, DAO, and text variables for use in exporting.
Dim blnSomeSkipped As Boolean Dim con As Outlook.ContactItem Dim dbs As DAO.Database Dim fldCalendar As Outlook.MAPIFolder Dim fldContacts As Outlook.MAPIFolder Dim i As String Dim itm As Object Dim lngContactID As Long Dim nms As Outlook.NameSpace Dim strBody As String Dim strCity As String Dim strCompanyName As String Dim strContactName As String Dim strCountry As String Dim strDocName As String Dim strDocsPath As String Dim strDocType As String Dim strEMailRecipient As String Dim strFile As String Dim strFullName As String Dim strJobTitle As String Dim strMessage As String Dim strName As String Dim strNameAndJob As String Dim strPostalCode As String Dim strPrompt As String Dim strSalutation As String Dim strShortDate As String Dim strStateProv As String Dim strStreetAddress As String Dim strTest As String Dim strTestFile As String Dim strText As String Dim strTextFile As String Dim strTitle As String Dim txt As Access.TextBox Dim varItem As Variant
Check that at least one record has been selected.
Set lst = Me![lstSelectMultiple] If lst.ItemsSelected.Count = 0 Then MsgBox "Please select at least one record." lst.SetFocus GoTo ErrorHandlerExit Else intColumns = lst.ColumnCount intRows = lst.ItemsSelected.Count End If
Set Outlook application variable; if Outlook is not running, the error handler defaults to CreateObject.
Set gappOutlook = GetObject(, "Outlook.Application")
Set a reference to the textbox for writing information about exported or skipped records.
Set txt = Me![txtExportProgress] If CheckDocsDir = False Then GoTo ErrorHandlerExit End If strDocsPath = GetDocsDir
Open a text file in the \My Documents\Access Merge folder for writing information about exported or skipped records.
strFile = strDocsPath & "Export Progress.txt" Debug.Print "Text file: " & strFile Open strFile For Output As #1
Write header information to the text file.
Print #1, "Information on progress exporting contacts to Outlook"
The following command creates a blank line in the text file.
Print #1,
Write the Outlook folder name to the text file.
Print #1, "Target folder: " & pfld.Name Print #1, Print #1, blnSomeSkipped = False strText = "" For Each varItem In lst.ItemsSelected ‘Get Contact ID for reference lngContactID = Nz(lst.Column(0, varItem)) Debug.Print "Contact ID: " & lngContactID ‘Check for required information strTest = Nz(lst.Column(1, varItem)) Debug.Print "Contact name: " & strTest If strTest = "" Then
If the contact name is missing, write information on this to the textbox and the text file.
blnSomeSkipped = True strText = "Contact No. " & lngContactID & " skipped; no name" Print #1, Print #1, strText
For writing to the textbox, append the new text to the existing text, and remove the leading CR + LF (Chr(10) & Chr(13)) so there won’t be a blank line before the first entry.
strText = txt.Value & vbCrLf & strText If Asc(Left(strText, 1)) = 10 Then strText = Mid(strText, 2) End If txt.Value = strText GoTo NextItemContact Else strFullName = Nz(lst.Column(7, varItem)) End If strEMailRecipient = Nz(lst.Column(12, varItem)) strTest = strEMailRecipient Debug.Print "Email address: " & strTest If strTest = "" Then
If the email address is missing, write information on this to the textbox and the text file.
blnSomeSkipped = True strText = "Contact No. " & lngContactID & " (" & strFullName & ") skipped; no email address" Print #1, Print #1, strText strText = txt.Value & vbCrLf & strText If Asc(Left(strText, 1)) = 10 Then strText = Mid(strText, 2) End If txt.Value = strText GoTo NextItemContact End If strStreetAddress = Nz(lst.Column(2, varItem)) strTest = strStreetAddress Debug.Print "Street address: " & strTest If strTest = "" Then
If the street address is missing, write information on this to the textbox and the text file.
blnSomeSkipped = True strText = "Contact No. " & lngContactID & " (" & strFullName & ") skipped; no street address" Print #1, Print #1, strText strText = txt.Value & vbCrLf & strText Debug.Print Asc(Left(strText, 1)) If Asc(Left(strText, 1)) = 13 Then strText = Mid(strText, 3) End If txt.Value = strText GoTo NextItemContact End If
All required information is present; create a contact.
Write a line about the creation of the contact to the textbox and the text file.
strText = "Contact No. " & lngContactID & " (" & strFullName & ") has all required information; creating an Outlook contact" Print #1, Print #1, strText strText = txt.Value & vbCrLf & strText txt.Value = strText strJobTitle = Nz(lst.Column(10, varItem)) strCity = Nz(lst.Column(3, varItem)) strStateProv = Nz(lst.Column(4, varItem)) strPostalCode = Nz(lst.Column(5, varItem)) strCountry = Nz(lst.Column(6, varItem)) strCompanyName = Nz(lst.Column(9, varItem)) strSalutation = Nz(lst.Column(11, varItem))
Create a new contact item in default local Contacts folder.
Set gappOutlook = GetObject(, Outlook.Application) Set con = gappOutlook.CreateItem(olContactItem) With con .CustomerID = lngContactID .FullName = strFullName .JobTitle = strJobTitle .BusinessAddressStreet = strStreetAddress .BusinessAddressCity = strCity .BusinessAddressState = strStateProv .BusinessAddressPostalCode = strPostalCode .BusinessAddressCountry = strCountry .CompanyName = strCompanyName .NickName = strSalutation .Email1Address = strEMailRecipient .Close (olSave) End With NextItemContact: Next varItem ErrorHandlerExit:
Close the text file.
Close #1 Exit Sub ErrorHandler: If Err = 429 Then ‘Outlook is not running; open Outlook with CreateObject. Set gappOutlook = CreateObject("Outlook.Application") Resume Next Else MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End If End Sub
The form also has a textbox that lists the same data that is written to the text file. You can see information about the export in the textbox in Figure 14.14.
If you are exporting only a few records, you can see all the data you need right in the textbox. However, when more than a few contacts are selected for export, you will need to refer to the text file for full information on the export. Figure 14.15 shows the text file produced by an export open in Notepad.
Категории