Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Question Unanswered: e-mailing multiple addresses

    I have a database set up of customers with their personal details which includes their e-mail address. Using a button on a form with the "sendobject" command on the click event I want to be able to send the same message to all of the people in the customers table with an e-mail address.

    I understand the docmd.sendobject method but I want to be able to automatically create a string of multiple address to go in the "To:" part.

    In laymans terms I need to extract the relevant data from the customers tables, create a long string of the addresses, separated by ";" and then put it in the sendobject method?? However i don't know how to achieve this.

    Hope that makes sense.
    Any ideas?

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Real quick....I'ld say try this....

    Code:
    Public Function GetAllEmailAddresses() As String
    	Dim dbs As Database
    	Dim rst As Recordset
    	Dim strSQL As String
    	
    	On Error Goto Err_GetAllAddresses
    	Set dbs = CurrentDb()
    	strSQL = "SELECT * FROM [myTableName]"
    	strSQL = strSQL & " WHERE Len([myTableEmailAddressField]) > 0;"
    	Set rst = dbs.OpenRecordset(strSQL)
    	GetAllEmailAddresses = ""
    	
    	' If there are no E-Mail Addresses found then,
    	' display a message.  Otherwise, group the addresses together.
    	If (rst.EOF) Then
    		MsgBox= "There are no Addresses to be found in table."
    	Else
    		While (Not (rst.EOF))
    			GetAllEmailAddresses = GetAllEmailAddresses & rst![myTableEmailAddressField] & ";"
    			rst.MoveNext
    		Wend
    	End If
    	If GetAllEmailAddresses <> "" Then GetAllEmailAddresses = Left(GetAllEmailAddresses, Len(GetAllEmailAddresses) - 1)
    Exit_GetAllAddresses:
    	' Close the recordset and the database.
    	rst.Close
    	dbs.Close
    	Exit Function
    
    Err_GetAllAddresses:
    	MsgBox"There was an error while processing E-Mail Addresses."
    	Resume Exit_FetAllAddresses
    End Function
    USAGE:

    Dim EmailAddresses As String
    EmailAddresses = GetAllEmailAddresses

    of just put the GetAllEmailAddresses function in place of your string variable located in the SendObject method like so:

    Code:
    DoCmd.SendObject acSendReport, , DocFormat, GetAllEmailAddresses, , , MailSubject, MailMessage, EditBeforeSendMail

  3. #3
    Join Date
    Jun 2004
    Posts
    3

    Thumbs up

    Thanx CyberLynx,

    I haven't tried it yet but it looks good. I couldn't quite make the connection between running the SQL code and storing the output as a string to work with.

    Hurrah

    mr_woody

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •