Using visual basic to send an e-mail

[Guest]

Hello

I am using Access 2002, and am trying to use Visual Basic to send an e-mail
to many recipients. I have a table which has a field which lists all the
recipients e-mail addresses.
I am using a 'SendObject' command, and am trying to send the e-mail to all
the recipients by referring to the field in the table.

Is there any way that this can be done?
Any assistance/suggestions would be greatly appreciated.

Thanking you in advance
AC

 

[Guest]

Hi AC,

You can use a function similar to this:

'************Begin Code****************
Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = "; "

Set db = CurrentDb
strSQL = "SELECT BEMSID " _
& "FROM tblContacts " _
& "WHERE BEMSID Is Not Null AND OnEmailDistribution=Yes;"

Set rs = db.OpenRecordset(strSQL)

With rs
Do While Not .EOF
strOut = strOut & ![BEMSID] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
Else
BulkEmail = ""
End If

ExitProc:
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, vbCritical, _
"Error in BulkEmail function..."
Resume ExitProc
End Function

'*************End Code****************

You will need to write the correct strSQL statement to match your field and
table names. The return value of this function, a delimited string of e-mail
addresses, would be fed to the SendObject function's To: parameter.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Ron2006]

The approach that Tom gave is a good one AS LONG AS THERE ARE NOT TOO
MANY ADDRESSES. I believe that at some time you will encounter a
problem with the number of characters that you can enter there.

At that point you have at least two choices.
Choice 1)
At this point in the code

With rs
Do While Not .EOF
strOut = strOut & ![BEMSID] & conSEP
.MoveNext
Loop
End With

instead of concatenating the addresses together, you would do the send
object using the email address of the individual. If you have 1,000
addresses you would send 1000 emails.

Choice 2)
Increase the logic at that point to add a counter and maybe concatenate
100 addresses and then send the message, clear the strOut field and
counter and start the concatenate process again. Don't forget to send
another message at the eof condition to get the last set of 1 to 99
addresses that are there but have not yet received the email. In this
situation 1000 addresses will get you 10 or so email messages. In this
situation you should be aware that ALL of the recepientes will ALSO see
the email address of all of the other 99 recepients and you have to
have at least 1 actual address in the to: address.

Also keep in mind that with Tom's apporach ALL of the recepients will
see ALL of the other addresses.

Ron.

 

[Guest]

Hello Tom

Thank you very much for your suggestion. I have added the function into the
database, and have tried it. Unfortunately, it only lists 3 and a half of
the e-mail addresses in the table (of the 15 or so e-mail addresses).

I would greatly appreciate any suggestions as to how to overcome this.

Thank you again
Kind regards
AC

Hi AC,

You can use a function similar to this:

'************Begin Code****************
Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = "; "

Set db = CurrentDb
strSQL = "SELECT BEMSID " _
& "FROM tblContacts " _
& "WHERE BEMSID Is Not Null AND OnEmailDistribution=Yes;"

Set rs = db.OpenRecordset(strSQL)

With rs
Do While Not .EOF
strOut = strOut & ![BEMSID] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
Else
BulkEmail = ""
End If

ExitProc:
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, vbCritical, _
"Error in BulkEmail function..."
Resume ExitProc
End Function

'*************End Code****************

You will need to write the correct strSQL statement to match your field and
table names. The return value of this function, a delimited string of e-mail
addresses, would be fed to the SendObject function's To: parameter.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hello

I am using Access 2002, and am trying to use Visual Basic to send an e-mail
to many recipients. I have a table which has a field which lists all the
recipients e-mail addresses.
I am using a 'SendObject' command, and am trying to send the e-mail to all
the recipients by referring to the field in the table.

Is there any way that this can be done?
Any assistance/suggestions would be greatly appreciated.

Thanking you in advance
AC

 

[Guest]

Hi AC,

Very strange. 3 and a half e-mail addresses? Are there any special
characters that you can spot, such as an apostrophe? I wouldn't think that
would be legal in an e-mail address, but I'm thinking you are hitting some
type of special character.

I just tested the function and it worked fine on a table that includes 482
e-mail addresses. The combined length of the string, including the delimiters
(semicolon + space) was 10,682 characters.

I'm willing to take a look at your database, if you can zip it and send it
to me. Please compact it first before zipping it (Tools > Database Utilities

Compact and repair database). My e-mail address is available at the bottom

of the contributor's page, indicated below, in my signature. Whatever you
choose to do, please do not post your e-mail address (or mine) to a reply to
any newsgroup.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Ron,

One may very likely have a problem with the number of e-mail addresses that
they feed to the SendObject function. However, I have done two test tonight,
and both ran flawlessly. The first test involved running the BulkEmail
function on 482 e-mail addresses (see my reply to AC). The string was 10,682
characters in length.

Then, I ran an append query 6 times, each time appending the table to
itself. Thus the first time, it added 482 records to the table. The second
time it added 964 records, then 1928 records, and so forth, doubling each
time. I ended up with 30,848 e-mail addresses. The BulkEmail function ran
just fine. The total length of the string was 683774 characters.

I'm sure that one would run into problems with most commercial ISP's long
before the limitations of the BulkEmail and/or SendObject were encountered.
For example, Comcast (my ISP) limits one to 49 (I'm pretty sure that is the
correct number) of e-mail addresses in an outgoing message.

Also keep in mind that with Tom's apporach ALL of the recepients will
see ALL of the other addresses.

Not necessarily true. If you used the To: parameter of SendObject this would
be true. And, it would be entirely appropriate in a small group within a
company.
However, there is nothing preventing one from using the BCC: parameter with
at least one valid e-mail address (sender's is a good choice) on the To:
line. The example I provided was just that: an example. It's up to the user
to make any necessary adjustments.

For 100's of e-mail addresses, I agree that sending them one-by-one is the
better choice. However, I would not do so using SendObject. At that point,
it's time to use something like Total Access E-mailer
(http://www.fmsinc.com/products/Emailer/index.asp) or Fairlogic's WorldCast
(http://www.fairlogic.com/worldcast/) to send out that many messages.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

The approach that Tom gave is a good one AS LONG AS THERE ARE NOT TOO
MANY ADDRESSES. I believe that at some time you will encounter a
problem with the number of characters that you can enter there.

At that point you have at least two choices.
Choice 1)
At this point in the code

With rs
Do While Not .EOF
strOut = strOut & ![BEMSID] & conSEP
.MoveNext
Loop
End With

instead of concatenating the addresses together, you would do the send
object using the email address of the individual. If you have 1,000
addresses you would send 1000 emails.

Choice 2)
Increase the logic at that point to add a counter and maybe concatenate
100 addresses and then send the message, clear the strOut field and
counter and start the concatenate process again. Don't forget to send
another message at the eof condition to get the last set of 1 to 99
addresses that are there but have not yet received the email. In this
situation 1000 addresses will get you 10 or so email messages. In this
situation you should be aware that ALL of the recepientes will ALSO see
the email address of all of the other 99 recepients and you have to
have at least 1 actual address in the to: address.

Also keep in mind that with Tom's apporach ALL of the recepients will
see ALL of the other addresses.

Ron.

 

[Ron2006]

Thanks for the update, Tom. I have not done it, so did not know the
limit. Glad to hear that it is such a large number. I should have
researched sendobject more to catch the BCC availability.

I agree, for large numbers, sending either by automation or even send
object thru outlook would get nerve-wracking, to say the least.

Thank you again for the clarification. Have a great day.

Ron