Mass Fax from Queries

[TheRacingOlds]

I need to fax a report to a whole bunch of people at once.

I have over a thousand companies filtered into queries by subcontractor scopes of work.
Acoustical Ceilings
Brick Pavers
Concrete
etc...

I'm wondering if it's possible to set up a form in which you can select multiple scopes of work and hit a fax button or something in which it would populate all of the fax numbers from every entry in each selected query.

I have done this with email, I select Drywall & Masonry & a bunch of other ones and it automatically puts all the companies' emails in the bcc field, I didn't know if this was possible through fax.

I'm open to software suggestions, right now we have winfax.

 

[Galaxiom]

Few things are as annoying as fax spam. Email is bad enough but having to pay for paper and ink really sucks.

I heard of a guy once who was so annoyed that he put a loop of black paper in his fax machine and sent it to the unsolicited sender in the middle of the night.

 

[TheRacingOlds]

That's nice, but I'm not spamming. This is business. In construction, you fax a lot of things.

Is there a way to do this?

 

[Galaxiom]

It has been a long time since I used it but I'm sure WinFax does have some kind of multiple send/mail merge function and a DDE (Dynamic Data Exchange) interface. Sorry but I don't know much about using DDE.

 

[ChrisO]

WinFaxPro can batch send.

This code was used seven years ago but should give you a starting place.
I will not be able to help further because I no longer have WinFaxPro on my machine.

The batching code…

Public Sub SendInvoiceBatch()
    Dim strFaxNumber    As String
    Dim rstInvoiceQuery As DAO.Recordset
    
    Set rstInvoiceQuery = CurrentDb.OpenRecordset("qryInvoiceGrouping", dbOpenDynaset)
    
    With rstInvoiceQuery
    
        Do Until .EOF
            Select Case !InvoiceMethod
                Case "Fax"
                    strFaxNumber = DLookup("[FAXNUM]", "Customer", "[ID] = " & ![CustomerID])
                    SendFax strFaxNumber, "rptFaxInvoice", , , , , , , "[CustomerID] = " & ![CustomerID]
                    UpdateInvoiceAsSent "Date Faxed", ![CustomerID]
                
                Case "Print"
                    DoCmd.OpenReport "rptPrintInvoice", acViewNormal, , "[CustomerID] = " & ![CustomerID]
                    UpdateInvoiceAsSent "Date Printed", ![CustomerID]
            End Select
            
            .MoveNext
        Loop
    
    End With
    
    Set rstInvoiceQuery = Nothing

End Sub

The single send code…

Public Function SendFax(ByVal strFaxNumber As String, _
                        ByVal strReport As String, _
               Optional ByVal strName As String, _
               Optional ByVal fLocal As Boolean = False, _
               Optional ByVal intDelay As Integer = 0, _
               Optional ByVal strPrefix As String, _
               Optional ByVal strSuffix As String, _
               Optional ByVal strCntryCode As String, _
               Optional ByVal strFilter As String, _
               Optional ByVal fOffPeak As Boolean = False) As Boolean

    Dim strAC      As String
    Dim strFax     As String
    Dim strDate    As String
    Dim strTime    As String
    Dim objWFXSend As Object
    
    On Error GoTo ErrorHandler
    
    DoCmd.SetWarnings False
        
    Set objWFXSend = CreateObject("WinFax.SDKSend")
        
    strFax = strPrefix & strFaxNumber & strSuffix
         
    strDate = CStr(Date)
    strTime = Format(DateAdd("h", intDelay, Time), "hh:nn:ss")
    
    If Val(Left(strTime, 2)) < Val(Left(Time, 2)) Then
        strDate = CStr(Date + 1)
    End If
    
    With objWFXSend
        .SetDate (strDate)
        .SetTime (strTime)
        .SetOffPeak (fOffPeak)
        .SetNumber (CStr(strFax))
        .SetTo (strName)
        .AddRecipient
        .SetPrintFromApp (1)
        .SEND (1)

        Do While .IsReadyToPrint = 0
            DoEvents
        Loop

        DoCmd.OpenReport strReport, acViewNormal, , strFilter

        SleepAPI 500
        .Done
        SleepAPI 500

    End With
        
    SendFax = True
        
ExitProcedure:
    On Error Resume Next
    DoCmd.SetWarnings True
    Set objWFXSend = Nothing
    Exit Function
    
ErrorHandler:
    Select Case Err.Number
            
        Case Else
            MsgBox "Sending Fax Error... " & Err.Number & ": " & Err.DESCRIPTION
               
    End Select
    
    SendFax = False

    Resume ExitProcedure
    
End Function

 

[TheRacingOlds]

Thank you SO MUCH! I will attempt to make it work

 

[TheRacingOlds]

I have placed the code in my db and it is giving me an error.

Compile Error: Sub or Function Not Defined, highlighted SendFax

Here is the code:
Private Sub Button_Click()
Dim MyDB As DAO.Database
Dim strFaxNumber As String
Dim rstInvoiceQuery As DAO.Recordset

Set MyDB = CurrentDb()
Set rstInvoiceQuery = MyDb.OpenRecordset("Concrete--Washout", dbOpenDynaset)
With rstInvoiceQuery
Do Until .EOF
Select Case !InvoiceMethod
Case "Fax"
strFaxNumber = DLookup("[FAXNUMBER]", "CompanyName", "[ID] = " & ![CompanyName])
SendFax strFaxNumber, "rptFaxInvoice", , , , , , , "[CompanyName] = " & ![CompanyName]
UpdateInvoiceAsSent "Date Faxed", ![CompanyName]
Case "Print"
DoCmd.OpenReport "00ITB-MitsubishiBldg", acViewNormal, , "[CompanyName] = " & ![CompanyName]
UpdateInvoiceAsSent "Date Printed", ![CompanyName]
End Select
.MoveNext
Loop
End With

Set rstInvoiceQuery = Nothing
End Sub

 

[xarxas]

you need to define the SendFax function. copy it somewhere

Public Function SendFax(ByVal strFaxNumber As String, _
                        ByVal strReport As String, _
               Optional ByVal strName As String, _
               Optional ByVal fLocal As Boolean = False, _
               Optional ByVal intDelay As Integer = 0, _
               Optional ByVal strPrefix As String, _
               Optional ByVal strSuffix As String, _
               Optional ByVal strCntryCode As String, _
               Optional ByVal strFilter As String, _
               Optional ByVal fOffPeak As Boolean = False) As Boolean

    Dim strAC      As String
    Dim strFax     As String
    Dim strDate    As String
    Dim strTime    As String
    Dim objWFXSend As Object
    
    On Error GoTo ErrorHandler
    
    DoCmd.SetWarnings False
        
    Set objWFXSend = CreateObject("WinFax.SDKSend")
        
    strFax = strPrefix & strFaxNumber & strSuffix
         
    strDate = CStr(Date)
    strTime = Format(DateAdd("h", intDelay, Time), "hh:nn:ss")
    
    If Val(Left(strTime, 2)) < Val(Left(Time, 2)) Then
        strDate = CStr(Date + 1)
    End If
    
    With objWFXSend
        .SetDate (strDate)
        .SetTime (strTime)
        .SetOffPeak (fOffPeak)
        .SetNumber (CStr(strFax))
        .SetTo (strName)
        .AddRecipient
        .SetPrintFromApp (1)
        .SEND (1)

        Do While .IsReadyToPrint = 0
            DoEvents
        Loop

        DoCmd.OpenReport strReport, acViewNormal, , strFilter

        SleepAPI 500
        .Done
        SleepAPI 500

    End With
        
    SendFax = True
        
ExitProcedure:
    On Error Resume Next
    DoCmd.SetWarnings True
    Set objWFXSend = Nothing
    Exit Function
    
ErrorHandler:
    Select Case Err.Number
            
        Case Else
            MsgBox "Sending Fax Error... " & Err.Number & ": " & Err.DESCRIPTION
               
    End Select
    
    SendFax = False

    Resume ExitProcedure
    
End Function

 

[TheRacingOlds]

Thanks...but now it is saying "Item Not Found in This Collection" and highlights "Select Case !InvoiceMethod"

 

[ChrisO]

TheRacingOlds.

I think you will need to understand a little more before you can simply copy/paste code into an application.

The first thing is that, sooner or later, this might work. When it works it will start sending a Fax, somewhere. So at this stage what you need is a receiving Fax for testing purposes so that you don’t aggravate a customer and you will need to restrict your query to that one and one only Fax number.

The second thing is that the code I posted did two things. It either sent a Fax or it printed an invoice for surface mail. At this point, even if you do not want to surface mail, I would leave the surface mail code in place. Create a new field in your query and call it InvoiceMethod and default it to the text ‘Fax’.

The third thing to do is comment out any line starting with ‘UpdateInvoiceAsSent’. Eventually you will need to update a Table to mark the Fax as sent. You don’t want to send duplicate Faxes and you do want to keep a record of when the Fax was sent. Not all Faxes will be sent immediately because a receiving Fax machine may not be switched on, or might not have paper, or blah blah blah. If that happens, WinFaxPro will spool the unsent Fax for retry latter. Therefore, strictly speaking, Fax sent time is not the time the Fax was actually sent, it is the time the Fax was sent to WinFaxPro and may hang in WinFaxPro’s outbox.

When you get to that point, your query should be modified to only select those customers where that particular Fax has not been sent.

The forth thing you will need is the SleepAPI call. I don’t recall why a half second was required but if I put it in there, it was required. Do a search on this site for the Sleep API call.

Fifth thing, when posting code to site could you please take the time to use the code tags. It not only makes the code more readable but it also indicates to the reader that some care was taken with the post.

Sixth thing, try not to ask a series of compile time questions. That sort of code can take days to write and test, not to mention the paper the receiving Fax machine will consume (which I just did). Even if the error seems difficult to solve you should still try and solve it. After all, it’s your baby and you will need to wipe its backside and that will need to be done without interfering with your customers Fax machines.

So, take your time, try hard and don’t send dummy test Faxes to your customers while testing.