WinXP Pro SP2, Excel 2000, OE6

I have a client shipping schedule which includes all of our clients'
shipping dates for a three month period. I have a program which gathers a
list of the clients in the most recent schedule, then filters on each
client, copy's the filtered schedule into a worksheet named after the
client. For example...

Oct 1-7th
Order Number - Client - Ship date...
1001 - Jones - 10/01/05
1004 - Smith - 10/03/05...

Becomes:
Oct 1-7th
Order Number - Client - Ship date...
1001 - Jones - 10/01/05
(on a worksheet titled "Jones")

and

Oct 1-7th
Order Number - Client - Ship date...
1004 - Smith - 10/03/05
(on a worksheet titled "Smith")

Now, on a separate sheet I have a constant list of client's e-mail
addresses. I basically want to browse this list and if I find a client with
an e-mail address who has a report, I want to e-mail said worksheet.

The built-in toolbar for E-Mail in Excel works exactly perfectly for my
needs. However trying to capture a macro to use the toolbar has been most
unsuccessful. Is there a way to exactly emulate the pressing of the
envelope toolbar button, fill in the "To:" and "Subject:" lines and send it
off?

I did find http://www.rondebruin.nl/sendmail.htm in my searching but it
seems like a long way to go when all I seem to need to do is push that
E-mail button, and send the sheet off.

Can anyone tell me if there's a simple way to do what I'm trying to do or if
I need to work with the sendmail thing?

Thank you for your time.
CB Hamlyn

Hi

Do you want to send the sheet in the Body ?

You can't program the envelope in Excel 2000
And you must use my Outlook body Examples.

Do you have the client Mail address also on the worksheet or only in
the constant list of client's e-mails

--
Regards Ron de Bruin
http://www.rondebruin.nl


"CB Hamlyn" wrote in message ...
WinXP Pro SP2, Excel 2000, OE6

I have a client shipping schedule which includes all of our clients' shipping dates for a three month period. I have a program
which gathers a list of the clients in the most recent schedule, then filters on each client, copy's the filtered schedule into a
worksheet named after the client. For example...

Oct 1-7th
Order Number - Client - Ship date...
1001 - Jones - 10/01/05
1004 - Smith - 10/03/05...

Becomes:
Oct 1-7th
Order Number - Client - Ship date...
1001 - Jones - 10/01/05
(on a worksheet titled "Jones")

and

Oct 1-7th
Order Number - Client - Ship date...
1004 - Smith - 10/03/05
(on a worksheet titled "Smith")

Now, on a separate sheet I have a constant list of client's e-mail addresses. I basically want to browse this list and if I find
a client with an e-mail address who has a report, I want to e-mail said worksheet.

The built-in toolbar for E-Mail in Excel works exactly perfectly for my needs. However trying to capture a macro to use the
toolbar has been most unsuccessful. Is there a way to exactly emulate the pressing of the envelope toolbar button, fill in the
"To:" and "Subject:" lines and send it off?

I did find http://www.rondebruin.nl/sendmail.htm in my searching but it seems like a long way to go when all I seem to need to do
is push that E-mail button, and send the sheet off.

Can anyone tell me if there's a simple way to do what I'm trying to do or if I need to work with the sendmail thing?

Thank you for your time.
CB Hamlyn

It doesn't matter much if the sheet is attached or if it composes the body.
If I could choose either probably make it the body as the recipients are not
all that computer literate.

There's basically a historical record worksheet that lists the possible
clients and whether they prefer printed/faxed copies or e-mail (and
addresses where appropriate). This is the only place where the client's
e-mail addresses are stored.

There is of course a front end user utility that allows the user of the
macro to view/edit the historical list.

What I plan to do is go through this historical list in a macro and for
every name I find in the historical list that has it's own worksheet
(schedule), I will either print it or e-mail it automatically based on that
clients preference.

I'll look further into your outlook examples in the mean time. I should be
able to figure out a way to do this, but I appreciate any information you
can give me that may be more appropriate to what I'm doing (if it's not
already laid out on your website that is).

Thank you very much for your time :)
CB Hamlyn


"Ron de Bruin" wrote in message
...
Hi

Do you want to send the sheet in the Body ?

You can't program the envelope in Excel 2000
And you must use my Outlook body Examples.

Do you have the client Mail address also on the worksheet or only in
the constant list of client's e-mails

--
Regards Ron de Bruin
http://www.rondebruin.nl


"CB Hamlyn" wrote in message
...
WinXP Pro SP2, Excel 2000, OE6

I have a client shipping schedule which includes all of our clients'
shipping dates for a three month period. I have a program which gathers
a list of the clients in the most recent schedule, then filters on each
client, copy's the filtered schedule into a worksheet named after the
client. For example...

Oct 1-7th
Order Number - Client - Ship date...
1001 - Jones - 10/01/05
1004 - Smith - 10/03/05...

Becomes:
Oct 1-7th
Order Number - Client - Ship date...
1001 - Jones - 10/01/05
(on a worksheet titled "Jones")

and

Oct 1-7th
Order Number - Client - Ship date...
1004 - Smith - 10/03/05
(on a worksheet titled "Smith")

Now, on a separate sheet I have a constant list of client's e-mail
addresses. I basically want to browse this list and if I find a client
with an e-mail address who has a report, I want to e-mail said worksheet.

The built-in toolbar for E-Mail in Excel works exactly perfectly for my
needs. However trying to capture a macro to use the toolbar has been
most unsuccessful. Is there a way to exactly emulate the pressing of the
envelope toolbar button, fill in the "To:" and "Subject:" lines and send
it off?

I did find http://www.rondebruin.nl/sendmail.htm in my searching but it
seems like a long way to go when all I seem to need to do is push that
E-mail button, and send the sheet off.

Can anyone tell me if there's a simple way to do what I'm trying to do or
if I need to work with the sendmail thing?

Thank you for your time.
CB Hamlyn

Hi CB

You can insert a Vlookup formula that lookup the E-mail address also in the sheets that you create with a macro
Then you can use this macro tio send them
http://www.rondebruin.nl/mail/folder3/mail5.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"CB Hamlyn" wrote in message ...
It doesn't matter much if the sheet is attached or if it composes the body. If I could choose either probably make it the body as
the recipients are not all that computer literate.

There's basically a historical record worksheet that lists the possible clients and whether they prefer printed/faxed copies or
e-mail (and addresses where appropriate). This is the only place where the client's e-mail addresses are stored.

There is of course a front end user utility that allows the user of the macro to view/edit the historical list.

What I plan to do is go through this historical list in a macro and for every name I find in the historical list that has it's own
worksheet (schedule), I will either print it or e-mail it automatically based on that clients preference.

I'll look further into your outlook examples in the mean time. I should be able to figure out a way to do this, but I appreciate
any information you can give me that may be more appropriate to what I'm doing (if it's not already laid out on your website that
is).

Thank you very much for your time :)
CB Hamlyn


"Ron de Bruin" wrote in message ...
Hi

Do you want to send the sheet in the Body ?

You can't program the envelope in Excel 2000
And you must use my Outlook body Examples.

Do you have the client Mail address also on the worksheet or only in
the constant list of client's e-mails

--
Regards Ron de Bruin
http://www.rondebruin.nl


"CB Hamlyn" wrote in message ...
WinXP Pro SP2, Excel 2000, OE6

I have a client shipping schedule which includes all of our clients' shipping dates for a three month period. I have a program
which gathers a list of the clients in the most recent schedule, then filters on each client, copy's the filtered schedule into
a worksheet named after the client. For example...

Oct 1-7th
Order Number - Client - Ship date...
1001 - Jones - 10/01/05
1004 - Smith - 10/03/05...

Becomes:
Oct 1-7th
Order Number - Client - Ship date...
1001 - Jones - 10/01/05
(on a worksheet titled "Jones")

and

Oct 1-7th
Order Number - Client - Ship date...
1004 - Smith - 10/03/05
(on a worksheet titled "Smith")

Now, on a separate sheet I have a constant list of client's e-mail addresses. I basically want to browse this list and if I
find a client with an e-mail address who has a report, I want to e-mail said worksheet.

The built-in toolbar for E-Mail in Excel works exactly perfectly for my needs. However trying to capture a macro to use the
toolbar has been most unsuccessful. Is there a way to exactly emulate the pressing of the envelope toolbar button, fill in the
"To:" and "Subject:" lines and send it off?

I did find http://www.rondebruin.nl/sendmail.htm in my searching but it seems like a long way to go when all I seem to need to
do is push that E-mail button, and send the sheet off.

Can anyone tell me if there's a simple way to do what I'm trying to do or if I need to work with the sendmail thing?

Thank you for your time.
CB Hamlyn

We are using XCL 2002. My macro file is stored on a shared drive an
folder. The macro is digitally signed, by me, and each of the user
has accepted and stored on his/her computer the appropriate digita
certificate. The main procedure is named 'Auto_Open'; this i
triggered by a Windows XP Pro 'Scheduled Task' for each user.

My objective is for each user to view zero pop-ups when the Schedule
Task is processing. I have 'pretty much' accomplished this via use o
'Application.Visible = False' at the beginning of my code (and NO
using 'Application.Visible = True' anywhere in my code).

I have two questions:

- I want to eliminate the Outlook pop-up that occurs when th
olMail.Display stmt (see my e-mail code below) is processed. I thin
that I can do this by inserting 'Application.Visible = F/T' a
indicated below. Do you agree?

- When the code runs on my machine, the e-mail is sent without an
need to click on 'Send' above the e-mail message. All of the users ar
experiencing a need to click on 'Send' for the macro to clear and fo
Excel to shut down. I have compared Outlook settings, especially re
sending e-mails, between my machine and corresponding settings fo
users -- I cannot find any significant differences. Users ar
complaining about this. Any ideas?

Sub EMAIL()

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
Set MyObject = Excel.Application

myPath = “S:\Investments\”

If fName < myPath & “BLANK” Then ‘Fund has an Adjustment
With olMail

.To = “Group”

.Subject = “MACRO-GENERATED E-MAIL”

.Body = “There is an Adjustment $10,000; “ & _
“see Attached File.” & vbCrLf & vbCrLf & vbCrLf

.Attachments.Add fName

End With

<<<<<<<<<<<<<<<<<<< Insert 'Application.Visible
False'

olMail.Display

‘Send “Alt-S” to OUTLOOK App
Application.SendKeys “%S”, True

<<<<<<<<<<<<<<<<<<< Insert 'Application.Visible
True' ???

‘Release Memory
Set olMail = Nothing
Set olApp = Nothing

Else ‘This means n
Adjustments
Call Elsewhere
End If

Application.Visible = True
Application.Quit
End

End Sub

TIA for a reply,
Chuckles12

--
Chuckles12
-----------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...fo&userid=1494
View this thread: http://www.excelforum.com/showthread.php?threadid=38832

The bottom 'Application.Visible = True' stmt in my e-mail code is
typographical error; my intent was for this stmt to not be there.

Chuckles12

--
Chuckles12
-----------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...fo&userid=1494
View this thread: http://www.excelforum.com/showthread.php?threadid=38832