Email multiple emails from a table

[jmuck]

Can someone help me try and figure out how I can get my code to not just pull the first email based on a dlookup from a table...

I am looking to enter information in a form and have outlook populated with to specific email addresses based on a groupid in the table. Right now this code works fine but only returns 1 email address not all with the same GroupID.

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click



Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stWho As String '-- Reference to tblUsers
Dim stEvalEmp As String '-- Person who assigned ticket
Dim stDelBidPrice As String '-- Delivery Bid Price
Dim stOverBidPrice As String '-- Override Bid Price
Dim stOverMeanPrice As String '-- Override Mean Price
Dim stOverOfferPrice As String '-- Override Offer Price

'-- Combo of names to assign price change to
stWho = Me.GroupID
stWhere = "ClientServices_tbl.GroupID = " & "'" & stWho & "'"


'-- Looks up email address from ClientServices_tbl
varTo = DLookup("[ClientEMAIL]", "ClientServices_tbl", stWhere)

RecDate = Me.Date

stSubject = ":: Price Change Request :: " & RecDate

stCusip = Me.CUSIP
stDelBidPrice = Me.DeliveredBidPrice
stOverBidPrice = Me.OverrideBidPrice
stOverOfferPrice = Me.OverrideOfferPrice
stOverMeanPrice = Me.OverrideMeanPrice

'-- Evaluators employee who assigns ticket
stEvalEmp = Me.EvalEmpId.Column(1)

stText = "A price change has been requested by Evaluators Department." & Chr$(13) & Chr$(13) & _
"This price change has been requested by by: " & stEvalEmp & Chr$(13) & _
Chr$(13) & _
"Price Change Details:" & Chr$(13) & _
"Received Date: " & RecDate & Chr$(13) & _
"CUSIP: " & stCusip & Chr$(13) & _
"Delivered Bid Price: " & stDelBidPrice & Chr$(13) & _
"Override Bid Price: " & stOverBidPrice & Chr$(13) & _
"Override Offer Price: " & stOverOfferPrice & Chr$(13) & _
"Override Mean Price: " & stOverMeanPrice & Chr$(13) & _
"This is an automated message. Please do not respond to this e-mail."


'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click
DoCmd.GoToRecord , , acNewRec
End Sub

 

[CEH]

Might take a look at this thread, see if anything here can help you. It's made to email a list of people a report in snapshot format.

http://www.access-programmers.co.uk/forums/showthread.php?t=96783

 

[apr pillai]

I maintain the Addressbook within my own Project and concatenate the E-Mail Adresses into a string. It will give flexibility for selection of addresses depending on several parameters. Try doing that rather than depending on Outlook.

visit my site http://msaccessblog.blogspot.com

You will see only a few things there now, but I am updating it continuously.

apr pillai

 

[jmuck]

Multiple Emails from a table

I have attached a copy of the database in question...

Can someone look at it and possibly give an answer on how to get all the email addresses associated to a GroupID that is picked from the form tblpriceadjustment to populate in the SendObject...

Your help is greatly appreiciated

 

[kentwood]

I would love to be able to help you, but I have exactly the same situation as you, using the same code format. Only problem is you got yours to work, even if for only 1 address. I cannot even get one address working. I have tried so many changes that I have lost track of where I am at. Even looked at your tables / code and compared to mine.

While you are waiting on a response, could you possibly look at my code and at least get it working to the point where yours is? Having played with this most of the day is making me nuts. Thanks.