From: "Jason Newland" <email@example.com> Subject: RE: [Emerald] reminder/late notices Date: Fri, 21 Nov 2003 14:06:37 -0600 Message-ID: <002f01c3b06a$f8b29160$ac010a0a@AT3528k>
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.):
bcp "SELECT ( <mailto:Loginfirstname.lastname@example.org'> Loginemail@example.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.):
c: cd\imail imail1 -f c:\imail\mailbody.txt -s "A Friendly Reminder about your AreaTech Account" -t <mailto:firstname.lastname@example.org> email@example.com -u <mailto:firstname.lastname@example.org> email@example.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:firstname.lastname@example.org> email@example.com -u <mailto:firstname.lastname@example.org> email@example.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.