Mail to multiple receipients

[Clapp]

I have a macro that is to automatically send the
worksheet to a particular email address.

At the end of a macro, I have written an InputBox for the
user to enter his email address if he wants a copy.

I want the worksheet to be sent to the first particular
address (same one every time, hard coded in) as well as
the address input by the user.

This seems like it should be easy. I'm using .SendMail

How do I combine the two addresses?

 

[Jan Kronsell]

Try something like this:

Sub SendSheet()

Dim rec(2) As String

rec(1) = "(e-mail address removed)"
rec(2) = Application.InputBox("Type your e-mail address to get a copy")

ActiveWorkbook.SendMail Recipients:=rec(), Subject:="My subject"


End Sub

Jan

 

[Clapp]

Thank you so much for your help, but I am getting:

Run-time error '1004' General Mail Failure

???

 

[Clapp]

NO wait! Sorry, I had mistyped something. Your method
worked! Thank you!

 

[Jan Kronsell]

Glad I Could help

Jan--

NO wait! Sorry, I had mistyped something. Your method
worked! Thank you!

 

[Clapp]

Sorry, I spoke too soon.

In the SendMail statement I had written rec(2) instead of
rec(). When I corrected this, it worked. Several times in
a row.
Then I added the following line at the top of my macro:
Application.ScreenUpdating = False

That's all I added, and the next time I ran the macro, I
got the General Mail Failure error (1004). I removed the
statement but keep getting the error. I've even rebooted
the machine. Now, I cannot make it work again.

Any ideas?

 

[Jan Kronsell]

I'm not sure what happens. The screen updating ashould have nothing to do
with the Send Mail function. Have you tried copying the original code to
another workbook and try it there?

Jan

 

[Martin]

Jan,

I've tried copying it to a completely new workbook, but
that doesn't work either.

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 you could give would be great
because the code (when it works) is EXACTLY what I want
to do.

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) = "(e-mail address removed)"
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