VBA Code to make a strung variable bold and underline

mecerrato · Apr 13, 2023

I have this working code but would like to apply bold and underline to this line of code:

.InsertBefore "Please Review, loan not financeable for bond in its current state." & vbCr & vbCr & boldtext & vbCr & Worksheets("Manager Report").Range("C32").value _

I tried using a variable called boldtext and am using the /b to bold but it displays the email as if it were regular text with the /b as if it wer part of the string.
How can I tweak this to make the text bold and underline?

I alos posted in Stackoverflow

VBA Code to make a strung variable bold and underline

I have this working code but would like to apply bold and underline to this line of code: .InsertBefore "Please Review, loan not financeable for bond in its current state." & vbCr &

stackoverflow.com

VBA Code:

Public Sub ScreenShotResults4_with_Current()
Sheets("Manager Report").Unprotect Password:="Mortgage1"
            Dim Rng As Range
            Dim olApp As Object
            Dim Email As Object
            Dim Sht As Excel.Worksheet
            Dim wdDoc As Word.Document
            Dim boldtext As String
            boldtext = "<b>Issue:</b>"
Call HideAllImages

            Set Rng = Sheets("Manager Report").Range("C4:F35")
                Rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
            'Sheets("Summary").Branch_ChkBox.Visible = False
            
            'Row1Circle Sheets("Summary").CheckBoxes("Branch_ChkBox").Visible = False
            
            With Application
                .EnableEvents = False
                .ScreenUpdating = False
            End With
        
            Set olApp = CreateObject("Outlook.Application")
            Set Email = olApp.CreateItem(0)
'            Set wdDoc = Email.GetInspector.WordEditor
        
            'strbody = "See production data for most recent 3 months.  "
        
            With Email
                .To = Worksheets("Manager Report").Range("F2").value
                .CC = Worksheets("Manager Report").Range("F3").value & ";" & "kirk.hudson@lgimortgagesolutions.com" & ";" & "carrie.budds@lgimortgagesolutions.com" & ";" & "Courtney Barreto" & ";" & Worksheets("Manager Report").Range("D5").value
                If Worksheets("Manager Report").Range("F24").value = "Not Financeable" Then
                .Subject = "Manager Report " & Worksheets("Manager Report").Range("D6").value & " " & Worksheets("Manager Report").Range("D4").value & _
                " (" & Worksheets("Manager Report").Range("F5").value & " )" & " NOT FINANCEABLE"
                End If
                If Worksheets("Manager Report").Range("F24").value = "Additional Docs Needed to Proceed" Then
                .Subject = "Manager Report " & Worksheets("Manager Report").Range("D6").value & " " & Worksheets("Manager Report").Range("D4").value & _
                " (" & Worksheets("Manager Report").Range("F5").value & " )" & " MAYBE"
                End If
                If Worksheets("Manager Report").Range("F24").value = "Submitted to Processing" Then
                .Subject = "Manager Report " & Worksheets("Manager Report").Range("D6").value & " " & Worksheets("Manager Report").Range("D4").value & _
                " (" & Worksheets("Manager Report").Range("F5").value & " )" & " GOOD-TO-GO"
                End If
                
                
    '            End If
                '.HTMLBody = "<BODY style=font-size:12.5pt;font-family:Calibri>" & "</p>" & strbody & RangetoHTML(rng) & Signature
                .Display
                        Set wdDoc = Email.GetInspector.WordEditor
                wdDoc.Range.PasteAndFormat Type:=wdChartPicture
            
                'if need setup inlineshapes hight & width
    With wdDoc.Content
        '--- paste the range image first, because it overwrites
        '    everything in the document
        
        .PasteAndFormat Type:=wdChartPicture
        .InlineShapes(1).Height = 750
        
        '--- now add our greeting at the start of the email
'        .InsertBefore "See current year production data and current pipeline. " & vbCr & vbCr
        If Worksheets("Manager Report").Range("F24").value = "Not Financeable" Or Worksheets("Manager Report").Range("F24").value = "Additional Docs Needed to Proceed" Then
        .InsertBefore "Please Review, loan not financeable for bond in its current state." & vbCr & vbCr & boldtext & vbCr & Worksheets("Manager Report").Range("C32").value _
        & vbCr & vbCr & "Solution:" & vbCr & Worksheets("Manager Report").Range("C38").value & vbCr & vbCr ' & vbCr & .Font.Bold & "<u><b>Issue: </u></b>"
        Else
        .InsertBefore "Please Review, loan looks good, submitted for processing. " & vbCr & vbCr
        End If
        '--- finally add our sign off after the image
        .InsertAfter vbCr & vbCr & _
                     "Thank you" & vbCr & vbCr
                     
    End With

        
                .Display
            End With
        
            With Application
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        
            Set Email = Nothing
            Set olApp = Nothing
    
    'Sheets("Summary").Range("K17").Visible = True
    'Sheets("Summary").Protect Password:="Mortgage1"
Call ShowAllImages
End Sub

Micron · Apr 13, 2023

Can't use rtf or html formatting in plain text body if that's what you're saying you have. Must be html or rtf. Try
.BodyFormat = olFormatHTML before .HTMLbody line, or if you use rtf, perhaps

.BodyFormat = olFormatRichText then
.RTFbody = "something"

mecerrato · Apr 14, 2023

Micron said:
Can't use rtf or html formatting in plain text body if that's what you're saying you have. Must be html or rtf. Try
.BodyFormat = olFormatHTML before .HTMLbody line, or if you use rtf, perhaps

.BodyFormat = olFormatRichText then
.RTFbody = "something"

I tried this but got error Method or data member not found, I am not a coder, I an amateur

, I am sure I am doing something obviously wrong but cannot figure it out

VBA Code:

If Worksheets("Manager Report").Range("F24").value = "Not Financeable" Or Worksheets("Manager Report").Range("F24").value = "Additional Docs Needed to Proceed" Then
        .InsertBefore "Please Review, loan not financeable for bond in its current state." & vbCr & vbCr & .BodyFormat = olFormatRichText & .RTFBody = "Issues:" & vbCr & Worksheets("Manager Report").Range("C32").value _
        & vbCr & vbCr & "Solution:" & vbCr & Worksheets("Manager Report").Range("C38").value & vbCr & vbCr

Micron · Apr 14, 2023

- you should post the entire procedure unless it's huge. If so, condense if you know how to eliminate the unrelated parts
- you should indicate which line raises the error(s)

That code uses a style that belongs in a With block and I see no With block so it doesn't provide a whole lot of information.

VBA Code to make a strung variable bold and underline

mecerrato

Board Regular