>This will only work for customers with email addresses defined in the Email
You can change the definition of "curMailer" to anything you want, something
like the users which going to expire in three days.
declare curMailer INSENSITIVE cursor
for select s.email as MailBox,
m.FirstName , m.LastName
from subaccounts s, masteraccounts m
where s.CustomerID=m.CustomerID
and DatePart(dy,maExpireDate) =
DatePart(dy,GETDATE()) + 3
and DatePart(yy,maExpireDate) =
DatePart(yy,GETDATE())
and datalength(s.email)>0
>field. What about those that are constructed by Username@Domain? Many ISPs
Use a script to update the email field, because that is the fuction which
IEA wants you to use, IEA may have other use of the DOMAIN in the future. It
is the best practice to use those fields.
>work that way. This script also does nothing to limit who gets the message.
>I thought the point was to email people with declined credit cards...
Obviously, if I do not know which customer has got their credit cards
declined, I could not program into it. But it should be pretty easy to
schedule a job in SQL 7.0 to send notice to those user which credit card
going to expire in one and half month.
>
>-----Original Message-----
>Subject: Re: [Emerald] rebatch behind the scenes .... ?
>From: "Dan Tang" <dan@dmn.com.au>
>Date: Thu, 10 Jun 1999 21:40:33 +0800
>
>A store procedure in SQL 7.0 for you
>
>declare curMailer INSENSITIVE cursor
> for select email,firstname,lastname
> from subaccounts
> where datalength(email)>0
>declare @email varchar(50)
>declare @FirstName varchar(50)
>declare @lastName varchar(50)
>declare @MsgBody varchar(2000)
>
>open curMailer
>fetch next from curMailer into @email,@firstname,@Lastname
>while @@FETCH_STATUS=0
>begin
> Select @MsgBody='Dear ' + @FirstName + ' ' + @LastName + ',' +
char(13)
> select @MsgBody='Your main message here' + char(13) + char(13)
> select @MsgBody='Regards,'+ char(13) + char(13)
> select @MsgBody='Your title'
> exec master..xp_sendmail @recipients=@email,
> @subject='Your
subject',
> @Message=@MsgBody
> fetch next from curMailer into @email,@firstname,@Lastname
>end
>close curMailer
>deallocate curMailer
>
>
>
>To test it, run this on SQL7.0 QueryAnalyzer, replace @email the
>@receipients=@email with your email address like that
>'yourmail@yourdomain.com'.
>
>Because this is a store procedure, you can acctually set up a ASP page,
>which you can type in the message and let the Web Server and SQL 7.0 handle
>the rest.
>
>
>Regards,
>
>Dan Tang
>Network Operation
>Domain Internet Access
>---------------------------------------------------
>Sign Up a Dial up account at http://isp.dmn.com.au and get connect right
>now.
>---------------------------------------------------
>Join the Emerald ASP Addon Product mailing list
>by sending email to emerald-request@dmn.com.au with message body SUBSCRIBE
>only
>
>