EXcel and Outlook integration

[hmlck17]

Please, try to imagine the following situation:

I have 2 files;

- the first one is FIRST.CSV, an Excel comma separated values file that is
constantly updated
by a particular appplication we have, in such a way that cell A1 contains
a different e-mail address
every time we issue a particolar command from this special application we
have.

- the second file is SECOND.XLS, and I would like this file to be able to
retrieve this e-mail
address (which is always different) from the first file;

This is what I would like to do:
I would like to create a macro in SECOND.XLS able to read the value (e-mail
address) of cell A1
in FIRST.CSV and open a blank Outlook message addressed to that recipient.
I would like to call it Autpen, so that every time that I click on
SECOND.XLS to open it
the macro would be launched and an Outlook message would pop up with a
recipient (different depending
on the value in A1 in FIRST.CSV) already filled in.

My question is: how do I realize such a macro? A macro that reads the
variable in cell A1 of another file (FIRST.CSV)
and opens a mail message for that recipient?

We use Excel 2000 and Outlook 2000.

This would be very useful for my work and I already thank in advance anyone
so
kind to answer.

 

[larai]

Here is a program I put together; you'll need to change the filenames.

False.xls = your Second.xls
Book1.xls = your file you want to retrieve data from.

I didn't try using a CSV;

you'll need to play with this.
Sub TestGetData()
Dim strEmailAddy As String
Workbooks.Open Filename:="Book1.xls"
Workbooks("False.xls").Activate
strEmailAddy = Workbooks("Book1.xls").Sheets(1).Range("A1")
ActiveWorkbook.SendMail Recipients:=strEmailAddy
Workbooks("Book1.xls").Close
End Sub

 

[Al Chara]

You can try the following (I don't have Excel on the computer I am at now, so the code is untested):
<pre>Sub Autpen()

Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim Rng As Range

Set Rng = Workbooks("FIRST.CVS").Range("A1")
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = Rng.Value
olMail.Subject = "Insert Subject"
olMail.Body = "Insert Body"
olMail.Display

End Sub</pre>

 

[hmlck17]

Thank you so much to the both of you.
I' ll try that straight away!

Cheers.