Scripting Question... Trying to send table info via email...

TechJunky

Ok, so it is sending an email fine. It actually is sending 250messages for some reason that I can not tell...

It also is not sending the database select information that I wanted, it is simply emailing me the subject "Inventory Items" and in the body "This is a test".

Any ideas?

This was written in vbs... I have poor vbs scripting as you can tell..



Set OBJdbConnection = CreateObject("ADODB.Connection")
OBJdbConnection.ConnectionTimeout = 20
OBJdbConnection.Open "DSN=DatabaseName;UID=sa;PWD=Password;DATABASE=MyDatabase"
SQLQuery = "SELECT cust_no, nam, bal FROM AR_CUST where bal > 1"
Set Result = OBJdbConnection.Execute(SQLQuery)
if Not Result.EOF then
Do While Not Result.EOF
SendMail Result("cust_no"), Result("bal")
Result.MoveNext
Loop
end if
OBJdbConnection.Close
Sub SendMail(TheName, TheAddress)
Dim objMessage, Rcpt
Rcpt = Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">"
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Inventory Items"
objMessage.Sender = "myemail@domain.org"
objMessage.To = "myemail@domain.org"
objMessage.TextBody = "This is a test."
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"smtp.mydomain.org"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
objMessage.Send
End Sub

dynamik

I really don't know VBS, but I can see you have your subroutine, SendMail, in the loop, so it is going to send one message for each interaction through the loop. What are you trying to have it do?

Do the number of results from this: SQLQuery = "SELECT cust_no, nam, bal FROM AR_CUST where bal > 1" equal the number of emails you're getting?

JDMurray

Check if the information in your database is valid (names, email addresses, etc.) and that the column names in the script are correct.

TechJunky

I am trying to create a ODBC connection to the database, select customers balance and employee number from the database and email the information out to a certian email address.

IE:

Select CUST_NO, BAL from AR_CUST where BAL > '1'

dynamik

You just want a list of all of that? You'll probably want to do something like this:

Before your loop, create a new string variable for the results: dim resultStr

In the loop, add each line to resultStr: resultStr = resultStr & Result("cust_no") & " - " & Result("bal") & "\n"

Or however you want to format it. I'm assuming \n is a newline like in most other languages, but you made need to tweak that. Maybe use
instead if you're sending html mail.

Then after your loop, call your mail subroutine and pass it resultStr and email that to wherever.

TechJunky

Here is exactly what I want to do...

They are using an Access Database, but basically the same thing. I tried referencing their code, but no luck.

Load Recipients from a Database
As is the case with most thing in Windows there are many ways to accomplish a task. This is one method of many.

Our database is an Access format database that resides on the local disk. The table in our database that we are interested in is called Customers and each record consists of 4 fields named "ID", "Name", "Email", and "Customer", where ID is an autogenerated index, Name is the full name of our customer, Email is the customer's email address and Customer is their customer identification number.

We are only interested here in two fields, Name and Email.

ID Name Email Customer
1 Bob Jones bjones@test.com 12345
2 Jane Smith jsmith@test.net 12346



Set OBJdbConnection = CreateObject("ADODB.Connection")
OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\Acme Inc\Databases\Customers.mdb"
SQLQuery = "SELECT Name, Email FROM Customers"
Set Result = OBJdbConnection.Execute(SQLQuery)
if Not Result.EOF then
Do While Not Result.EOF
SendMail Result("Name"), Result("Email")
Result.MoveNext
Loop
end if
OBJdbConnection.Close

As you can see the code is simple. We create a database connection object then open the database and query it for the Name and Email fields of each customer. Those values are passed for each customer to a subroutine that sends the customer an email.

Sub SendMail(TheName, TheAddress)
Dim objMessage, Rcpt

Rcpt = Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">"
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "This Month's Sales"
objMessage.From = """Acme Sales"" <me@my.com>"
objMessage.To = Rcpt
objMessage.HTMLBody = TextBody
objMessage.Send

End Sub


If you are not accustomed to working with databases then this may have seemed a daunting task but as you can see from the code above, it's really quite simple.

We've already covered sending email so I'll just mention that this subroutine assumes the HTML body text is a variable called TextBody (see Loading email body text from a file)

Also we format the recipient's address in the standard format of "Name" <email@address.net> for a more professional look to the recipient..

Remarks
As previously stated there are many ways to do this. I've presented one simple method here. Your own use may be with an ODBC connection; it may use mySQL or SQL Server; it may include personalization of the email body text and more. My intent here was to provide you with the basics to get you started.

TechJunky

Does anyone know of another way to accomplish this? I dont have SQL Mail, so thats why I am trying to use another method.

Here is the website I was trying to reference earlier.

http://www.paulsadowski.com/WSH/cdo.htm

meadIT

Are you wanting to email directly from Access through VBA?

TechJunky

I dont care how I get the SQL data out of the Database... I just need a way that can be automated and sent in an email.

meadIT

Assuming you're running it from a PC with Outlook, I've always had good luck with using an Outlook object when emailing from Access. You would use this code in a VBA Module and can either pass in the Recipient(s), Subject, and Body of the message. Since your data is already in the Access database, this would make it easier to maintain the code all in one place.

You can also add attachments if you are sending montly sales reports.

Function MainFunction()
    Dim EmailList As Recordset

    Set EmailList = CurrentDb.Execute("SELECT Name, Email FROM Customers")

    Do Until EmailList.EOF
        Call SendEmail(EmailList!Name & "<" & EmailList!Email & ">", , , "This Month's Sales", "HTML Body Here")
    Loop

End Function


Function SendEmail(ToEmail, CCEmail, BCCEmail, Subject, HTMLBody)
        Dim olApp As Outlook.Application
        Dim objMail As Outlook.MailItem
        Set olApp = Outlook.Application
        
        'Create e-mail item
        Set objMail = olApp.CreateItem(olMailItem)
        With objMail
            .To = ToEmail
            .CC = CCEmail
            .BCC = BCCEmail
            .Subject = Subject
            .BodyFormat = olFormatHTML
            .HTMLBody = "<HTML><BODY>" & HTMLBody & "</BODY></HTML>"
            '.Attachments.Add
           .Send
            
        End With
End Function