Passing Data from and Sqlstatement to an email

[Daniel J. Rychlik]

Hello,

This is probably and easy one but Im having trouble getting it figured
out.  I cannot figure out how to pass the output from my sqlstatement to
the body of an email.  

Heres my code.  

# We need to use proper 
use DBI;
use Net::SMTP;

# This "agent" has to be setup in ODBC drivers in control panel. Tested
# on Windows 2000.
$dbh = DBI->connect('dbi:ODBC:agent');

my $agn_lst = 'agent_list.txt';
my $file = 'ticketscrub.xls';

# Mail specific variables
my $mailsrvr = 'mailgate..com';
my $sndraddy = 'drychlik@.com';
my $mailrcv = 'agents_mail.txt';

# Begin Subroutine to retrieve data from scrubdata
sub agent_report {

my $agent = shift;

$sqlstatement = "SELECT * FROM tblScrubInfo, tblScrubDetail 
WHERE txtAgentID='$agent' AND tblScrubInfo.numTicketNumber =
tblScrubDetail.numTicketNumber
AND ynWasFixed= No ORDER BY txtCustomer ";

$sth = $dbh->prepare($sqlstatement);
$sth->execute ||
        die "Cannot open my $dbh ? ";

while (@row=$sth->fetchrow_array)
{print $file "@row\n"}
}

open (LIST, "$agn_lst") or die "Cannot open the $agn_lst: !$\n";

while ( my $current_agent = <LIST>  ) {
        chomp($current_agent);
        agent_report($current_agent);

}
close (LIST);

# Begin to send the report (hopefully)

sub  send_rpt {
my $address = shift;

my $smtp = Net::SMTP-> new ($mailsrvr)  
        or die "Cannot connect to the $mailsrvr: $!\n";

$smtp->mail($sndraddy);
$smtp->to($address);

$smtp->data();
$smtp->datasend("Subject: Quality Control Report for daily ticket
scrub\n\n");
$smtp->datasend("Hello, after you fix your tickets please reply to this
message.\n");
$smtp->datasend(" ");
$smtp->datasend(" ");
$smtp->datasend("$file\n");
$smtp->dataend();

$smtp->quit;
}

# Get our list of agents and pass them off to send_rpt () process
open (LIST, "$agn_lst") or die "Cannot open the file $mailrcv: $!\n"; 

while( my $current_address = <LIST> ) {
        chomp($current_address);
        send_rpt($current_address);
}
close (LIST); 


Sincerely,

Daniel J. Rychlik
" Money does not make the world go round , Gravity does ."