I am trying to use SendMail with an array and I
intermittently get the '1004' General mail failure error.
Note that this is intermittent. The macro WILL work. But
the moment I make any alteration to the macro, the
SendMail will fail.

However, there doesn't seem to be any rhyme or reason to
the error. I will try various things, such as remarking
out
the input box statement or the sendmail and eventually
when I put them back in everything works. And will work
over and over. But as soon as I make ANY change to the
macro, even cosmetic (such as adding a remarked
statement), the error returns. I am running WXP SP2 with
Outlook 2003 as my mail client and Excel 2003. It seems
as if the sendmail statement is keeping a process hung up
somewhere. However, neither shutting down nor rebooting
seems to have any effect. I am copying the last part of
the macro below in case it helps. As you can see, this is
the section that prompts the user for an address and then
sends mail to that address as well as the static address.
I know this is difficult since it's not exactly
reproduceable, but any help that could be given would be
great because the code (when it works) does EXACTLY what
I need.

Thanks!

' Get SLM address

Dim Message, Title, Default
Message = "Enter your email address: "
Title = "To receive a copy..."

Dim Add(2) As String
Add(1) = "
Add(2) = InputBox(Message, Title)

ActiveWorkbook.SendMail Recipients:=Add(),
Subject:="SW/Equip. Order for Cust: " & CustNum.Value
& " - " & OrdDate.Value

ActiveWindow.Close
Kill sPath & "SWE Order for Customer " & CustNum.Value
& ".xls"
Application.ScreenUpdating = True
Application.Quit

End Sub

Hi Martin,

Just a few guesses. First, do you use any global variables that must have
values to proceed with your code? When you do anything in your VBA project,
you are in effect resetting the project, which clears out of memory any
global or module-level variables you may have set values for. Second, are
you calling this routine from a CommandButton on a Worksheet? If so, make
sure the TakeFocusOnClick property is set to False.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Martin wrote:
I am trying to use SendMail with an array and I
intermittently get the '1004' General mail failure error.
Note that this is intermittent. The macro WILL work. But
the moment I make any alteration to the macro, the
SendMail will fail.

However, there doesn't seem to be any rhyme or reason to
the error. I will try various things, such as remarking
out
the input box statement or the sendmail and eventually
when I put them back in everything works. And will work
over and over. But as soon as I make ANY change to the
macro, even cosmetic (such as adding a remarked
statement), the error returns. I am running WXP SP2 with
Outlook 2003 as my mail client and Excel 2003. It seems
as if the sendmail statement is keeping a process hung up
somewhere. However, neither shutting down nor rebooting
seems to have any effect. I am copying the last part of
the macro below in case it helps. As you can see, this is
the section that prompts the user for an address and then
sends mail to that address as well as the static address.
I know this is difficult since it's not exactly
reproduceable, but any help that could be given would be
great because the code (when it works) does EXACTLY what
I need.

Thanks!

' Get SLM address

Dim Message, Title, Default
Message = "Enter your email address: "
Title = "To receive a copy..."

Dim Add(2) As String
Add(1) = "
Add(2) = InputBox(Message, Title)

ActiveWorkbook.SendMail Recipients:=Add(),
Subject:="SW/Equip. Order for Cust: " & CustNum.Value
& " - " & OrdDate.Value

ActiveWindow.Close
Kill sPath & "SWE Order for Customer " & CustNum.Value
& ".xls"
Application.ScreenUpdating = True
Application.Quit

End Sub

Jake,

Thanks for your response.

No, I'm not using any global variables. I am calling this
routine from a form button on the worksheet, but I don't
know where to change the TakeFocusOnClick property.

But FYI, I came in this morning and the macro worked
fine. I ran it several times in a row and it worked
perfectly each time. Then all I did was go into the macro
and unremarked a remarked line. Then I remarked back out
the same line and saved the macro. Now all of a sudden
the error is back. ???

Thanks for any help,
Martin


-----Original Message-----
Hi Martin,

Just a few guesses. First, do you use any global
variables that must have
values to proceed with your code? When you do anything
in your VBA project,
you are in effect resetting the project, which clears
out of memory any
global or module-level variables you may have set values
for. Second, are
you calling this routine from a CommandButton on a
Worksheet? If so, make
sure the TakeFocusOnClick property is set to False.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


Martin wrote:
I am trying to use SendMail with an array and I
intermittently get the '1004' General mail failure
error.
Note that this is intermittent. The macro WILL work.
But
the moment I make any alteration to the macro, the
SendMail will fail.

However, there doesn't seem to be any rhyme or reason
to
the error. I will try various things, such as remarking
out
the input box statement or the sendmail and eventually
when I put them back in everything works. And will work
over and over. But as soon as I make ANY change to the
macro, even cosmetic (such as adding a remarked
statement), the error returns. I am running WXP SP2
with
Outlook 2003 as my mail client and Excel 2003. It seems
as if the sendmail statement is keeping a process hung
up
somewhere. However, neither shutting down nor rebooting
seems to have any effect. I am copying the last part of
the macro below in case it helps. As you can see, this
is
the section that prompts the user for an address and
then
sends mail to that address as well as the static
address.
I know this is difficult since it's not exactly
reproduceable, but any help that could be given would
be
great because the code (when it works) does EXACTLY
what
I need.

Thanks!

' Get SLM address

Dim Message, Title, Default
Message = "Enter your email address: "
Title = "To receive a copy..."

Dim Add(2) As String
Add(1) = "
Add(2) = InputBox(Message, Title)

ActiveWorkbook.SendMail Recipients:=Add(),
Subject:="SW/Equip. Order for Cust: " & CustNum.Value
& " - " & OrdDate.Value

ActiveWindow.Close
Kill sPath & "SWE Order for Customer " & CustNum.Value
& ".xls"
Application.ScreenUpdating = True
Application.Quit

End Sub

.

More info...

As mentioned below, the error is back. Now, I have waited
a couple of hours and tried again and it works. I have
not TOUCHED the file or the macro.

Does that help?

Martin


-----Original Message-----
Jake,

Thanks for your response.

No, I'm not using any global variables. I am calling
this
routine from a form button on the worksheet, but I don't
know where to change the TakeFocusOnClick property.

But FYI, I came in this morning and the macro worked
fine. I ran it several times in a row and it worked
perfectly each time. Then all I did was go into the
macro
and unremarked a remarked line. Then I remarked back out
the same line and saved the macro. Now all of a sudden
the error is back. ???

Thanks for any help,
Martin


-----Original Message-----
Hi Martin,

Just a few guesses. First, do you use any global
variables that must have
values to proceed with your code? When you do anything
in your VBA project,
you are in effect resetting the project, which clears
out of memory any
global or module-level variables you may have set
values
for. Second, are
you calling this routine from a CommandButton on a
Worksheet? If so, make
sure the TakeFocusOnClick property is set to False.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


Martin wrote:
I am trying to use SendMail with an array and I
intermittently get the '1004' General mail failure
error.
Note that this is intermittent. The macro WILL work.
But
the moment I make any alteration to the macro, the
SendMail will fail.

However, there doesn't seem to be any rhyme or reason
to
the error. I will try various things, such as
remarking
out
the input box statement or the sendmail and eventually
when I put them back in everything works. And will
work
over and over. But as soon as I make ANY change to the
macro, even cosmetic (such as adding a remarked
statement), the error returns. I am running WXP SP2
with
Outlook 2003 as my mail client and Excel 2003. It
seems
as if the sendmail statement is keeping a process
hung
up
somewhere. However, neither shutting down nor
rebooting
seems to have any effect. I am copying the last part
of
the macro below in case it helps. As you can see,
this
is
the section that prompts the user for an address and
then
sends mail to that address as well as the static
address.
I know this is difficult since it's not exactly
reproduceable, but any help that could be given would
be
great because the code (when it works) does EXACTLY
what
I need.

Thanks!

' Get SLM address

Dim Message, Title, Default
Message = "Enter your email address: "
Title = "To receive a copy..."

Dim Add(2) As String
Add(1) = "
Add(2) = InputBox(Message, Title)

ActiveWorkbook.SendMail Recipients:=Add(),
Subject:="SW/Equip. Order for Cust: " & CustNum.Value
& " - " & OrdDate.Value

ActiveWindow.Close
Kill sPath & "SWE Order for Customer " & CustNum.Value
& ".xls"
Application.ScreenUpdating = True
Application.Quit

End Sub

.

.

Martin,

The TakeFocusOnClick property only applies to an ActiveX control. So I
don't think that's your problem, since you used the Forms version.

If you'd like to email the workbook to me, I can take a look. It's mvp
<-at- longhead <-dot- com.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Martin wrote:
More info...

As mentioned below, the error is back. Now, I have waited
a couple of hours and tried again and it works. I have
not TOUCHED the file or the macro.

Does that help?

Martin


-----Original Message-----
Jake,

Thanks for your response.

No, I'm not using any global variables. I am calling this
routine from a form button on the worksheet, but I don't
know where to change the TakeFocusOnClick property.

But FYI, I came in this morning and the macro worked
fine. I ran it several times in a row and it worked
perfectly each time. Then all I did was go into the macro
and unremarked a remarked line. Then I remarked back out
the same line and saved the macro. Now all of a sudden
the error is back. ???

Thanks for any help,
Martin


-----Original Message-----
Hi Martin,

Just a few guesses. First, do you use any global variables that
must have values to proceed with your code? When you do anything
in your VBA project, you are in effect resetting the project, which
clears out of memory any global or module-level variables you may
have set values for. Second, are you calling this routine from a
CommandButton on a Worksheet? If so, make sure the
TakeFocusOnClick property is set to False.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Martin wrote:
I am trying to use SendMail with an array and I
intermittently get the '1004' General mail failure error.
Note that this is intermittent. The macro WILL work. But
the moment I make any alteration to the macro, the
SendMail will fail.

However, there doesn't seem to be any rhyme or reason to
the error. I will try various things, such as remarking
out
the input box statement or the sendmail and eventually
when I put them back in everything works. And will work
over and over. But as soon as I make ANY change to the
macro, even cosmetic (such as adding a remarked
statement), the error returns. I am running WXP SP2 with
Outlook 2003 as my mail client and Excel 2003. It seems
as if the sendmail statement is keeping a process hung up
somewhere. However, neither shutting down nor rebooting
seems to have any effect. I am copying the last part of
the macro below in case it helps. As you can see, this is
the section that prompts the user for an address and then
sends mail to that address as well as the static address.
I know this is difficult since it's not exactly
reproduceable, but any help that could be given would be
great because the code (when it works) does EXACTLY what
I need.

Thanks!

' Get SLM address

Dim Message, Title, Default
Message = "Enter your email address: "
Title = "To receive a copy..."

Dim Add(2) As String
Add(1) = "
Add(2) = InputBox(Message, Title)

ActiveWorkbook.SendMail Recipients:=Add(),
Subject:="SW/Equip. Order for Cust: " & CustNum.Value
& " - " & OrdDate.Value

ActiveWindow.Close
Kill sPath & "SWE Order for Customer " & CustNum.Value
& ".xls"
Application.ScreenUpdating = True
Application.Quit

End Sub

.

.