How to copy excel data to Outlook email body programmatically? 1

[armadillo0]

Hi Foxpro Expert,
Wow, time flies, seems almost 20 years since the last time I posted.

What I want to do is actually pretty simple, but have no solution to it at the moment.
Basically, I have excel data to copy and just paste it to the outlook body.
I tried with all the below syntax, but none is working, any clue what is the proper way to paste the clipboard data to outlook body?

Million thanks for your great assistance

oExcel.Visible=.T.
oExcel.Range("A1").Activate
oExcel.Range("A1:C"+ALLTRIM(STR(nRow,9))).SELECT
oExcel.Range("A1:C"+ALLTRIM(STR(nRow,9))).copy

*oItem.body=oExcel.Range("A1:C"+ALLTRIM(STR(nRow,9))) &&"Testing....123"+CHR(13)
*oItem.HTMLbody=oExcel.Range("A1:C"+ALLTRIM(STR(nRow,9))).paste &&"Testing....123"+CHR(13)
*oItem.body.PasteSpecial()
oitem.display
oItem.HTMLbody="Press Ctrl-V now"+CHR(13)
*oItem.HTMLbody=_CLIPTEXT &&"Testing HTML..."+CHR(13)
oItem.HTMLbody.PasteSpecial &&=oExcel.GetText &&GetFromClipboard &&_CLIPTEXT &&"Testing HTML..."+CHR(13)
*oItem.HTMLbody=oItem.HTMLbody+_CLIPTEXT+CHR(13)
*oItem.PasteSpecial(1) &&.PasteSpecial(1)

 

[Mike Lewis]

So, your second visit in nearly 20 years. Welcome back.

Your code is a little confusing. For a start, it would help if you deleted all the commented-out lines, which do nothing to clarify what you are trying to do.

It is not usual to use the clipboard to copy data from one app to another. In this case, yoou can simply set oItem.HTMLBody to the contents of the Excel range. Something like:

loRange = oExcel.Range("A1:C"+ALLTRIM(STR(nRow,9)))
...
...
oItem.HTMLbody= loRange.Text

The reason not to use the clipboard is that doing so will overwrite any clipboard data that is being used by other apps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[armadillo0]

Thank you Mike,
Well, I visited tek-tips almost weekly, only never posted haha...
Maybe will be more active now

Seems I'm still encountering below errors, any idea:

 

[armadillo0]

Also any idea how to disable the Outlook safety prompting?

 

[armadillo0]

I found something interesting, if I select bigger range, unable to paste the data.
But if I just select 1 cell, then no errors.
Maybe there is other way to paste data with excel HTML/XML format?? any clue?

* doesn't work: loRange=oExcel.Range("A2:C"+ALLTRIM(STR(nRow,9)))
loRange=oExcel.Range("A2:A2")

oitem.display
oItem.HTMLbody=loRange.Text

Thank you again....

 

[Mike Lewis]

Regarding your Error 1426. This is what the Help says:

You have received a hexadecimal error code from OLE and a brief description of the error. The remedy depends on the error returned by OLE

In other Words, the error was generated by Excel (or Outlook?), not by VFP. The solution is to use the AERROR() function to get information about the specific error. Seee the Help for AERROR() for details.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Also any idea how to disable the Outlook safety prompting?

If this is happening on an end-user's system, you won't be able to disable it - and rightly so. It would be up the client's administrator to do that, and they probably wouldn't want to do so.

However, there are ways of bypassing the prompt. One tool that I have in the past is Redemption. I don't have any up-to-date information on it, but you can find out more here:

https://www.dimastr.com/redemption/home.htm

For general information on the subject, see

https://www.msoutlook.info/question/883

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[armadillo0]

Thank you Mike for your help.
I think outlook unable to receive the html format from excel.

 

[Chris Miller]

HTML is HTML, just the Range.Text property contains text, tab delimited, likely, haven#t tried, but not HTML, no HTML tags for cells.

So what you need from an Excel Range is HTML for the range, not text.

The range.text would make a tab delimited appearance in the nrmal text body, without cell border lines, so that could be an easy compromise.

Otherwise search the web for Excel Range to HTML and you'll find some solutions.



Chriss

 

[Mike Lewis]

I think outlook unable to receive the html format from excel.

The fact that the message text is coming from Excel is irrelevant. And the fact that you are copying into the HTMLBody property of the message object does not automatically make the text into HTML.

Presumably you want the final message to contain some sort of formatting, such as bold or italic text or borders and shading. If so, it is up to you to add the appropriate HTML tags. You do that by programmatically modifying the text.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

it is up to you to add the appropriate HTML tags

Your assumption is wrong:

outlook unable to receive the html format from excel.

It's simply that Range.text isn't HTML at all.

Range.text is just text. I assume it would separate values of columns with a TAB, which could be used to replace every TAB with "</td><td>" (end of current cell, begin of next cell). This won't cater for rows, though, which means every CRLF or just LF needs to be replaced with "</tr><tr>" (end of current row, begin of next row).

Finally prefix the string with "<table><tr><td>" (start table, first row and first cell) and append "</td></tr></table>" at the end (end last cell, last row and table). That can turn the rang.text to simple table HTML without shading, headings bold etc.

I think there's an easier way of using an Excel.Selection into the clipboard that turns the range into HMTL format and can be pasted then, or you get the HTML out of the clipboard. For that to work you need the clipboard functions of foxtools.fll.

And I guess there are ther ways to let Excel export a range into HTML, perhaps an HTML file you then can read in and put into the mail HTMLbody.

Chriss

 

[Mike Lewis]

I think there's an easier way of using an Excel.Selection into the clipboard that turns the range into HMTL format and can be pasted then, or you get the HTML out of the clipboard.

As I mentioned earlier, I am against using the clipboard in this way, mainly because it will overwrite whatever content the user has placed in the clipboard, which is not very polite. That's why I steered the OP away from that solution.

And I guess there are ther ways to let Excel export a range into HTML, perhaps an HTML file you then can read in and put into the mail HTMLbody.

That sounds more promising. After all, the Excel UI File / Save As to HTML, so it must be possible to do it programmatically. It might need some experimenting though.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

it will overwrite whatever content the user has placed in the clipboard, which is not very polite

Okay, you could finally put the clipboard into its original state, Foxtools clipboard functions explicitly allow you to instate a new clipboard "channel" and not touch whatever is pasted with CTRL+V.

But I agree not touching it at all is simplest.

It's still important to have the aha effect. When you do this manually, manually select a range of excel and do manually use copy&paste you get the Excel cells into Outlook. But a range object is not the selection object and reading out Range.text does not put it into the clipboard nor does it contain HTML. It wouldn't even suffice to set a selection from a range, a selection itself is only marking the cells that would be copied into the clipboard, if you then use CTRL+C. Just like selecting any text in notepad does only select and mark it, it doesn't put it in the clipbaord.

You can easily fall into the trap of confusing the concepts of an Excel.range, an Excel.selection and the clipboard if the idea to copy programmatically stems from programmatically redoing what you can do with the help of the clipboard, manually. Defining a reange or selection is just a step before even populating the clipboard and pasting that into outlook also isn't as straight forward as setting the HTMLbody of a mail object.

You can learn one thing from this, armadillo0, what's easy to do manually is not necessarily straight forward the best idea to do it programmatically, too. It is indeed simple to add to a mails HTMLbody, as that simply is a property and you don't need to use the clipboard, which is intended for usage by a user, manually. Even though you just want to use what you know works, it's not the easiest way.

The VFP _clipboard variable is only the tip of an iceberg, it only holds an ANSI text or the ANSI text equivalent of a unicode text, let's say this is the simple VFP clipbard to distinguish it from the Windows system clipboard, which is capable to hold in many more things, a whole file, an image, many things that won't be seen and available in VFPs simple ANSI text _clipboard.

So turn the thought around, since the clipboard isn't easy to automate, concentrate on the HTMLbody, so you can ideally process an Excel.Range into html and put that into the mail HTMLbody.
I loooked into the Excel object model, something you can do, yourself, too, at [URL unfurl="true"]https://learn.microsoft.com/en-US/office/vba/api/excel.range(object)[/url], and there is no HTML property, it's even worse, the Range.text property is described as only giving the text when the range is a single cell, if the range has multiple cells you don't get tab delimited text as I thought, you get NULL.

So perhaps the best way is to actually first export the range to a HTML file and then read that back. There's PublishObjects for that, see a discussion at foxite:

https://www.foxite.com/archives/excel-automation-0000371742.htm

Chriss

 

[SkipVought]

When assigning an OBJECT such as a RANGE, use SET

Set loRange=oExcel.Range("A2:C"+ALLTRIM(STR(nRow,9)))

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[TamarGranor]

When assigning an OBJECT such as a RANGE, use SET

Not in VFP. The SET command in VFP doesn't do assignment.

Tamar

 

[armadillo0]

Thank you again for many feedback, sound promising.. I will try out later.
Ctrl-V is working when I manually press ctrl-V on the outlook.
Just curious, if anyway, I have simulate <Ctrl>-V using VFP programmatically?

 

[Chris Miller]

armadillo0,

even if you ignore Mike Lewis warning about involving the clipboard in a solution, the Keyboard() function would only affect a VFP window. Sending keys to other applications is not as easy as a solution creating an HTML file, reading it in and adding it to the mail HTMLbody.

Chriss

 

[armadillo0]

thank you again for all the feedback, haven't got time to try out, as got bigger problems today, got server issues

 

[SkipVought]

Then if you're NOT assigning the OBJECT properties, it seems most reasonable that it is the VALUE property that would transfer text in Outlook

loRange=oExcel.Range("A2:C"+ALLTRIM(STR(nRow,9))).[b]Value[/b]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Chris Miller]

Skip,

you're focussing extremely on getting the range, what fails is what I found out about the Text property and already mentioned: If a Range has more than 1 cell, the Text property becomes NULL, as is documented:

https://learn.microsoft.com/en-US/office/vba/api/excel.range.text

See Remark

Now, if you get the NULL and try to add that to the HGTMLbody property, that errors.

Chriss