[RESOLVED] Need to send emails from SQL Server database

[cpeterson36]

Hello,

I need to send copies of the invoices from yesterday's sales to the customers that would like an email copy of them. I currently have the invoice created in a Crystal Reports and export it as a pdf. My issue is how do I get the list of email addresses and then loop to send them. My background is not in VB but I do work with it regularly. I use old code and modify it. I have code that now looks in the database and gets an email address for different reports. They are all Crystal Reports and export as a pdf. I have created my Invoice Crystal report and it works well by me providing the invoice number. I also have the logic to just check yesterday even though it may be Monday or there may have been a holiday.

So what I need to do is develop the code the check my SQL Server table for a key (T/F) that they do want the invoice emailed. Of course there are some emails in the table that do not want or need the invoice sent. Then get the email address and then send the invoice pdf to that email address. I would use my outlook account to send these. I could create a view in SQL that would only have the customers that have that "Y" in the request email invoice field and then that would relieve me of the requirement to even check that. However, my question is that I am looking for some very specific guidance to be able to loop through the table or view and check for a customer that wants their invoice and had a sale yesterday.

Thank you all in advance!

[DataMiser]

You would get the data you want by using a simple select query with a where clause. Not sure about sending the emails as I have not had a need or desire to do this from a PC in VB6

[cpeterson36]

Hi Datamiser,

Thank you for your response. I have the SQL Server Query down. Not a problem there. Maybe I am not being clear. What I really need is the logic to tell the program how to look for a customer who had a sale yesterday and then execute the report.

such as
if cust had sale yesterday
make report
else
do nothing
end if

My question is how do I tell it to loop through the table or the view I create. I am not sure on the logic. Can I use a simple EOF? VB isn't my strong suit so I am asking for very basic help.

Thanks!

[SamOscarBrown]

To loop through your table (actually the recordset you return in you query) do something like this:

Do While NOT rs.EOF 'where rs is your recordset
'do what you want to for this record here (call a send mail routine, put info into a form control, etc
Rs.movenext
Loop

You will ned a reference to to ADO in you project (I'm assuming you've already done that if you are already successfully running queries)

[cpeterson36]

Hi Sam,

Now that makes sense to me. Thanks. I have done that lots with VB in Excel spreadsheets. I will give it a try. I just wasn't sure of how to do the logic in the loop and what to use as a counter or EOF for the loop.

Have a great day!

[SamOscarBrown]

If you need a counter, dim it before the loop as integer, like Dim myCntr as integer
Then just before the movenext line, insert myCntr = myCntr + 1

Have fun.

[cpeterson36]

Thank you!