Worksheet Selection to Outlook. How?

[eddy]

Had a lot of help from TSTom and Snozgur but had recent crash and lost your E-Mail address's.

Need a macro that will:-
a) Select a range in worksheet (CopySheet) Range C3:H12 (Using Excel 2000)
b) Open Outlook (2000)
c) Past selection into text area whilsh maintaining formating.
Addressee will then be selected in the normal way via the Outlook address book.
Snozgur almost got the answer but it seemed very much O/S and Office version specific.

 

[Jack in the UK]

OK thats not exactly right is it ermmm.

 

[eddy]

Jack ..

Thanks a lot for your suggestions. I will play with your code to see if if I can get it to work..
This is the code that almost works. It works for Smozur but not me. I supect its due to different OS/ or Office progs. Not you need to use the Outlook Add In :-

'This sub set the range
Sub CallSub()
Dim newRange As Range
Set newRange = Sheet2.Range("B1:E38")
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)
Dim myOLApp As Outlook.Application

Set myOLApp = New Outlook.Application
Dim myOLItem As Outlook.MailItem
Set myOLItem = myOLApp.CreateItem(olMailItem)
myRange.Copy

myOLItem.Subject = "QUOTATION : " & Sheet2.Range("D2").Value
myOLItem.Display

SendKeys "%{TAB}"

SendKeys "{TAB 3}"
SendKeys "^v"

myOLItem.To = Sheet2.Range("H14").Value

End Sub

 

[artslave]

sorry, Eddy -- I haven't coded for Save As HTML or for using Outlook before. I was going to at least get you started, but there's no references at all in my Help files about Saving As HTML, and the Macro Recorder records nothing when I try!

I am, of course, still stuck using Excel 97, but since you were searching for a version-independent solution I figured I would give it a shot. Still, I know the support and documentation of web publishing has to be better in 2000. I would investigate the VBE help files on the PublishObject and SaveAs methods, then see if someone else can provide basic code for you to tweak.

Jack's got a good start for you on controlling the Outlook portion of it; again, you'll need to check Outlook 2000's VB help files for the syntax for inserting a file as text only.

Good luck -- sorry I couldn't do more on the code front!

Cath

 

[Anne Troy]

Smozgur can be reached at Oz@TheWordExpert.com