+ Reply to Thread
Results 1 to 30 of 30

Conditional formatting not copying over correctly into body of HTML email

  1. #1
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Conditional formatting not copying over correctly into body of HTML email

    Hi all,

    I've spent DAYS trying to figure this out and it's really getting me frustrated and down

    I'm using Ron de Bruins' VBA code to send an email of a range of cells that have conditional formatting in them. If I manually copy and paste the cells into a new e-mail message, the conditional formatting shows up exactly as displayed in the excel worksheet.

    BUT...when I use a button with a VBA script to copy and paste it into the body of a new Outlook email message, the conditional formatting is all messed up! It doesn't even make sense how it's figuring out the formatting (especially since a normal copy/paste works fine).

    Can someone PLEASE help me figure this one out!?

    I'm using Excel 2010 and Outlook 2010

    Thanks everyone!
    Last edited by seaottr; 09-12-2011 at 11:53 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello seaottr,

    Can you post your workbook so we can see your code and the layout?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Re: Conditional formatting not copying over correctly into body of HTML email

    Thanks for the response Leith. I've included the workbook in this post.

    As you can see, the conditional formatting is fine in the worksheet, and if you manually copy and paste it into the body of an HTML email in Outlook, it shows up perfectly. But when I use the "Email" button to send the range in the body of the email message, all of the fill colours are wonky (or don't show up the BAHT column).

    Any help would be greatly appreciated!

    Once again, I'm using Excel 2010 and Outlook 2010.

    Thanks in advance!
    Attached Files Attached Files

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello seaottr,

    Copy and paste the Module1 macro code into a new VBA module in your workbook. Change the CommandButton2 in the "Results" worksheet module also.

    Module1 Macro Code
    Please Login or Register  to view this content.

    CommandButton2_Click() Event Code
    Be sure to change the email address to what you want to use.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Re: Conditional formatting not copying over correctly into body of HTML email

    Leith!!!! You are a LIFESAVER!!!!!! Thanks SO much! You don't know what a relief this is!!!!

    Thanks again! You rock!!!!

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello seaottr,

    Glad I could help. I still haven't discovered why the other code failed. I ran tests reading the email in Gmail and Outlook and the results were the same. If I discover the cause of problem I will let Ron De Bruin know about.

  7. #7
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hi Leith,

    Do you know how I would update your code so that it only sends the range A1:L## only up until the row that has blanks? (so that it doesn't send all of the blank cells in the email)

    Your help would be greatly appreciated (again)

    Thanks in advance!

    Quote Originally Posted by Leith Ross View Post
    Hello seaottr,

    Glad I could help. I still haven't discovered why the other code failed. I ran tests reading the email in Gmail and Outlook and the results were the same. If I discover the cause of problem I will let Ron De Bruin know about.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello seaottr,

    The change will limit the table to columns "A:J" down to the last row with an entry.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Re: Conditional formatting not copying over correctly into body of HTML email

    Thanks again Leith!

    But is there anyway I can search through only a certain range (A4:J30) for the first blank in column A and email the range A1:J ## (the row that has the blank)?

    I honestly wish I had your excel knowledge and skills...A lot of this is so confusing to me

    Thanks again!

    Quote Originally Posted by Leith Ross View Post
    Hello seaottr,

    The change will limit the table to columns "A:J" down to the last row with an entry.
    Please Login or Register  to view this content.

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello seaottr,

    Since you know which column you want to use as the control for the last row, you can do it this way.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    02-10-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2002
    Posts
    28

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hey Leith,

    I don't think that's working for me It's only sending the first column (column A) in the e-mail and still leaving the blanks too.

    I basically want to have columns A-J and rows 1 to the first blank cell in column A to be emailed to me. So the range would be A1:J # (whatever row number has the first blank in column A). This row number will change based on the number of names in the column.

    Let me know if I'm not explaining it too clearly? It's sort of difficult to describe.

    For example: In the attachment, it would be A1:J24, but other times it may be A1:J29 (depending on where the first blank row is)

    Thanks again!

    Quote Originally Posted by Leith Ross View Post
    Hello seaottr,

    Since you know which column you want to use as the control for the last row, you can do it this way.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    03-13-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hi,

    I tried the above shared code.But it does not copy the Conditional Formatting Data Bars from Excel to body of html e-mail.
    Can you please help.I can share my worksheet if required

    Thanks in advance.

  13. #13
    Registered User
    Join Date
    03-18-2014
    Location
    honduras
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Conditional formatting not copying over correctly into body of HTML email

    The answer that Leith Ross gave at 09-13-2011, 10:20 AM worked beautiful for me. I use windows 7 and office 2007. But when I tried in a computer with windows 8 and office 2013 I get the next error message: "Run-time error '429': ActiveX component can't create object". What could be the cause?

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello daniela9488b,

    I have since updated this macro. This newer version may run on Windows 8, but I have not tested it on Windows 8. Give this a try a let me know.

    Macro to Send Range in Email Body
    Please Login or Register  to view this content.
    Macro Example
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    03-18-2014
    Location
    honduras
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hi Leith,

    I get the same message in windows 8 :/

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello daniela9488b,

    The only ActiveX object being created in this macro is Outlook. Is it installed in Windows 8?

  17. #17
    Registered User
    Join Date
    03-18-2014
    Location
    honduras
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Conditional formatting not copying over correctly into body of HTML email

    Leith,

    Yes, it is installed.

  18. #18
    Registered User
    Join Date
    03-18-2014
    Location
    honduras
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Conditional formatting not copying over correctly into body of HTML email

    Leith,

    Yes, it is installed.

  19. #19
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello daniela9488b,

    Windows 8 is a very different OS and undoubtedly affects Office and VBA. I don't have an answer for you but will look into this problem.

  20. #20
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello daniela9488b,

    If you are using 2013 then it lacks support for Outlook VBA macros. You will need a third party add-in (app).
    Last edited by Leith Ross; 03-19-2014 at 12:31 PM.

  21. #21
    Registered User
    Join Date
    12-14-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hi Leith,

    I was able to use your codes in my file to copy the excel range with conditional formatting, however when I use icon sets or data bars as conditional formatting, the code fails to do so.
    I would really appreciate if you help me with this.
    @ astha : I guess you are also stuck where I am..

    Leith, please help both of us.

    Regards,
    Ashish

  22. #22
    Registered User
    Join Date
    12-10-2014
    Location
    Indiana, US
    MS-Off Ver
    2010
    Posts
    7

    Re: Conditional formatting not copying over correctly into body of HTML email

    Mine is working but it isn't copying the whole entire chart just a portion of it. I tried to edit the range but it's still not working properly. I'm also using Word 2010 on Windows 7. Just curious on how I can get it to grab the whole worksheet.

  23. #23
    Registered User
    Join Date
    12-10-2014
    Location
    Indiana, US
    MS-Off Ver
    2010
    Posts
    7

    Re: Conditional formatting not copying over correctly into body of HTML email

    I figured out how to get it to work with your code . Although I want it to send from [email protected] , and I can't seem to get that to work. Any suggestions.

  24. #24
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Conditional formatting not copying over correctly into body of HTML email

    myles8908,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  25. #25
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Conditional formatting not copying over correctly into body of HTML email

    Curious about this code, Leith could you elaborate here. I brought your code over and am looking to understand what properties carry over the CF and/or if I will need to completely change to your code in order to make this happen.

    Thanks

    -Eddie
    -If you think you are done, Start over - ELeGault

  26. #26
    Registered User
    Join Date
    12-10-2014
    Location
    Indiana, US
    MS-Off Ver
    2010
    Posts
    7

    Re: Conditional formatting not copying over correctly into body of HTML email

    Quote Originally Posted by protonLeah View Post
    myles8908,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Okay sorry. I solved my problem using CDO. Thanks though.

  27. #27
    Registered User
    Join Date
    11-09-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2007
    Posts
    1

    Re: Conditional formatting not copying over correctly into body of HTML email

    I have the same problem than seaottr regarding conditional formatting and I don't seem to be able to aapply the answer from leith to my workbook.

    If the conditional formatting I use is based on a formula it will not work but it will apply the conditional formatting if it's based on a value (greater than for example)

    I have a workbook with many sheets in it, each sheets are used to send a different email when a contingency plan is activated. I have no conditional formatting in PdC Code 2 and Code 3 but I do in Code 4. Cells A8 to A12 needs to appear in red or strike through based on the value of cells C8 to C12. Although the conditional formatting works in Excel it doesn't show in outlook once the email is generated.

    Module 3 is the one associated to pdc code 4. If I can use to original code posted, can you tell me where to paste it as it doesn't seem to work in a new module or in the original module...

    (The texts in the sheets are all in french but everything in VBA is in english)

    Thanks!
    Attached Files Attached Files

  28. #28
    Registered User
    Join Date
    04-10-2017
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    5

    Re: Conditional formatting not copying over correctly into body of HTML email

    Hello,

    Is there a way to modify this to only copy non-hidden columns?

    In a different piece of code i found on net it uses

    Set Rng = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    Set Rng = Selection.SpecialCells(xlCellTypeVisible)

    But doesn't copy the conditional formatting (hence me finding this forum).

    I am ultra new to this to sorry if i ask silly questions

  29. #29
    Registered User
    Join Date
    04-10-2017
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    5

    Re: Conditional formatting not copying over correctly into body of HTML email

    Further to my previous post I think I may get more support if I explain what I am trying to do...

    I am building a data tracker for my research lab, where an analyst can highlight a selection of data and by clicking a button send the data to the research team.

    I have a macro to hide columns that are not on interest. I would like to be able to highlight data in B15:AL14 and only mail out the visible columns, keeping the conditional formatting.

    I found a piece of code that allows me mail out selected cell (excluding the hidden ones) but it does not carry over the conditional formatting.

    The code given is this thread is able to carry across the conditional formatting but brings across hidden cells.

    I would be very grateful if someone could point me in the right direction to modifying the code given in this thread so that it only pastes the visible cells. Code I wish to modify is assigned to button 7 in the attachment. As I previously mentioned, I an very new to this.

    Thank you in advance
    Attached Files Attached Files

  30. #30
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional formatting not copying over correctly into body of HTML email

    Protonspounge welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1