You should leave Outlook 98 open in SQL Server, and on Mail Delivery Option
tick Send Message immediately.
Regards,
DAN TANG
Network Operations
Domain Internet Access
-----Original Message-----
From: Will LaSala <will@greennet.net>
To: emerald@iea-software.com <emerald@iea-software.com>
Date: Saturday, June 12, 1999 12:22 AM
Subject: Re: [Emerald] rebatch behind the scenes .... ?
>Ok I have a little better Stored Proc for emailing those customer with
>expired and about to expire CC
>This script will email the customer 3 months before thier account is going
>to expire
>to inform them to fix this before it does.
>There maybe bugs in this and make sure you change the email message and
>blindcc address.
>I have yet to full test it here since I have no idea how to get SQLMail
>working on the SQL Server without an Exchange server but I'll figure
>something out (any help out there?)
>
>Thanks to Dan Tang for his part in this script since this is just a build
>off of his.
>P.S. I forgot to mention that you must have only four or three digits in
>your CCExpireDate fields!!!! It is very important that you don't have any
>"-"'s or "/"'s in it!
>The field should be in the format of mmyy or myy!
>Will ,,,=^. .^=,,,
>Webmaster
>GreenNet® ==========================================
>"Link Locally ~ Surf Globally©" http://www.greennet.net
>Come Play On The Best Quake Server in the Northeast
>Serving Eastern Massachusetts and Southern New Hampshire
>TEL: 978-363-8898 FAX: 978-363-1225 email:will@greennet.net
>===================================================
>--------CUT Here----------
>declare CCExpireDate INSENSITIVE cursor for
> Select m.CreditCardExpire, m.CustomerID
> from subaccounts s, masteraccounts m
> where s.CustomerID=m.CustomerID
> and m.PayMethod = "Credit Card"
> and DatePart(mm,maExpireDate) = DatePart(mm,GETDATE()) + 3
> and DatePart(yy,maExpireDate) = DatePart(yy,GETDATE())
> and datalength(s.email)>0
>declare @CCDate varchar(25)
>declare @CustID int
>
>open CCExpireDate
>fetch next from CCExpireDate into @CCDate,@CustID
>while @@FETCH_STATUS=0
>begin
>if DATALENGTH(@CCDate) = 3
> declare curMailer INSENSITIVE cursor for
> select s.email as MailBox, m.FirstName , m.LastName, m.maExpireDate,
>SUBSTRING(CreditCardExpire, 1, 1) as CCExpireMonth, Right(CreditCardExpire,
>2) as CCExpireYear
> from subaccounts s, masteraccounts m
> where m.CustomerID = @CustID
> and s.CustomerID = @CustID
> and m.PayMethod = "Credit Card"
> and DatePart(mm,maExpireDate) = DatePart(mm,GETDATE()) + 3
> and DatePart(yy,maExpireDate) = DatePart(yy,GETDATE())
> and datalength(s.email)>0
>Else
> declare curMailer INSENSITIVE cursor for
> select s.email as MailBox, m.FirstName , m.LastName, m.maExpireDate,
>SUBSTRING(CreditCardExpire, 1, 2) as CCExpireMonth, Right(CreditCardExpire,
>2) as CCExpireYear
> from subaccounts s, masteraccounts m
> where m.CustomerID = @CustID
> and s.CustomerID = @CustID
> and m.PayMethod = "Credit Card"
> and DatePart(mm,maExpireDate) = DatePart(mm,GETDATE()) + 3
> and DatePart(yy,maExpireDate) = DatePart(yy,GETDATE())
> and datalength(s.email)>0
>
>declare @email varchar(50)
>declare @FirstName varchar(50)
>declare @LastName varchar(50)
>declare @MsgBody varchar(255)
>declare @CCMonth varchar(4)
>declare @CCYear varchar(4)
>declare @AccountExpireDate datetime
>
>open curMailer
>fetch next from curMailer into
>@email,@FirstName,@LastName,@AccountExpireDate,@CCMonth,@CCYear
>IF (Convert(int, DatePart(mm, @AccountExpireDate)) >=
Convert(int,@CCMonth))
>and (Convert(int, DatePart(yy, @AccountExpireDate)) = Convert(int,"19" +
>@CCYear))
> Select @MsgBody='Dear ' + @FirstName + ' ' + @LastName + ',' +
char(13)
> select @MsgBody='Your Credit Card is about to Expire.' + char(13)
> select @MsgBody='Our Records show that the Credit Card you have given
>us will expire on ' + @CCMonth + "/" + @CCYear + "." + char(13)
> select @MsgBody='Please call us before this time to update our
>records.' + char(13)
> select @MsgBody='If we do not here from you before this time we will
>start issuing you an invoice, and an addtional invoicing fee of $2.00.' +
>char(13)
> select @MsgBody='To avoid this additional charge please call us today
>at 978-363-8898' + char(13) + char(13)
> select @MsgBody='Regards,'+ char(13) + char(13)
> select @MsgBody='GreenNet Billing Department'
> exec master..xp_sendmail @recipients=@email,
>@blind_copy_recipients="billing@greennet.net", @subject='Our Credit Card
>Information', @Message=@MsgBody
>close curMailer
>deallocate curMailer
>fetch next from CCExpireDate into @CCDate,@CustID
>end
>close CCExpireDate
>deallocate CCExpireDate
>
>