Send e-mail To, Subject, Message

[Bruce]

A little while ago I asked in this forum about how to
select a name from a combo box and have a new e-mail
message start with the address for the selected name
filled in. I knew that SendObject was not the best way to
go, do to its various limitations. Somebody suggested the
following code (which is attached to a command button's
Click event).

Dim strEmail As String

strEmail = "mailto:" & Me![cboEmailDirectory]
Application.FollowHyperlink Address:=strEmail

[cboEmailDirectory] is the combo box from which a name is
selected. Its row source is a table.

This works really well. I have placed it near the middle
of the form. Now I would like to have the Subject filled
in with the identifying number that was generated
automatically when the form was opened, which appears in a
text box (txtRecordID) at the top of the form. The number
is stored in [RecordID] in the table tblMain. Also, I
would like to have the message contain some standard
text. The text could come from a table (tblMessage). The
user would have the option of adding to the e-mail before
sending it - no automatic sending. How can I accomplish
this?

 

[Jim/Chris]

This is the complete Email code. I obtained this from one
of the MVP's(Forgot wich one).
I have seen quite a bit of code for sending email. I have
tried many of them. If you are just sending an Access
report you can use the SendObject command. If attachments
are files of any type try the code below. The one below( I
have it stored as a function in a new module) has been the
most reliable for me with MS Outlook and Access 2000 and
can send up to 5 attachments or none. Also I am able to
pull from an open form email address of the recipient, CC,
BCC, and the subject and text and file name of the
attachment(s).
To reference data on an open form use
=[forms]![formname].[emailaddress]
=[forms]![formname].[subject]
=[forms]![formname].[attachment1]
ect.


Good Luck

Jim

Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String, _
strAttachment1 As String,strAttachment2 As String, _
strAttachment3 As String, strAttachment4 As String

strTo = "email address"
strSubject = "put subject here"
varBody = "put message for body here"
' Add more strattachments if needed and modify IF statement
' below
strAttachment = "attachment1"
strAttachment1 = "attachment2"
strAttachment2 = "attachment3"
strAttachment3 = "attachment4"
strAttachment4 = "attachment5"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
If Len(strAttachment) <> 0 Then
olMail.Attachments.Add (strAttachment)
If Len(strAttachment1) <> 0 Then
olMail.Attachments.Add (strAttachment1)
If Len(strAttachment2) <> 0 Then
olMail.Attachments.Add (strAttachment2)
If Len(strAttachment3) <> 0 Then
olMail.Attachments.Add (strAttachment3)
If Len(strAttachment4) <> 0 Then
olMail.Attachments.Add (strAttachment4)
End If
End if
End if
End if
End If
olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function

Jim

-----Original Message-----
A little while ago I asked in this forum about how to
select a name from a combo box and have a new e-mail
message start with the address for the selected name
filled in. I knew that SendObject was not the best way to
go, do to its various limitations. Somebody suggested the
following code (which is attached to a command button's
Click event).

Dim strEmail As String

strEmail = "mailto:" & Me![cboEmailDirectory]
Application.FollowHyperlink Address:=strEmail

[cboEmailDirectory] is the combo box from which a name is
selected. Its row source is a table.

This works really well. I have placed it near the middle
of the form. Now I would like to have the Subject filled
in with the identifying number that was generated
automatically when the form was opened, which appears in a
text box (txtRecordID) at the top of the form. The number
is stored in [RecordID] in the table tblMain. Also, I
would like to have the message contain some standard
text. The text could come from a table (tblMessage). The
user would have the option of adding to the e-mail before
sending it - no automatic sending. How can I accomplish
this?
.

 

[Matthew]

Actually, I think you can do everythoing you want with SendObject. See below
and the Access help for more detail.

Private Sub btnSendEmail_Click()

DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , , "Subject
here", "Message Body here."

End Sub

 

[Bruce]

I need to pick a name from a list, and have that person's
e-mail address show up in a new message. The method I
described does that. I cannot get SendObject to have the
same functionality. All I want to do is add the subject
from a text box elsewhere on the form, and a brief
message. If I can make SendObject do that, I will.
Access Help does not consider the possibility that anyone
would want to send a simple message to a name on a list.
Please pardon my frustration. I have been working on this
for a week, and seem to be the first person who has ever
attempted what I am trying to do.

-----Original Message-----
Actually, I think you can do everythoing you want with SendObject. See below
and the Access help for more detail.

Private Sub btnSendEmail_Click()

DoCmd.SendObject

acSendNoObject, , , "(e-mail address removed)", , , "Subject

here", "Message Body here."

End Sub

A little while ago I asked in this forum about how to
select a name from a combo box and have a new e-mail
message start with the address for the selected name
filled in. I knew that SendObject was not the best way to
go, do to its various limitations. Somebody suggested the
following code (which is attached to a command button's
Click event).

Dim strEmail As String

strEmail = "mailto:" & Me![cboEmailDirectory]
Application.FollowHyperlink Address:=strEmail

[cboEmailDirectory] is the combo box from which a name is
selected. Its row source is a table.

This works really well. I have placed it near the middle
of the form. Now I would like to have the Subject filled
in with the identifying number that was generated
automatically when the form was opened, which appears in a
text box (txtRecordID) at the top of the form. The number
is stored in [RecordID] in the table tblMain. Also, I
would like to have the message contain some standard
text. The text could come from a table (tblMessage). The
user would have the option of adding to the e-mail before
sending it - no automatic sending. How can I accomplish
this?

.

 

[Bruce]

Thanks, but I could not make it work. In the code I
provided with my original posting, I selected a name from
a combo box, clicked a command button, and the e-mail
message opened with the associated e-mail address in To:
box. I thought that if I added

Dim strSubject As String
Dim VarBody As Variant

strSubject = "Test Subject"
varBody = "Test Message"

so that the entire code would be

Dim strEmail As String
Dim strSubject As String
Dim VarBody As Variant

strEmail = "mailto:" & Me![cboEmailDirectory]
strSubject = "Test Subject"
varBody = "Test Message"
Application.FollowHyperlink Address:=strEmail

then I would get an e-mail message with the name selected
from the combo box cboEmailDirectory, Test Subject in the
subject line, and Test Message in the body of the e-mail.

However, I still get just an e-mail message with the e-
mail address I selected from the combo box
[cboEmailDirectory], but nothing else. Clearly I am
missing something that I am supposed to understand. The
command button is in a form (frmRequest) that, of course,
is open when I click the command button. A field on the
same form contains the field [IDnumber], which is also a
field in the underlying table (tblMain). I would like the
subject of the e-mail to be the contents of that field,
whatever is the best way to do that (from the form or from
the table?), and the Message to come from the [Message]
field in a table [tblMessage] if that is possible, or
otherwise I could just type the message into the code.

-----Original Message-----
This is the complete Email code. I obtained this from one
of the MVP's(Forgot wich one).
I have seen quite a bit of code for sending email. I have
tried many of them. If you are just sending an Access
report you can use the SendObject command. If attachments
are files of any type try the code below. The one below( I
have it stored as a function in a new module) has been the
most reliable for me with MS Outlook and Access 2000 and
can send up to 5 attachments or none. Also I am able to
pull from an open form email address of the recipient, CC,
BCC, and the subject and text and file name of the
attachment(s).
To reference data on an open form use
=[forms]![formname].[emailaddress]
=[forms]![formname].[subject]
=[forms]![formname].[attachment1]
ect.


Good Luck

Jim

Function SendEMail()
Dim strTo As String, strSubject As String, _
varBody As Variant, strCC As String, _
strBCC As String, strAttachment As String, _
strAttachment1 As String,strAttachment2 As String, _
strAttachment3 As String, strAttachment4 As String

strTo = "email address"
strSubject = "put subject here"
varBody = "put message for body here"
' Add more strattachments if needed and modify IF statement
' below
strAttachment = "attachment1"
strAttachment1 = "attachment2"
strAttachment2 = "attachment3"
strAttachment3 = "attachment4"
strAttachment4 = "attachment5"
'Start Outlook
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

'Logon
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'Send a message
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
'Fill Out and Send Message
olMail.To = strTo
olMail.CC = strCC
olMail.BCC = strBCC
olMail.Subject = strSubject
olMail.Body = varBody
' Modify these statements if more attachmewnts are needed
If Len(strAttachment) <> 0 Then
olMail.Attachments.Add (strAttachment)
If Len(strAttachment1) <> 0 Then
olMail.Attachments.Add (strAttachment1)
If Len(strAttachment2) <> 0 Then
olMail.Attachments.Add (strAttachment2)
If Len(strAttachment3) <> 0 Then
olMail.Attachments.Add (strAttachment3)
If Len(strAttachment4) <> 0 Then
olMail.Attachments.Add (strAttachment4)
End If
End if
End if
End if
End If
olMail.Send

Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Function

Jim

-----Original Message-----
A little while ago I asked in this forum about how to
select a name from a combo box and have a new e-mail
message start with the address for the selected name
filled in. I knew that SendObject was not the best way to
go, do to its various limitations. Somebody suggested the
following code (which is attached to a command button's
Click event).

Dim strEmail As String

strEmail = "mailto:" & Me![cboEmailDirectory]
Application.FollowHyperlink Address:=strEmail

[cboEmailDirectory] is the combo box from which a name is
selected. Its row source is a table.

This works really well. I have placed it near the middle
of the form. Now I would like to have the Subject filled
in with the identifying number that was generated
automatically when the form was opened, which appears in a
text box (txtRecordID) at the top of the form. The number
is stored in [RecordID] in the table tblMain. Also, I
would like to have the message contain some standard
text. The text could come from a table (tblMessage). The
user would have the option of adding to the e-mail before
sending it - no automatic sending. How can I accomplish
this?
.

.

 

[Dan Artuso]

Hi,
I'm not sure I understand how your form is setup but let's try this:
Private Sub btnSendEmail_Click()

DoCmd.SendObject acSendNoObject, , , Me.cboEmailDirectory, , , Me.IDnumber, "Message Body here."
'the above should be *all on one line*
End Sub

Try that for starters and if I've got it right, we'll work on getting your message in there too.

--
HTH
Dan Artuso, Access MVP

I need to pick a name from a list, and have that person's
e-mail address show up in a new message. The method I
described does that. I cannot get SendObject to have the
same functionality. All I want to do is add the subject
from a text box elsewhere on the form, and a brief
message. If I can make SendObject do that, I will.
Access Help does not consider the possibility that anyone
would want to send a simple message to a name on a list.
Please pardon my frustration. I have been working on this
for a week, and seem to be the first person who has ever
attempted what I am trying to do.

-----Original Message-----
Actually, I think you can do everythoing you want with SendObject. See below
and the Access help for more detail.

Private Sub btnSendEmail_Click()

DoCmd.SendObject

acSendNoObject, , , "(e-mail address removed)", , , "Subject

here", "Message Body here."

End Sub

A little while ago I asked in this forum about how to
select a name from a combo box and have a new e-mail
message start with the address for the selected name
filled in. I knew that SendObject was not the best way to
go, do to its various limitations. Somebody suggested the
following code (which is attached to a command button's
Click event).

Dim strEmail As String

strEmail = "mailto:" & Me![cboEmailDirectory]
Application.FollowHyperlink Address:=strEmail

[cboEmailDirectory] is the combo box from which a name is
selected. Its row source is a table.

This works really well. I have placed it near the middle
of the form. Now I would like to have the Subject filled
in with the identifying number that was generated
automatically when the form was opened, which appears in a
text box (txtRecordID) at the top of the form. The number
is stored in [RecordID] in the table tblMain. Also, I
would like to have the message contain some standard
text. The text could come from a table (tblMessage). The
user would have the option of adding to the e-mail before
sending it - no automatic sending. How can I accomplish
this?

.

 

[Matthew]

That's what the code snippet actually does do. I'm not quite sure why
SendObject is not working for you. What mail client are you using -
Outlook/Express, Eudora, Netscape,etc?
Have you tried cutting and pasting the code below into your Access app? If
not create a test form called "Email"; create a command button on it called
btnSendEmail and paste this:

DoCmd.SendObject acSendNoObject, , , Me![cboEmailDirectory], , , "Subject",
"Message Body"

into it's click event. It does work.
Do you get any errors? Does your mail app open? More details of exactly
what's going wrong

I need to pick a name from a list, and have that person's
e-mail address show up in a new message.

Are you sure that the combo-box is bound to an email address and not a
different field like somebody's name? I've taken my queue from your code.

 

[Bruce]

Perhaps I had brain damage on Friday. It works today.
Now there is something else. Some background: I have a
table (tblList) that is the row source for a combo box
(cboSelectName) on the form. The table has two fields of
interest: [Name] and . [Name] shows up on the
combo box drop-down list, but {Email] is the bound
column. The command button (cmdSendEmail) has the
following code:
DoCmd.SendObject acSendNoObject, , , Me!
[cboSelectName], , , Me![IDnumber], "Message Body".
([IDnumber] is a field at the top of the form). Here's
how it works: Let's say this is a form for registering a
complaint. The complaint number goes into [IDnumber], a
description of the complaint is added, and the form comes
to the attention of the Quality department. Quality
assigns somebody (I hope by selecting the name from
[cboSelectName] to act on the complaint. AFter filling in
the date due, Quality clicks cmdSendEmail to generate an e-
mail containing in the To: line the name of the person
assigned to the complaint, in the Subject: line the
IDnumber, and a message that will eventually include the
date due and other details.
Here is the difficulty: If I have a text box or combo box
for wntering the name of the person assigned to the
complaint and another one for selecting a name for sending
e-mail, then the command button works fine. However, I
would like to select from the combo box the name of the
person assigned to the complaint, have that name stored in
the table [tblMain], and have an e-mail addressed to that
person's e-mail address when I click the command button
[cmdSendEmail]. Again, both values are in the table
[tblList] that is the row source for the combo box.[QUOTE]
-----Original Message-----
That's what the code snippet actually does do. I'm not quite sure why
SendObject is not working for you. What mail client are you using -
Outlook/Express, Eudora, Netscape,etc?
Have you tried cutting and pasting the code below into your Access app? If
not create a test form called "Email"; create a command button on it called
btnSendEmail and paste this:

DoCmd.SendObject acSendNoObject, , , Me![/QUOTE]
[cboEmailDirectory], , , "Subject",

 

[Bruce]

Thanks for you help. I have to use the web-based
newsreader, so I do not know how these threads will show
up on a dedicated reader such as OE. I directed some
additional questions to another response that showed up at
the same level as yours did (both replies showed up as
responses to the same question). I answered some of your
follow-up questions there. If you get a chance to look at
that, I would appreciate it. Thanks again.

-----Original Message-----
Hi,
I'm not sure I understand how your form is setup but let's try this:
Private Sub btnSendEmail_Click()

DoCmd.SendObject acSendNoObject, , ,

Me.cboEmailDirectory, , , Me.IDnumber, "Message Body here."

'the above should be *all on one line*
End Sub

Try that for starters and if I've got it right, we'll

work on getting your message in there too.

--
HTH
Dan Artuso, Access MVP


"Bruce" <[email protected]> wrote in

message news:[email protected]...

I need to pick a name from a list, and have that person's
e-mail address show up in a new message. The method I
described does that. I cannot get SendObject to have the
same functionality. All I want to do is add the subject
from a text box elsewhere on the form, and a brief
message. If I can make SendObject do that, I will.
Access Help does not consider the possibility that anyone
would want to send a simple message to a name on a list.
Please pardon my frustration. I have been working on this
for a week, and seem to be the first person who has ever
attempted what I am trying to do.

-----Original Message-----
Actually, I think you can do everythoing you want with SendObject. See below
and the Access help for more detail.

Private Sub btnSendEmail_Click()

DoCmd.SendObject

acSendNoObject, , , "(e-mail address removed)", , , "Subject

here", "Message Body here."

End Sub

A little while ago I asked in this forum about how to
select a name from a combo box and have a new e-mail
message start with the address for the selected name
filled in. I knew that SendObject was not the best

way
to

go, do to its various limitations. Somebody

suggested
the

following code (which is attached to a command button's
Click event).

Dim strEmail As String

strEmail = "mailto:" & Me![cboEmailDirectory]
Application.FollowHyperlink Address:=strEmail

[cboEmailDirectory] is the combo box from which a

name
is

selected. Its row source is a table.

This works really well. I have placed it near the middle
of the form. Now I would like to have the Subject filled
in with the identifying number that was generated
automatically when the form was opened, which appears in a
text box (txtRecordID) at the top of the form. The number
is stored in [RecordID] in the table tblMain. Also, I
would like to have the message contain some standard
text. The text could come from a table (tblMessage). The
user would have the option of adding to the e-mail before
sending it - no automatic sending. How can I accomplish
this?


.

.