Expert One-on-One Microsoft Access Application Development
The previous three chapters discussed how to use other Office components to enhance your Access applications. There are other export options as well, beyond the bounds of Office—you can write code to send faxes via Symantec WinFax, using either the older DDE technology or Automation, and you can use the antique DOS Write method to create plain text files with information about the progress of mail merges and other procedures.
Sending Faxes with WinFax
Depending on your Windows and Office version, you may have some built-in faxing capability, but if you need more control over sending faxes, especially with attachments, WinFax Pro is an excellent solution. For many versions now, WinFax has supported DDE (Dynamic Data Exchange), an older method of data exchange. With WinFax v. 10.0 or higher, you can also use Automation code to communicate with WinFax.
| Important | There have been reports of compatibility problems between WinFax 10.0 and Windows XP; the 10.02 upgrade should fix these problems. On my system, WinFax 10.02 works fine with Windows XP Pro, SP 1. |
Sending a Single Fax Using DDE
The main menu of the Non-Office Data Exchange sample database (shown in Figure 14.1) lets you send a simple fax to a recipient selected from a combobox.
There are two textboxes at the bottom of the main menu for entering the Docs path (for creating document files to fax as attachments) and the WinFax folder. The AfterUpdate event procedures for these textboxes are listed below, together with two sets of functions that verify the paths; the functions are called from various procedures in the database.
Private Sub txtDocsPath_AfterUpdate() On Error GoTo ErrorHandler Dim strFolderPath As String DoCmd.RunCommand acCmdSaveRecord ‘Test the validity of the new folder path strFolderPath = Nz(Me![txtDocsPath].Value) Debug.Print "Folder path: " & strFolderPath If strFolderPath = "" Then strTitle = "No path entered" strPrompt = "Please enter a Docs folder path" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit Else ‘Check for trailing backslash, and add if needed If Right(strFolderPath, 1) <> "\" Then strFolderPath = strFolderPath & "\" Me![txtDocsPath].Value = strFolderPath Call SaveDocsDir(strFolderPath) End If Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(strFolderPath) = False Then strTitle = "Folder path invalid" strPrompt = "Please enter a valid Docs folder path" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit End If End If ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub Private Sub txtWinFaxPath_AfterUpdate() On Error GoTo ErrorHandler Dim strFolderPath As String DoCmd.RunCommand acCmdSaveRecord ‘Test the validity of the new folder path strFolderPath = Nz(Me![txtWinFaxPath].Value) Debug.Print "Folder path: " & strFolderPath If strFolderPath = "" Then strTitle = "No path entered" strPrompt = "Please enter a WinFax folder path" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit Else ‘Check for trailing backslash, and add if needed If Right(strFolderPath, 1) <> "\" Then strFolderPath = strFolderPath & "\" Me![txtWinFaxPath].Value = strFolderPath Call SaveWinFaxDir(strFolderPath) End If Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(strFolderPath) = False Then strTitle = "Folder path invalid" strPrompt = "Please enter a valid WinFax folder path" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit End If End If ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub Public Function CheckDocsDir() As Boolean On Error GoTo ErrorHandler Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset) With rst .MoveFirst strFolderPath = Nz(![DocsPath]) If strFolderPath = "" Then strFolderPath = "C:\My Documents\" End If End With ‘Test the validity of the folder path Debug.Print "Folder path: " & strFolderPath If strFolderPath = "" Then strTitle = "No path entered" strPrompt = "Please enter a Docs folder path on the main menu" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle CheckDocsDir = False GoTo ErrorHandlerExit Else Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(strFolderPath) = False Then strTitle = "Folder path invalid" strPrompt = "Please enter a valid Docs folder path on the main menu" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit CheckDocsDir = False End If End If CheckDocsDir = True ErrorHandlerExit: Exit Function ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Function Public Function GetDocsDir() As String On Error GoTo ErrorHandler Dim strFolderPath As String Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset) With rst .MoveFirst strFolderPath = Nz(![DocsPath]) If strFolderPath = "" Then strFolderPath = "C:\My Documents\" End If End With ‘Test the validity of the folder path Debug.Print "Folder path: " & strFolderPath If strFolderPath = "" Then strTitle = "No path entered" strPrompt = "Please enter a Docs folder path on the main menu" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit Else Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(strFolderPath) = False Then strTitle = "Folder path invalid" strPrompt = "Please enter a valid Docs folder path on the main menu" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit End If End If strDocsDir = strFolderPath & "Access Merge\" Debug.Print "Access Merge subfolder: " & strDocsDir ‘Test for existence of Access Merge subfolder, and create ‘it if it is not found Set fso = CreateObject("Scripting.FileSystemObject") If Not fso.FolderExists(strDocsDir) Then ‘Access Merge subfolder does not exist; create it fso.CreateFolder strDocsDir End If GetDocsDir = strDocsDir ErrorHandlerExit: Exit Function ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Function Public Function CheckWinFaxDir() As Boolean On Error GoTo ErrorHandler Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset) With rst .MoveFirst strFolderPath = Nz(![WinFaxPath]) If strFolderPath = "" Then strFolderPath = "C:\My Documents\" End If End With ‘Test the validity of the folder path Debug.Print "Folder path: " & strFolderPath If strFolderPath = "" Then strTitle = "No path entered" strPrompt = "Please enter a WinFax folder path on the main menu" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle CheckWinFaxDir = False GoTo ErrorHandlerExit Else Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(strFolderPath) = False Then strTitle = "Folder path invalid" strPrompt = "Please enter a valid WinFax folder path on the main menu" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit CheckWinFaxDir = False End If End If CheckWinFaxDir = True ErrorHandlerExit: Exit Function ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Function Public Function GetWinFaxDir() As String On Error GoTo ErrorHandler Dim strFolderPath As String Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInfo", dbOpenDynaset) With rst .MoveFirst strFolderPath = Nz(![WinFaxPath]) If strFolderPath = "" Then strFolderPath = "C:\Program Files\WinFax\" End If End With ‘Test the validity of the folder path Debug.Print "Folder path: " & strFolderPath If strFolderPath = "" Then strTitle = "No path entered" strPrompt = "Please enter a WinFax folder path on the main menu" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit Else Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(strFolderPath) = False Then strTitle = "Folder path invalid" strPrompt = "Please enter a valid WinFax folder path on the main menu" MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit End If End If GetWinFaxDir = strFolderPath ErrorHandlerExit: Exit Function ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Function
After entering the fax subject and text into textboxes (bound to fields in tblInfo, so the values are preserved between Access sessions), and selecting a fax recipient from the combobox, clicking the large Fax command button runs the code listed below. This Click event procedure sets a number of variables to use when sending the fax, picking up information from the textboxes and various columns of the selected row in the combobox’s list. If no subject or text was entered, a default subject (“Reminder”) and a message about scheduling the next meeting is composed. Finally, a dialog form opens for inspection and possible editing of the fax text and date (the fax is sent from this form).
Private Sub cmdFax_Click() On Error GoTo ErrorHandler Dim strFaxRecipient As String Dim strFaxNumber As String Dim dteLastMeeting As Date Dim strSubject As String Dim strMessage As String Dim strBody As String Dim strCompanyName As String Dim frm As Access.Form ‘Check for required fax information strFaxNumber = Nz(Me![cboRecipients].Column(1)) If strFaxNumber = "" Then GoTo ErrorHandlerExit Else strFaxRecipient = Nz(Me![cboRecipients].Column(3)) End If strCompanyName = Nz(Me![cboRecipients].Column(4)) dteLastMeeting = CDate(Me![cboRecipients].Column(2)) strSubject = Nz(Me![FaxSubject], "Reminder") strMessage = Nz(Me![FaxText]) If strMessage <> "" Then strBody = strMessage Else strBody = "Your last meeting was on " & dteLastMeeting & "; please call to arrange a meeting by the end of the year." End If ‘Open form for creating new fax DoCmd.OpenForm FormName:="fdlgFax" Set frm = Forms![fdlgFax] With frm ![txtFaxNumber] = strFaxNumber ![txtContactName] = strFaxRecipient ![txtCompanyName] = strCompanyName ![txtFaxDate] = Date ![txtSubject] = strSubject ![txtBody] = strBody End With ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
The Edit and Send Fax dialog form is shown in Figure 14.2.
The Edit and Send Fax form’s Load event calls a Sub procedure, OpenWinFax. This procedure (which follows) creates an instance of WinFax and opens the Message Manager so you can see the progress of the faxes you send. I open WinFax at this point to prevent delays when clicking the Send Fax button. If you do a lot of faxing, you could run this function from the main menu’s Load event or an AutoExec macro. Once the Message Manager has been opened, it is available for any future faxes you may send from the database.
Public Function OpenWinFax() ‘Opens an instance of WinFax (if WinFax is not open) when the ‘Fax dialog is opened On Error GoTo ErrorHandler Dim lngChannel As Long lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL") DDETerminate channum:=lngChannel ErrorHandlerExit: Exit Function ErrorHandler: If Err.Number = 282 Then strWinFaxDir = GetWinFaxDir & "FAXMNG32.EXE" Shell strWinFaxDir Resume ErrorHandlerExit Else MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End If End Function
The error handler of this procedure gets the WinFax directory from tblInfo, in case WinFax is not found in the default directory. The GetWinFaxDir function is one of the set of path-checking functions described earlier in this chapter.
| Important | If you look up information about DDE communication with WinFax in WinFax Help or the Symantec Knowledge Base, the sample code is not quite right for Access (the nearest code samples I could find were for Word 95, as near as I could tell). I had to do a good deal of trial-and-error tinkering to figure out the DDE syntax that would work in Access 2000 and higher. |
The Send Fax command button’s Click event procedure follows, with explanatory text.
Private Sub cmdSendFax_Click() On Error GoTo ErrorHandler
Declare variables for sending fax.
Dim strFaxNumber As String Dim strSubject As String Dim strContactName As String Dim strCompany As String Dim strBody As String Dim dteSend As Date Dim frm As Access.Form Dim strMessage As String Dim strSendTime As String Dim strSendDate As String Dim strRecipient As String Dim lngChannel As Long Dim strWinFaxDir As String Dim strCoverSheet As String
Test for required information, and exit the procedure if not found.
strFaxNumber = Me![txtFaxNumber].Value If strFaxNumber = "" Then MsgBox "Please enter a fax number." GoTo ErrorHandlerExit Else strFaxNumber = "1" & strFaxNumber End If Debug.Print "Fax: " & strFaxNumber strSubject = Me![txtSubject].Value strBody = Me![txtBody].Value If strBody = "" Then MsgBox "Please enter the fax text." Me![txtBody].SetFocus GoTo ErrorHandlerExit End If
Check whether a valid date has been entered, and exit if it has not.
If IsDate(Me![txtFaxDate].Value) = False Then MsgBox "Please enter a send date." Me![txtFaxDate].SetFocus GoTo ErrorHandlerExit Else dteSend = Me![txtFaxDate].Value End If
All tests passed; send fax.
If strFaxNumber <> "" Then strContactName = Nz(Me![txtContactName]) strCompany = Nz(Me![txtCompanyName]) If dteSend = Date Then strSendTime = Format(Now(), "hh:mm:ss") Else strSendTime = "08:00:00" End If strSendDate = Format(dteSend, "mm/dd/yy") strCoverSheet = GetWinFaxDir & "COVER\BASIC1.CVP" Debug.Print "Cover sheet: " & strCoverSheet
Start the DDE connection to WinFax.
Create the link and disable automatic reception in WinFax.
lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL") DDEExecute channum:=lngChannel, Command:="GoIdle" DDETerminate channum:=lngChannel
Create a new link with the TRANSMIT topic.
lngChannel = DDEInitiate("FAXMNG32", "TRANSMIT")
Start DDEPokes to control WinFax.
strRecipient = "recipient(" & Chr$(34) & strFaxNumber & Chr$(34) & "," & Chr$(34) & strSendTime & Chr$(34) & "," & Chr$(34) & strSendDate & Chr$(34) & "," & Chr$(34) & strContactName & Chr$(34) & "," & Chr$(34) & strCompany & Chr$(34) & "," & Chr$(34) & strSubject & Chr$(34) & ")" Debug.Print "Recipient string: " & strRecipient DDEPoke channum:=lngChannel, Item:="sendfax", Data:=strRecipient
Specify the cover page to use.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="setcoverpage(" & Chr$(34) & strCoverSheet & Chr$(34) & ")"
Send the cover sheet text.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="fillcoverpage(" & Chr$(34) & strBody & Chr$(34) & ")"
Show the WinFax send screen.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="showsendscreen(" & Chr$(34) & "0" & Chr$(34) & ")"
Set the resolution for the fax.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="resolution(" & Chr$(34) & "HIGH" & Chr$(34) & ")"
Send the fax.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="SendfaxUI" DDETerminate channum:=lngChannel lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL") DDEExecute channum:=lngChannel, Command:="GoActive" DDETerminate channum:=lngChannel End If ErrorHandlerExit: DoCmd.Close objecttype:=acForm, objectname:=Me.Name Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
You will see a WinFax dialog (shown in Figure 14.3) showing the progress of the fax call. If it doesn’t connect the first time, WinFax will try again several times until the fax is sent.
Faxing a Report Using DDE
The Main Menu faxing button only sends a fax cover page with a message. If you need to fax a report, you can use another form in the database to select a report to fax to a recipient. This dialog form is similar to the form in Chapter 12, Working with Outlook, but with fewer formatting choices: You have a choice of Snapshot or PDF format, or printing directly to the fax. The Snapshot and PDF format choices create a Snapshot or PDF file, which is then attached to an outgoing fax. The Print to Fax selection just prints the report directly to the fax. Printing directly to fax saves the time it takes to create the Snapshot or PDF file, but you have to enter the phone number, fax subject, and text manually, so it may not save time in the long run.
The Fax Access Report (DDE) dialog form is shown in Figure 14.4.
When you click the Fax Report command button, if you selected the Snapshot format, you will first get a Printing dialog; while the report is exported to Snapshot format, and then a Creating Attachment Image dialog from WinFax, while the Snapshot file is converted to an attachment, and then the fax with the selected attachment is sent.
If you selected the PDF format, you will get a Save PDF File As dialog, with the \My Documents\Access Merge folder preselected; save the document to this folder with the default file name, because that is where the code will look for it. This dialog is shown in Figure 14.5.
The Creating Adobe PDF dialog appears, while the PDF file is created. After the file is created, it opens in Adobe Acrobat or Acrobat Viewer. Next the Creating Attachment Image dialog appears, and finally the fax is sent with the attached report.
In Access 2003, there is a problem with converting both snapshot and PDF files to fax format for use as fax attachments; at the time of writing it is not clear whether the problem is with Access 2003 or WinFax.
Both the Snapshot and PDF selections pick up the recipient’s name and fax number from the Access dialog. The last selection, Fax to Printer, does not pick up this information; you need to enter it into the WinFax Send window, as shown in Figure 14.6.
After filling in the name, fax number, and other information on this screen, and clicking the Send button, you get another WinFax screen where you can type in the text to appear on the cover sheet (if desired), and click the Send button to start sending the fax.
The Fax Report command button on the Fax Access Report dialog runs the Click event procedure listed below, which calls the appropriate procedure for each of the three available format types.
Private Sub cmdFaxReport_Click() On Error GoTo ErrorHandler Dim strFormatType As String strFormatType = Me![cboSelectFormat].Column(0) Debug.Print "Selected format: " & strFormatType Select Case strFormatType Case "Access Snapshot" Call FaxReportSNP(Me) Case "Adobe PDF" Call FaxReportPDF(Me) Case "Print to Fax" Call FaxReport(Me) End Select ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
The Snapshot procedure is listed below, with commentary.
Sub FaxReportSNP(frm As Access.Form) On Error GoTo ErrorHandler
Declare variables for fax properties, picking up information from columns of various comboboxes on the form.
strReport = Nz(frm![cboSelectReport].Column(0)) strDataSource = Nz(frm![cboSelectReport].Column(2)) strDisplayName = Nz(frm![cboSelectReport].Column(1)) strRecipient = Nz(frm![cboSelectRecipient].Column(1)) strFileName = Mid(Nz(frm![cboSelectReport].Column(0)), 4) strExtension = Nz(frm![cboSelectFormat].Column(1))
Get the Documents directory from tblInfo.
If CheckDocsDir = False Then GoTo ErrorHandlerExit End If strFilePath = GetDocsDir() strFileAndPath = strFilePath & strFileName & strExtension Debug.Print "File name and path: " & strFileAndPath strCompany = Nz(frm![cboSelectRecipient].Column(4))
Test for required information, and exit if not found.
strFaxNumber = frm![cboSelectRecipient].Column(1) If strFaxNumber = "" Then MsgBox "Please enter a fax number" GoTo ErrorHandlerExit Else strFaxNumber = "1-" & strFaxNumber End If Debug.Print "Fax: " & strFaxNumber strSubject = strDisplayName & " report" strBody = "This file was exported from the " & strSubject & " on " & Format(Date, "m/d/yyyy") & "." & vbCrLf strSendTime = Format(Time, "hh:mm:ss") strSendDate = Format(Date, "mm/dd/yy")
Initialize the progress bar (using an arbitrary division of four units).
varReturn = SysCmd(acSysCmdInitMeter, "Creating output file ...", 4)
Update the progress bar.
varReturn = SysCmd(acSysCmdUpdateMeter, 1)
Delete the old file, if there is one.
Set fso = CreateObject(“Scripting.FileSystemObject”).
If fso.FileExists(strFileAndPath) = True Then fso.DeleteFile strFileAndPath End If
Update the progress bar.
varReturn = SysCmd(acSysCmdUpdateMeter, 2)
Create new snapshot file in Documents\Access Merge folder.
DoCmd.OutputTo objecttype:=acOutputReport, objectname:=strReport, outputformat:=acFormatSNP, outputfile:=strFileAndPath, autostart:=False
Update the progress bar.
varReturn = SysCmd(acSysCmdUpdateMeter, 3)
Test for existence of specified report file, with a loop to prevent premature cancellation.
TryAgain: Set fso = CreateObject("Scripting.FileSystemObject") If fso.FileExists(strFileAndPath) = False Then GoTo TryAgain End If
Update the progress bar.
varReturn = SysCmd(acSysCmdUpdateMeter, 4)
Create new fax and attach snapshot file to it.
strCoverSheet = GetWinFaxDir & "COVER\BASIC1.CVP" Debug.Print "Cover sheet: " & strCoverSheet
Start DDE connection to WinFax.
Create the link and disable automatic reception in WinFax.
lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL") DDEExecute channum:=lngChannel, Command:="GoIdle" DDETerminate channum:=lngChannel
Create a new link with the TRANSMIT topic.
lngChannel = DDEInitiate("FAXMNG32", "TRANSMIT")
Start DDEPokes to control WinFax.
strRecipient = "recipient(" & Chr$(34) & strFaxNumber & Chr$(34) & "," & Chr$(34) & strSendTime & Chr$(34) & "," & Chr$(34) & strSendDate & Chr$(34) & "," & Chr$(34) & strContactName & Chr$(34) & "," & Chr$(34) & strCompany & Chr$(34) & "," & Chr$(34) & strSubject & Chr$(34) & ")" Debug.Print "Recipient string: " & strRecipient DDEPoke channum:=lngChannel, Item:="sendfax", Data:=strRecipient
Specify the cover page.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="setcoverpage(" & Chr$(34) & strCoverSheet & Chr$(34) & ")"
Send the cover sheet text.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="fillcoverpage(" & Chr$(34) & strBody & Chr$(34) & ")"
Attach the saved report snapshot file.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="attach(" & Chr$(34) & strFileAndPath & Chr$(34) & ")"
Show the send screen.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="showsendscreen(" & Chr$(34) & "0" & Chr$(34) & ")"
Set the resolution.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="resolution(" & Chr$(34) & "HIGH" & Chr$(34) & ")"
Send the fax.
DDEPoke channum:=lngChannel, Item:="sendfax", Data:="SendfaxUI" DDETerminate channum:=lngChannel lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL") DDEExecute channum:=lngChannel, Command:="GoActive" DDETerminate channum:=lngChannel
Update the progress bar.
varReturn = SysCmd(acSysCmdUpdateMeter, 4) ErrorHandlerExit:
Remove the progress bar.
varReturn = SysCmd(acSysCmdRemoveMeter) Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
The PDF procedure is very similar; only the different portion of this procedure is listed below.
Sub FaxReportPDF(frm As Access.Form) ‘This code assumes that you have installed Adobe Acrobat and have assigned ‘the PDF printer to a copy of each report with the PDF suffix. On Error GoTo ErrorHandler
The suffix “PDF” is added to the report name, to pick up the report version with the PDF printer selected.
strReport = Nz(frm![cboSelectReport].Column(0)) & "PDF" . . .
Print the report to the PDF printer.
DoCmd.OpenReport strReport, acViewNormal
The final procedure is very simple; it just prints the selected report (a version with “Fax” appended to its name) to the WinFax printer.
Sub FaxReport(frm As Access.Form) ‘This code assumes that you have assigned the WinFax printer to a copy of ‘each report with the Fax suffix. On Error GoTo ErrorHandler strReport = Nz(frm![cboSelectReport].Column(0)) & "Fax"
Print the report to Fax printer.
DoCmd.OpenReport strReport, acViewNormal ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
For faxing a report, I recommend using the Snapshot format, because it is somewhat quicker than the PDF format, and doesn’t require that you own Adobe Acrobat. And unlike the case with sending a file as an Outlook mail message attachment, the recipient doesn’t need Access to view the report converted to Snapshot format. The Snapshot selection also lets you specify the fax information on the Access dialog form, instead of having to type it into two WinFax dialogs, as with the Print to Fax selection.
Sending Multiple Faxes Using Automation
Just as when you create Outlook items from records in an Access table, you may want to create a fax and send it to multiple recipients. This can be done using a MultiSelect listbox on an Access form. The Fax to Multiple Recipients (Automation) form also illustrates use of Automation (instead of DDE) to communicate with WinFax.
As with DDE, the syntax in the WinFax documentation (in this case, the WinFax 10.0 SDK manual) is not quite right for use in Access VBA. The closest examples are for VB 5.0, and I had to experiment with syntax variants to get the Automation code to work right.
The Fax to Multiple Recipients (Automation) form is shown in Figure 14.7.
To work with WinFax using Automation code, you need to set a reference to the WinFax Automation Server, in the References dialog opened from the Tools menu in the Visual Basic window, as shown in Figure 14.8.
The only WinFax object you need to work with when sending faxes is the Send object. I found that the CreateObject syntax given in the WinFax SDK manual did not work in Access VBA. However, I was able to create a Send object (strictly speaking, a CSDKSend object, but it is usually referred to as just the Send object) using the New keyword, as in the following line of code:
Dim wfxSend As New wfxctl32.CSDKSend
The Send object has only methods and events, no properties. They can be viewed in the Object Browser with the wfxctl32 library selected. Figure 14.9 shows some of the methods of the Send object.
More information is also available in the WinFax SDK manual, a PDF file you can print out. This manual is available on the WinFax 10.0 or higher CD, and it can also be downloaded from the Symantec Web site. However, the syntax for the VB samples (there are no VBA samples) does not always work in VBA. See the sample code that follows for some syntax that does work.
When you select a report on this form, a Snapshot file is produced for attachment to the fax, using code similar to that in the Snapshot procedure listed previously. When you click the Send Fax command button, a Click event procedure is run. This procedure follows, with commentary.
Private Sub cmdSendFax_Click() On Error GoTo ErrorHandler
Declare variables for use in fax.
Dim blnSomeSkipped As Boolean Dim i As String Dim lngContactID As Long Dim strBody As String Dim strSubject As String Dim strDate As String Dim strTime As String Dim strDocName As String Dim strDocsPath As String Dim strFaxNumber As String Dim strFile As String Dim strFullName As String Dim strPrompt As String Dim strTest As String Dim strTestFile As String Dim strText As String Dim strTextFile As String Dim strTitle As String Dim varItem As Variant
Declare WinFax Send object variable. This is the only syntax that I found would work in Access VBA code.
Dim wfxSend As New wfxctl32.CSDKSend
Check that at least one record has been selected, and exit if it has not.
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
Check that subject and fax body have been entered, and exit if they have not.
strSubject = Nz(Me![txtSubject]) If strSubject = "" Then strTitle = "Missing Subject" strPrompt = "Please enter fax subject." Me![txtSubject].SetFocus GoTo ErrorHandlerExit End If strBody = Nz(Me![txtBody]) If strBody = "" Then strTitle = "Missing Fax body" strPrompt = "Please enter fax body." Me![txtBody].SetFocus GoTo ErrorHandlerExit End If
Open the text file for writing information about export progress.
strFile = strDocsPath & "Export Progress.txt" Debug.Print "Text file: " & strFile Open strFile For Output As #1 Print #1, "Information on progress faxing selected contacts" Print #1, Print #1, blnSomeSkipped = False
Create the fax, using the methods of the WinFax Send object.
With wfxSend .SetSubject (strSubject) .SetCoverText (strBody)
Attach the report, if one has been selected.
Debug.Print "File attachment: " & pstrSnapshotFile .AddAttachmentFile pstrSnapshotFile
Set up a For Each . . . Next structure to process all the selected records in the listbox, using the ItemsSelected collection.
For Each varItem In lst.ItemsSelected
Get the Contact ID for reference.
lngContactID = Nz(lst.Column(0, varItem)) Debug.Print "Contact ID: " & lngContactID
Check for required information, and skip any records that are missing a value in one of the required fields.
strTest = Nz(lst.Column(1, varItem)) Debug.Print "Contact name: " & strTest If strTest = "" Then blnSomeSkipped = True strText = "Contact No. " & lngContactID & " skipped; no name" Print #1, Print #1, strText GoTo NextItemContact Else strFullName = Nz(lst.Column(7, varItem)) End If strFaxNumber = Nz(lst.Column(13, varItem)) strTest = strFaxNumber Debug.Print "Fax number: " & strTest If strTest = "" Then blnSomeSkipped = True strText = "Contact No. " & lngContactID & " (" & strFullName & ") skipped; no fax number" Print #1, Print #1, strText GoTo NextItemContact Else strFaxNumber = "1-" & strFaxNumber End If
All required information present; add recipient to fax.
.SetNumber (strFaxNumber) .SetTo (strFullName) .AddRecipient strText = "Contact No. " & lngContactID & " (" & strFullName & ") has all required information; adding to fax" Print #1, Print #1, strText NextItemContact: Next varItem
Send the fax to all recipients.
.Send (0) End With ErrorHandlerExit:
Close the text file, and set the WinFax Send object to Nothing.
Close #1 Set wfxSend = Nothing Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
After clicking this button, the Creating Attachment Image dialog appears as the Snapshot file is converted into a WinFax attachment, and then the fax is sent to all the selected recipients.
Категории