From: "Jason Newland" <newlandj@areatech.com> Subject: RE: [Emerald] reminder/late notices Date: Fri, 21 Nov 2003 14:06:37 -0600 Message-ID: <002f01c3b06a$f8b29160$ac010a0a@AT3528k>
Brad,
Here are the batch files that I have in operation. Note that we are
using Imail for the mail server, and Emerald 4.5 w/sql2000. These
scripts may/will need to be modified to your specific setups.
On the SQL machine (ran at 1:00 a.m.):
notice.bat
bcp "SELECT ( <mailto:Login+'@yourdomain.com'> Login+'@yourdomain.com')
as name FROM Emerald4..SubAccounts where SendBill=1 and DateDiff(Day,
ExpireDate, GetDate()) = 0 and email IS NULL" queryout
f:\imail\yourdomain_com\lists\billingsupport\users.lst -U insert sql
username -P insert pass -c -t
exit
(Note: The f:\imail\yourdomain_com is a mapped drive to the imail c$
share you will need to do the same to put the list of extracted
addresses on the mail server.
The location that the f: points to is where Imail houses the
mailing lists for our domain, and the users.lst is the file that Imail
uses to send notices to)
On the Mail server (ran at 1:30 a.m.):
latenotice.bat
c:
cd\imail
imail1 -f c:\imail\mailbody.txt -s "A Friendly Reminder about your
AreaTech Account" -t <mailto:billingsupport@yourdomain.com>
billingsupport@yourdomain.com -u <mailto:support@yourdomain.com>
support@yourdomain.com
del C:\usernotice.txt
copy c:\imail\yourdomain_com\lists\billingsupport\users.lst
c:\imail\usernotice.txt
imail1 -f c:\imail\usernotice.txt -s "These accounts were sent late
notices today" -t <mailto:support@yourdomain.com>
support@yourdomain.com -u <mailto:support@yourdomain.com>
support@yourdomain.com
exit
(this batch file runs the imail1 command line mailer with a body of
mailbody.txt and subject of "A friendly Remider.." You can call it
anything you want. Billing support is the name of our mailing list
(that previously recieved a fresh list of late pay addresses) and
support@ is the return address.)
(the second imail1 command line is optional, but we use it to send US a
list of the users that were sent the late notices)
I know that this isn't the best way to do this, and someone with actual
SQL skills could write something better :), but this has worked fine for
us for a while now without incident. Feel free to ask me any questions.
Regards,
Jason