send data between other office programs?

Is it possible to send the contents of a cell to outlook via VBA trigger?

Originally Posted by royUK

Yes, but as what?

just as text, if you have any how-to's I could certainly figure it out not to trouble you.

I have a row of cells which contains a persons name, and I want to be able to send that name to outlook as the addressee, and the title of the e-mail pulled from one of the cells, and the body pulled from comments. All of this real time from a button or keystroke to execute the macro/code against the current row and pop up a outlook mail window and populate the various fields. I know how to reference the comments, I just need to know how to send the data across to outlook.

is there any how-to's I might find this information on?

Check out this site

http://www.erlandsendata.no/english/index.php

and look under the OLE automation

I've used their code to create appointments in outlook via macros. For the most part, you can copy and paste with minimal tweaking, but you're going to have to figure out some VB to be able to use it.

good luck!

Originally Posted by ilovedurango

Check out this site

http://www.erlandsendata.no/english/index.php

and look under the OLE automation

I've used their code to create appointments in outlook via macros. For the most part, you can copy and paste with minimal tweaking, but you're going to have to figure out some VB to be able to use it.

good luck!

Well with everyone's help I am getting better. This particular code will not run though. I think it may be due to security priveledges or possibly my excel version. Any idea what would cause the error message "User defined type not defined".

Well, I read into it a bit, and it appears I don't know enough about VBA to do this unfortunately. The code supplied doesn't seem to work for me. I did create a late binding code set and ran it, however it states that it isn't supported... so not sure what the hell is going on.. The code itself looks pretty simplistic, however it just doesn't work for me and I can't figure it out.

Please Login or Register  to view this content.

Is there some trick I've missed in using OLE automation?

Hello Pyrex238,

I suspect you need to set a reference in VBA to the Outllook library. If the reference is missing then the VBE won't be able to match the Outlook VBA code to any existing references. The VBE then assumes these objects are user defined types.

To Set/Check a library reference:
1.) With Excel open, press ALT+F11 to open the VBE
2.) Press ALT+T to activate the Tools Menu
3.) Press r to display the References Dialog
4.) Scroll through the list and look for Microsoft Outlook x.x Object library (x.x is the version number)
5.) Click the check box to set it, and click OK to set the reference

You may have a problem using this code on later version of Office since this code was written for Outlook 8.0.(pre 2000) If you still have a problem after the reference has been set, then the code will have to be cleaned to work with the version of Office you have.

Sincerely,
Leith Ross

Originally Posted by Leith Ross

Hello Pyrex238,

I suspect you need to set a reference in VBA to the Outllook library. If the reference is missing then the VBE won't be able to match the Outlook VBA code to any existing references. The VBE then assumes these objects are user defined types.

To Set/Check a library reference:
1.) With Excel open, press ALT+F11 to open the VBE
2.) Press ALT+T to activate the Tools Menu
3.) Press r to display the References Dialog
4.) Scroll through the list and look for Microsoft Outlook x.x Object library (x.x is the version number)
5.) Click the check box to set it, and click OK to set the reference

You may have a problem using this code on later version of Office since this code was written for Outlook 8.0.(pre 2000) If you still have a problem after the reference has been set, then the code will have to be cleaned to work with the version of Office you have.

Sincerely,
Leith Ross

Thanks Leith, I had actually already set up the library files before making the post, it wasn't any help unfortunately. I appreciate your time though.

Originally Posted by ilovedurango

Edit:

I am sorry, I did not read further up on the thread.

What version of Excel are you using? If you send me a dummy file, I can make the code work for your application.

I would certainly be ecstatic if you could. The code above in the thread is what I'm needing to work. I can take care of the data, I just need to get outlook to fire up and take the data from various cells. I'm using Excel 2003 SP1 here in the office.

If you do in fact want the spreadsheet I'm applying it to, I could certainly supply it.

Anyone care to share some insight?

Hi,

Watching this with interest. Any success yet?

It might be trickier than first thought. I think I remember reading something that said that Outlook had been tightened up against access from other programs. What you're trying to write could be percieved as virus attack, especially with all the efforts going in to avoid spam etc.
I haven't got Outlook on my home computer but will see what I can dig out of my book.

From the first bit of code above I easily managed to get Word up and running so opening whichever Application you choose to substitute the "xxx" should work fine.

Is this infringing copyright?? I dunno... anyway its extracted from "Excel 2003 , VBA Programer's Reference" by Paul T Kimmel et al, published by WROX.
This book taught me all I know (apart from the stuff I've picked up from this mesage board). I reccommend it to anyone.

Please Login or Register  to view this content.

Give it a go and change the bits you need to reference your data. This is a latebinding model, when they werre trying anything early-binding Norton Anti-Virus cleared it from the cmoputer whenever it was saved.

Also here's the bit about viruses

SendEMail has input parameters for the e-mail address of the recipient and the filename of the
attachment for the e-mail. If your Outlook configuration requires you to logon, you will need to
uncomment the lines that get a reference to the Namespace and supply the username and password.
A new mail item is created using the CreateItem method. Text is added for the subject line and the
body of the e-mail and the recipient and attachment are specified. The Send method sends the
e-mail.
You will need to respond to three dialog boxes when executing this code in Office XP. The first two warn
you that Outlook is being accessed and the second forces a five second delay and warns you that a
program is sending an e-mail. The techniques you are using, while being very useful to achieve legitimate
ends, are also, obviously, employed by virus writers.
While Office 2003 has strong protection against e-mail viruses, earlier versions of Office are more
vulnerable. Patches are available from Microsoft to add protection to earlier versions of Outlook, but they
might make it impossible to send e-mail programmatically. It can be very difficult to allow the legitimate
use of programmatically generated e-mail and prevent viruses doing the same thing. The best answer is
to have the latest virus protection software installed and keep it up-to-date.

Well, hope this helps you.

Hi again,

Stumbled across this when lookign for something else and thought of you. Check it out.
[link]
http://www.rondebruin.nl/sendmail.htm
[/link]

Tris

Originally Posted by Ivan F Moala

The code worked for me using To.
using Xl2003 + Outlook2003
You don't mention what does not work.
Note: security warning does pop up

it's nice to know the code I edited does actually work... too bad not for me!


It's possible the administrators have disabled OLE on my system for security... there's really no way to tell since I have the library enabled and functioning in office.