none
VBA email body font RRS feed

  • Question

  • Hi all!

    I need help in changing a specific part of the email body to be in Red and bold.

    below is a portion of the code.

            iTo = Bcell.Offset(0, 5)
                    
            iSubject = "FIRST REMINDER - IN/SSGIFR no. " & Bcell.Offset(0, -2)
                    
            iBody = "Dear all," & vbCrLf & vbCrLf & _
            "IN/SSGIFR No. " & Bcell.Offset(0, -2) & " - " & Bcell.Offset(0, 1) & " (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
            Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on " & _
            cell & "." & vbCrLf & "Please ensure that the consignment is closed by the due date and forward the closure reports ASAP." & _
            vbCrLf & vbCrLf & "Thank you" & vbCrLf & vbCrLf & "Regards," & vbCrLf & "xxx Department" & _
            vbCrLf & "Company Pte Ltd."

    How the email looks like:

    Dear all, <o:p></o:p>

    IN/SSGIFR No. IN1662481 - Calculator (Batch: GDL7900, Qty: 13000 x 25), notified on 26/10/2014 will be
    due on 25/10/2016.
    <o:p></o:p>

    Please ensure that the consignment is closed by the due date and forward the closure reports ASAP.
    <o:p></o:p>

    Thank you <o:p></o:p>

    Regards,
    xxx Department
    Company Pte Ltd.

    _______________________

    would like to bold and red-font the due date. Have tried things like <p><b> </b></p> etc from what other sites have answered but doesn't seems to work probably because its not a quoted text.<o:p></o:p>



    <o:p></o:p>



    Tuesday, October 18, 2016 8:11 AM

Answers

  • Hi,

    We could use <b></b> to bold and <font color="#FF0000"></font> to color as red for the HTMLBody

     

    You could use the following in your function.

    .BodyFormat = olFormatHTML

    .HTMLBody =iBody 

    You could try the following string. I am setting Bcell.Offset(0, -2) as Bold and Bcell.Offset(0, 1) as Red.

     

    iBody = "<p>Dear all,</p>" & _
    "<p>IN/SSGIFR No. <b>" & Bcell.Offset(0, -2) & "</b> - <font color=" & """#FF0000""" & ">" & Bcell.Offset(0, 1) & "</font> (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
    Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on " & _
    ".<br />Please ensure that the consignment is closed by the due date and forward the closure reports ASAP.</p>" & _
    "<p>Thank you</p><p>Regards,<br />xxx Department<br />Company Pte Ltd.</p>"


    Wednesday, October 19, 2016 9:37 AM
    Moderator

All replies

  • not really sure why there are <o:p></o:p>, but please do ignore
    Tuesday, October 18, 2016 8:12 AM
  • If it is formatted in HTML then you can <b> tag but it seems plain text. Then you can use it to set HTMLBody of Mail Item.

    Sub asa()
    
        Dim MI As MailItem
        
        ibody = "<html>This is just to show<p> how we can get<p> <b>bold</b> and <i>Italic</i>text</html>"
                
        Set MI = Application.CreateItem(olMailItem)
        
        MI.HTMLBody = ibody
        
        MI.Save
            
            
    End Sub
    


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, October 18, 2016 2:43 PM
    Answerer
  • Hi! Thanks for the response!

    Where of my code do I add the portion of code you have suggested? Am new to macro and would be great if you could guide me along!

    Also I notice in the example you wrote, everything is within 1 quotation mark. The portion I wanted to change is not in a quotation mark as it is taken from the excel cell itself (namingly BCell)

    Tuesday, October 18, 2016 2:52 PM
  • That was a sample to show you how it can be made Bold. Run it in outlook as test . Is it doing what you want ?

    Just a part of your actual string. See how <b> tag used.

    "due on <b>" & cell & "</b>." & vbCrLf & "Please ensure that"

    You just use the string HTMLBody property of MailItem


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, October 18, 2016 5:42 PM
    Answerer
  • Apologies, but still have no idea which part of my code to change with reference to:

    Sub asa()
    
        Dim MI As MailItem
        
        ibody = "<html>This is just to show<p> how we can get<p> <b>bold</b> and <i>Italic</i>text</html>"
                
        Set MI = Application.CreateItem(olMailItem)
        
        MI.HTMLBody = ibody
        
        MI.Save
            
            
    End Sub

    Do I Add this whole part under my current code?  then add in <b> and </b> in each of my email template codes?

    Wednesday, October 19, 2016 1:07 AM
  • I tried copy pasting the whole thing and it doesn't work.

    Do I copy it to be together with:

    Private Sub SendEmail()
    
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
        
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
            
        With OutMail
            .To = iTo
            .CC = "DEPARTMENT@EMAIL.COM" & ";COLLEAGUE@EMAIL.COM"
            .BCC = ""
            .Subject = iSubject
            .Body = iBody
            .Importance = ImportanceLevel
            'You can add a file like this
            '.Attachments.Add ("C:\test.txt")
            .Display
        End With
    
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub


    • Edited by SakuraiHiro Wednesday, October 19, 2016 1:18 AM
    Wednesday, October 19, 2016 1:17 AM
  • Hi,

    We could use <b></b> to bold and <font color="#FF0000"></font> to color as red for the HTMLBody

     

    You could use the following in your function.

    .BodyFormat = olFormatHTML

    .HTMLBody =iBody 

    You could try the following string. I am setting Bcell.Offset(0, -2) as Bold and Bcell.Offset(0, 1) as Red.

     

    iBody = "<p>Dear all,</p>" & _
    "<p>IN/SSGIFR No. <b>" & Bcell.Offset(0, -2) & "</b> - <font color=" & """#FF0000""" & ">" & Bcell.Offset(0, 1) & "</font> (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
    Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on " & _
    ".<br />Please ensure that the consignment is closed by the due date and forward the closure reports ASAP.</p>" & _
    "<p>Thank you</p><p>Regards,<br />xxx Department<br />Company Pte Ltd.</p>"


    Wednesday, October 19, 2016 9:37 AM
    Moderator
  • It works! Thanks a lot!

    On a rather unrelated to the topic above, how do I get the macro to work on multiple sheet at once. Meaning after opening the workbook, email reminders for sheet 1,2,3...(all sheets) will all be sent. Do I place the macro into myworkbook, or into each individual sheet or create a new module?

    Also I would like if there is a macro or some solution to getting the excel saved automatically without prompt as I intend to use task scheduler to open the excel, generate the emails required and close after doing so.

    Really appreciate your help!

    Wednesday, October 19, 2016 2:27 PM
  • Hi,

    Since this original issue has been resolved and these two issues are different from the original one, I suggest you create and post new threads for these issues. More community members to help you and we could focus on the specific issue.

    Besides, please vote to helpful reply and mark it as answer if the post helps you resolve the issue.

    Thanks for your understanding.

    Regards,

    Celeste

    Thursday, October 20, 2016 2:26 AM
    Moderator
  • Alright! Thank you so much for your help! appreciate it alot
    Thursday, October 20, 2016 5:58 AM
  • Hi! Am In urgent need of help. How do I change the font to arial? for some reason the default font is times new roman.

    Edit: manage to resolve on my own. If possible could you look through if there are ways to make the code more neat/efficient?

            iBody = "<font face=""Arial"">" & "Dear all,<br/><br/>" & _
            "IN/IFR No. " & Bcell.Offset(0, -2) & " - " & Bcell.Offset(0, 1) & " (Batch: " & Bcell.Offset(0, 3) & ", Qty: " & _
            Bcell.Offset(0, 2) & ")" & ", notified on " & Bcell.Offset(0, -1) & " will be due on <b><FONT COLOR=#ff0000>" & _
            Bcell & "</font></b>." & " Please ensure that the consignment is closed by the due date and forward the closure reports ASAP." _
            & "Thank you<br/><br/>" & "Regards,<br/><br/>" & "yyy Department<br/>" _
            & "xxx Ltd.<br/>" & "</font>"







    • Edited by SakuraiHiro Tuesday, October 25, 2016 1:38 AM
    Tuesday, October 25, 2016 1:24 AM
  • Hi,

    >>If possible could you look through if there are ways to make the code more neat/efficient?

    Unfortunately, there is no brief way to format HTMLbody.

    For more information about using the tags in HTMLbody, please visit Working with Item Bodies

     

    Besides, since the original issue has been resolved, if there are any new issues, I suggest you post new threads for new issues, so that more community members would see the new thread and help you.

    Thanks for your understanding.

    Regards,

    Celeste

    Tuesday, October 25, 2016 7:16 AM
    Moderator