Creating mail message with a button click

  • Thread starter Thread starter Ray Proudfoot
  • Start date Start date
R

Ray Proudfoot

Hi,

I'm trying to create a button on a worksheet that when
clicked will create a new Outlook message with the Send
To,Subject and Message body already completed and will
then automatically send the message. The only way I can
find of doing this is via the Workbook.SendMail command
but although this sends an e-mail it also sends the xls
file which I don't want to be sent.

Are there any other ways of doing this? If I had Visual
Basic v6 installed would it give me the capability via
MAPI commands but still allow end-users with a standard
Excel97 installation to use the facility?

Any help greatly appreciated.
 
Looks as though you trying to use Outlooks Objects and Methods to be able to
send an email. This will mean that you will need to create an application
level variable set to the Outlook Application, which will require the use of
either the CreateObject Method (program not open) or GetObject Function
(program is open). Once you have set the application variable to the
Outlook.Application, you will then use the variable to refer to Outlook's
Objects, Properties, Functions, and Methods.

Before setting up the code to do this, be sure that you have the OutLook
Object Library checkmarked in your list of References, which can be gotten
to under the Tools menu in the VBE. Getting Multiple applications talking
to each other can be a tricky matter as I had some examples given to me, and
even with the changes that I made for the specific files, it still didn't
work out for me. However, after I worked with the code and made some other
adjustments, it finally worked out for me as my code dealt with working
between Excel and Project. Now, I been working quite a bit in Access and at
some point of time, I will be getting into having Access and Excel talking
with each other given that Access has some limitation that Excel does better
while at the same time, Excel has some limitations that Access does better,
thus why I have been working with Access quite a bit here lately.
 
Hi Ronald,

Thanks for your reply. This is a bit heavier than I
anticipated. I'm at home at the moment but when I get
into the office tomorrow I'll print off your reply and
start to work things out.

There's a distinct possibility I'll be asking more
questions :-)

Thanks,
Ray.
 
Hi Ron,

Thanks for the links - some useful ones there that may
help me. I hadn't anticipated this would be so difficult.
After all, aren't MS applications supposed to be able to
talk to each other relatively easily? This isn't what I
anticipated.

Thanks,
Ray.
-----Original Message-----
See
http://www.rondebruin.nl/sendmail.htm#body

Note : Outlook and Outlook Express are different programs

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Ray Proudfoot" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Ronald,

I found a much simpler way of achieving this. Although
it's not activated by a button I've inserted a hyperlink
and by adding "SendTo:" in front of the e-mail address
the user can now open a Outlook new message by clicking
on the hyperlink. Applying protection to the sheet
prevents any changes being made to the address.

The alternative was a sledgehammer to crack a nut
scenario.

Many thanks,
Ray.
 
I have used this process with the Outlook Objects,
specifically "Microsoft Outlook 10.0 Object Library". I
use Office XP. When I give my workbook to a person with
Office 2000, it complains about not having this Object
Library. Should I try to create the specific object I
need from this Library using the CreateObject Method you
mention? If so, I'm at a loss as to how to create such an
object if the Library doesn't exist.

Thanks.
 
A good link for the difference of Early Binding vs Late Binding, which using
the Late binding for JM as it shows should serve the purpose, just as I had
to do to get PROJ98 to work with XL97.
 
Back
Top