email each page to different address

[Caren]

I have a report that identifies invoices paid by ACH to various people. I want to notify them that I sent the ACH and what invoices were paid. My report groups by person with each group on a separate page. Different people are paid each time. Crystal support said that this can't be done in Crystal 7.

Currently I am exporting the file to Word and e-mailing it to someone who individually copies and Pastes each page into separate e-mails. We would like to expand the use of ACH payment but the notification process is very cumbersome. Any suggestions? Can I export it somewhere else where it can be split out for separate e-mailing? [sig][/sig]

 

[billredd]

Sounds like you need to export to text and read that file
into VB or some other programming language. In VB here's what I would do:

In the report:
the first line of each page must contain some unique string
to identify that it is the first line


paste this into a VB module:

'-------------------------------------------

Option Explicit
Public olapp As Object

Sub main()

Dim strLine As String
Dim strMsg As String
Dim strTo As String
Dim lngMailCtr As Long
Dim f As Integer

'assuming you will send through MS-Outlook
'you should add MS-Outlook also in the project references
Set olapp = CreateObject("Outlook.Application&quot

f = FreeFile(0)

'change this path to your file location / name
Open "c:\report.txt" For Input As #f

strMsg = ""
strLine = ""
lngMailCtr = 0

Do While Not EOF(f)
Line Input #f, strLine 'get a record from the report
'I'm going to assume that the unique string is
'E-MAIL ID: and nothing else is on the line after it
'that way I have the id to mail it!
If InStr(strLine, "E-MAIL ID:&quot Then 'are we on a new page
'extract the e-mail address
strTo = Trim$(Mid$(strLine, InStr(strLine, "E-MAIL ID:&quot + 11))
If strMsg > "" Then
'try to mail the message
lngMailCtr = lngMailCtr + mailMsg(strTo, strMsg)
End If
strMsg = strLine & vbCrLf 'start a new one
Else
'add to the one we already have
strMsg = strMsg & strLine & vbCrLf
End If
Loop

'catch the last one, that didn't get done
'because we hit eof

If strMsg > "" Then
'try to mail the message
lngMailCtr = lngMailCtr + mailMsg(strTo, strMsg)
End If

MsgBox lngMailCtr & " e-mails sent!"
Close #f

End Sub

'this assumes you will send the mail via MS-Outlook
Function mailMsg(t As String, s As String) As Integer

On Error GoTo Err_NoMail

Call SendMailThruMSOutLook("Invoice", t, s)
mailMsg = 1 ' true
Exit Function

Err_NoMail:

mailMsg = 0 ' false

End Function

'this assumes you will send the mail via MS-Outlook
Public Sub SendMailThruMSOutLook(strSub As String, strTo As String, strBody As String)

Dim oitem As Object

On Error GoTo Err_Handler

Set oitem = olapp.CreateItem(0)

With oitem
.Subject = strSub
.To = strTo
.Body = strBody
.Send
End With

Exit Sub

Err_Handler:

MsgBox Err & " - " & Error, vbInformation, "Warning"

End Sub

'-------------------------------------------

NOTE:
This is quite crude, but it's a start, I didn't test
it completely because my Outlook is messed up at the
moment, but I've done something similiar before.

Good Luck!

 

[billredd]

In my previous message I say:

'I'm going to assume that the unique string is
'E-MAIL ID: and nothing else is on the line after it

Well that's misleading, you should have something like this
in the first line of each page of your report:

E-MAIL ID: jsmith@yahoo.com

No other text should follow that on that line! But you
could have something before it though. This will work for
my example code.

Good Luck!

 

[retro]

Unless i have the wrong end of the stick (not unknown!) you could use something like Crystal Info to schedule a number of separate instances of your report, each running for one particular group. Info allows u to schedule reports to run out-of-hours or whenever.
Then for each report (group) you could specify where u want the report output to go - you could specify that report #1 be emailed direct to Fred@somewhere, report #2 (different group) email to nora@somewhereelse, and so on..

 

[billredd]

Retro, I've never heard of Crystal Info, must be something
new, sounds like it might work. But, how would you know
what records you have, to be able to set it up ahead of time? If a new account is added and has an invoice, do you
have to make a change in Crystal Info to include the new
account?

Sounds interesting, I wonder how flexible it is?

 

[Caren]

I think Crystal info might help but until I run the report, I don't know who I want to send e-mails to. I think I would need to first find a way to break that report into individual reports. I am not a VB programmer. The VB answer is way over my head. I will try to find someone at work to help me with determining if this will do the trick. We haven't installed Crystal Info Server yet.

My 16 year old son mentioned that using something like CGI might work. If I understood him correctly I would need to place something in the report that would act like a trigger. When it gets to that, it would do something to send the e-mail, then continue on. Perhaps that is where the VB macro could go? I don't know. As I said, I am way over my head here. I have no idea what CGI is.

[sig][/sig]

 

[mmeagle]

I am trying the same thing in Crystal Reports 8. The easiest solution I have found so far is export the data, including email, to a text file and send the messages using a program called WorldMerge. It allows me to send personalized messages using data from the text file.

 

[IdoMillet]

Looks like R&R Version 9 from:

http://www.livewarepub.com/

has several new features:
(see

http://www.livewarepub.com/version9.0.htm)

including the ability to do exactly what
you want (e-mail each report group to a
different e-mail address).

I haven't tested this myself and only the Xbase
edition is out. Sounds like the SQL version will
come out by the end of the 2nd quarter.

hth,
- Ido

 

[dcm]

Caren: The solution is definitely Info. This has the ability to schedule on an event (i.e. based upon another report which contians details of who needs to be paid) and each report can be sent to an e-mail address - actually the way I would probably set this up is to use Info's recurring schedule capability to have multiple versions of the payment advice report set up in advance and then have the event trigger which one I want. I fyou need more information on this just ask.
David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[rino]

I have written a VB routine that does a similar function except I am using it to fax separate pages of a report to Winfax.

The package is written in such a way as to function with any report/database. All that is required is to add a standard formula to your detail lines in the report. This does not effect the running of report during design mode or other modes ie it is only activated when running via the special report driver - the formula looks like this:

if onfirstrecord then
vcfCRUFLfaxOpenFile (filename);

vcfCRUFLfaxLog(**KeyField**, **NameField**, **FaxNumberField**, pagenumber);

if onlastrecord then
vcfCRUFLfaxCloseFile (pagenumber+1);

It would need to modified to pass the email address from data on the report. I was going to look at emailing next - perhaps we could help each other here?

 

[Cavitt]

Did anyone ever solve this problem? Im facing the same issue...

-cd

 

[IdoMillet]

There are now several 3rd party report managers, including my Visual CUT software, that address this type of need:

http://www.kenhamady.com/bookmarks.html

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[VulcanJedi]

Caren i belive Word's mail merge feature incorporates seperate personailzed emails in office xp. Nice feature I am using to work on a project for a friend. The nice part about this is the feature does appear to bypass MS new seucuity warning alert system w/ outlook....

Tranpkp