Access email button - Multiple recipients from query

[mikeharari]

Hi,

I'm not a VB expert and this is driving me crazy -I am trying to have a button located (on an Access form) to open Outlook, attach a particular report and populate the email with addresses from a query.

The query name is High_risk and the field that contains the addresses is called "Email". The report I am trying to attach is a called "High-Risk Investors - NDN Weekly Newsletter" and I am trying to atach it as text.


When I try to click my button, I get the error "User-defined type not defined" and the line "Dim rs As DAO.Recordset" is highlighted in the compiler.

Here's the code I have:
--------------------------------------------------
Private Sub Command45_Click()
On Error GoTo Err_Command45_Click
Dim strTo As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from High_risk;")
If rs.EOF Then strTo = ""
rs.MoveFirst
Do Until rs.EOF
strTo = rs!Email & strTo & ";"
rs.MoveNext
Loop
If Right(strTo, 1) = ";" Then strTo = Left(strTo, Len(strTo) - 1)
rs.Close
Set rs = Nothing
DoCmd.SendObject , "High-Risk Investors - NDN Weekly Newsletter", "acformattxt", strTo, "", "", "", True, ""

Exit_Command45_Click:
Exit Sub

Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub
---------------------------------------------------------

Thanks very much in advance for any input.

-Mike

[hansup]

When I try to click my button, I get the error "User-defined type not defined" and the line "Dim rs As DAO.Recordset" is highlighted in the compiler.
It sounds like your project doesn't have a reference set for DAO. If you are using Access 2003, you can go to the Visual Basic Editor (Alt+F11) and select Tools->References from the menu. Scroll down the list of available references until you find one similar to "Microsoft DAO 3.6 Object Library" and click to place a check mark next to it. Then click OK and try your code again.

If your Access version is not 2003, your DAO library may not be 3.6. Choose whichever version is available for your system.

Good luck,
Hans

[OBP]

You can also use
dim rs as Object
to get over that particular problem, but to use recordsets you still need to reference DAO or ADO.

[CreganTur]

Welcome to the forum- it's always good to see new members.



You can also use
dim rs as Object
to get over that particular problem, but to use recordsets you still need to reference DAO or ADO.

This method is called Late Binding. Basically it means that you dimension a variable as a basic object, and then set it to the type of object you want later.

The opposite of this is Early Binding, which is when you explicitly declare the exact object you want at the start. That's what you're doing when you use: Dim rs As DAO.Recordset

Personally, I use early binding for almost everything- the only exception is when I'm dealing with an outside application, like Excel, and I know that my users each have different versions of Excel. If I used early binding, then users who don't have the exact version of Excel I declared would encounter an error. By using late binding, I am able to pull a generic Excel object that would work for everyone.

HTH:thumb

[OBP]

Randy, Nice explanation :thumb

[hansup]

Randy, Nice explanation :thumb Indeed! Both you and Randy are very generous.

You put my effort to shame. It was late. I saw a missing reference and told him how to fix it. Then went to sleep without even looking at the rest of his code.

I looked again today and noticed he will encounter an error with rs.movenext when his recordset is empty. I made several changes as follows:
Private Sub Command45_Click()
On Error GoTo Err_Command45_Click
Dim strTo As String
Dim rs As DAO.Recordset
'Microsoft says "You should assign Database objects to object variables
'and refer to them by variable name", so let's Dim a var for a ref to
'the current database:
Dim db As DAO.Database

'Set rs = CurrentDb.OpenRecordset("select * from High_risk;")
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from High_risk;")
'If rs.EOF Then strTo = ""
strTo = vbNullString 'it already was; this makes it explicit
If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
'strTo = rs!Email & strTo & ";"
strTo = strTo & rs!Email & ";"
rs.MoveNext
Loop
'If Right(strTo, 1) = ";" Then strTo = Left(strTo, Len(strTo) - 1)
strTo = Left$(strTo, Len(strTo) - 1)
'rs.Close
'Set rs = Nothing
DoCmd.SendObject , "High-Risk Investors - NDN Weekly Newsletter", _
"acformattxt", strTo, "", "", "", True, ""
End If 'EOF
rs.Close

Exit_Command45_Click:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub
Regards,
Hans

[OBP]

Looks Good. :friends:
Personally I am old fashioned and find the Recordset Record Count and use it in a For Next loop. Harping back to my ancient BASIC days when you had to do it all yourself.

[hansup]

Looks Good. :friends: OK. Thanks for looking. I've been learning a lot from watching how you guys tackle this stuff.:beerchug:

Personally I am old fashioned and find the Recordset Record Count and use it in a For Next loop. Harping back to my ancient BASIC days when you had to do it all yourself. Boy Howdy! Even dBase had BOF and EOF indicators!

Later,
Hans

[mikeharari]

Thank you very much all for your help guys, much appreciated!
I'll go ahead and give this a try.

Cheers,
Mike