Access 97 - Sending an Email with Content using a Command Button

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe Coulter - 22 Feb 2007 10:10 GMT

I have a database that is used to log details about received materials, when
a new receipt is entered, I want some of the record detail sent to a choosen
person via the users email when a button is clicked

I currently have a command button "eMail_Btn" with the following in the "On
Click" Event - Application.FollowHyperlink "mailto:" & Me.EMailAddress.Value
EMailAddress being the field on the input form to choose the email address
to send.

This works fine, but only Opens the email software and populates the To
section with the selected email address, but what I also need to do is also
populate the CC, Subject and include two or three pieces of information
within the body of the email with data also taken from fields within the
input form and automatically send the email.

I have been doing this with another system that emails to Lotus Notes from
Lotus Notes, but I now need to be able to change this to use any email
software.

I appreciate any help offered.

Joe

Reply to this Message

Rob Parker - 22 Feb 2007 12:48 GMT

Check out the SendObject method in the help file. It allows you to set
several parameters (including cc: and subject: fields), and send any of
several objects (query, report, table, ...) as an attachment, in various
formats.

HTH,

Rob

>I have a database that is used to log details about received materials,
>when
[quoted text clipped - 23 lines]
>
> Joe

Reply to this Message

Joe Coulter - 22 Feb 2007 14:27 GMT

Rob

Thanks for you reply, I tried the following to send the table "TBL_MAIN" in
XL format to Nancy Davolio and cc to Joan Weber with the subject being "New
Data Table"

DoCmd.SendObject acSendTable, "TBL_MAIN", acFormatXLS, "Nancy Davolio",
"Joan Weber", , "New Data Table"

This has returned the run time error 2287, "Microsoft Access cannot open the
mail session"

Any suggestions would be appreciated.

Thanks

Joe

> Check out the SendObject method in the help file. It allows you to set
> several parameters (including cc: and subject: fields), and send any of
[quoted text clipped - 32 lines]
> >
> > Joe

Reply to this Message

Rob Parker - 23 Feb 2007 01:45 GMT

Hi Joe,

The line you posted works fine for me (when I change TBL_MAIN to the name of
an actual table). I get an email message with an Excel spreadsheet
attachment (tblName.xls), and the To:, cc:, and Subject: fields as expected,
ready to be sent.

I've not come across the error message you've found, but from the
description I think Access is unable to find your email program. Are you
using Outlook or Outlook Express (I know there is no problem with either of
those), or something else? If it's something else, was the email program
open at the time?

Other than that, I'm at a loss for suggestions. If it's a problem with
another email program, I suggest you post a new question with details of
that.

HTH,

Rob

> Rob
>
[quoted text clipped - 56 lines]
>> >
>> > Joe

Reply to this Message

Joe Coulter - 26 Feb 2007 09:32 GMT

Hi Rob

Again, thanks for your reply, I am using Lotus Notes which was open at the
time, therefore Access 97 must have a problem recognising this email
software,if the code I tried works with outlook, it will probably work with
most others, I will try some more tests, such as with Hotmail etc, If I can
get it to work with most popular email software, that will have to do.

Thanks for your help with this, It was greatly appreciated.

Joe

> Hi Joe,
>
[quoted text clipped - 77 lines]
> >> >
> >> > Joe

Reply to this Message

Rob Parker - 26 Feb 2007 10:48 GMT

Hi Joe,

I strongly suspect that DoCmd.SendObject will not work with Hotmail (or
gmail, or Yahoo mail), since that is not an email program per se; it (they)
are web-based mail services. It might well work with other email clients
such as Thunderbird, Eudora, Pegasus, etc.

I've never used Lotus Notes, so I can't comment on whether that might be the
source of your problem.

Rob

> Hi Rob
>
[quoted text clipped - 106 lines]
>> >> >
>> >> > Joe

Reply to this Message

DubboPete - 26 Feb 2007 10:58 GMT

Hi Rob,

I agree with you here, but I have made this work with Novell Groupwise,
Lotus Notes, et al... and even Outlook!
All proprietary email programs...

Pete

> Hi Joe,
>
[quoted text clipped - 124 lines]
>>> >> >
>>> >> > Joe

Reply to this Message

DubboPete - 26 Feb 2007 10:07 GMT

Joe,

I had the same problem, and I use Novell Groupwise...

But, I think I have it cracked, and this may help you....

Try this code, and adapt the obvious insertions to fit your needs - just try
it to yourself (your email) to see if it works!

Private Sub CommandWhatever_Click()

DoCmd.SendObject actable, "TblName", "MicrosoftExcel(*.xls)",
"recipient@emailaddress.com", "cc@whatever.com", "bcc@thebcc.com",
"subject", "body goes here and you can add " & strfield_ofYour_choice & "to
say thankyou", False, ""

and further to that, you don't have to send a table, you can just send a
message by using this....

Private Sub CommandWhatever_Click()

DoCmd.SendObject, , , "recipient@emailaddress.com",
"cc@whatever.com","bcc@thebcc.com", "subject", "body goes here and you can
add " & strfield_ofYour_choice & "to say thankyou", False, ""

End Sub

... but beware of line breaks in this reply to your enquiry - it happens...

If you want the code in one line, email me

osnabruc@removeme@tpg.com.au

Pete

End Sub

> Rob
>
[quoted text clipped - 56 lines]
>> >
>> > Joe

Reply to this Message

Joe Coulter - 26 Feb 2007 10:20 GMT

Thanks for your reply DubboPete

I will try your solution right away and let you know the results as soon as
I run it.

Thanks in anticipation

Joe

> Joe,
>
[quoted text clipped - 92 lines]
> >> >
> >> > Joe

Reply to this Message

DubboPete - 26 Feb 2007 10:13 GMT

change the first part of the line to

DoCmd.SendObject acTable, ...................

(take out "send" from acSendTable to make acTable)

> Rob
>
[quoted text clipped - 56 lines]
>> >
>> > Joe

Reply to this Message

Joe Coulter - 26 Feb 2007 11:30 GMT

Hi DubboPete

I have tried both options which you suggested, I am beginning to think that
Lotus Notes is not the email to have, the two options failed, below is what I
have coded and the messages returned, maybe I have made an error (not for the
first time)

As you will see, I have renamed the Private Sub to reflect My Command
Button, also the Table for a real table in my daytabase, and inserted a
string called Ref

Private Sub eMail_Btn_Click()
Dim strRef As String
strRef = Me!Rec_Ref

DoCmd.SendObject acTable, "Tbl_Procedure", "MicrosoftExcel(*.xls)",
"recipient@emailaddress.com", "cc@whatever.com", "Test Subject", "Test Body
Text " & strRef & "Thanks", False, ""

This Returned the message "An expression you enterer is the wrong data type
for one of the arguments"

DoCmd.SendObject , , , "recipient@emailaddress.com", "cc@whatever.com",
"bcc@thebcc.com", "Test Subject", "Test Body " & strRef & "Test Thanks",
False, ""

This Returned the message "Microsoft Access cant open the Mail session"

End Sub

> change the first part of the line to
>
[quoted text clipped - 62 lines]
> >> >
> >> > Joe

Reply to this Message