#!/usr/bin/perl --

use strict;
use warnings;
use POSIX qw(strftime);
chdir "$ENV{'USERPROFILE'}\\Desktop"; #this will direct email log file
+ to each users My Documents folder
# my $infolog = strftime("%Y%m%d",localtime(time));  
# $infolog .="_email_log.txt";

my $logtime = strftime("%Y-%m-%d %H:%M:%S",localtime(time));  

$|++;

#make encrypted smtp sender
# package SMTPS;
# { #enclose the package in a block.  Probably not necessary with "pac
+akge main" but why not
 # no strict 'refs';
  use IO::Socket::SSL;
  use Net::SMTP;
 # use Email::Send::SMTP::Gmail
 # use vars qw(@ISA);
 # @ISA = ('IO::Socket::SSL', grep {$_ ne 'IO::Socket::INET'} @Net::SM
+TP::ISA);
 # foreach (keys %Net::SMTP::) {
 #   next unless defined *{$Net::SMTP::{$_}}{CODE};
 #   *{$_} = \&{"Net::SMTP::$_"};
 # }
#}

package main;
#open(STDERR, ">>", "$infolog");
#open (OUTPUT, ">>", "$infolog");  
open(STDERR, ">>email_log.txt");
open (OUTPUT, ">>email_log.txt"); 


my %conf;
while (my ($key, $value) = splice(@ARGV,0,2)) {
  if ($key eq '-v') {
    unshift @ARGV, $value if $value;
    $conf{'-v'}++;
  }  else {$conf{$key} = $value;}
}

if ($conf{'-c'}) {
  open(CONF, $conf{'-c'}) or die qq($logtime: Can't open the file spec
+ified with the '-c' switch: "$conf{'-c'}"\nThe error message was:\n\t
+$!\n);
  while (<CONF>) {
    chomp;
    next if /^\s*$/ or /^#/;
    my ($key, $value) = split ' ', $_, 2;
    if ($key eq '-v') {$conf{'-v'}++;}
    else {$conf{$key} = $value unless $conf{$key};}
  }
  close CONF;
}

my $work_book = $conf{'-w'} or die qq($logtime: You must specify a wor
+kbook with the '-w' switch\n);
my $work_sheet = $conf{'-t'} or die qq($logtime: Make sure there are n
+o spaces in the file path where param file is kept. (You must specify
+ a sheet in the workbook with the '-t' switch.) \n);
my $range = $conf{'-r'} or die qq($logtime: You must specify a range o
+n the sheet with the '-r' switch\n);
my $mail_host = $conf{'-m'} or die qq($logtime: You must specify an em
+ail server with the '-m' switch\n);
my $return_address = $conf{'-a'} || q(branch_finance@sil.org);
my $msg_file = $conf{'-f'} || '';
my $subject = $conf{'-s'} || qq(Accounting Report);
my $authuser = $conf{'-u'} || '';
my $authpass = $conf{'-p'} || '';
my $debug = $conf{'-v'} || 0;
my $smtp_debug = 1; # 0=no debug; 1=debug
if ($debug > 1) { $smtp_debug = 1;}

my $mail_host_port = 465;
if ($mail_host =~ /:/) { ($mail_host, $mail_host_port) = split /:/, $m
+ail_host; }
#change backslashes to forward slashes just in case
$work_book =~ tr|\\|/|;

if ($debug) {
print OUTPUT qq($logtime: You haven't specified a return address for t
+he message.  Return address set to "branch_finance\@sil.org".\n) unle
+ss $conf{'-a'};
  print OUTPUT qq($logtime: You haven't specified a subject for the me
+ssage.  Subject set to "Accounting Report".\n) unless $conf{'-s'};
  print OUTPUT qq($logtime: You haven't specified any text for the mes
+sage.  The message will consist of only the attachment.\n) unless $ms
+g_file;
}

my $msgtxt = qq( \n);
if (($msg_file) && (open(MSGTXT, "$msg_file"))) {
    local $/ = undef;
    $msgtxt = <MSGTXT>;
    close MSGTXT;
}

use Spreadsheet::ParseExcel;
print qq(Trying to open "$work_book"\n) if $debug;
my $ss = Spreadsheet::ParseExcel::Workbook->Parse("$work_book") or die
+ qq($logtime: Can't read $work_book\n$!\n);
print qq(Checking for sheet "$work_sheet"\n) if $debug;
my $ws = $ss->Worksheet("$work_sheet");
die qq($logtime: Can't find sheet "$work_sheet" in "$work_book"\n) unl
+ess defined $ws;
my ($start_range, $end_range) = split /:/, $range; 
$start_range =~ /([a-zA-Z]+)(\d+)/;
my($first_col,$first_row) = (ord($1)-65,$2-1);
$end_range =~ /([a-zA-Z]+)(\d+)/;
my($last_col,$last_row) = (ord($1)-65,$2-1);

print qq(Making a secure connection to mail server "$mail_host" on por
+t "$mail_host_port".\n) if $debug;
my $smtp = Net::SMTP->new($mail_host,Port=>$mail_host_port,Hello=>'ACC
+PAC_Reports.sil.org',Debug=>$smtp_debug, SSL=>1) 
or die qq($logtime: Cannot make a secure connection to "$mail_host" on
+ port "$mail_host_port" so no messages can be sent.  Please make sure
+ that you can access "$mail_host".\nThe error was $@\n);

#ab my $smtp = SMTPS->new($mail_host,Port=>$mail_host_port,Hello=>'ACC
+PAC_Reports.sil.org',Debug=>$smtp_debug) or die qq($logtime: Cannot m
+ake a secure connection to "$mail_host" on port "$mail_host_port" so 
+no messages can be sent.  Please make sure that you can access "$mail
+_host".\nThe error was $@\n);
if ($authuser and $authpass) {
  print qq(Authenticating as user "$authuser"\n) if $debug;
  $smtp->auth($authuser,$authpass) or print qq(Unable to authenticate 
+to "$mail_host" with account "$authuser".\nTrying to send messages an
+yway... \n); #ab: changed warn to print 
}

use File::Basename qw(fileparse);
foreach my $i ($first_row..$last_row) {
  my $full_file_name = $ws->Cell($i,$first_col)->Value();
  $full_file_name =~ tr|\\|/|;  #just in case
  print qq(CELL ), chr($first_col+65), $i+1, qq( = "$full_file_name"\n
+) if $debug;
  (my $email_address_line = $ws->Cell($i,$last_col)->Value()) =~ s/\s*
+[,;]\s*/, /g;
  my @email_addresses = split /, /, $email_address_line;
  print qq(CELL ), chr($last_col+65), $i+1, qq( = "$email_address_line
+"\n) if $debug;
  my ($file_name, $dir, $ext) = fileparse($full_file_name,'\.[^.]*$');
  print qq(PATH = "$dir", FILE = "$file_name", EXT = "$ext"\n) if $deb
+ug;
  unless (-e $full_file_name) { 
    print OUTPUT qq($logtime: The file "$full_file_name" does not exis
+t.  Please check the data in "$work_book" and make sure that you have
+ access to this file.\nTrying next file if there is one.\n); #ab: cha
+nged warn to print 
    next;
  }
  my $mime;
  $ext = lc($ext);
  if ($ext eq '.pdf') {$mime = 'application/pdf';}
  elsif ($ext eq '.xls') {$mime = 'application/msexcel';}
  elsif ($ext eq '.xlsx') {$mime = 'application/msexcel';}
  elsif ($ext eq '.zip') {$mime = 'application/x-zip-compressed';}
  elsif ($ext eq '.txt') {$mime = 'text/plain';}
  elsif (($ext eq '.html') or ($ext eq '.htm')) {$mime = 'text/html';}
  else {
    $mime = 'application/octet-stream';
    print OUTPUT qq($logtime: The extension "$ext" is unknown so the a
+ttachment will be encoded as 'application/octet-stream'.\n) if $debug
+;
  }
  my $email = make_header($email_address_line,$full_file_name, "${file
+_name}$ext", $mime) or do {
     warn qq($logtime: Can't properly encode "$full_file_name".  Make 
+sure you have access to this file.\nTrying next file if there is one.
+\n);
     $smtp->reset();
     next;
  };
  print qq(Attempting to send $full_file_name to $email_address_line\n
+) if $debug;
  print qq(Setting return address: "$return_address"\n) if $debug;
  unless ($smtp->mail($return_address)) {
      $smtp->quit();
      die qq($logtime: The server "$mail_host" has refused to accept a
+ny mail with the return address: "$return_address".  Please make sure
+ this is a valid email address.\n);
  }
  my @good_addresses;
  foreach my $address (@email_addresses) {
       print qq(Adding recipient: "$address"\n) if $debug; 
      if ($smtp->recipient($address)) { push @good_addresses, $address
+; }
      else {
        warn qq($logtime: The server "$mail_host" refused to accept ma
+il for user "$address", so the message cannot be sent to this address
+.\nCheck if the address is properly formed (user\@domain.com) or see 
+if you need to authenticate before sending.\nTrying next recipient if
+ there is one.\n);
        next;
      }
  }
  unless (scalar @good_addresses > 0) {
      warn qq($logtime: The server refused all the recipients\nPerhaps
+ you need to authenticate before sending?\nTrying next message if the
+re is one.\n);
      $smtp->reset();
      next;
  }
  print qq(No more recipients... Sending message data\n) if $debug;
  unless ($smtp->data($email)) {
      warn qq($logtime: The server "$mail_host" refused to accept the 
+message.\nTrying next message if there is one.\n);
      $smtp->reset();
      next;
  }
  print OUTPUT qq($logtime: File "$full_file_name" successfully sent t
+o @good_addresses\n) if $debug;
  $smtp->reset();
}
print qq(Done sending. Closing connection to "$mail_host"\n) if $debug
+;
# print qq(Done sending. Closing connection to "$mail_host"\n) if $deb
+ug;
close OUTPUT;
close STDERR;
$smtp->quit();

sub make_header {
  use MIME::Base64;
  use MIME::Lite;
   my ($to_address, $filepath, $filename, $app_type) = @_;
   my $msg = MIME::Lite->new(
        To    =>$to_address,
        From    =>$return_address,
        Subject =>$subject,
        Type    =>'multipart/mixed'
   ) or return undef;

   $msg->attach(
    Type    =>'TEXT',
        Data    =>$msgtxt
   ) or return undef;

   $msg->attach(
        Type     =>"$app_type",
        Path     =>$filepath,
        Filename =>$filename
   ) or return undef;

   return $msg->as_string;
}
[download]