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.
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."
While (Not (rst.EOF))
GetAllEmailAddresses = GetAllEmailAddresses & rst![myTableEmailAddressField] & ";"
If GetAllEmailAddresses <> "" Then GetAllEmailAddresses = Left(GetAllEmailAddresses, Len(GetAllEmailAddresses) - 1)
' Close the recordset and the database.
MsgBox"There was an error while processing E-Mail Addresses."
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: