×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to copy excel data to Outlook email body programmatically?

How to copy excel data to Outlook email body programmatically?

How to copy excel data to Outlook email body programmatically?

(OP)
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 pc2

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)

RE: How to copy excel data to Outlook email body programmatically?

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:

CODE -->

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

RE: How to copy excel data to Outlook email body programmatically?

(OP)
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:

RE: How to copy excel data to Outlook email body programmatically?

(OP)
Also any idea how to disable the Outlook safety prompting?

RE: How to copy excel data to Outlook email body programmatically?

(OP)
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....

RE: How to copy excel data to Outlook email body programmatically?

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

Quote:

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

RE: How to copy excel data to Outlook email body programmatically?

Quote:

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

RE: How to copy excel data to Outlook email body programmatically?

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

RE: How to copy excel data to Outlook email body programmatically?

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

RE: How to copy excel data to Outlook email body programmatically?

Quote:

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

RE: How to copy excel data to Outlook email body programmatically?

Quote (Mike Lewis)

it is up to you to add the appropriate HTML tags

Your assumption is wrong:

Quote:

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

RE: How to copy excel data to Outlook email body programmatically?

Quote:

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.

Quote:

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

RE: How to copy excel data to Outlook email body programmatically?

Quote (Mike Lewis)

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 https://learn.microsoft.com/en-US/office/vba/api/excel.range(object), 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-0...


Chriss

RE: How to copy excel data to Outlook email body programmatically?

When assigning an OBJECT such as a RANGE, use SET

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: How to copy excel data to Outlook email body programmatically?

Quote (SkipVought)

When assigning an OBJECT such as a RANGE, use SET

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

Tamar

RE: How to copy excel data to Outlook email body programmatically?

(OP)
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?

RE: How to copy excel data to Outlook email body programmatically?

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

RE: How to copy excel data to Outlook email body programmatically?

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

RE: How to copy excel data to Outlook email body programmatically?

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

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: How to copy excel data to Outlook email body programmatically?

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/e...

See Remark

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

Chriss

RE: How to copy excel data to Outlook email body programmatically?

This is something I have done in the past for pasting Excel Data into an Outlook email. I create a table in the body of the email and then just scan through the data to fill in all the data I want and have attached the excel file itself for good measure.

CODE --> VFP

TRY
	o=CREATEOBJECT([outlook.application])
CATCH
	o=CREATEOBJECT([outlook.application.14])
ENDTRY
oitem=o.createitem(0)
oitem.subject=[****** AAAAAAA *****]
lto=[zzzzz@zzzzzzz.com]+lemail
lcc=[ffffffff@ffffffff.com]
oitem.TO=lto
oitem.cc=lcc
oitem.Attachments.ADD(lfile2)

lhtml=[]
lhtml=[<head><style> table, th, td {border: 1px solid black;border-collapse: collapse} th, td {padding: 1px} th {text-align: left} tr {text-align: left}</style></head>]
lhtml=lhtml+[<table border="1" style="width:100%">]
lhtml=lhtml+[<tr><th>AAAAAA</th><th>BBBBBB</th><th>CCCCCC</th><th>DDDDDDD</th><th>EEEEEEEEE</th><th>FFFFFFFFFFFFFF</th></tr>]

aaa=[]
bbb=[]
ccc=[]
ddd=[]
eee=[]
fff=[]

SCAN
laaa=aaa
lbbb=bbb
lccc=ccc
lddd=ddd
leee=eee
lfff=fff

lhtml=lhtml+[<tr><td>]+laaa+[</td><td>]+lbbb+[</td><td>]+lccc+[</td><td>]+lddd+[</td><td>]+leee+[</td><td>]+lfff+[</td></tr>]
ENDSCAN

lhtml=lhtml+[</table><footer> Info <br><br> ]
lhtml=lhtml+[ info <br> ]
lhtml=lhtml+[ info <br>  OR  <br>]
lhtml=lhtml+[ <br> ]+SYS(16)+[ </footer>]
oitem.htmlbody=lhtml
*oitem.DISPLAY
*cancel
oitem.SEND
o=.NULL. 

RE: How to copy excel data to Outlook email body programmatically?

(OP)
Thank you @TinyNinja,
very details sample.
I will try out soon.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close