Solved

Fax, email or print

Posted on 1997-10-10
15
346 Views
Last Modified: 2008-03-10
I have a system (Access 97) which is 2 weeks overdue.  The one thing we need is a routine which will, at a particular time, go through a list of company names and either fax, email, or print a report for/to each company.  This needs to be done unattended.

The timing won't be a problem, neither will be the retrieval of the list from the database.  If the program is provided with a desired output mode (say txtOutMode), and a phone number or email address (say txtAddress) as appropriate, how can I do this?  It would be preferable if the coding were done within the database, since that is where the report is being generated.

This one is fascinating, and I wish I had the time to research it myself.  Any help would be appreciated.

0
Comment
Question by:john_price
  • 8
  • 2
  • 2
  • +2
15 Comments
 

Author Comment

by:john_price
ID: 1437235
Edited text of question
0
 

Author Comment

by:john_price
ID: 1437236
Edited text of question
0
 
LVL 5

Expert Comment

by:y96andha
ID: 1437237
For FAX and e-mail, look at the MAPI control if you have it?
0
 

Author Comment

by:john_price
ID: 1437238
Thanks for the fast reply.  I do have the MAPI controls.  I guess the real problem seems to be how to fax.  My program needs to read the fax phone number and send the fax to that number, without user intervention.  I can't find anything on faxing in the MAPI help.

email is also a little confusing, since I want to email (or fax or print) the output from a report.  How would that be done?

This has got to be a common thing to do.

At this late stage, I am hoping for a code example, say somthing like:

do until Company.eof
... get strDeliveryMode      '  E=email, F=fax, P=Print
                             ' Don't need help here

  Select case strDeliveryMode
     case "P"
        ... print it         ' Don't need help here

     case "E"
        ... get strAddress   ' email address (internet)
                             ' Don't need help on that either

'>>> and here's where I need help:
        ... send report via email
     case "F"
        ... get strAddress   ' fax phone Number
                             ' don't need help here

'>>> also need help here:
        ... send report via fax  ' Must dial phone without
                                 ' without operator intervention
  end select

loop
 

0
 
LVL 5

Expert Comment

by:y96andha
ID: 1437239
I think it should all be possible via MAPI if you install the fax service which is used in Windows Messaging. I am not sure if I'll be able to provide you with a MAPI sample quickly enough though, so I won't attempt an answer right now.
0
 
LVL 1

Expert Comment

by:jannea
ID: 1437240
Hello John !

I am interested of this code if you can make it work.
I use VB5 and have the MAPI controls.

Janne Sweden

janne.a@mailbox.swipnet.se
0
 

Author Comment

by:john_price
ID: 1437241
y96andha -- Its quick enough if it beats any others.  So far, I'm still at the same spot.  Have another project kicking off tomrrow, so there is still no chance for me to dig in.

jannea  -- be my guest.  If we get it working, share away.

-- John
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 9

Expert Comment

by:cymbolic
ID: 1437242
Check out Q145787 and Q125853 in The MS Knowledge Base.  These are not complete solutions, but they'll get you in the ballpark.
0
 

Author Comment

by:john_price
ID: 1437243
Sorry for the delay getting back.  Had to get the articles, write the code and test.  Got it all working.  Thanks very much.

-- John
0
 

Author Comment

by:john_price
ID: 1437244
Guess you'll have to answer me so that I can grade the answer.  Thanks again.  The Knowledge base articles led to a line of research.  While one of the articles did not help directly, being for EXCEL, the "see also's" and related topics got me the rest.  I just today provided the working code to the client.


0
 
LVL 1

Expert Comment

by:jannea
ID: 1437245
Thanks for your code John !

But i have solve my problem in another way whith MAPI-Calls, Keep in toutch if you are interested.
I´ll  found a book 'MAPI, SAPI & TAPI' by Michael Amundsen, I think its wery good book if you work with this kind off App´s.

/Janne SWEDEN
0
 
LVL 1

Expert Comment

by:olx
ID: 1437246
Hello John Price!!

Im interested in this code too, i like to know if you can share it with me.
Months ago i asked some question like this and got no response.

10,000 thanks in advance.
OLX.

olx@hotmail.com
0
 

Author Comment

by:john_price
ID: 1437247
Note to board sysops:  I don't know how to award the points at this point.  I guess Cymbolic deserves the points.  He didn't give me the answer, but pointed me to it, and so probably deserves a c - b grade on the answer?  Thanks.
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 150 total points
ID: 1437248
John, I'll submit this as my answer, but you're right, it sure doesn't deserve a high grade, but this way you can close out the question.  If you have interesting code snippets that you'd like to share, it would be good for others who use the Experts Exchange answers to post them here as comments.  That way when they look up answered questions(this one) they get something for their points.  Congratulations and hang in there!
0
 

Author Comment

by:john_price
ID: 1437249
Well, I got my problem solved, and that was the purpose.  High grade or not, I appreciate your response.  It saved me the time spent looking through the kb.  The end result is a kick.  Thanks.

Here is the code for all to share:

Private Sub cmdPrintReports_Click()
    Dim strDBName As String         ' name of database
    Dim rsCreditUnion As Recordset  ' Credit Union table
    Dim strbin As String            ' BIN ID
    Dim strDeliverOption As String  ' "M"=MAil, "E"=Email, "F"=Fax
    Dim strAddress As String        ' email address or fax number
    Dim strReportName As String     ' Name of report
    Dim strWhere As String          ' Where clause
    Dim rptReport As Report         ' Report object being printed
   
' Open table
    Set rsCreditUnion = CurrentDb.OpenRecordset("tblCreditUnion", dbOpenTable)
    rsCreditUnion.MoveFirst
   
    strDBName = CurrentDb.NAME
    strReportName = "rptSummarySheet"
   
    Do Until rsCreditUnion.EOF
' get credit union information
       strbin = rsCreditUnion("txtBIN")
       strDeliverOption = rsCreditUnion("txtDeliverOption") & ""
   
       strWhere = "txtBinNumber='" & strbin & "'"

' This report uses a query which is built from the tblHeaders and
' tblHistory tables.
' strWhere is the appropriate "Where" clause for this BIN.
           
            Select Case UCase(strDeliverOption)
               
                Case "M"                                                            ‘ Mail (hard copy)
    ' Reference: Q145707
                    If Not OLEOpenReport(strDBName, _
                        strReportName, _
                        acNormal, _
                        , _
                        strWhere) Then
                       '... Log exception to log file
                    End If
   
                Case "F"                                                            ‘ Fax
    ' Reference: Q145787
                    strAddress = rsCreditUnion("txtAddress")
                    Reports(strReportName).Filter = strWhere
                    DoCmd.SendObject acReport, _
                        strReportName, _
                        acFormatRTF, _
                        "[fax:" & strAddress & "]", , , , , _
                        False
   
                Case "E"                                                            ‘ Email
    ' Reference: Q145787
                    strAddress = rsCreditUnion("txtAddress")
                    DoCmd.OpenReport strReportName, acViewDesign

                    Reports(strReportName).Filter = strWhere
                    DoCmd.Close acReport, strReportName, acSaveYes
                    DoCmd.SendObject acReport, _
                        strReportName, _
                        acFormatRTF, _         ‘<<<<<<< could also use acFormatTXT
                        strAddress, , , , , _
                        False
            
                Case "T"                 ' Text file
' 4th param to .OutputTo - file name of file to create
                    strAddress = rsCreditUnion("txtAddress")
                    DoCmd.OutputTo acOutputReport, _
                        strReportName, _
                        acFormatTXT, _
                        strAddress, _
                        False

            End Select
        End If
        rsCreditUnion.MoveNext
    Loop

' Close and release the table
    rsCreditUnion.Close
    Set rsCreditUnion = Nothing
End Sub


the function OLEOpenReport is in the referenced article, and I simply copied it and it’s sister function to a new module.  Works neat.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

912 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

15 Experts available now in Live!

Get 1:1 Help Now