john_price
asked on
Fax, email or print
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.
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.
ASKER
Edited text of question
For FAX and e-mail, look at the MAPI control if you have it?
ASKER
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
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
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.
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
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
ASKER
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
jannea -- be my guest. If we get it working, share away.
-- John
Check out Q145787 and Q125853 in The MS Knowledge Base. These are not complete solutions, but they'll get you in the ballpark.
ASKER
Sorry for the delay getting back. Had to get the articles, write the code and test. Got it all working. Thanks very much.
-- John
-- John
ASKER
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.
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
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
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
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("t blCreditUn ion", dbOpenTable)
rsCreditUnion.MoveFirst
strDBName = CurrentDb.NAME
strReportName = "rptSummarySheet"
Do Until rsCreditUnion.EOF
' get credit union information
strbin = rsCreditUnion("txtBIN")
strDeliverOption = rsCreditUnion("txtDeliverO ption") & ""
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).Fil ter = strWhere
DoCmd.SendObject acReport, _
strReportName, _
acFormatRTF, _
"[fax:" & strAddress & "]", , , , , _
False
Case "E" ‘ Email
' Reference: Q145787
strAddress = rsCreditUnion("txtAddress" )
DoCmd.OpenReport strReportName, acViewDesign
Reports(strReportName).Fil ter = 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.
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("t
rsCreditUnion.MoveFirst
strDBName = CurrentDb.NAME
strReportName = "rptSummarySheet"
Do Until rsCreditUnion.EOF
' get credit union information
strbin = rsCreditUnion("txtBIN")
strDeliverOption = rsCreditUnion("txtDeliverO
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).Fil
DoCmd.SendObject acReport, _
strReportName, _
acFormatRTF, _
"[fax:" & strAddress & "]", , , , , _
False
Case "E" ‘ Email
' Reference: Q145787
strAddress = rsCreditUnion("txtAddress"
DoCmd.OpenReport strReportName, acViewDesign
Reports(strReportName).Fil
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.
ASKER