Excel and Outlook

[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.

 

[MarkHenri]

This code will get the email address from the CSV file--<pre>Function GetEmailAddress()
Dim FName
FName = "C:My Documentsfirst.csv"
Open FName For Input Access Read As #1
Dim line
Dim email
' just get the first line then the first column
If Not EOF(1) Then
Line Input #1, WholeLine
line = Split(WholeLine, ",")
email = line(0)
email = Replace(email, """", "")
End If
Close #1
Application.ScreenUpdating = True
GetEmailAddress = email
End Function

</pre>

 

[hmlck17]

Thank you very much indeed !
I' ll try that straight away and
will let you know !

Cheers.

 

[hmlck17]

Ooops...

Sorry Mike,
I am certainly doing something wrong, but...

absolutely nothing happens when I launch the macro...

this is the exact code I am using:

Sub Autpen()
'
' Autpen Macro
' Macro recorded 2/09/2002 by "my name"
'
' Keyboard Shortcut: Ctrl+m
'
End Sub

Function GetEmailAddress()
Dim FName
FName = "Path of my FIRST.CSV"
Open FName For Input Access Read As #1
Dim line
Dim email
' just get the first line then the first column
If Not EOF(1) Then
Line Input #1, WholeLine
line = Split(WholeLine, ",")
email = line(0)
email = Replace(email, """", "")
End If
Close #1
Application.ScreenUpdating = True
GetEmailAddress = email
End Function

 

[MarkHenri]

See where it says "Path to first.csv"? You have to enter your path to first.csv there. It should look something like this-- "c:Documents and Settingshmlck17My documentsmailerfirst.csv"

Also, since it's a function, it'll need to be used like this--

dim emailAddress
emailAddress = GetEmailAddress()

then use it in to call your email macro.

 

[hmlck17]

I know I have to put the path for the csv file (I just did not post it for security reasons, since the path includes my enterprise username);
What I do not understand is the second part of your post: how exactly should I implement
the code you kindly posted?
I am not a VBA expert and my macro knowledge is very basic.
I was doing it like this:
I started recording a macro (calling it Autpen to make it automatic at file opening), then I stopped recording immediately (without recording any action actually);then I edit the "empty" macro with the visual basic editor copy-pasting the code you posted so that I had both the code you wrote and the code I added (the one that specifies the Autpen characteristic).
Unfortunately this does not work.
Am I supposed to edit the code or am I recording the macro in the wrong way?
I'm a bit lost now

 

[Corticus]

Mark has given you a User Defined Function

In order to use,
open the VBA editor,
insert a module
insert the code into the module

then you can use:
GetEmailAddress()
like any standard Excel formula (like if(), sum(), etc)

if you were to make a new sub()
and use the function
variable = GetEmailAddress()
in it, variable will equal your E-mail list

You could also use the formula in the spreadsheet itself...

UDF's Rock!

Any Help?
Corticus