Emails from Access

[Bald Eagle]

How can I use email addresses stored in a Table for the production of an
email to the whole group (or part of the group selected via a parameter
query)? By clicking on the hyperlink field I get an Outlook Express window
with the individual recipient's address inserted ready for me to write the
email. But how can I create a group to produce the same result?

I tried using Mail Merge in Word but in the email address field I got the
correct email address followed by #Mail to: and the email address repeated
plus anoth #. What's happening here?

What is the simplest way to achieve what I am after? I am not very familiar
with Visual Basic - although I can cut & paste!

 

[Alex Dybenko]

Hi,
the easiest way - to use docmd.sendobject, see online help for details

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Bald Eagle]

Thanks Alex. But how do I get my email addresses grouped and entered into
the 'To' line in the Macro?

 

[Rick Brandt]

Thanks Alex. But how do I get my email addresses grouped and entered
into the 'To' line in the Macro?

With VBA code you...

Create a Recordset based on a query that returns the column containing the
Email addresses.

Loop through all the rows in the Recordset appending each Email address into
a string variable separated by semi-colons.

Use the resulting string variable as the To in SendObject.

 

[Bald Eagle]

Thanks Rick. That seems to be what I am looking for. However my Visual
Basic skills are not up to this! Can you point me to somewhere where I could
get the appropriate wording which I could amend and paste into my 'On click'
event procedure?
When I get this, what wording do I put into the 'To' line to call up the
created string?

Thanks in anticipation.

 

[JLo]

Here visit this link
http://www.databasedev.co.uk/send_email.html

 

[JLo]

Also, I dont want to send the email automatically. I want to type my own
personal message before it is send. How function do I call before instead of
sendobject..

 

[Rockn]

I have tried this as well and have run into the limitation as to the number
of email addresses that can go into the TO field because the string has a
limitation of 255 charachters. If you have a large list to send to it will
truncate the string at 255 charachters.

 

[Rick Brandt]

I have tried this as well and have run into the limitation as to the
number of email addresses that can go into the TO field because the
string has a limitation of 255 charachters.

That is incorrect. It might be using SendObject from a Macro, but not from
VBA code.

 

[Rick Brandt]

Also, I dont want to send the email automatically. I want to type my
own personal message before it is send. How function do I call before
instead of sendobject..

There is an argument (EditMessage) for SendObject that takes a True/False
value. True would cause the message to be opened for you to edit and then
send it manually.

 

[Alex Dybenko]

Hi,
you can separate into several emails, each with, say, 10 recipients

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Bald Eagle]

Thanks Rick. I'm aware of the facility to get the email to open for editing
before it is actually sent.

However can some one help me with my last post? You had said in your
previous reply 'With VBA code you...
Create a Recordset based on a query that returns the column containing the
Email addresses.
Loop through all the rows in the Recordset appending each Email address into
a string variable separated by semi-colons.
Use the resulting string variable as the To in SendObject.'
I then said "That seems to be what I am looking for. However my Visual
Basic skills are not up to this! Can you point me to somewhere where I could
get the appropriate wording which I could amend and paste into my 'On click'
event procedure?
When I get this, what wording do I put into the 'To' line to call up the
created string?"
Some help with this would be greatly appreciated.

 

[Rick Brandt]

Thanks Rick. I'm aware of the facility to get the email to open for
editing before it is actually sent.

However can some one help me with my last post? You had said in your
previous reply 'With VBA code you...
Create a Recordset based on a query that returns the column
containing the Email addresses.
Loop through all the rows in the Recordset appending each Email
address into a string variable separated by semi-colons.
Use the resulting string variable as the To in SendObject.'
I then said "That seems to be what I am looking for. However my
Visual Basic skills are not up to this! Can you point me to
somewhere where I could get the appropriate wording which I could
amend and paste into my 'On click' event procedure?
When I get this, what wording do I put into the 'To' line to call up
the created string?"
Some help with this would be greatly appreciated.

Dim db As Database
Dim rs As Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT EmailAddress FROM TableName"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!textField & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar,,,"Subject","Message",True

 

[Bald Eagle]

Thanks again. I have used your wording but it is throwing up an error
message 'Run Time Error 3061 Too few parameters expected 1' at the following
line - Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
Is there a typographical error in this line?

What does this code do? Will this actually open the email window in Outlook
Express? Or do I have to do something else?

 

[Rick Brandt]

Thanks again. I have used your wording but it is throwing up an error
message 'Run Time Error 3061 Too few parameters expected 1' at the
following line - Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
Is there a typographical error in this line?

You did replace this...

"SELECT EmailAddress FROM TableName"

...with SQL that will pull your data from your table right? Mine was just an
example.

What does this code do? Will this actually open the email window in
Outlook Express? Or do I have to do something else?

SendObject uses your default MAPI client usually Outlook Express or Outlook.

 

[Bald Eagle]

Thanks again.
The following are the 3 key lines as they appear in my Visual Basic (I have
temporarily called the Query 'Query2' and the field with the email addresses
is 'Email Address' -
sql = "SELECT [Email Address] FROM Query2;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

When this runs I now get the following error message 'Run Time Error 13
Type Mismatch' and it is the 'Set rs....' line which is highlighted. What's
happening?

Thanks for the clarification about what should happen on click.

 

[Rick Brandt]

Thanks again.
The following are the 3 key lines as they appear in my Visual Basic
(I have temporarily called the Query 'Query2' and the field with the
email addresses is 'Email Address' -
sql = "SELECT [Email Address] FROM Query2;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

When this runs I now get the following error message 'Run Time Error
13
Type Mismatch' and it is the 'Set rs....' line which is highlighted.
What's happening?

Thanks for the clarification about what should happen on click.

My code uses the DAO object model. That is referenced by default in Access
97, and 2003, but not in 2000, or 2002. Even in 2003 I believe both the DAO
and the ADO libraries are referenced and they both have Recordset objects in
them. So if you are using 2003 try instead...

Dim rs as DAO.Recordset
....

If you are using 2000 or 2002 then you will need to open the references
dialog (Tools - References while in VBA window) and add the DAO reference.
Referenced libraries are at the top of the dialog with a "checked" Checkbox.
You would need to scroll through the list of non-checked references until
you find the one for the DAO library. If you currently have the ADO library
checked you likely want to un-check that.

 

[Rockn]

It is using the SendObject in VBA and cuts it off at 255 charachters. He
will experience the same thing.

 

[Bald Eagle]

Some more progress! I have added the DAO 3.6 Object Library to the
References. I have also used 'Dim rs As DAO.Recordset'. This now gets me to
the line ' ToVar = ToVar & rs!textField & ";" ' where I am now getting a Run
Time Error 3265 - Item not found in this collection'. Does this mean that
there is another reference which I need to open?
By the way I am using MS Access 2002.
Thanks again for your patience!
Bald Eagle

Thanks again.
The following are the 3 key lines as they appear in my Visual Basic
(I have temporarily called the Query 'Query2' and the field with the
email addresses is 'Email Address' -
sql = "SELECT [Email Address] FROM Query2;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

When this runs I now get the following error message 'Run Time Error
13
Type Mismatch' and it is the 'Set rs....' line which is highlighted.
What's happening?

Thanks for the clarification about what should happen on click.

My code uses the DAO object model. That is referenced by default in Access
97, and 2003, but not in 2000, or 2002. Even in 2003 I believe both the DAO
and the ADO libraries are referenced and they both have Recordset objects in
them. So if you are using 2003 try instead...

Dim rs as DAO.Recordset
....

If you are using 2000 or 2002 then you will need to open the references
dialog (Tools - References while in VBA window) and add the DAO reference.
Referenced libraries are at the top of the dialog with a "checked" Checkbox.
You would need to scroll through the list of non-checked references until
you find the one for the DAO library. If you currently have the ADO library
checked you likely want to un-check that.

 

[Rick Brandt]

Some more progress! I have added the DAO 3.6 Object Library to the
References. I have also used 'Dim rs As DAO.Recordset'. This now
gets me to the line ' ToVar = ToVar & rs!textField & ";" ' where I
am now getting a Run Time Error 3265 - Item not found in this
collection'. Does this mean that there is another reference which I
need to open?
By the way I am using MS Access 2002.
Thanks again for your patience!
Bald Eagle

That is another example I used. You need to change "textField" to the name
of your field in the query containing the Email addresses.