E-Mail a Worksheet Selection. How?

eddy · Apr 21st, 2002, 02:21 AM

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

Tom Schreiner · Apr 21st, 2002, 06:19 AM

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 · Apr 21st, 2002, 06:25 AM

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

Tom Schreiner · Apr 21st, 2002, 06:28 AM

Hey Smoz?
Will that code retain the formatting?
Tom

smozgur · Apr 21st, 2002, 07:46 AM

Tom,

What kind of formatting you mean?

Tom Schreiner · Apr 21st, 2002, 08:11 AM

The formatting from the sheet as is...

smozgur · Apr 21st, 2002, 08:15 AM

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

eddy · Apr 21st, 2002, 02:08 PM

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 · Apr 22nd, 2002, 03:43 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 21st, 2002, 02:21 AM	#1
eddy Board Regular Join Date: Mar 2002 Posts: 473	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

Apr 21st, 2002, 06:19 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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 ]

Apr 21st, 2002, 06:25 AM	#3
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	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

Apr 21st, 2002, 06:28 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hey Smoz? Will that code retain the formatting? Tom

Apr 21st, 2002, 07:46 AM	#5
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Tom, What kind of formatting you mean?

Apr 21st, 2002, 08:11 AM	#6
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	The formatting from the sheet as is...

Apr 21st, 2002, 08:15 AM	#7
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	No, just data with space delimiter for columns and new line for rows.

Apr 21st, 2002, 02:08 PM	#8
eddy Board Regular Join Date: Mar 2002 Posts: 473	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

Apr 22nd, 2002, 03:43 PM	#9
eddy Board Regular Join Date: Mar 2002 Posts: 473	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