dcsimg
Results 1 to 7 of 7

Thread: [RESOLVED] Need to send emails from SQL Server database

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2012
    Location
    Omaha
    Posts
    17

    Resolved [RESOLVED] Need to send emails from SQL Server database

    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!

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,894

    Re: Need to send emails from SQL Server database

    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

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2012
    Location
    Omaha
    Posts
    17

    Re: Need to send emails from SQL Server database

    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!

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,011

    Re: Need to send emails from SQL Server database

    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)

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Nov 2012
    Location
    Omaha
    Posts
    17

    Re: Need to send emails from SQL Server database

    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!

  6. #6
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,011

    Re: Need to send emails from SQL Server database

    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.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Nov 2012
    Location
    Omaha
    Posts
    17

    Re: Need to send emails from SQL Server database

    Thank you!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width