Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Bulk email?

Status
Not open for further replies.

Taleya

Programmer
Mar 9, 2000
12
AU
Thanks for the help everyone - one last one...then I'm done...<br>
<br>
Ok, I need to run a mass email off certain catagories. these cats are hard-coded. I've built the queries that suck out the email addresses that correlate to the radio button selection...<br>
<br>
now what?<br>
<br>
DoCmd can't run off a query.... <p>Taleya<br><a href=mailto:thezoomistress@yahoo.com.au>thezoomistress@yahoo.com.au</a><br><a href=homepages.ihug.com.au/~taleya>Gorillaweed Graphics</a><br>You spam me, I castrate you
 
If you're asking how to trigger the code, create a button and put your code under the click event.
 
I would make a loop<br>
Open a recordset that is the same as your query.<br>
Copy the SQL code from your Query and put it in the SQL statement below.<br>
<br>
Dim db As database, rst As Recordset, SQL As String<br>
Set db = CurrentDb<br>
' SQL string.<br>
SQL = &quot;SELECT * Blah Blah Blah&quot;<br>
Set rst = db.OpenRecordset(SQL)<br>
rst.MoveLast<br>
For A = 1 To rst.RecordCount<br>
'E-mail people<br>
DoCmd.SendObject acSendNoObject, &quot;&quot;, actext, rst!EmailAddr, , , Subject, Message, False<br>
Next<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>
 
Ok, Doug? I've adapted that code to fit the database...but depending on whether or not you have it set to movefirst or move last - I'm just getting multiple instances of one record. I need to put multiple addresses *on the one email* Any ideas? <p>Taleya<br><a href=mailto:thezoomistress@yahoo.com.au>thezoomistress@yahoo.com.au</a><br><a href=homepages.ihug.com.au/~taleya>Gorillaweed Graphics</a><br>You spam me, I castrate you
 
Never mind - got it sorted &lt;g&gt; <br>
<br>
this is why you never let me near a computer before 12pm....<br>
<br>
<br>
For those of you interested, the final code is:<br>
<br>
(using categories to divide company types)<br>
<br>
<br>
Case 1<br>
SQL = &quot;Select StrEmailAddress from tblClients where strconClub IS NULL;&quot;<br>
<br>
Case 2<br>
SQL = &quot;Select stremailaddress from tblclients WHERE ynAccountant = True AND strConClub IS NULL;&quot;<br>
<br>
Case 3<br>
SQL = &quot;Select stremailaddress from tblclients WHERE ynCorporate = True AND strConClub IS NULL;&quot;<br>
<br>
Case 4<br>
SQL = &quot;Select stremailaddress from tblclients WHERE ynEstateAgent = True AND strConClub IS NULL;&quot;<br>
<br>
Case 5<br>
SQL = &quot;Select stremailaddress from tblclients WHERE ynFinancialAdvisor = True AND strConClub IS NULL;&quot;<br>
<br>
Case 6<br>
SQL = &quot;Select stremailaddress from tblclients WHERE ynFranchisee = True AND strConClub IS NULL;&quot;<br>
<br>
Case 7<br>
SQL = &quot;Select stremailaddress from tblclients WHERE ynFranchisor = True AND strConClub IS NULL;&quot;<br>
<br>
Case 8<br>
SQL = &quot;Select stremailaddress from tblclients WHERE ynMedicalPractitioner = True AND strConClub IS NULL;&quot;<br>
<br>
Case 9<br>
SQL = &quot;Select stremailaddress from tblclients WHERE ynOther = True AND strConClub IS NULL;&quot;<br>
<br>
Case 10<br>
SQL = &quot;Select stremailaddress from tblclients WHERE ynProperty= True AND strConClub IS NULL;&quot;<br>
<br>
End Select<br>
<br>
Set rs = DB.OpenRecordset(SQL)<br>
<br>
rs.MoveFirst<br>
<br>
Do While rs.EOF = False<br>
email = email & rs!strEmailAddress & &quot;;&quot;<br>
rs.MoveNext<br>
Loop<br>
<br>
<br>
If email = &quot;&quot; Then<br>
MsgBox &quot;There are currently no client records listed under this category&quot;, vbExclamation, &quot;E-Mail Error&quot;<br>
Exit Sub<br>
End If<br>
<br>
DoCmd.SendObject acSendNoObject, , , , , email, , , , True<br>
<br>
End Sub <p>Taleya<br><a href=mailto:thezoomistress@yahoo.com.au>thezoomistress@yahoo.com.au</a><br><a href=homepages.ihug.com.au/~taleya>Gorillaweed Graphics</a><br>You spam me, I castrate you
 
You could change your SQL statemnet slightly and put a variable instead of all of those select case statements.<br>
Except I'm not sure what your select case is looking at...<br>
<br>
SQL = &quot;Select stremailaddress from tblclients WHERE &quot; & Somevalue & &quot; = True AND strConClub IS NULL;&quot;<br>
<br>
The Somevalue changes depending on what you are passing.<br>
And you may have to modify some of your other code above as well.<br>
<br>
<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>
 
The code has to select a particular business category. This is actually the second in ANOTHER case set, that lets you choose All clients, Standalones (where there is no business name listed), or ones that are ONLY part of a business &lt;g&gt; the &quot;select what type&quot; (as opposed to category) makes variables true or false, so if you click &quot;Standalone clients only&quot; that makes the variable &quot;standalone&quot; = true, and then that shifts into this IF on the case select.<br>
<br>
well, it makes sense if you have a screen shot &lt;g&gt;<br>
<br>
(case700) * All Clients<br>
(case800) * Standalone clients<br>
(case900) * Company Contacts<br>
<br>
(case1, etc))* * (all those little categories like &quot;accountant&quot; etc)<br>
<br>
Case 800 <br>
standalone = true<br>
<br>
Case 900<br>
Company = True<br>
(ect ect ect)<br>
<br>
THEN, on the button click, it runs an If statement on the true/false of the variables, then runs variations on the case statement sitting in my last post<br>
<br>
It works good as is, although I had to trap a blank email error (no email for a client when it loops thru the rs) and also the &quot;No current record&quot; on the rs (recordset). I suppose I could have based the variables on the cases themselves...but it's done now, and I'll piss about when I don't have a deadline :) <p>Taleya<br><a href=mailto:thezoomistress@yahoo.com.au>thezoomistress@yahoo.com.au</a><br><a href=homepages.ihug.com.au/~taleya>Gorillaweed Graphics</a><br>You spam me, I castrate you
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top