E-Mail a Worksheet Selection. How?

[eddy]

Hi
I have a worksheet open called CopySheet
I need to :-
a) Copy a worksheet selection (Range B1 -E38) to the clipboard.
c) Open Outlook
d) Select recipient from the address book.
e) Paste the clipboard contents into the text area.
Is it possible to this with a macro?
Thanks Ted

 

[Legacy 98055]

Hit Ted...
I think you are using office 2000?
Select your range and click on the email icon.
I sent you a test email.
Let me know if it works ok and I'll see if I can come up with the code to do it as well...
Tom
This message was edited by TsTom on 2002-04-21 05:22

 

[smozgur]

And in code:

Add Microsoft Outlook Object Library by selecting VBA_Tools_References then use this code:

'This sub set the range
Sub CallSub()
Dim newRange As Range
Set newRange = Sheet1.Range("B4:D7")
Call CreateNewEmail(newRange)
End Sub

'This sub creates a new email message and fill the subject and also body as your range data
Sub CreateNewEmail(myRange As Range)
Set myOLApp = New Outlook.Application
Dim myOLItem As Outlook.MailItem
Set myOLItem = myOLApp.CreateItem(olMailItem)
With myOLItem
.Subject = "Here is subject"
For i = 1 To myRange.Rows.Count
For j = 1 To myRange.Columns.Count
tmptext = tmptext & " " & myRange.Cells(i, j).Value
Next j
tmptext = tmptext & vbCrLf
Next i
.Body = tmptext
End With
myOLItem.Display
End Sub


It is possible to play with this more and select To: etc in code.

Regards

 

[Legacy 98055]

Hey Smoz?
Will that code retain the formatting?
Tom

 

[smozgur]

Tom,

What kind of formatting you mean?

 

[Legacy 98055]

The formatting from the sheet as is...

 

[smozgur]

No, just data with space delimiter for columns and new line for rows.

 

[eddy]

Thanks Tom / Smozgur

Still trying to get the code to work this end.
Will let you know how it goes.
Tom I haven,t got your test mail
Thanks all!
Ted

 

[eddy]

Hi Smozgur / Tom

Your code works really well Smozgur and it's lightning fast! I need to retain the formatting though of the original sheet as it's not plain text I am sending but a quotation which has columns Qty and Description with total Equipment,VAT and Total + VAT calls at the bottom. If the formatting could be sorted then thats the answer to all my E-Mail problems. Maybe if it were sent as HTML or a picture it may solve the problem! Is that possible
Regards Ted