Mail From Excel Using Outlook... Could be a toughy...

[tweek312]

Ive been fiddling with the xlDialogSendMail ClassMember and would like to know if there is a way to send only one of the sheets in a workbook. Better yet... only a selected range. I have yet to find any information on this that does not involve converting to html. The reason I am opposed to using the xl to html conversion is because for some reason most of the sheets colors turn red when sent in an email using html.

This is what I have so far...
NOTICE!!!! I am not a programmer or VBA freak... so dont go crazy if my code looks funny.

7h4nk$
=D_tW33k_3|2

Sub prep_everpt()
MsgBox "Are you sure you want to send the report?"
With Sheet1
Application.Dialogs(xlDialogSendMail).Show arg1:="michael.reutter@thomson.net;"
End With
End Sub

 

[SkipVought]

tweek312,

Macro record.

Copy the RANGE that you want to a new sheet.

Copy the new SHEET and then send.

Notice that the Expression that is required is a WORKBOOK object.



Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[tweek312]

How do I retain the filename of the original sheet? I cant use Book1.xls.

I guess I would have to save the sheet as the name of the other active sheet. But how do I do that automatically; if the exact name of the sheet is always different.

The name is as follows:

live_report_10.07.05(vUBER).xls

The only thing that changes is the date. More than one of the sheets may be open at a time... For instance:
live_report_10.06.05(vUBER).xls and
live_report_10.07.05(vUBER).xls and
live_report_10.08.05(vUBER).xls
May be open all at the same time making it hard to designate the name of the file to be sent.

Thanks,
Tweek

 

[xlbo]

MySaveName = Activesheet.Name ???



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tweek312]

But how do I pass that On to the Book1.xls? Do I simply put the (MySaveName = Activesheet.Name) before the copy or after...? At one point in the process the active sheet is actually Book1.xls.

Little bit more info would help...

Thankies!

tW33k

 

[xlbo]

Depends what you want to do with it - you've hardly been clear so far. Please outline your process step by step and highlight the lines that are giving you issues...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question