Manipulate Excel objects from within Outlook, using VBA

Posted on 2013-12-16
Medium Priority
Last Modified: 2013-12-26
I've seen the usual internet templates for dealing with email, in Excel/Outlook, but can't find this functionality. Please help.

Very simply, take a table from Excel, paste into an Outlook email, and then resize that table from within Outlook and left align the columns.

This involves using what I think is called the Word Editor from within Outlook, which is necessary because

1. Excel will not left align numbers with format 1,000,000
2. The tables look prettier and I need to paste multiple separate tables with different column alignments.
Question by:newparadigmz
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 10

Expert Comment

ID: 39728971

1) Does the below macro work for you to left align the selected cells?

Option Explicit

Sub LeftAlignmentOfValues()
    With Selection 'activecell
        .NumberFormat = "#,##0_ ;[Red]-#,##0 "
        .HorizontalAlignment = xlLeft
        .[/b]VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Open in new window

Can you please upload a sample file with examples of the table ranges that you need to paste into the email?


Author Comment

ID: 39729403
That did work, Thanks!

The table is not special. It's just any range.

On Excel, copy a range. On an Outlook email, paste that range inside. It comes out ugly/too large, so on the bottom right corner, you select the resizer thing and drag to the upper left to make it fit.

How do you do that in VBA? Editing in Excel before the copy does not work.
LVL 10

Expert Comment

ID: 39729475
Please provide an example file because:
- my interpretation of "ugly/too large" may be quite different to yours.
- I may create a range where I believe that "Editing in Excel before the copy does work."
In other words, when you provide a sample it becomes more likely that I can replicate your situation and provide a better solution :-)

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 39729672
attached, two ranges pasted to one email, resized so that the email does not need horizontal scrolling when full screen.

Also, the left align did not work for this format after all...

.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
LVL 10

Expert Comment

ID: 39730082

Thank you for attaching the example. I may not be able to have a proper look at this until later this weekend but I will give another suggestion.

I'll also try & figure out why that number format won't play nicely & if there is an alternative format that will give a similar visual appearance which will play nicely,

LVL 10

Accepted Solution

broro183 earned 1500 total points
ID: 39735643

I've found an answer for the formatting by reading through this KB page (#264372). The right alignment is defined by "* " being placed at the left of each section because this instructs the left side of the cell to be filled with repeated space characters ie the spaces forces the value to the right of the cells. Once I moved the "* " to the right of each section my earlier code works. Although, I think that the change in formatting means that the code probably becomes unnecessary.
_(#,##0_)* ;_((#,##0)* ;_("-"??_);_(@_)* 

Open in new window

(Note, there is a space at the right hand end of the above string.)

Do you still want code to answer question number 2?


Author Closing Comment

ID: 39740039
Thanks for the reference!

No, while I am curious on how to access the word editor on outlook using excel, I can live with just using the rangetohtml method found online, in combination with your solution.

Thanks again!
LVL 10

Expert Comment

ID: 39740046
Thank you for the points :-)


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question