Sending E-mail

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" <cbh@newenglandhomes.net> wrote in message news:11dpshephvgc188@corp.supernews.com...
> 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" <rondebruin@kabelfoon.nl> wrote in message
news:uGbleaHjFHA.2852@TK2MSFTNGP14.phx.gbl...
> 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" <cbh@newenglandhomes.net> wrote in message
> news:11dpshephvgc188@corp.supernews.com...
>> 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" <cbh@newenglandhomes.net> wrote in message news:11dq8o68j8blje2@corp.supernews.com...
> 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" <rondebruin@kabelfoon.nl> wrote in message news:uGbleaHjFHA.2852@TK2MSFTNGP14.phx.gbl...
>> 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" <cbh@newenglandhomes.net> wrote in message news:11dpshephvgc188@corp.supernews.com...
>>> 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 and folder. The macro is digitally signed, by me, and each of the users has accepted and stored on his/her computer the appropriate digital certificate. The main procedure is named 'Auto_Open'; this is triggered by a Windows XP Pro 'Scheduled Task' for each user.

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

I have two questions:

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

- When the code runs on my machine, the e-mail is sent without any need to click on 'Send' above the e-mail message. All of the users are experiencing a need to click on 'Send' for the macro to clear and for Excel to shut down. I have compared Outlook settings, especially re: sending e-mails, between my machine and corresponding settings for users -- I cannot find any significant differences. Users are 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 no Adjustments
Call Elsewhere
End If

Application.Visible = True
Application.Quit
End

End Sub

TIA for a reply,
Chuckles123

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

Chuckles123