Choosing suppliers from form to send an e-mail to

[Guest]

I have been tasked to create a simple e-mail method from a form in access. I
have a form based on a table, the table has 3 fields supplier, e-mail &
pick(yes/no) field.

The user has asked that in the form they are able to pick specific e-mail
addresses by clicking in the Pick (yes/No) field than clicking the button to
open up outlook with the chosen e-mail addresses in the To field of outlook.

This is what I used so far

Dim StrTo As String
StrTo = DLookup("[e-mail]", "[Supplier]", "[pick]=true")
DoCmd.SendObject,,,To:=StrTo

It seems to work but only one e-mail address shows up in the to field
instead of all of them that were chosen.
I am new to programming and I'm sure I am doing something wrong but I'm
really not to sure what it is, could someone be of assistance out there?

Thanks

 

[Steve Schapel]

DGH,

The DLookup function returns one record. If there are more than one
record in the table, or more than one record that matches the criteria,
it will return the first of them. If you want to construct a string
containing the email addresses of all the matching records , you will
need to loop through a recordset. Here's a skeleton example...

Dim StrTo As String
Dim rst As DAO.RecordSet
Set rst = CurrentDb.OpenRecordset("SELECT [e-mail] FROM [Supplier]
WHERE [pick]=-1"
With rst
Do Until .EOF
StrTo = StrTo & !Email & ","
.MoveNext
Loop
.Close
End With
StrTo = Left(StrTo, Len(StrTo) - 1)
DoCmd.SendObject acSendNoObject, , , StrTo
Set rst = Nothing

 

[Guest]

Thank you Steve for the info I really appreciate the quick response. There
is one thing I am unsure of however. When the user chooses the e-mails from
the list the last one is always missing. I'm not really sure why?

Thanks
DGH

DGH,

The DLookup function returns one record. If there are more than one
record in the table, or more than one record that matches the criteria,
it will return the first of them. If you want to construct a string
containing the email addresses of all the matching records , you will
need to loop through a recordset. Here's a skeleton example...

Dim StrTo As String
Dim rst As DAO.RecordSet
Set rst = CurrentDb.OpenRecordset("SELECT [e-mail] FROM [Supplier]
WHERE [pick]=-1"
With rst
Do Until .EOF
StrTo = StrTo & !Email & ","
.MoveNext
Loop
.Close
End With
StrTo = Left(StrTo, Len(StrTo) - 1)
DoCmd.SendObject acSendNoObject, , , StrTo
Set rst = Nothing

--
Steve Schapel, Microsoft Access MVP

I have been tasked to create a simple e-mail method from a form in access. I
have a form based on a table, the table has 3 fields supplier, e-mail &
pick(yes/no) field.

The user has asked that in the form they are able to pick specific e-mail
addresses by clicking in the Pick (yes/No) field than clicking the button to
open up outlook with the chosen e-mail addresses in the To field of outlook.

This is what I used so far

Dim StrTo As String
StrTo = DLookup("[e-mail]", "[Supplier]", "[pick]=true")
DoCmd.SendObject,,,To:=StrTo

It seems to work but only one e-mail address shows up in the to field
instead of all of them that were chosen.
I am new to programming and I'm sure I am doing something wrong but I'm
really not to sure what it is, could someone be of assistance out there?

Thanks

 

[Steve Schapel]

DGH,

What event is the code running on? Maybe the update to the Pick field
has not been saved yet?

 

[Guest]

I developed the form from the table directly so I figured that by clicking on
the yes/no field in the form the table data is updated at the same time. So I
didn't put an event to the pick field in the form. Forgive my ignorance as
I'm still quite new at VB programming.

 

[Steve Schapel]

DGH,

If you edit a field via a form, the changed data is not updated to the
table until you either close the form, or move to another record on the
form, or otherwise explicitly save it. So if you tick the Pick
checkbox, and then run your code without any of the above occurring,
then that record will not be included in the reordset based on the Pick
criteria.

 

[Guest]

Thank You for your help. Worked !

DGH,

If you edit a field via a form, the changed data is not updated to the
table until you either close the form, or move to another record on the
form, or otherwise explicitly save it. So if you tick the Pick
checkbox, and then run your code without any of the above occurring,
then that record will not be included in the reordset based on the Pick
criteria.