Emailing from address in Table

[Geoff]

Hi everyone,

This question has been answered many times but I'm afraid, after spending
all day researching it, I cannot get it to work.

I have a table with one of the fields containing email addresses. I want to
have a command button that can be clicked on, which will find all the
addresses and open a new email in Outlook Express, with the addresses in the
BCC field. I also want to add an attachment but I'm not that bothered if I
have to do that bit manually.

I've read several answers to this question from other people and the
simplest way, although you can't specify an attachment, seems to be to use
the SendObject command. I understand the syntax of SendObject but I don't
think that's the problem.

Various different ideas of how to write the code all have something in
common, which is that they have a line similar to
"CurrentDB.OpenRecordset("Tablename")". There is then some kind of loop that
goes through all the records and adds the email addresses into one string,
with a colon between each one. This string is then used for the BCC part of
the SendObject command.

However, what I can't work out is where the particular field that has my
email addresses in is specified? I can't see that in any of the suggested
code. I can see that the table name is there, but not the email address
field. Even so, I've tied several different bits of code and the computer
just sits there and doesn't even try to open Outlook Express. I've also
tried creating a new query with just the email address field in, but that
doesn't work either.

Would someone be so kind as so tell me what I'm missing, and suggest some
code that will work? Thanks very much.

By the way, I'm using Access 2000.

Geoff.

 

[Steve Schapel]

Geoff,

This can't really be done with a macro. As you have intimated in your
post, you will need a vba procedure.

The general answer to the question of "where is my email address field
in the code?" is that it will be within the Loop construct that you
mentioned.

Maybe you could post back with the actual code you have been trying, and
we could use that as a starting point.

 

[Geoff]

Geoff,

This can't really be done with a macro. As you have intimated in your
post, you will need a vba procedure.

The general answer to the question of "where is my email address field
in the code?" is that it will be within the Loop construct that you
mentioned.

Maybe you could post back with the actual code you have been trying, and
we could use that as a starting point.

Thanks for replying Steve. I've tried several bits of code, but below is one
of them...

----------------------------------------------------
Dim rst as Recordset
Dim addy as String
Set rst = CurrentDB.OpenRecordset("Tablename")
str = ""
rst.MoveFirst
Do While Not rst.EOF
addy = addy & rst.EmailAddress & ";"
rst.MoveNext
Loop
addy = Left(addy,Len(addy)-1)

DoCmd.SendObject , , , addy, , , "subject", "message text", True
rst.Close
Set rst = Nothing

 

[Steve Schapel]

Geoff,

There are some inefficiencies and laxity in this code, but essentially
it should probably work ok. Here's a slightly tidier version...

----------------------------------------------------
Dim rst as DAO.Recordset
Dim addy as String
Set rst = CurrentDB.OpenRecordset("SELECT EmailAddress FROM Tablename")
Do While Not rst.EOF
addy = addy & rst!EmailAddress & ";"
rst.MoveNext
Loop
addy = Left(addy,Len(addy)-1)
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , addy,
"Subject", "MessageText", True
rst.Close
Set rst = Nothing
----------------------------------------------------

Obviously, substitute the actual names of your email address field and
table name where applicable, and also the address to put in the To:
field of the email. If this doesn't work, let us know what does
actually happen when you try to run this.

 

[Mortsiefer, Markus]

Hallo

I hope you could help me to i am trying to do the same as Geoff and i copied
the code from below and when i click on the command butten i recieve a error
message Compile error and it highlights Dim rst as DAO.Recordset Steve could
you maybe give me a idea i am not very good with VB at all so i dont
understand much of it here is how i made the code.

My table is called Email and the Field is called Email to

Private Sub Email_Click()

Dim rst As DAO.Recordset
Dim addy As String
Set rst = CurrentDb.OpenRecordset("SELECT Email from Email")
Do While Not rst.EOF
addy = addy & rst!Email & ";"
rst.MoveNext
Loop
addy = Left(addy, Len(addy) - 1)
DoCmd.SendObject acReport, "Email Report", "SnapshotFormat(*.snp)", "addy",
"", "", Forms![Emial Report]!Subject
rst.Close
Set rst = Nothing

End Sub

 

[Steve Schapel]

Markus,

Access 2000 does not hace the DAO library referenced by default. In
your VB Editor window, of any mudule, go to the Tools|References menu,
find 'Microsoft DAO 3.6 Object Library' and tick the box. Should work
ok then.

 

[Geoff]

Markus,

Access 2000 does not hace the DAO library referenced by default. In
your VB Editor window, of any mudule, go to the Tools|References menu,
find 'Microsoft DAO 3.6 Object Library' and tick the box. Should work
ok then.

Still doesn't work for me.

Geoff.

 

[Steve Schapel]

Geoff,

Please copy/paste the exact code you are using into your reply post.

Then, temporarily comment out the SendObject line, and put in a message
box, the equivalent of the following, and run the code, and see what the
messagebox says...
----------------------------------------------------
Dim rst as DAO.Recordset
Dim addy as String
Set rst = CurrentDB.OpenRecordset("SELECT EmailAddress FROM Tablename")
Do While Not rst.EOF
addy = addy & rst!EmailAddress & ";"
rst.MoveNext
Loop
addy = Left(addy,Len(addy)-1)
' DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", ,
addy, "Subject", "MessageText", True
MsgBox addy
rst.Close
Set rst = Nothing

 

[Mortsiefer, Markus]

Hi Steve, Geoff

It still does not work I got past the error message after inserting the
Microsoft DAO Library thank you for that. Now it goes as fare as the
sendobject it opens my Outlook but in the To section where the email address
are suppose to be it only has the word addy, and not the email address from
the table other then that it looks fine

Here is my code again

Private Sub Email_Click()

Dim rst As DAO.Recordset
Dim addy As String
Set rst = CurrentDb.OpenRecordset("SELECT Email from Email")
Do While Not rst.EOF
addy = addy & rst!Email & ";"
rst.MoveNext
Loop
addy = Left(addy, Len(addy) - 1)
DoCmd.SendObject acReport, "Email Report", "SnapshotFormat(*.snp)", "addy",
"", "", Forms![Emial Report]!Subject
rst.Close
Set rst = Nothing

End Sub

Markus,

Access 2000 does not hace the DAO library referenced by default. In
your VB Editor window, of any mudule, go to the Tools|References menu,
find 'Microsoft DAO 3.6 Object Library' and tick the box. Should work
ok then.

--
Steve Schapel, Microsoft Access MVP

Hallo

I hope you could help me to i am trying to do the same as Geoff and i copied
the code from below and when i click on the command butten i recieve a error
message Compile error and it highlights Dim rst as DAO.Recordset Steve could
you maybe give me a idea i am not very good with VB at all so i dont
understand much of it here is how i made the code.

My table is called Email and the Field is called Email to

Private Sub Email_Click()

Dim rst As DAO.Recordset
Dim addy As String
Set rst = CurrentDb.OpenRecordset("SELECT Email from Email")
Do While Not rst.EOF
addy = addy & rst!Email & ";"
rst.MoveNext
Loop
addy = Left(addy, Len(addy) - 1)
DoCmd.SendObject acReport, "Email Report", "SnapshotFormat(*.snp)", "addy",
"", "", Forms![Emial Report]!Subject
rst.Close
Set rst = Nothing

End Sub

 

[Steve Schapel]

Markus,

You need to just put addy without the ""s!

 

[Mortsiefer, Markus]

Thank you Steve

It working now the only think I notice is it does not send the email
automatically I 0have to click on the send button in Outlook is there a way
i can to make it send it with out me clicking on the send Button, And I
receive a error message if I don't send it and I just close the message I
receive in access the error message Run Time error '2501' The sendobject
action not completed
Is there a way I can get around it.

But I have to say thank you very much so fare you really help me a lot.

Markus,

You need to just put addy without the ""s!

--
Steve Schapel, Microsoft Access MVP

Hi Steve, Geoff

It still does not work I got past the error message after inserting the
Microsoft DAO Library thank you for that. Now it goes as fare as the
sendobject it opens my Outlook but in the To section where the email address
are suppose to be it only has the word addy, and not the email address from
the table other then that it looks fine

Here is my code again

Private Sub Email_Click()

Dim rst As DAO.Recordset
Dim addy As String
Set rst = CurrentDb.OpenRecordset("SELECT Email from Email")
Do While Not rst.EOF
addy = addy & rst!Email & ";"
rst.MoveNext
Loop
addy = Left(addy, Len(addy) - 1)
DoCmd.SendObject acReport, "Email Report", "SnapshotFormat(*.snp)", "addy",
"", "", Forms![Emial Report]!Subject
rst.Close
Set rst = Nothing

End Sub

 

[Geoff]

Well I still can't get it to work. I'm using the same code you told me to
use. The code is fine anyway-It's just the sendobject line that's in
question here. I'm sure the problem is elsewhere and not in the actual code
though.

Geoff.

 

[Mortsiefer, Markus]

Hi Steve

I got this to work now all I had to do is add a error in the code and change
the senobject from true to false so all is working perfect Thanks again for
all your help

Thank you Steve

It working now the only think I notice is it does not send the email
automatically I 0have to click on the send button in Outlook is there a way
i can to make it send it with out me clicking on the send Button, And I
receive a error message if I don't send it and I just close the message I
receive in access the error message Run Time error '2501' The sendobject
action not completed
Is there a way I can get around it.

But I have to say thank you very much so fare you really help me a lot.

Markus,

You need to just put addy without the ""s!

--
Steve Schapel, Microsoft Access MVP

Hi Steve, Geoff

It still does not work I got past the error message after inserting the
Microsoft DAO Library thank you for that. Now it goes as fare as the
sendobject it opens my Outlook but in the To section where the email address
are suppose to be it only has the word addy, and not the email address from
the table other then that it looks fine

Here is my code again

Private Sub Email_Click()

Dim rst As DAO.Recordset
Dim addy As String
Set rst = CurrentDb.OpenRecordset("SELECT Email from Email")
Do While Not rst.EOF
addy = addy & rst!Email & ";"
rst.MoveNext
Loop
addy = Left(addy, Len(addy) - 1)
DoCmd.SendObject acReport, "Email Report", "SnapshotFormat(*.snp)", "addy",
"", "", Forms![Emial Report]!Subject
rst.Close
Set rst = Nothing

End Sub

 

[Steve Schapel]

Geoff,

The only other thing I can think of to check is that you've got Outlook
Express set up on your computer as the default email program.

 

[Geoff]

Geoff,

The only other thing I can think of to check is that you've got Outlook
Express set up on your computer as the default email program.

Yep it is. I've tried it with Outlook too, after setting that up as the
default mail program.

Oh well, thanks for your help anyway Steve.

Geoff.

 

[Steve Schapel]

Geoff,

Sorry I haven't been able to help resolve the problem so far. There's
some good information here, if you are interested in scouting around in
case anything hits the mark...
http://www.granite.ab.ca/access/email.htm

 

[Guest]

Hi Geoff,

Unfortunatly I haven't got an answer for you question but
what code do you have for opening an new email message.
We currently have a similar feature which with a button
click opens word but we want to change that to open a new
email. I would really appreciate a sopy of that code if
you have it.

regards,
Leanne