Manipulate Excel objects from within Outlook, using VBA

Posted on 2013-12-16
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
  • 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 :-)


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);_(* ""-""??_);_(@_)"
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now