pasting text and excel selections into outlook

I’m working on a script to insert a few lines of text into a new outlook email, then a couple blank lines, then a selection from excel.

I can do either or correctly. That is, paste the text, or paste the selection.

But trying to do both and not have the table obliterate the previously pasted text, or have the selection pasted in as a bunch of . System.__ComObject lines is proving tricky. set-clipboard and get-clipboard work very, very poorly with excel.

Ideally I’d paste the leading text. Then get the selection from excel. Then paste that in with one or two blank lines between it and the leading text, and then have the signature show up correctly.


Are you sure that is the only way to accomplish what you want to do? If the goal is to provide a email with text and a table, you should consider another approach. Look at a simple example (make sure you use the Gist link if you post any HTML\XML):

If you leverage HTML, you can format as much as you want, you just need to ensure it works with the mail clients. You can get some ideas in this ebook.

The source data isn’t an entire table, it’s rows in an existing table in a spreadsheet. The number of rows can change weekly, so hardcoding a number in, or just dumping the table won’t work. So the manual selection is required. There’s any number of different email destinations involved, so that’s a human process as well, although choosing from a list is pretty easy to code up.

The reasons for using outlook are first, the person who’d be using this is familiar and comfortable with it. Secondly, displaying the message gives them a chance to sanity-check before sending, making sure they’re not sending the wrong data to the wrong email address, and thirdly, if they need to pull a copy of an older email up, that’s much easier for a non-technical person to do in outlook.

Figured it out:

#set up the first part of the email. Note that for this, `n is the newline character
$theBodyText="This is the body of the email that will be in front of the table we're pasting in from excel`nnewline.`n`n"

#get the running copy of outlook

#create a new message with the specified destination email address/subject/

#get the running copy of excel

#get the selected cells in the current workbook
$theSelectedCells = $theExcelApp.Selection

#copy the selection to the clipboard

#get the actual message body object

#get the message body "selection". If there's no actual selection, this gets the current cursor position
#in a brand new outlook message, this is at the top of the body over the signature (if any)

#insert the body text at the "beginning" of the cursor position

#move the cursor to the end of the message body. This does not move to the end of the sig, it stays above that
#note that if you don't do this, what you just inserted is "selected", so anything new you insert obliterates
#the existing body

#get the content of the clipboard as HTML. This makes those cells into an HTML table
$theCopiedCells = Get-Clipboard -TextFormatType Html

#insert the html content into the message where we want it to be.

#display the new message for the hu-mon

Glad you figure it out!!