none
Copy rows from excel to outlook mailbody RRS feed

  • Question

  • Hi,

    I wanted to copy some set of rows from excel to outlook mailbody based on conditions.with the help of html i have achieved this.but i have problem with the formatting of rows(In the mail rows are just getting copied without the formatting properties).In the excel the rows are red coloured and also some fields are filled with amount values and dates like(but the same is not replicating in the mailbody)

    -2,43,000(commas are missing in mail,getting as 243000)

    -Date format 21-Jul-2014(printing as 07/21/2014 in the mail)

    Is there any html tag to fix this problem.

    I am using Ms-Outlook 2010 and Ms-Excel 2010.Given below is the sample of my code

    Set DictEmails = New Scripting.Dictionary
        LastRow = obj.Range("A" & obj.Rows.Count).End(xlUp).Row
        CountDest = 6
        While (CountDest <= LastRow)
                
        
                If Not DictEmails.Exists(Trim(obj.Cells(CountDest, 17).Value)) Then
               
                
                msgbody = "<html><body>"
                msgbody = msgbody + "<tr><font color=black>"
                msgbody = msgbody + "Hi,<br><br>Please see the below  as of: " & Day(Now()) & "/" & Month(Now()) & "/" & Year(Now()) & ".Can you please investigate and take action as necessary?<br><br><table border=1 cellspacing=0 width=300 height=50>"
                msgbody = msgbody + "<tr><font color=black><th align=center bgcolor=#CCECFF>Code</th><th align=center  bgcolor=#CCECFF width=300>Name</th><th align=center  bgcolor=#CCECFF>Portfolio Type</th><th align=center  bgcolor=#CCECFF>Status</th><th align=center  bgcolor=#CCECFF>Expiry</th><th align=center  bgcolor=#CCECFF>Band</th><th align=center  bgcolor=#CCECFF>CCY</th><th align=center  bgcolor=#CCECFF>Cap (CCY)</th><th align=center  bgcolor=#CCECFF>Availability (CCY)</th><th align=center  bgcolor=#CCECFF>Committed</th><th align=center  bgcolor=#CCECFF></th><th align=center  bgcolor=#CCECFF>Excess Status</th><th align=center  bgcolor=#CCECFF>Excess Amount (CCY)</th><th align=center  bgcolor=#CCECFF></th></font></tr>"
                For i = 1 To 16
                msgbody = msgbody + "<td align=center bgcolor=#E6B8B7> "
               
                msgbody = msgbody + CStr(obj.Cells(CountDest, i).Value)
                msgbody = msgbody + "</td>"
                Next i
                 msgbody = msgbody + "</tr>"
                DictEmails.Add Trim(obj.Cells(CountDest, 17).Value), msgbody
                Else
                msgbody = DictEmails.Item(Trim(obj.Cells(CountDest, 17).Value))
                msgbody = msgbody + "<tr><font color=black>"
                For i = 1 To 16
                msgbody = msgbody + "<td align=center bgcolor=#E6B8B7> "
                msgbody = msgbody + CStr(obj.Cells(CountDest, i).Value)
                msgbody = msgbody + "</td>"
                Next i
                msgbody = msgbody + "</tr>"
                DictEmails.Item(Trim(obj.Cells(CountDest, 17).Value)) = msgbody
               
                End If
        
                CountDest = CountDest + 1
       
        
      Wend

    Any help would be appreciated.

    Thanks in advance.

    Wednesday, August 20, 2014 11:02 AM

Answers

  • Hi Renu,

    Range.Value Property (Excel) is used to get the value of the specified range, it will not contain format of this range.

    For example:

    The displayed text of "B1" is "21-Jul-2014", but the actual value of this range is "7/21/2014". If you use Range.value, you will get "7/21/2014".

    Another example:

    In this case, you need to use Range.Text Property (Excel) to get displayed text of a range.

    Regards,

    George


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 21, 2014 6:44 AM
    Moderator

All replies

  • Hello Renu,

    What code do you use for preparing the MailItem object in Outlook?

    Make sure that you set the HTMLBody property, not Body. The Chapter 17: Working with Item Bodies describes all possible ways for setting the body.

    Also you may find the How to automate Outlook from another program article helpful. It describes the required steps for automating Outlook from Excel.

    Wednesday, August 20, 2014 11:17 AM
  • Hi Renu,

    Range.Value Property (Excel) is used to get the value of the specified range, it will not contain format of this range.

    For example:

    The displayed text of "B1" is "21-Jul-2014", but the actual value of this range is "7/21/2014". If you use Range.value, you will get "7/21/2014".

    Another example:

    In this case, you need to use Range.Text Property (Excel) to get displayed text of a range.

    Regards,

    George


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 21, 2014 6:44 AM
    Moderator