Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

Manipulate Excel objects from within Outlook, using VBA

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
newparadigmz
Asked:
newparadigmz
  • 5
  • 3
1 Solution
 
broro183Commented:
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
 
newparadigmzAuthor Commented:
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
 
broro183Commented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
newparadigmzAuthor Commented:
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
 
broro183Commented:
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
 
broro183Commented:
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
 
newparadigmzAuthor Commented:
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
 
broro183Commented:
Thank you for the points :-)

Rob
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now