sending email to selected contacts

[acni]

I have the following peice of code to try and send an email to selected
contacts in my form.The problem is this line:

StrStore = DLookup("[email]", "qrySelectEmail ", "??????")

This looks up the email field in the records returned for the query
qrySelectEmail. The final bit needs to tell it to go to the first record
the first time the loop runs, then the second record the second time the
loop runs etc… But I cannot figure out a way to do this, which I am
certain there must be.

Thanks in anticipation
Code:
--------------------------------------------------------------------------------

Private Sub send_email_Clic k()On Error GoTo Err_send_email_ ClickDim StrTo
As StringDim StrBcc As StringDim StrStore As StringDim NumSelected As
IntegerDim NumCurrentRecor d As IntegerDim NumTotalRecords As Integer' Set
StrTo and StrBcc to emptyStrTo = ""StrBcc = ""' Check there are records
selected "NumSelecte d = DCount("[selected]", "qrySelectEmail ", "[selected]
= TRUE")'the qryselectemail query only returns records selected and with
email adddress enteredIf NumSelected > 0 Then' Count total records
selected so that loop will stop at last recordNumTotalR ecords =
DCount("[selected]", "qrySelectEmail ", "[selected] = TRUE")' Set current
record to first recordNumCurren tRecord = 1' Loop through recordsDo Until
NumCurrentRecor d > NumTotalRecords 'The next line should needs to look up
query for each record in turnStrStore = DLookup("[email]",
"qrySelectEmail ", "??????")' store email addressStrTo = StrTo + StrStore +
";"NumCurrentRe cord = NumCurrentRecor d + 1Loop DoCmd.SendObjec t
acSendNoObject, , , StrTo, , StrBcc, , , , FalseElse' There are no
selected records MsgBox ("No records have been selected")End
IfExit_send_ema il_Click: Exit SubErr_send_ema il_Click: MsgBox "The
email was not sent" Resume Exit_send_email _ClickEnd Sub
--------------------------------------------------------------------------------

[loringdo]

I never use or seen example of DLookup Function used they way your
using it. But I have used it. I had to reformat copy of your code.
I'm noticing that you DLookup the store number. I think if you
selected just the store number then you could easy retrieve the email
addresses best on that particular store. If email address. changes just
change info in database through form. But use store number to pull info
you need. If that the case here then all your ducks are in order. But
that's ideal. I think ADO would be a lot faster so I am showing you
this piece of code. This doesn't answer your question. However hope
this helps.

Sub FindmeStore()
Dim mArr()
Dim mGetArr As Variant
Dim sTo As String, sBc As String, sNum As Variant

'I had nothing to test this, but this is where I would do something
similar

'known store num's in array

mArr = Array("1sd", "123")

For Each mGetArr In mArr

sNum = DLookup("[StoreNum]", "Employees" , "[Selected] = True And
[StoreNum] = & mGetArr)
sBc = DLookup("[EmailManger]", "Employees" , "[Selected] = True And
[StoreNum] = " & mGetArr)
sTo = DLookup("[EmployeeAddress]", "Employees" , "[Selected] = True And
[StoreNum] " & mGetArr)

' DoCmd.SendObjec t acSendNoObject, , , sTo, , sBc, , , False
DoCmd.SendObjec t acSendReport, "ObjectName ", , sTo, "CC", sBc,
"Store Num:" & sNum, ""
Next mGetArr

End Sub
'
'--------------------------------------------------------------------------------------------------------------------------------
Sub GetCriteria()

sql1 = "SELECT qryEmail_Addres ses.Selected,
qryEmail_Addres ses.EmployeeAdd ress ,
qryEmail_Addres ses.EmailStoreM angersAddress, Email_Addresses .StoreNum"
& _
" FROM qryEmail_Addres ses" & _
" Where StoreNum = """ & intStoreNum & """"

'One store at a time or if Selected = true then comment out the where
statement.
' Because of the Docmd. in the loop. I would do one store at time,
depends how many stores of course.
Set rs = New ADODB.Recordset

intStoreNum = "45D"

rs.ActiveConnec tion = CurrentProject. Connection
rs.LockType = adLockPessimist ic
rs.CursorType = adOpenKeyset
rs.Open sql1

rs.MoveFirst

strEmpAdd = ""
strManAdd = ""

Do Until rs.EOF

If rs.Fields("Sele cted") = True Then

StrTo = strEmpAdd + rs.Fields("Empl oyeeAddress") & ";"
StrBcc = strManAdd + rs.Fields("Emai lStoreMangersAd dress") & ";"

'DoCmd.SendObje ct acSendNoObject, , , StrTo, , StrBcc, , , ,
FalseElse

'Open up immediate window see the result.

Debug.Print StrTo
Debug.Print StrBcc

End If

rs.MoveNext
Loop

' Reset Variable

StrTo = ""
StrBcc = ""

rs.Close

Set rs = Nothing
End Sub

[Ian Davies]

I wrote the following to do something similar

Put the following in 'on click' event of button (called cmdEmailReps) on the
form (MyForm) you want to email from. Put a text box on this form called
'Text'.

You will need a table (MyTable) with the email addresses (E-mail) and an
index number(IndexNo) for each record. You may have a table with this info
already if so just modify field names in code below to suit your own.

The table also needs fields 'Address', 'Recipient'

You need a query (qMyTable) with all the fields from MyTable and
'[Forms]![fEmailReps]![text]' in the criteria for 'IndexNo' (no quotes)

This was quickly cobbled together from something that was specific to me to
produce code that is more general. I have not tested it so it may have some
errors. Play around with it until you get it working

Ian

*************** *************** *************** *************** ***
Private Sub cmdEmailReps_Cl ick()

Dim Counter As Integer
Dim stDocName As String
Dim Address As String
Dim Recipient As String
Dim Sender As String

PupFilter = "[IndexNo] = Forms!MyForm!te xt"
For Counter = CLng(DMin("[IndexNo]", "[qMyTable]")) To
CLng(DMax("[IndexNo]", "[qMyTable]"))

Forms!YourFormN ame!text = Counter
If Counter = DLookup("[IndexNo]", "[qMyTable]") Then

Recipient = DLookup("[Recipient]", "[qMyTable]", PupFilter)
Sender = "Your Name"

If IsNull(DLookup( "[E-mail]", "[qMyTable]", PupFilter)) Then
MsgBox "You do not have an e-mail address for " & Recipient,
vbCritical, "Warning"
GoTo line1
Else: Address = DLookup("[E-Mail]", "[qMyTable], PupFilter")
End If


DoCmd.SendObjec t acReport, stDocName, acFormatRTF, Address, , ,
"Title", "Dear" & " " & Recipient & Chr(10) & Chr(10) & Chr(13) & "Type your
text here." & Chr(10) & Chr(10) & Chr(13) & "Regards" & Chr(10) & Chr(13) &
Sender, 0
MsgBox Recipient & "'s e-mail was sent successfully.", vbInformation
, "Informatio n"
End If

line1:
Next

Exit_cmdEmailRe ps_Click:
Exit Sub

Err_cmdEmailRep s_Click:
MsgBox Err.Description
Resume Exit_cmdEmailRe ps_Click

End Sub

*************** *************** *************** ***********
"acni" <nb***@artscoun cil-ni.org> wrote in message
news:f2******** *************** *******@localho st.talkaboutdat abases.com...

I have the following peice of code to try and send an email to selected
contacts in my form.The problem is this line:

StrStore = DLookup("[email]", "qrySelectEmail ", "??????")

This looks up the email field in the records returned for the query
qrySelectEmail. The final bit needs to tell it to go to the first record
the first time the loop runs, then the second record the second time the
loop runs etc. But I cannot figure out a way to do this, which I am
certain there must be.

Thanks in anticipation
Code:
-------------------------------------------------------------------------- ------
Private Sub send_email_Clic k()On Error GoTo Err_send_email_ ClickDim StrTo
As StringDim StrBcc As StringDim StrStore As StringDim NumSelected As
IntegerDim NumCurrentRecor d As IntegerDim NumTotalRecords As Integer' Set
StrTo and StrBcc to emptyStrTo = ""StrBcc = ""' Check there are records
selected "NumSelecte d = DCount("[selected]", "qrySelectEmail ", "[selected]
= TRUE")'the qryselectemail query only returns records selected and with
email adddress enteredIf NumSelected > 0 Then' Count total records
selected so that loop will stop at last recordNumTotalR ecords =
DCount("[selected]", "qrySelectEmail ", "[selected] = TRUE")' Set current
record to first recordNumCurren tRecord = 1' Loop through recordsDo Until
NumCurrentRecor d > NumTotalRecords 'The next line should needs to look up
query for each record in turnStrStore = DLookup("[email]",
"qrySelectEmail ", "??????")' store email addressStrTo = StrTo + StrStore +
";"NumCurrentRe cord = NumCurrentRecor d + 1Loop DoCmd.SendObjec t
acSendNoObject, , , StrTo, , StrBcc, , , , FalseElse' There are no
selected records MsgBox ("No records have been selected")End
IfExit_send_ema il_Click: Exit SubErr_send_ema il_Click: MsgBox "The
email was not sent" Resume Exit_send_email _ClickEnd Sub
-------------------------------------------------------------------------- ------

[Ian Davies]

Typo

'[Forms]![fEmailReps]![text]' in the criteria for 'IndexNo' (no quotes)
should be
'[Forms]![MyForm]![text]' in the criteria for 'IndexNo' (no quotes)
or what ever you decided to call your form

Also to make it more selective you can put dropdown lists on the form to
enable u to choose criteria. These criteria can then be fed into the query

Ian

"Ian Davies" <ia********@vir gin.net> wrote in message
news:Tv******** *******@newsfe2-win.ntli.net... I wrote the following to do something similar

Put the following in 'on click' event of button (called cmdEmailReps) on the form (MyForm) you want to email from. Put a text box on this form called
'Text'.

You will need a table (MyTable) with the email addresses (E-mail) and an
index number(IndexNo) for each record. You may have a table with this info already if so just modify field names in code below to suit your own.

The table also needs fields 'Address', 'Recipient'

You need a query (qMyTable) with all the fields from MyTable and
'[Forms]![fEmailReps]![text]' in the criteria for 'IndexNo' (no quotes)

This was quickly cobbled together from something that was specific to me to produce code that is more general. I have not tested it so it may have some errors. Play around with it until you get it working

Ian

*************** *************** *************** *************** ***
Private Sub cmdEmailReps_Cl ick()

Dim Counter As Integer
Dim stDocName As String
Dim Address As String
Dim Recipient As String
Dim Sender As String

PupFilter = "[IndexNo] = Forms!MyForm!te xt"
For Counter = CLng(DMin("[IndexNo]", "[qMyTable]")) To
CLng(DMax("[IndexNo]", "[qMyTable]"))

Forms!YourFormN ame!text = Counter
If Counter = DLookup("[IndexNo]", "[qMyTable]") Then

Recipient = DLookup("[Recipient]", "[qMyTable]", PupFilter)
Sender = "Your Name"

If IsNull(DLookup( "[E-mail]", "[qMyTable]", PupFilter)) Then
MsgBox "You do not have an e-mail address for " & Recipient,
vbCritical, "Warning"
GoTo line1
Else: Address = DLookup("[E-Mail]", "[qMyTable], PupFilter")
End If


DoCmd.SendObjec t acReport, stDocName, acFormatRTF, Address, , ,
"Title", "Dear" & " " & Recipient & Chr(10) & Chr(10) & Chr(13) & "Type your text here." & Chr(10) & Chr(10) & Chr(13) & "Regards" & Chr(10) & Chr(13) & Sender, 0
MsgBox Recipient & "'s e-mail was sent successfully.", vbInformation , "Informatio n"
End If

line1:
Next

Exit_cmdEmailRe ps_Click:
Exit Sub

Err_cmdEmailRep s_Click:
MsgBox Err.Description
Resume Exit_cmdEmailRe ps_Click

End Sub

*************** *************** *************** ***********
"acni" <nb***@artscoun cil-ni.org> wrote in message
news:f2******** *************** *******@localho st.talkaboutdat abases.com...

I have the following peice of code to try and send an email to selected
contacts in my form.The problem is this line:

StrStore = DLookup("[email]", "qrySelectEmail ", "??????")

This looks up the email field in the records returned for the query
qrySelectEmail. The final bit needs to tell it to go to the first record
the first time the loop runs, then the second record the second time the
loop runs etc. But I cannot figure out a way to do this, which I am
certain there must be.

Thanks in anticipation
Code:

--------------------------------------------------------------------------
------

Private Sub send_email_Clic k()On Error GoTo Err_send_email_ ClickDim StrTo As StringDim StrBcc As StringDim StrStore As StringDim NumSelected As
IntegerDim NumCurrentRecor d As IntegerDim NumTotalRecords As Integer' Set StrTo and StrBcc to emptyStrTo = ""StrBcc = ""' Check there are records
selected "NumSelecte d = DCount("[selected]", "qrySelectEmail ", "[selected] = TRUE")'the qryselectemail query only returns records selected and with
email adddress enteredIf NumSelected > 0 Then' Count total records
selected so that loop will stop at last recordNumTotalR ecords =
DCount("[selected]", "qrySelectEmail ", "[selected] = TRUE")' Set current
record to first recordNumCurren tRecord = 1' Loop through recordsDo Until
NumCurrentRecor d > NumTotalRecords 'The next line should needs to look up
query for each record in turnStrStore = DLookup("[email]",
"qrySelectEmail ", "??????")' store email addressStrTo = StrTo + StrStore + ";"NumCurrentRe cord = NumCurrentRecor d + 1Loop DoCmd.SendObjec t
acSendNoObject, , , StrTo, , StrBcc, , , , FalseElse' There are no
selected records MsgBox ("No records have been selected")End
IfExit_send_ema il_Click: Exit SubErr_send_ema il_Click: MsgBox "The
email was not sent" Resume Exit_send_email _ClickEnd Sub

--------------------------------------------------------------------------
------

[Smartin]

acni wrote:

I have the following peice of code to try and send an email to selected
contacts in my form.The problem is this line:

StrStore = DLookup("[email]", "qrySelectEmail ", "??????")

This looks up the email field in the records returned for the query
qrySelectEmail. The final bit needs to tell it to go to the first record
the first time the loop runs, then the second record the second time the
loop runs etc… But I cannot figure out a way to do this, which I am
certain there must be.

Thanks in anticipation

This approach may be a little different but it works well for me and
it's easy to maintain.

I have a table of "Groups" which contains logical group names I want to
email. Then I have a table of "Recipients " that has a foreign key to
Groups, actual email addresses, and a boolean "active" value. On my form
I have a few command buttons corresponding to the groups. You may
consider populating a combobox or listbox with the group names instead
of individual command buttons, especially if you have several groups or
the group population tends to change.

The buttons fire code which runs SQL to obtain the active email addys
for the group, builds the recipient list, subject and message, and does
the DoCmd that actually creates the email.

HTH
--
Smartin