Emailing Exported Text Files

Once you have created text files from your Access data, you might want to email them to others who need to review the data. Clicking the "Send Job Lists to Contacts" button opens a form (shown in Figure 10-34) where you can select multiple contacts, and a job file (either .csv or .txt) to send as an attachment to the selected contacts. The figure also shows three email messages with the selected job file attachment.

FIGURE 10.34

A form for selecting contacts and a job file to email to them, with three email messages created from the form.

FIGURE 10.34

The cmdMergetoEMailMulti_Click event procedure is listed below: Private Sub cmdMergetoEMailMulti_Click() On Error GoTo ErrorHandler Dim strJobFile As String Set lst = Me![lstSelectContacts]

Check that at least one contact has been selected.

If lst.ItemsSelected.Count = 0 Then

MsgBox "Please select at least one contact" lst.SetFocus GoTo ErrorHandlerExit End If

Test for required fields.

strSubject = Me![txtSubject].Value If strSubject = "" Then

MsgBox "Please enter a subject" Me![txtSubject].SetFocus GoTo ErrorHandlerExit End If strBody = Me![txtBody].Value If strBody = "" Then

MsgBox "Please enter a message body" Me![txtBody].SetFocus GoTo ErrorHandlerExit End If

For Each varItem In lst.ItemsSelected Check for email address.

strEMailRecipient = Nz(lst.Column(1, varItem)) Debug.Print "EMail address: " & strEMailRecipient If strEMailRecipient = "" Then

GoTo NextContact End If strJobFile = Nz(Me![txtJobFile])

Create a new mail message with the job file attachment and send to contact.

Set appOutlook = GetObject(, "Outlook.Application") Set msg = appOutlook.Createltem(olMailltem) With msg

.To = strEMailRecipient .Subject = strSubject .Body = strBody If strJobFile <> "" Then

.Attachments.Add strJobFile End If .Display End With

NextContact:

Next varltem ErrorHandlerExit:

Set appOutlook = Nothing Exit Sub ErrorHandler:

Outlook is not running; open Outlook with CreateObject.

If Err.Number = 42 9 Then

Set appOutlook = CreateObject("Outlook.Application") Resume Next Else

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

End Sub

You may have contacts that have only an email address, or a phrase like "Tech. Support" entered as the last name, or contacts with just a first name, or a whole name entered into the LastName field, or sets of contacts who work for the same company, where the company name is entered differently on different contact records. Importing from such contacts can cause problems, such as creating multiple Company records with variations of a company name.

I am planning to upgrade the Synchronizing Contacts database to deal with various types of problem data, and to add some new features; look for an updated version of the database on my Web site, http://www.helenfeddema.com.

0 0

Post a comment