Sending a group of e-mails from a query

  • Thread starter Thread starter Norm75
  • Start date Start date
N

Norm75

I have a DB in Access 2003 and following a query to select out a particular
group, I want to send them all the same e-mail. I have the e-mail field as
usual with each e-mail shown: mailto:xxxx@xxxx . Individually, I can click on
the e-mail and outlook will send it OK. But to get it to function with a
group is my problem. I would certainly not want to select each person
individually as there could be 40-50 at a time from the DB.

I would appreciate your help with this problem. Many thanks. Norm.
 
You'll need to create a vba function that loop through the records returned
by your queries to populate the TO field of the SendObject method. Such as,

(Non Tested Air Code)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sTo As String

'Build To recipient list
Set db = CurrentDb()
Set rst = db.OpenRecordset("YourQueryName")

rst.MoveLast 'Ensure valid count
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do While Not .EOF
sTo = sTo & ";" & ![QueryEmailAddressFieldName]
.MoveNext
Loop
End With
sTo = Right(sTo, Len(sTo) - 1) 'trim leading ;
End If

'Send Email
DoCmd.SendObject acSendNoObject, , , sTo, , , "Subject Line Goes Here",
"Email Body Text Goes Here", True
 
Thank you Arvin and Daniel. Very helpful. However, not being a programmer, I
shall have to do some studying to get my mind around this topic. I don't
suppose there is a simpler way to do this that doesn't require programming?

Norm.

Daniel Pineault said:
You'll need to create a vba function that loop through the records returned
by your queries to populate the TO field of the SendObject method. Such as,

(Non Tested Air Code)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sTo As String

'Build To recipient list
Set db = CurrentDb()
Set rst = db.OpenRecordset("YourQueryName")

rst.MoveLast 'Ensure valid count
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do While Not .EOF
sTo = sTo & ";" & ![QueryEmailAddressFieldName]
.MoveNext
Loop
End With
sTo = Right(sTo, Len(sTo) - 1) 'trim leading ;
End If

'Send Email
DoCmd.SendObject acSendNoObject, , , sTo, , , "Subject Line Goes Here",
"Email Body Text Goes Here", True
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.



Norm75 said:
I have a DB in Access 2003 and following a query to select out a particular
group, I want to send them all the same e-mail. I have the e-mail field as
usual with each e-mail shown: mailto:xxxx@xxxx . Individually, I can click on
the e-mail and outlook will send it OK. But to get it to function with a
group is my problem. I would certainly not want to select each person
individually as there could be 40-50 at a time from the DB.

I would appreciate your help with this problem. Many thanks. Norm.
Click to expand...
Click to expand...
 
You can send a single email from the File menu, but there isn't a way to get
the concatenated string of email addresses without some programming. Cutting
and pasting code from one of the samples into the code window of a command
button, or in my second example, a standard module, then changing the
obvious (like the query name of the query used to get the recipient list)
should be enough for you to get it working.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Norm75 said:
Thank you Arvin and Daniel. Very helpful. However, not being a programmer,
I
shall have to do some studying to get my mind around this topic. I don't
suppose there is a simpler way to do this that doesn't require
programming?

Norm.

Daniel Pineault said:
You'll need to create a vba function that loop through the records
returned
by your queries to populate the TO field of the SendObject method. Such
as,

(Non Tested Air Code)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sTo As String

'Build To recipient list
Set db = CurrentDb()
Set rst = db.OpenRecordset("YourQueryName")

rst.MoveLast 'Ensure valid count
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do While Not .EOF
sTo = sTo & ";" & ![QueryEmailAddressFieldName]
.MoveNext
Loop
End With
sTo = Right(sTo, Len(sTo) - 1) 'trim leading ;
End If

'Send Email
DoCmd.SendObject acSendNoObject, , , sTo, , , "Subject Line Goes Here",
"Email Body Text Goes Here", True
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.



Norm75 said:
I have a DB in Access 2003 and following a query to select out a
particular
group, I want to send them all the same e-mail. I have the e-mail field
as
usual with each e-mail shown: mailto:xxxx@xxxx . Individually, I can
click on
the e-mail and outlook will send it OK. But to get it to function with
a
group is my problem. I would certainly not want to select each person
individually as there could be 40-50 at a time from the DB.

I would appreciate your help with this problem. Many thanks. Norm.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Arvin - I'll give it a shot and see how I do.
Norm.

Arvin Meyer said:
You can send a single email from the File menu, but there isn't a way to get
the concatenated string of email addresses without some programming. Cutting
and pasting code from one of the samples into the code window of a command
button, or in my second example, a standard module, then changing the
obvious (like the query name of the query used to get the recipient list)
should be enough for you to get it working.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Norm75 said:
Thank you Arvin and Daniel. Very helpful. However, not being a programmer,
I
shall have to do some studying to get my mind around this topic. I don't
suppose there is a simpler way to do this that doesn't require
programming?

Norm.

Daniel Pineault said:
You'll need to create a vba function that loop through the records
returned
by your queries to populate the TO field of the SendObject method. Such
as,

(Non Tested Air Code)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sTo As String

'Build To recipient list
Set db = CurrentDb()
Set rst = db.OpenRecordset("YourQueryName")

rst.MoveLast 'Ensure valid count
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do While Not .EOF
sTo = sTo & ";" & ![QueryEmailAddressFieldName]
.MoveNext
Loop
End With
sTo = Right(sTo, Len(sTo) - 1) 'trim leading ;
End If

'Send Email
DoCmd.SendObject acSendNoObject, , , sTo, , , "Subject Line Goes Here",
"Email Body Text Goes Here", True
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.



:

I have a DB in Access 2003 and following a query to select out a
particular
group, I want to send them all the same e-mail. I have the e-mail field
as
usual with each e-mail shown: mailto:xxxx@xxxx . Individually, I can
click on
the e-mail and outlook will send it OK. But to get it to function with
a
group is my problem. I would certainly not want to select each person
individually as there could be 40-50 at a time from the DB.

I would appreciate your help with this problem. Many thanks. Norm.
Click to expand...
Click to expand...
Click to expand...
 
I am getting an error message too few parameter, expected 1. Can you shed
any light?
--
Dar


Daniel Pineault said:
You'll need to create a vba function that loop through the records returned
by your queries to populate the TO field of the SendObject method. Such as,

(Non Tested Air Code)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sTo As String

'Build To recipient list
Set db = CurrentDb()
Set rst = db.OpenRecordset("YourQueryName")

rst.MoveLast 'Ensure valid count
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do While Not .EOF
sTo = sTo & ";" & ![QueryEmailAddressFieldName]
.MoveNext
Loop
End With
sTo = Right(sTo, Len(sTo) - 1) 'trim leading ;
End If

'Send Email
DoCmd.SendObject acSendNoObject, , , sTo, , , "Subject Line Goes Here",
"Email Body Text Goes Here", True
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.



Norm75 said:
I have a DB in Access 2003 and following a query to select out a particular
group, I want to send them all the same e-mail. I have the e-mail field as
usual with each e-mail shown: mailto:xxxx@xxxx . Individually, I can click on
the e-mail and outlook will send it OK. But to get it to function with a
group is my problem. I would certainly not want to select each person
individually as there could be 40-50 at a time from the DB.

I would appreciate your help with this problem. Many thanks. Norm.
Click to expand...
Click to expand...
 
Sounds like you've created an sql query in code and it's halting with error.

Stop the code at the point where the sql is created. Ether debug.print the
sql or put it in a msgbox. Go to create a query and paste the sql into it.
run the query.

It will ask you for a value of the 'missing parameter'. You'll then be able
to look at he sql and se what's wrong with it.


DarS said:
I am getting an error message too few parameter, expected 1. Can you shed
any light?
--
Dar


Daniel Pineault said:
You'll need to create a vba function that loop through the records
returned
by your queries to populate the TO field of the SendObject method. Such
as,

(Non Tested Air Code)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sTo As String

'Build To recipient list
Set db = CurrentDb()
Set rst = db.OpenRecordset("YourQueryName")

rst.MoveLast 'Ensure valid count
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do While Not .EOF
sTo = sTo & ";" & ![QueryEmailAddressFieldName]
.MoveNext
Loop
End With
sTo = Right(sTo, Len(sTo) - 1) 'trim leading ;
End If

'Send Email
DoCmd.SendObject acSendNoObject, , , sTo, , , "Subject Line Goes Here",
"Email Body Text Goes Here", True
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.



Norm75 said:
I have a DB in Access 2003 and following a query to select out a
particular
group, I want to send them all the same e-mail. I have the e-mail field
as
usual with each e-mail shown: mailto:xxxx@xxxx . Individually, I can
click on
the e-mail and outlook will send it OK. But to get it to function with
a
group is my problem. I would certainly not want to select each person
individually as there could be 40-50 at a time from the DB.

I would appreciate your help with this problem. Many thanks. Norm.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Daniel. I was using it in the CC field. You have been very helpful.
Thanks again.
--
Dar


Daniel Pineault said:
You'll need to create a vba function that loop through the records returned
by your queries to populate the TO field of the SendObject method. Such as,

(Non Tested Air Code)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sTo As String

'Build To recipient list
Set db = CurrentDb()
Set rst = db.OpenRecordset("YourQueryName")

rst.MoveLast 'Ensure valid count
If rst.RecordCount > 0 Then
With rst
.MoveFirst
Do While Not .EOF
sTo = sTo & ";" & ![QueryEmailAddressFieldName]
.MoveNext
Loop
End With
sTo = Right(sTo, Len(sTo) - 1) 'trim leading ;
End If

'Send Email
DoCmd.SendObject acSendNoObject, , , sTo, , , "Subject Line Goes Here",
"Email Body Text Goes Here", True
--
Hope this helps,

Daniel Pineault
If this post was helpful, please rate it by using the vote buttons.



Norm75 said:
I have a DB in Access 2003 and following a query to select out a particular
group, I want to send them all the same e-mail. I have the e-mail field as
usual with each e-mail shown: mailto:xxxx@xxxx . Individually, I can click on
the e-mail and outlook will send it OK. But to get it to function with a
group is my problem. I would certainly not want to select each person
individually as there could be 40-50 at a time from the DB.

I would appreciate your help with this problem. Many thanks. Norm.
Click to expand...
Click to expand...
 
Back
Top