Multiple Email Address

[Tomfernandez1]

Hello,

I have applied the code below to a command button on a form. The form shows single records. When I engage the command button it opens my outlook and with the current form email address record shown in the "To:" area.

What I want to do is add the email addresses of the records that have been filtered by selection on a email message ("To:" area). What must I do to the code to acheive this?

Private Sub Command11_Click()
On Error GoTo Err_Command15_Click
DoCmd.SendObject acSendNoObject, , , EmailAddress, , , Subject, MainText, True

Exit_Command15_Click:
Exit Sub
Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub

Thanks,



Tommy

[pbaldy]

As I mentioned here:

https://www.accessforums.net/program...ect-15712.html

you'd open a recordset. The basics of a recordset with a loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  'following would be an SQL statement that retrieved the appropriate records.  You could also use a query directly
  strSQL = "SELECT * FROM TableName WHERE Whatever"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'build your string here
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

[Tomfernandez1]

Hi Paul,

Where does this get inserted in the previous code?

[pbaldy]

Before the SendObject, to build a string containing all the addresses to be used in the To.

[Tomfernandez1]

Hello,

I have been on holiday, but I am back and have attached a copy of a database to provide an example of my situation. If you open the form that shows driver contact information and click on the command button, it opens a new email message and places the record email address in the "To:" area.
What I need is to place multiple email address in the "To:" area when the driver form is filtered for a result of >1 records. For instance, if you filter the number 2 in the "Action" field on the form the result will be 2 records. Then click on the command button and you'll see that only one email address appears.

Can anyone help me with the code behind the command button that would allow for multiple email address when the form is filtered - resulting in multiple records?

Thanks,

Tommy

[pbaldy]

Beyond what's already been provided? In the loop where it says:

'build your string here

you'd have something like:

YourVariableName = YourVariableName & ";"

then you'd use YourVariableName in the SendObject line.

[Tomfernandez1]

I really do appreciate your time and effort in assisting me. I tried the recommendations but I still cannot to get the filtered emails to appear in the "To:" area. Could you insert the code in the example database and post for me to review?

Tommy

[pbaldy]

Why don't we fix what you've tried that isn't working? Post that code here and we'll find the problem.

[Tomfernandez1]

Here is the code behind the command button. I attached a database example to provide further information if needed.

Again, your assistance is much appreciated!




Private Sub Command639_Click()

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
'following would be an SQL statement that retrieved the appropriate records. You could also use a query directly
strSQL = "SELECT * FROM [KT VANPOOL DRIVER RECORDS]"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rs.EOF
YourVariableName = [E-MAIL ADDRESS] & ";"
rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing

DoCmd.SendObject acSendNoObject, , , [E-MAIL ADDRESS], , , , , , True

Exit_Command15_Click:
Exit Sub
Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click


End Sub

[pbaldy]

Two mistakes, one mine one yours. I goofed on the code setting the addresses, you didn't use the variable in SendObject. Try this:

  Dim strSQL                  As String
  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset
  Dim strEmailAddy            As String

  Set db = CurrentDb()
  'following would be an SQL statement that retrieved the appropriate records.  You could also use a query directly
  strSQL = "SELECT * FROM [KT VANPOOL DRIVER RECORDS]"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    strEmailAddy = strEmailAddy & rs![E-MAIL ADDRESS] & ";"
    rs.MoveNext
  Loop

  Set rs = Nothing
  Set db = Nothing

  DoCmd.SendObject acSendNoObject, , , strEmailAddy, , , , , True

That builds an email with this in the "To":

EMAILTEST@1.COM; EMAILTEST@4.COM; EMAILTEST@3.COM; EMAILTEST@2.COM

[Tomfernandez1]

We are so close....

The code populates 'all' email addresses, but what I need is the email address only for the current record; and the email addresses only for records that have been filtered.

We are almost there...

the progress we've made has been a good learning experience!!

[pbaldy]

As mentioned in the code, you create an SQL statement that limits the records as desired. If I'm reading right, that might look like:

strSQL = "SELECT * FROM [KT VANPOOL DRIVER RECORDS] WHERE [ACTION]=2"

[Tomfernandez1]

strSQL = "SELECT * FROM [KT VANPOOL DRIVER RECORDS] WHERE [ACTION]=2

This will work! Now, how do we change this code so that the "2" in "[ACTION]=2" is variable. Say, if I entered "4" in the ACTION field?

Tommy

[pbaldy]

Presuming you have it on a form somewhere:

strSQL = "SELECT * FROM [KT VANPOOL DRIVER RECORDS] WHERE [ACTION] = " & Forms!FormName.ControlName

[Tomfernandez1]

It all works fine now. I cannot thank you enough!

-Tommy