Solved

MS-Access question

Posted on 1997-09-24
26
292 Views
Last Modified: 2006-11-17
Hi There !

I'm using MsAccess of Office 97 and I want to send a report for all the customers,
If I use sendObject or outputTo methods then I have to convert the report to type in the list (HTML,RTF etc) and in the conversion I lose the lines and the design (BMPs and etc).

If I'm maualy printing the report to MS fax printer then it's OK
What can I do in order to get the report faxed with the design I have ?

Thanks !

avior nissim : mailto:aviorn@public.tadiran.com

0
Comment
Question by:avior
  • 12
  • 11
  • +2
26 Comments
 

Author Comment

by:avior
ID: 1956619
Edited text of question
0
 

Author Comment

by:avior
ID: 1956620
Edited text of question
0
 
LVL 3

Expert Comment

by:d4jaj1
ID: 1956621
This is a utiliy created by Microsoft that wil allow you to send MS Acess reports -fully formated - via email.  You didn't state whether e-mail was acceptable, that's why I didn't answer the questions.  If it is, let me know and I'll answer the questions with the utility name and the website.
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956622
Do you mean faxed directly from Access to a customer list obtained from a customer table?

Could you please clarify this.
0
 

Author Comment

by:avior
ID: 1956623
Hi again , some clarificatios:

1. I want to send the report via FAX and NOT e-mail.
2. Yes, the customer got the fax, but as a wrote the design changed complitly
3. I want to send to all the customers from customers table, but this is not the question, my question is how can i not lose the design of the report
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956624
Is your preference Winfax or MS Exchange. From networked fax machine?

0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956625
Its ok. i understand the requirements now.
0
 
LVL 1

Expert Comment

by:arimsoft
ID: 1956626
1. In design mode of report set up it on printer: "Microsoft Fax"
2. Save report
3. Make button for print this report.
4. If you press of your button, will running Microsoft Fax
(or WinFax, I'm use WinFax), you input to MS Fax number and
your report will send.
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956627
I think avior wants the process automated, so fax numbers do not have to be entered manually.
0
 

Author Comment

by:avior
ID: 1956628
This doesn't answer my question,
I want to automate the fax for all the customers, as you can see from what I wrote in my question that I knew this.
0
 
LVL 1

Expert Comment

by:BBC
ID: 1956629
So if you "print"/fax it directly from the screen, its okay. But if you do this automated by VB the design is lost ?
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956630
I can automate using DDE with Winfax (i have the code example).

0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956631
You might be intereseted in a new Microsoft frreebie - Snapshot Viewer.

You can email your Access reports or snapshot files and the recipients can view/print (even selcted pages) them with Snapshot viewer.

So you knowing how to automate using email, apply this to snapshot files.

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 4

Expert Comment

by:ozphil
ID: 1956632
The snapshot reports are reproduced exactly as the Access design - not like the  RichTextFile joke.

Ive tried it Using Access97, MS exchange, saving to files, etc. And it works well.

0
 

Author Comment

by:avior
ID: 1956633
Hello Again !

1. I don't want to use SnapShot because the user has to have the viewer.
2. the thing that drives me crazy is that it's ok when I print it to ms-fax printer manualy, so there MUST be some way to do it automaticly.
0
 

Author Comment

by:avior
ID: 1956634
Adjusted points to 150
0
 

Author Comment

by:avior
ID: 1956635
Maybe This Will give someone a push ... 150 points now !!!
0
 
LVL 4

Accepted Solution

by:
ozphil earned 150 total points
ID: 1956636
The Winfax solution given below should do the job for you.

Option Compare Database
Option Explicit

Global strInvoiceWhere ' Global for both MSFax and WinFax examples

Declare Function FindWindow& Lib "user32" Alias "FindWindowA" (ByVal IpClassName As String, ByVal IpWindowName As String)    ' WinFax only








    '********************************************************************************************
    ' This function will walk through the Customers table and fax the Invoices report which
    ' is filtered by the CustomerId field using MSFax through Access SendObject.
    '
    ' This function assumes the report rptMSFaxInvoices has the default printer set to MSFax
    ' and the MSFax driver is installed correctly.
    ' *******************************************************************************************
    '
Function MSFaxInvoices()

    '********************************************************************************************
    ' Diming all variables
    '********************************************************************************************
   
    Dim dbsNorthwind As Database
    Dim rstCustomers As Recordset
   
    '********************************************************************************************
    ' Setting database and recordset variables
    '********************************************************************************************

    Set dbsNorthwind = CurrentDb()
    Set rstCustomers = dbsNorthwind.OpenRecordset("Customers", dbOpenDynaset) ' Set Recordset to Customers table
   
    '********************************************************************************************
    ' Walking through the Customers recordset until end of file, setting the global variable
    ' strInvoicesWhere to the current where and using SendObject passing
    ' customers fax number and report name.
    '********************************************************************************************
   
    If MsgBox("Do you want to fax invoices" & Chr(13) & "to all customers using MSFax?", 4) = 6 Then
        With rstCustomers
        Do Until .EOF
            strInvoiceWhere = "[customerid] = '" & ![CustomerID] & "'" ' sets global strInvoiceWhere
            DoCmd.SendObject acReport, "rptMSFaxInvoice", acFormatRTF, "[fax: " & ![Fax] & "]", , , , , False ' Runs Report to MSFax
            .MoveNext ' Move to next record in Recordset
        Loop
        End With
    End If
   

End Function




   
    '********************************************************************************************
    ' This function will walk through the Customers table and fax the Invoices report which
    ' is filtered by the CustomerId field using the user defined function SendWinFax.  SendWinFax
    ' is located in this module.
    '
    ' This function assumes the report rptWinFaxInvoices default printer is set to Delrina
    ' MAPI Services and WinFax Pro is correctly installed.
    ' *******************************************************************************************
Function WinFaxInvoices()

    '********************************************************************************************
    ' Diming all varibles
    '********************************************************************************************

    Dim dbsNorthwind As Database
    Dim rstCustomers As Recordset
    Dim intSendFaxReturnVal As Integer
   
    '********************************************************************************************
    ' Setting database and recordset varibles
    '********************************************************************************************

    Set dbsNorthwind = CurrentDb()
    Set rstCustomers = dbsNorthwind.OpenRecordset("Customers", dbOpenDynaset) ' Set Recordset to Customers table
   
    '********************************************************************************************
    ' Walking throgugh the Customers recordset until end of file, setting the golbal varible
    ' strInvoicesWhere to the current where and calling SendWinFax passing the Customers Name,
    ' Customers Fax number and Report name.
    '********************************************************************************************
   
    intSendFaxReturnVal = -1   ' Priming return value to True
    If MsgBox("Do you want to fax invoices" & Chr(13) & "to all customers using WinFax?", 4) = 6 Then ' Make sure user wants to send fax to customers
        intSendFaxReturnVal = -1   ' Priming return value to True
        With rstCustomers
        Do Until .EOF Or intSendFaxReturnVal = 0 ' start do until end of file or return value = false
            strInvoiceWhere = "[customerid] = '" & ![CustomerID] & "'" ' sets global strInvoiceWhere
            intSendFaxReturnVal = SendWinFax(![ContactName], ![Fax], "rptWinFaxInvoice") 'Call user defined function and pass in data
            .MoveNext   ' Move to next record in Recordset
        Loop
        End With
    End If
   
End Function

    '
    '
    '********************************************************************************************
    ' Create Date 4/15/96
    '
    ' SendWinFax function will fax any report to any phone number, and address it to any name
    ' which was provided in the variables.
    '
    ' This function was *ONLY* tested under Win95 and WinFax 7.0 with the patch installed
    ' from Delrina.   The patch "wf702d1.exe" can be found on http://www.delrina.com.
    ' Without the patch you may get IPF's when running this code.
    '
    '************Microsoft Product Support only supports Microsoft Products.*********************
    '
    '********************************************************************************************
    '
    '
    '
Function SendWinFax(strFaxName As String, strFaxNumber As String, strReportName As String) As Integer

    '********************************************************************************************
    ' Diming all variables
    '********************************************************************************************
   
    Dim lngChannelNumber As Long
    Dim strFaxStatus As String
    Dim FaxTime As String
    Dim strRecipFaxNum As String
    Dim strRecipTime As String
    Dim strRecipDate As String
    Dim strRecipName As String
    Dim strRecipient As String

    On Error GoTo SendWinFax_Error  'Error Trap
   
    '********************************************************************************************
    ' If WinFax is closed then none of the fax functionality will be run.
    ' Be aware that there may be timing issues when trying to start WinFax in this function.
    ' I elected to have the user start WinFax manually for this example.
    '********************************************************************************************

    If FindWindow("Sfaxmng", "Delrina Winfax PRO") > 0 Then
   
    '********************************************************************************************
    ' Building Recipient string to send in DDEPoke
    '********************************************************************************************
   
        strRecipFaxNum = Chr$(34) & strFaxNumber & Chr$(34)
        strRecipTime = Chr$(34) & Format$(Now, "h:nn:ss") & Chr$(34)
        strRecipDate = Chr$(34) & Date & Chr$(34)
        strRecipName = Chr$(34) & Left$(strFaxName, 24) & Chr$(34)
       
        strRecipient = strRecipFaxNum & "," & strRecipTime & "," & strRecipDate & "," & strRecipName

    '********************************************************************************************
    ' Start of DDE section
    '********************************************************************************************
       
        lngChannelNumber = DDEInitiate("faxmng32", "CONTROL")   'Initiate DDE connection
        strFaxStatus = DDERequest(lngChannelNumber, "STATUS")   'Get and set status of DDE connection
       
        'If busy then loop until not busy
        While strFaxStatus Like "Busy*"
            strFaxStatus = DDERequest(lngChannelNumber, "STATUS")
        Wend
       
        lngChannelNumber = DDEInitiate("FAXMNG", "TRANSMIT")    'set ChannelNumber to current DDE Channel
       
        DDEPoke lngChannelNumber, "Sendfax", "recipient(" & strRecipient & ")"          'Poke recipient data
        DDEPoke lngChannelNumber, "Sendfax", "showsendscreen(""0"")"                    'Poke to showscreen
       
        'DDEPoke lngChannelNumber, "Sendfax", "setcoverpage(""Cover Page"" )"     'Untested Other Poke examples
        'DDEPoke lngChannelNumber, "Sendfax ", "fillcoverpage(""Stuff to go inside."")" 'Untested Other Poke examples
        'DDEPoke lngChannelNumber, "SendFax", "resolution(""HIGH"")"                    'Untested Other Poke examples

    '********************************************************************************************
    ' Run the Report
    '********************************************************************************************
        DoCmd.OpenReport strReportName, A_NORMAL
       
        SendWinFax = -1 ' setting return value to True
       
    Else 'If WinFax is not started
       MsgBox "Please Start WinFax and try again"   'Prompt
       SendWinFax = 0 ' setting return value to False
    End If
   
    '********************************************************************************************
    ' Exit Stuff
    '********************************************************************************************

SendFax_Exit:

    DDETerminateAll             'Terminate all links.
    lngChannelNumber = False    'Set lngChannelNumber to false clears the varible.
    Exit Function               'All Done

    '********************************************************************************************
    ' Error Trap
    '********************************************************************************************

SendWinFax_Error:
    MsgBox "Error:" + Error$, 0, "SendFaxl"   'Print Error message and number
    Resume SendFax_Exit
   
   
End Function





0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956637
The given  Winfax solution or MS Fax solution may work for you avior.
I leave it up to you to try them out.
0
 

Author Comment

by:avior
ID: 1956638
first, thanks 4 the answer and the code.

I want 2 avoid any extra parchasing, I don't know how much money WinFax is, but I'm sure it's not chip and the big problem is that I'm not sure it'll work with hebrew.

The solution I came with is to print the report to a printer that outputs a file in a tif format (I think that fax can use it) and then send a fax and attach this file to it.
I'm not sure about this, and I couldn't find too much info about it, what do u say ?
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956639
ok. its time for OLE messaging, which is what i was going to come to.

Send your document (.tif, .doc,etc) via OLE messaging and you can send as fax or email via MS exchange or othe MAPI compliant program.

Here is an example:

Option Compare Database
Option Explicit
Public objSession As Object

'-----------***Detailed Notes re Address------------
'You can address a message using either of two methods. The
'first, as I’ve already hinted at, is to use existing entries in the
'Address Book. To do this, do not provide a value for the Address
'property of the Recipient object.
'The second method, providing a custom address, is more
'pertinent to this discussion. By providing a value for the Address
'property, you can send the message to anyone, by e-mail or fax.
'MAPI messages accept several types of addresses: fax, SMTP,
'and X.400. By beginning the Address with one of these settings,
'you can direct the message accordingly.
'For example, you’d use SMTP addresses to directly enter an
'Internet address. Thus an SMTP address for e-mail would be:
'objRecipient.Address = "SMTP:bradw@paradigms.com"
'Another equally valid method of sending this message is to
'use the address:
'display name <e-mail address>
'which would be entered as:
'objRecipient.Address = _
'"Brad w <bradw@paradigs.com>"
'Addressing a fax is not much different. According to MSDN,
'you can address fax numbers using these formats:
'[FAX:Dialable | Name @ Canonical | SpecialDialing]
'[MSFAX:Name@ PhoneNumber | SpecialDialing]
'and as a result, when sending a fax the Address property would
'look like this:
'objRecipient.Address = "FAX:1-555-555-5555"

Private Sub Command0_Click()
'Example uses Microsoft Exchange as the
'mail system, the same applies to any MAPI-compliant e-mail
'system, whether it’s cc:Mail, Exchange, or anything else.
'Programming Interface (MAPI), sending a fax is really no
'different than sending e-mail.

' MAKE SURE THE OLE/Messaging 1.0 Object Lirary is referenced
' This is usually found at C:\WINDOWS\SYSTEM\MDISP32.TLB
' If you get 'object not created' error, try registering the object
' library by double clicking on MDISP32.REG

    ' fax the recipients of a document named xfiles
    'In a global area, declare objSession
    Dim strProfile As String, strPassword As String
    'In code, create the session and logon
    Set objSession = CreateObject("MAPI.Session")
    objSession.Logon profileName:=strProfile, profilePassword:=strPassword
    If objSession Is Nothing Then
        'Session Initiation Error
        MsgBox "Session failed to initiate."
    End If
   
'----ADD MESSAGE TO OUTBOX----
Dim objMessage As Object
Dim strSubject As String, strText As String
strSubject = "Test Faxing of Word Document from Access"
strText = "Here is the doc you should read ASAP."
Set objMessage = objSession.Outbox.Messages.Add
If objMessage Is Nothing Then
    'Error in Creating Message
Else
    With objMessage
    .subject = strSubject
    .Text = strText
    End With
End If

'-----ADD RECIPIENTS-----
' ADAPT THIS SO YOU ADD MULTIPLE RECIPIENTS
Dim objRecip As Object
Dim strAddress As String, strName As String

Set objRecip = objMessage.Recipients.Add
If objRecip Is Nothing Then
    'Error adding recipient
Else
    strAddress = "FAX:1-555-555-5555"   '***See Detailed Notes re Address
    strName = "Experts Exchange"
With objRecip
    .Address = strAddress
    .Name = strName
    .Type = mapiTo
    End With
    'objRecip.Resolve ShowDialog:=bShowDialog  'OMIT THIS. SO NO CONFIRMATION WITH ADDRESS BOOK
End If


'---ATTACHMENTS----
Dim objAttach As Object
Dim strFileName As String
' PUT YOUR OWN TEST WORD DOCUMENT NAME HERE ( or other type doc)
' Avior, try using your .tif doc here, or even .snp (snapshot image)
strFileName = "c:\temp\report1.snp" '"e:\philip\stella\tray2.doc"
Set objAttach = objMessage.Attachments.Add
If objAttach Is Nothing Then
'Error in Adding Attachment
Else
With objAttach
.Type = mapiFileData    'mapiFileData is a MAPI Constant
.position = 0
.Name = strFileName
.ReadFromFile (strFileName)  'strFileName contains the name of your WORD documnet
End With
End If
objMessage.Update


'----SEND IT------------------
Dim sID As String
sID = objMessage.ID
objMessage.Send showDialog:=False, saveCopy:=True


'---CLOSE SESSION----------
objSession.Logoff
Set objSession = Nothing

End Sub

0
 

Author Comment

by:avior
ID: 1956640
Thanks Again 4 the code but I had this example ,

What I asked was if u could give me some directions were can i find a driver that outputs to a tif format, and what formats can i use 4 the fax.
do u think this solution will work ?
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956641
What do you mean you already  have the example. Its an original.

You have been given a number of viable solutions. It is up to you to try them out.

You keep presenting a moving target. I can only work so many miracles.

Winfax is $150. Our sanity is worth a lot more than that.

Or output to a .snp file instead of .tif file using code, and use OLE messaging to fax it. Snapshot for Access97  is **FREE**.







0
 

Author Comment

by:avior
ID: 1956642
Hello ozphil !

The last thing I wanted to do is to hert your feelings ,what I ment that I had an example on the same subject taked from the MSDN.
Thanks again 4 the answer & the code
have a good day !  
 
0
 

Author Comment

by:avior
ID: 1956643
Hello ozphil !

The last thing I wanted to do is to hert your feelings ,what I ment that I had an example on the same subject taked from the MSDN.
Thanks again 4 the answer & the code
have a good day !  
 
0
 
LVL 4

Expert Comment

by:ozphil
ID: 1956644
I thought thats what you meant. Thanks avior.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now