Need solution for SendTo: Mail Recipient

[sswilcox]

I have developed a small tool to aid our salespeople in preparing sales
proposals. One feature is a button they click that sends all of the
details of the proposal to an expert in our firm for approval and so
that he can keep tabs on everyone's activities and catch any errors.

Yes, I have tried Ron de Bruin's very helpful methods, but they don't
quite work for my situation. They work fine when I use my workbook on
my own PC, but the user-facing workbook is located on our Intranet in a
document management environment, which I suspect is causing all of my
problems.

The data I want to send is all located on one worksheet. If I were
doing it manually I could just go to File/Send To/Mail Recipient option
and everything takes care of itself.

Is there VBA code that will simply allow me to execute that command?

Basically, most of Ron's techniques require a temporary file to be
created, saved, then sent in an email. I get errors on the creation of
the file in that document management environment.

I found another Ron technique that is very promissing
(http://www.rondebruin.nl/mail/folder3/mail2.htm) and actually
generates the email I need, but instead of the data from my worksheet I
just see a blank image that I can click on to select. It also creates a
copy of the worksheet and adds it to my workbook ("Sheet1 (2)").

I'm stuck with Office 2000, unfortunately, which I understand is also
limiting what I can do.

Thanks guys,

S Wilcox

 

[Ken Puls]

Try looking up SendMail in the VBA help files or object browser. It can
be used like this:

ActiveWorkbook.SendMail recipients:="Recipient Name"

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[sswilcox]

Thanks for taking the time to reply Ken. I appreciate it.

This isn't quite what I'm looking for. It sends the entire workbook,
whereas I am wanting to send only the active worksheet. I browsed for
"SendMail" in the VBA Help and Object Browser for additional info but I
don't see anything beyond what you have suggested.

S Wilcox

 

[Ron de Bruin]

Hi sswilcox

You can use sendmail if you want but can't give the file a normal name

Sub Mail_ActiveSheet()
Dim wb As Workbook
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SendMail "(e-mail address removed)", _
"This is the Subject line"
.Close False
End With
Application.ScreenUpdating = True
End Sub

Talk to your IT department about this

 

[sswilcox]

Thanks Ron. I should have posted this problem months ago when it first
reared it's nasty head.

This code is so close. It sends the worksheet perfectly when run from
my own PC. However, it still sends the entire workbook when run from
the document management intranet location.

S Wilcox

 

[Ron de Bruin]

Very Strange

The macro not saving anything it only send the activeworkbook
and that is on that moment the workbook with only your activesheet

 

[GA]

On Wed, 25 Oct 2006 19:55:18 +0200, "Ron de Bruin"

Sub Mail_ActiveSheet()
Dim wb As Workbook
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SendMail "(e-mail address removed)", _
"This is the Subject line"
.Close False
End With
Application.ScreenUpdating = True
End Sub

This is just what I've been trying to do and it works great!

Just one bit of code is missing for my purposes and I don't know how
to do it so a bit more help would be much appreciated.

Instead of hard coding the recipient, I want to get it from another
worksheet in the same workbook so for arguments sake - the above code
is on sheet2, I want to get the recipient from cell A1 on sheet1.

Many thanks
GA

 

[Ron de Bruin]

Hi GA

See
http://www.rondebruin.nl/mail/tips1.htm

 

[GA]

Hi GA

See
http://www.rondebruin.nl/mail/tips1.htm

Absolutely spot on very many thanks again Ron.

GA
p.s. - your site is now bookmarked 8^)

 

[sswilcox]

Thanks for the help guys. I've got a temporary workaround in place
until I can get our IT dept. on board.

S Wilcox