Solved

Manipulate Excel objects from within Outlook, using VBA

Posted on 2013-12-16
8
484 Views
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.
0
Comment
Question by:newparadigmz
  • 5
  • 3
8 Comments
 
LVL 10

Expert Comment

by:broro183
ID: 39728971
hi,

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


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

Thanks
Rob
0
 

Author Comment

by:newparadigmz
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.
0
 
LVL 10

Expert Comment

by:broro183
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 :-)

Rob
0
 

Author Comment

by:newparadigmz
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);_(* ""-""??_);_(@_)"
example.xlsx
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 10

Expert Comment

by:broro183
ID: 39730082
hi,

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,

Rob
0
 
LVL 10

Accepted Solution

by:
broro183 earned 500 total points
ID: 39735643
hi,

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?

hth
Rob
0
 

Author Closing Comment

by:newparadigmz
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!
0
 
LVL 10

Expert Comment

by:broro183
ID: 39740046
Thank you for the points :-)

Rob
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

If you don't know how to downgrade, my instructions below should be helpful.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

867 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

16 Experts available now in Live!

Get 1:1 Help Now