Solved

Manipulate Excel objects from within Outlook, using VBA

Posted on 2013-12-16
8
486 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Find out what you should include to make the best professional email signature for your organization.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

785 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