Pasting text from Excel to Outlook email

[KarenLloyd]

Hi to All.

I have a new question for you...

I need to copy text from Excel into Outlook email to create the Message Body / Fax Header.

I will know the workbook file name and ask the user to select the worksheet name from an array.

I couldn't find any way to paste with automation into Outlook, so I have used a combination of Excel's PublishObjects and STRTOFILE() to do this.

* Excel Constants
#DEFINE xlSourceRange 4 
#DEFINE xlHtmlStatic 0
* Outlook Constants
#DEFINE olMailItem 0

* Workbook Name and Worksheet Name
myWBName = "filename.XLS"
myWSName = "sheetname"

* Temp file name for Header text in HTML?
myTmpFile = "\tempdir\TempHdr.htm"
DELETE FILE (myTmpFile)

* Get what you need from Excel
oExcel = GetObject("", 'Excel.Application')
oWorkBook = oExcel.Workbooks.Open(myWBName)
oExcel.ActiveWorkBook.WorkSheets[myWSName].Select

* Publish the range A1:H35 as HTM
oExcel.ActiveWorkbook.PublishObjects.Add(xlSourceRange, ;
  myTmpFile, myWSName, "$A$1:$H$35", xlHtmlStatic, "", "").Publish

* Convert HTM to String
myHTMLBody = FILETOSTR(myTmpFile)

* Open Outlook and create an email
myOLApp = createobject("Outlook.Application")
myOLItem = myOLApp.CreateItem(olMailItem)

* use text string for HTMLBody
myOLItem.HTMLBody = myHTMLBody
* Display
myOLItem.Display

* Close Excel
oWorkBook.Close( .F. )
oExcel.Quit

This does seem to work OK. But as I have probably taken the long route first, I just wondered is there another [better/faster] method that I should try before I go any further?

Thanks

Karen