Send Excel data in outlook body with borders

Send Excel data in outlook body with borders

I was working with somebody the other
day and they wanted to send data from their excel sheet in in the body of
their outlook mate email and then he said said this request can we copy the
cells with the lines of border tables not just the pure text to the data so I
worked on this for a bit and so here’s my sheet now the big thing here is is I
put the border lines on by selecting it and of course going to the Home tab and
selecting borders and then I slice I selected all borders and then I copied
or I put a fill in the background with using that method so this is the data
that I want to send on my email the code that I found and manipulated so what
I’ve done here is I I started out at the at the top here and I said send email
table and then I was out defining I’ll look as an object new email as an object
now X inspect has an object page editor as an object and then I’m setting
outlook to outlook create object outlook application send set new email to
Outlook create item and then we’re going through the with the new email you go to
and I’ve got the email address ting range a two and then I’m blank or carbon
copies blank blind carbon copies blank and my subject is data and the
body is please find the requested information and then a character turn in
best regards and then we’re going to do set set X inspector new email get
inspector set page editor X inspect word editor emphasis on the word editor so
reason the word editor to work to edit our outlook email okay and then I’ve
selected the range where my data is and so that is
b2 to e5 and copy and then with page editor application selection start
equals length dot body page editor application selection end is page editor
application selection start page editor application select now instead of just
paste here I’ve said paste and format this is I looked this up and this is in
the VB forward and then word format plain text and then I’ve got display
Dotson set page edge there nothing said inspector – nothing end with set email
so we’re clearing all our fields set new email to nothing you said Outlook to
nothing and then in sub so let’s run this so basically this is what the end email
will look like when you when it sends the data so that’s the data has the the
table and the borders around it so that’s how you send data in the body of
the email with the formatting including the borders and the colors on it please

77 thoughts on “Send Excel data in outlook body with borders”

  1. Hi Berb Henderson,

    I am using Windows 2007 version.
    When I using your code paste in module as well as click the run button then a msg show like
    compile error:
    syntex error

    Now you tell me how to solve this problem… waiting your replay⚘
    Good Night…

  2. Hi Berb Henderson,

    I am using Windows 2007 version.
    When I using your code paste in module as well as click the run button then a msg show like
    compile error:
    syntex error

    Now you tell me how to solve this problem… waiting your replay⚘
    Good Night…

  3. Hi Berb Henderson,

    Mam, again i used your code now any msg not show me but when i click the run button outlook body open stay with few seconds thereafter close the outlook body.
    Any solution said problem pls tell me… Thank You⚘

  4. Tried it and it works. Thanks Barb. I like your code, simple and get to the point.
    Easy to be understood by a non macro expert like me

  5. Hi im running into a peculiar error. If an instance of outlook 2016 is open prior to the running of the macro it throws the error "the item has been moved or deleted". If no instances are open then the macro works fine. The error / debug is at the "Set xInspect = newEmail.GetInspector" line. Do you know whats causing this error and how to resolve this? Thanks.

  6. Hi barb,
    How we can send same excel file as attachment with email body (as shown in video).
    For example I want to send excel file that contains data and in email body I want to send pivot table range of that data.

  7. Thanks for this video.

    I want to know how to send the Excel data with use range of any person's name or code or drop down or filter via VBA paste in outlook mail body with the common mail draft..

    If possible please help me friend.

  8. Hello Barn,

    Thanks for the code

    Facing Run-time error '287';
    Application-defined or object-defined error in Excel 2016

    Debug points to
    Set pageEditor = xInspect.WordEditor

    I have added Tools >> Reference Microsoft Word 12.0 Object Library.

    Please advise what needs to be to done in order to resolve this issue.


  9. Hello Barb,
    I am facing a "Run-time error '287' : Application defined or object-defined error" @ "Set pageEditor = xInspect.WordEditor". I am using office 2016 version. I have added "Microsoft Word Object Library" from the reference list. But it ain't made any impact either. It would be really helpful if you could guide me on this.

  10. Hello Barb,

    great job, tks!!
    I have this Error on: "pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)"
    Err 91 Object variable or with block variable not set

    I checked all typed words, the code seems correct…
    can you/someone help me to solve please?

  11. Dear madam,
    I really appreciate your work and i m Loving it.😘
    In this video i needed an help the data which you took was the starting value and the ending value which was E5. But in my case my ending data varies on every mail. For example if in one mail my ending data is" E5 "then in other it will be "E25"
    Kindly help me in this error.

  12. Thanks Barb, is there a way to include place holders in the body of the email. For example : Dear [@Name]. The name would be picked from excel.

  13. Hi Mam, the code in this video is working fine for me… but the email gets stuck in the outbox of outlook. It will remain there till I open the outlook. I am using outlook 2010. Please provide a fix for that. Thanks

  14. This is excellent. Thank you.  I have been going nuts. I trap my selected range in an application.InputBox. How would I modify your code to incorporate that> The variable name for the selection is MySelection. Thank you very much.

  15. My colleagues are getting error at (wdformatrichtext) step in 2010 and 2016. Is there any set up I need to do in Excel or outlook to fix this

  16. I chance upon your video. In my opinion, you presents the MOST ELEGANT solution to this problem! So, thanks for sharing your expertise.

    I'm also looking to add my signature to the end of your solution, but it doesn't seem to work when I add "& .HTMLBody" to the end of the .Body line. Would you be kind enough offer a solution to add a signature AFTER the table is pasted? Many thanks!!

  17. Hi barb,
    I wanted to add multiple recipients r users in the 'To list' r 'cc list'
    Hope u understand
    ll be helpful for ur kind response

  18. Thanks for your help Barb. Great tutorial. Short. Precise. Great audio. Not a second wasted. Good code that I can use.

  19. I tried this but i am getting error (Error 438 Object doesn't support this property or method). Can you please help me with this error

  20. Hello Barb. Thank you for this great video!
    I have one question though. I do select (A1:E11) and some of the cells have a border but not all. (There is a company name and logo in A1 and B1.) All cells have a formatted white background. Some cells have a black border and in the advanced options for the worksheet I have unchecked the show grid lines.
    In the created email all cells do have their initial grey cell borders. It seems to ignore the forced 'disabling' of borders. Does your mighty pool of wisdom offer a quick fix for that also? 🙂 Highly appreciated!

  21. hi, how do i keep the format after the past as its getting distorted in the email paste and how do i send to multiple recipients

  22. Hi Barb, is it possible to do this, but say on a much larger scale – like one email per row with 8 columns of data for each row and about 250 rows – hence 250 emails?

  23. Thank you Barb… this is very helpful. But how do i add additional text after the table? for example, "Kind Regards," then my signature part.

  24. Hi Barb, is there a way to save this to my personal macro workbook and it still work. Currently it pastes blank cells? all is there away to paste conditional formatting from the source?

  25. I want this using SMTP Method.. because i dont have MS outlook installed in my server. can any one please help!!!

  26. Hi. For anyone struggling with adding text after the table try pageEditor.Range.InsertAfter "some text". It worked for me.

  27. Hi Barb, i tried and it worked successfully thank you so much for your efforts. i just have one question, how could i insert me default signature in the e-mail?!

  28. As always knowing a little about VBA, I looked for the shortest video. I saw some up to 20 minutes long.

    You laid it out perfectly in less than 4.

    I know exactly what to do now.

    Thanks SO MUCH.

  29. Hi guys, great tutorial. I am looking to code a macro that send emails but with tables with differents sizes and rows…

  30. Hi Barb, thanks for this! It worked fine for me until it needed to copy the table. I encountered a runtime error – file is locked for editing. I read that you have to either delete or set as read-only this hidden file under Excel folder but I just can't find it. Help? Any other way to get past this?

  31. hi Barb i tried the macro, I could generate the table in the mail body but i have a data who's range is varying day to day how can i update the mail till the last row

Leave a Reply

Your email address will not be published. Required fields are marked *