Simple Code Problem

[kinetix]

I have the following module which sends a report to an e-mail address. Instead of sending it to a stated address i want it to send the report to
e-mail addresses contained in a Customer E-mail field in a Customer table.

Function send()
On Error GoTo send_Err

DoCmd.SendObject acReport, "Title", "HTML(*.html)", "email@email.com", "", "", "Subject", "Message", False, ""


send_Exit:
Exit Function

send_Err:
MsgBox Error$
Resume send_Exit

End Function


So basically i want to know how i can replace the "email@email.com" part with a reference to a field in a table.

Thanks In Advance!

 

[RichO]

Check Access help for information on DLookup. That should give you what you need.

 

[sfreeman@co.mer]

Do you want a long sample code listing that handles this issue?

You will have to work thru it to suit your needs....

 

[modest]

Dim strEmail As String
strEmail = DLookup("[Email Field Name]", "Table Name", "[Email Field Name] = " & Forms![Form Name]![Edit Box].Value)

Replace "email@email.com" with strEmail


Note:
The criteria: "[Email Field Name] = " & Forms![Form Name]![Edit Box].Value can be replace with anything you would puy in the WHERE clause of an SQL statement to find the record you're looking for.

 

[kinetix]

Is there anyway i could get it so the E-mail sends to all addreses listed in the table instead of having a set criteria?

 

[RichO]

This is just typed on the fly so excuse any errors

Dim db As Database
Dim rs As Recordset
Dim strEmail As String

Set db = Currentdb

Set rs = db.OpenRecordset ("SELECT EmailAddress FROM MyTable")
Do While Not rs.EOF
     strEmail = strEmail & rs!EmailAddress & "; "
     rs.MoveNext
Loop
strEmail = Left$(strEmail, Len(strEmail)-2)
rs.Close
Set db = Nothing
Set rs = Nothing

If you are running this code from the form that uses the necesary table as its record source, you can change 'Set rs = db.OpenRecordset....' to

Set rs = Me.RecordsetClone

Good Luck!

 

[modest]

This is good, but I'd add a few things.

Dim db As Database
Dim rs As Recordset
Dim strEmail As String
[B]Dim strSQL As String[/B]

[B]strSQL = "SELECT [EmailAddress] FROM [MyTable];"[/B]

Set db = Currentdb
Set rs = db.OpenRecordset([B]strSQL[/B])
[B]rs.MoveLast[/B]

[B]If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst[/B]

    Do While Not rs.EOF
         strEmail = strEmail & rs!EmailAddress & ";"
         rs.MoveNext
    Loop

    strEmail = Left$(strEmail, Len(strEmail)-2)
[B]End If[/B]

rs.Close
Set db = Nothing
Set rs = Nothing

It's mainly error checking. Also, the semi-colon may have to be replaced by a comma as an email address separator. I'm not sure, but macros use commas, while Outlook uses semi-colons.