Mass email 1

[schltzy99]

Hi,

I need to create a mass email list to send to clients who have become inactive within my database. What code can I write in VB to do this. The approach I want to use is loop through the database using a do while loop and add peoples names to the "To" part of the email until the amount of names equals 100, then I want to send the email, reset the counter and do it again up until the count equals 100 and so on until it has completely went through all the people in my table. I have the database connection established and the mailer working, I just need to add the people to the To field and I cant seem to get it to work, any ideas?

Thanks!

RSchultz
**Access 2000**

 

[Bastien]

Set dbs = CurrentDb()
'open the db and get the addresses
Set objRS = dbs.OpenRecordset(strSQLEmailAddr)
'assign the email addresses to a string to be placed into the BCC field
Do While Not objRS.EOF
strBCC = strBCC & objRS(0) & ";"
objRS.MoveNext
Loop

then elsewhere onthe page

Set outApp = CreateObject("Outlook.application&quot
Set outMessage = outApp.CreateItem(olMailItem)

'assign the message variables
With outMessage
.BCC = strBCC
wend

hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[schltzy99]

Thanks for the reply Bastien but...

that email apparently sends an email to all of the members at once, I would like to due it in 100 people increments. Also, I am using access as my database. Its an older site that has yet to be converted to SQL.

To better help you help me, here is what I have so far...


<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<%
Set DataMan = server.createObject(&quot;ADODB.COnnection&quot
DataMan.open session (&quot;TLUCon&quot
DataString =&quot;SELECT Email FROM AppInfo WHERE StartDay > 0 AND StartMonth > 0 AND StartYear > 2000 AND Active = 'Inactive' AND Email <> ' ' ORDER BY APPID&quot;
set uRS=DataMan.execute(DataString)


sch= &quot;

http://schemas.microsoft.com/cdo/configuration/&quot;

Set cdoConfig = Server.CreateObject(&quot;CDO.Configuration&quot
cdoConfig.Fields.Item(sch & &quot;sendusing&quot = 2
cdoConfig.Fields.Item(sch & &quot;smtpserver&quot= &quot;database.website.com&quot;
cdoConfig.fields.update
Set cdoMessage = Server.CreateObject(&quot;CDO.Message&quot
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = &quot;&quot;
cdoMessage.To = &quot;&quot;
cdoMessage.Subject = &quot;&quot;
cdoMessage.TextBody = &quot;&quot;
cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
DataMan.close
Response.Redirect(&quot;inactive_email.asp&quot
%>



Thanks for your help!


RSchultz
**Access 2000**

 

[Tarwn]

Just a minor addition, not really code related, you may want to add the majority of the people to the CC or BBC field, similar to how junkmail does it. That way no one feels you are handing out their email to the whole world, just my feeling, but it may help the header of the email look a little less crowded as well
-Tarwn
my 2c

 

[schltzy99]

Tarwn,

Agreed!

Thanks for the comment, I took that into consideration and it is one of the changes I was actually working on.

Thanks again!

RSchultz
**Access 2000**

 

[Tarwn]

Actually it appears that you could do the same thing with your script that Bastien posted above, here is my crack at it:
I won't set the from and so on, assume you can handle it

<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<%
Set DataMan = server.createObject(&quot;ADODB.COnnection&quot;)
DataMan.open session (&quot;TLUCon&quot;)
DataString =&quot;SELECT Email FROM AppInfo WHERE StartDay > 0 AND StartMonth > 0 AND StartYear > 2000 AND Active = 'Inactive' AND Email <> ' ' ORDER BY APPID&quot;
set uRS=DataMan.execute(DataString) 


sch= &quot;[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/&quot;;[/URL] 
Set cdoConfig = Server.CreateObject(&quot;CDO.Configuration&quot;) 
cdoConfig.Fields.Item(sch & &quot;sendusing&quot;) = 2 
cdoConfig.Fields.Item(sch & &quot;smtpserver&quot;)= &quot;database.website.com&quot; 
cdoConfig.fields.update 
Set cdoMessage = Server.CreateObject(&quot;CDO.Message&quot;) 
Set cdoMessage.Configuration = cdoConfig 
cdoMessage.From = &quot;&quot;
cdoMessage.To = &quot;&quot; 
cdoMessage.Subject = &quot;&quot; 
cdoMessage.TextBody = &quot;&quot;

'db loop starts here
Dim pplCtr, bccString
Do While NOT uRs.EOF                             'loop until end of db
      bccString = bccString & &quot;;&quot; & uRs(&quot;Email&quot;) 'add name to bcc string
      pplCtr = pplCtr + 1                        'increment people counter
      If (pplCtr mod 100) = 0 Then               'if people counter is multiple of 100
         cdoMessage.bcc = bccString              'set bcc
         cdoMessage.Send                         'send
         bccString = &quot;&quot;                          'clear bcc
      End If
      uRs.MoveNext                               'next record
Loop

'we may have people in the bcc string if the loop ended on anything other than a multiple of 100 (99% chance :-) )
If strlen(bccString) > 0 Then
   cdoMessage.bcc = bccString
   cdoMessage.Send
End If
Set cdoMessage = Nothing 
Set cdoConfig = Nothing 
DataMan.close 
Response.Redirect(&quot;inactive_email.asp&quot;)    
%>

Now, I am pretty sure that you won't have to reset the cdo properties with every send, as the object is justa a configuration object that it uses when you tell it to send, but if you do have problems go ahead and set everything for the message inside the if mod check above and the if strlen check lower down. I just write this on the fly so I apologize for any spelling errors or minor problems.

Testing:
A good way to test this would be to set up a small access db with the same table and only 4 or 5 email addresses in it. Then change the mod 100 to mod 2 so it will send out after every second email address it adds and you can see if it is sending everything fine.
-Tarwn

 

[schltzy99]

Tarwn,

Thanks again for your input!

I have been working on it and have made some progress.
I also figured that a good way to test it was to put the peoples names as the body text instead of the bcc.

However, I have hit a few more stumbling blocks:
(1) How many addresses can a BCC field hold? is there a chance that it wont hold 100 addresses?
(2) Inside of the database I noticed that a number of members entered their information in incorrectly. What is the best way to check and see whether or not they have an @ symbol in their address? I figured that it is probably the best item to check for. I dont think I should validate the extensions (.com, . org...) because there is a wide variety listed within the database.

Thanks all!

This is a great help to a beginner ASP programmer!

RSchultz
**Access 2000**

 

[Tarwn]

Before adding the address to the bccString just do an inStr check:

If inStr(uRs(&quot;Email&quot,&quot;@&quot > 0 Then
bccString = bccString & uRS(&quot;Email&quot
End If

You could even include the counter increment in there. It would be the difference in mailing to every 100 addresses you add to the BCC field and every 100 addresses you process but don't necessarilly add.

As to how many a BCC can hold, I don't remember seeing that anywhere, so I couldn't tell you, sorry
-Tarwn

 

[Bastien]

as for my input, it is from an access db Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[schltzy99]

Tarwn,

You don't have to apologize for anything! You have been a most helpful mammal!

I keep trying to mark this post as helpful but am getting an error. Thank you for all your help! It is definitely making a lot more sense now!

I am having 1 problem however with this guy right here:

If strlen(bccString) > 0 Then
cdoMessage.bcc = bccString
cdoMessage.Send
End If


For some reason the site chokes here. It still sends all the other emails, but it does not do the response.redirect. I tried to test it by making the textbody &quot;Last Email&quot; instead just so I could monitor it to see if it was working, and I didnt get the email. I also thought maybe I had to put all of the email controls in there to make it work. No luck.

RSchultz
**Access 2000**

 

[Tarwn]

Heh, my mistake, to many languages...
strlen is not ASP, use len to get the string length
-Tarwn

 

[schltzy99]

Well,

Its finally done! Many thanks to Tarwn for his wonderful efforts and patience! The purpose of this email was to email clients who have become inactive that registered on or after 1/1/2001. Below is the final product:

<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<%
Set DataMan = server.createObject(&quot;ADODB.COnnection&quot
DataMan.open session (&quot;TLUCon&quot
DataString =&quot;SELECT Email FROM AppInfo WHERE StartDay > 0 AND StartMonth > 0 AND StartYear > 2000 AND Active = 'Inactive' AND Email <> ' ' ORDER BY Email&quot;
set uRS=DataMan.execute(DataString)

count = 0
Do While Not uRS.eof
count = count + 1
if count<=100 then
If inStr(uRS(&quot;Email&quot,&quot;@&quot > 0 Then
strBBC = strBBC & uRS(&quot;Email&quot & &quot;;&quot;
End If
else
count = 0
sch = &quot;

http://schemas.microsoft.com/cdo/configuration/&quot;

Set cdoConfig = Server.CreateObject(&quot;CDO.Configuration&quot
cdoConfig.Fields.Item(sch & &quot;sendusing&quot = 2
cdoConfig.Fields.Item(sch & &quot;smtpserver&quot = &quot;server address&quot;
cdoConfig.fields.update
Set cdoMessage = Server.CreateObject(&quot;CDO.Message&quot
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = &quot;&quot;
cdoMessage.To = strBBC
cdoMessage.Subject = &quot;&quot;
cdoMessage.TextBody = &quot;&quot;
cdoMessage.Send
strBBC = &quot;&quot;
end if
uRS.movenext
loop

if len(strBBC)>0 then
strBBC = Left(strBBC, Len(strBBC) - 1)
cdoMessage.To = strBBC
cdoMessage.Send
end if

Set cdoMessage = Nothing
Set cdoConfig = Nothing

DataMan.close
Response.Redirect(&quot;inactive_email.asp&quot
%>

I hope that someday, somewhere, someone else will find this post helpful, but remember: SPAMING IS BAD AND WILL HELP YOU TO LOSE FRIENDS!

Enjoy!
RSchultz
rschultz@fivestardev.com
**Access 2000**