Afternoon,I have a request from a user to send a report of outstanding orders to each vendor on a weekly basis. I can do this when I know the email but am having a hard time figuring out how to loop through the list and send one to each. I can't use the sendmail proc since some clown installed 64 bit on the server. I can use the cdosysmail though. I put this together: DECLARE @body1 varchar(1000), @vendor varchar(10), @po varchar(8), @Email varchar(255), @description varchar(25), @due_date datetime, @open_qty int, @count int, @MaxCount intset @body1='<table border="2" cellspacing="2" cellpadding="2"> <TR> <td style="color: #A0522D; font-family: Verdana; font-size: 10;" align=left>'set @body1=@body1+'Customer</TD>'+ '<td style="color: #A0522D; font-family: Verdana; font-size: 10;" align=left> Vendor </TD>'+ '<td style="color: #A0522D; font-family: Verdana; font-size: 10;" align=left> PO </TD>'+ '<td style="color: #A0522D; font-family: Verdana; font-size: 10;" align=left> Description </TD>'+ '<td style="color: #A0522D; font-family: Verdana; font-size: 10;" align=left> Due Date </TD>'+ '<td style="color: #A0522D; font-family: Verdana; font-size: 10;" align=left> Open Qty </TD></TR>'SELECT @MaxCount = max(PO) FROM vw_Get_Vendor_Info -- Loop through each row to send mail WHILE @count <= @MaxCount BEGIN SELECT @vendor,@po,@Email,@description,@due_date,@open_qty FROM vw_Get_Vendor_Info AS S WHERE S.po = @Kount exec master.dbo.usp_send_cdosysmail @from='myemail', @to =@Email, @subject ='Missing orders', @body =@body1, @smtpserver ='stmtpservername', @bodytype ='HTMLBody' SET @count = @count + 1 END
Parses okay but no email is ever sent. FYI the from email and the smtp server are correct in my script I just didn't put them out here.Any thoughts would be great.Laura
email looping
Posted - 2010-11-30 : 13:36:09
