Multiple DLookup Problem

[Stan]

I'm trying to include multple names in the'To' field of some DoCmd.SendObject
code. The code is pulling from a query labeled 'qryEmployee'. I'm not sure
how to separate the names so they both appear on the 'To' line of an email.
Any thoughts?


DLookup("[Employee]", "qryEmployee")____ DLookup("[Supervisor]","qryEmployee")

 

[Tom Wickerath]

Hi Stan,

DLookup will return only the first matching value. It appears as if you want
to send a message to an employee, and to this person's supervisor. Is this
correct?

You can submit a string of semicolon delimited e-mail addresses. Here is a
function, for the sample Northwind database, that builds such a string.
You'll want to substitute the name of your table and field in place of
Customers and ContactName in the BulkEmail function shown below. The
SendEmail shown after the BulkEmail function provides an example of using a
concatentated list of ContactNames in the BCC field. Does this help you
achieve your objective?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html


Option Compare Database
Option Explicit

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 = ";"

strSQL = "SELECT [ContactName] FROM [Customers] " _
& "WHERE [ContactName] Is Not Null;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

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

lngLen = Len(strOut) - Len(conSEP)

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

Debug.Print BulkEmail

ExitProc:
If Not rs Is Nothing = True Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Function

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

Function SendEmail()
On Error GoTo ProcError

DoCmd.SendObject _
To:="YourEmailAddressHere", _
BCC:=BulkEmail, _
Subject:="Enter your subject here", _
MessageText:="Enter your text here", _
EditMessage:=True

ExitProc:
Exit Function

ProcError:
Select Case Err.Number
'User cancelled message (2293 & 2296 are raised by Outlook, not Outlook
Express).
Case 2501, 2293, 2296
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure SendEMail..."
End Select
Resume ExitProc
End Function


__________________________________________

 

[Douglas J. Steele]

Use a semi-colon. (You can also use whatever character has been set as the
list separator on the Number tab of the Regional Settings Properties dialog
box in Windows Control Panel.)

 

[Douglas J. Steele]

Looks like I may have misinterpretted what it was you were looking for.

As Tom points out, DLookup will only return the first value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Use a semi-colon. (You can also use whatever character has been set as the
list separator on the Number tab of the Regional Settings Properties
dialog box in Windows Control Panel.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to include multple names in the'To' field of some
DoCmd.SendObject
code. The code is pulling from a query labeled 'qryEmployee'. I'm not
sure
how to separate the names so they both appear on the 'To' line of an
email.
Any thoughts?


DLookup("[Employee]", "qryEmployee")____
DLookup("[Supervisor]","qryEmployee")