EXcel and Outlook integration

hmlck17

New Member
Joined
Aug 22, 2002
Messages
6
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 Auto_Open, 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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

larai

New Member
Joined
Aug 28, 2002
Messages
20
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

MrExcel MVP
Joined
Feb 21, 2002
Messages
1,701
You can try the following (I don't have Excel on the computer I am at now, so the code is untested):
<pre>Sub Auto_Open()

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

New Member
Joined
Aug 22, 2002
Messages
6
Thank you so much to the both of you.
I' ll try that straight away!

Cheers.
 

Forum statistics

Threads
1,147,690
Messages
5,742,638
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top