Bold First Name in a VBA generated Email

[Shywawa]

Hi,

I have build a macro to send/display an email for a column of email ids (this is not mine entirely, inspired). This email has some text in it but also the name of the person i am sending the email to. This name occurs a few times in the email and i need this to be bold.
My problem is that i am getting this from a Instr formula, i have looked around but the HTML solution i found won't really do for me (don't know HTML).

This is my code, let me know if you have any idea on how to make it work.

Capture.jpg


What i need it to do is:

"
Hi Shywawa,

Nice to meet you Shywawa, no.

kind regards,
"


Thank you!

Shywawa

[Paul_Hossler]

It's very difficult to work with a picture of the macro

Use the [#] icon to insert CODE and /CODE tags, and then paste the macro between them, or include a workbook as an attachment

My signature has more details

[Dave]

Hi Shywawa and Welcome to this forum. 77 views and no replies yet. I'll get U started. I have no experience VBAing outlook. I'm not sure why U can't just BOLD the names before U copy and paste... I'm not sure if that would work? Anyways, as far as you code above, I can't google "strconversion" so I assume it is a custom function. So...

'Str = strconversion(Left(Cells(Cells.Row, "A"), InStr(1, Cells(Cells.Row, "A"), ".") - 1), 3)
'Str= function?( Left( left chars of this string, char position of "." in str as val -1) 3? function input)                    )
'the strconversion function is getting the first X numbers of characters from the "A" row string
'up to "." but doesn't include it (ie. X chars -1)
'then the strconversion is doing something with 3 of the X chars (maybe?) ie. strconversion(( X numbers of chars),3))

It doesn't seem like that's what your trying to achieve. Please post code. HTH. Dave

[Shywawa]

Hi,

i keep getting issues with posting the code here , hence the bad pic
(Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words.)

i have attached the file

work macro.xlsm

thank you!

[Dave]

Well that strconversion thing is a real thing. That line of code does produce the name output that U want. According to the great google...

"<b>" & StrConv(Left(Cells(cell.Row, "A"), InStr(1, Cells(cell.Row, "A"), ".") - 1), 3) & "</b>"

HTH.Dave
Here's the code. U need t have sum number of posts before U can post code. The above in code...

Sub email()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Application.ScreenUpdating = False
    On Error GoTo cleanup
    For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)
    
        If cell.Value Like "?*@?*.?*" Then
            On Error Resume Next
            With OutMail
               .To = Cells(cell.Row, "A") & ";" & Cells(cell.Row, "B")
                .Subject = "Reminder"
                .Body = "Dear " & StrConv(Left(Cells(cell.Row, "A"), InStr(1, Cells(cell.Row, "A"), ".") - 1), 3)  & "," _
                      & vbNewLine & vbNewLine & _
                        "Please contact us to discuss bringing " & _
                        "your account up to date"
                .Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

[Shywawa]

Hi Dave,

Thank you for the help!
I have tried the code with <b></b> but it doesn't work.
I even switched to .HTMLbody but i still get a VBA error : Compile Error / Expected expression. This is highlighting the "<" from the first <b>.
The macro won't run.

Please advise.

thanks!

[Dave]

Sorry Shywawa that seems to be the only google fix. Unfortunately I don't have outlook installed on this pc and can't test your code. Hopefully others will tag in and offer U assistance. Good luck, Dave
ps. note that your code is missing this..

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(olMailItem)

[Paul_Hossler]

Hi,

i keep getting issues with posting the code here , hence the bad pic
(Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words.)

i have attached the file

work macro.xlsm

thank you!

Attachment is OK, but you can use the [#] icon to insert CODE tags and paste the macro code between them

Example:

[CODE]macro code goes here[/CODE]

Sub email()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)
   If cell.Value Like "?*@?*.?*" Then
      Set OutMail = OutApp.CreateItem(0)
      On Error Resume Next
      With OutMail
         .To = Cells(cell.Row, "A") & ";" & Cells(cell.Row, "B")
         .Subject = "Reminder"
         .Body = "Dear " & StrConv(Left(Cells(cell.Row, "A"), InStr(1, Cells(cell.Row, "A"), ".") - 1), 3) & "," _
         & vbNewLine & vbNewLine & _
         "Please contact us to discuss bringing " & "your account up to date"
         .Display
      End With
      On Error GoTo 0
      Set OutMail = Nothing
   End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

1. I'd suggest using Option Explicit at the top of the module to require all variables be explicitly Dim-ed

2. Also I think a standard module and not a worksheet module would be a better location for the macro. In the editor, Insert, Module

3. Some one in the Outlook forum might have suggestions

[p45cal]

This worked here; replace your .body = line with

.htmlBODY = "Dear <STRONG>" & StrConv(Left(Cells(cell.Row, "A"), InStr(1, Cells(cell.Row, "A"), ".") - 1), 3) & "</STRONG>," _
    & "<p>" & "<p>" & "Please contact us to discuss bringing your account up to date"

2018-01-21_174949.jpg
You say you want to use it several times in the message, so perhaps something like:

.htmlBODY = "Dear " & BoldName & "," & "<p>" & "<p>" & "Please contact us to discuss bringing your account in the name of " & BoldName & " up to date"

or a bit shorter:

.htmlBODY = "Dear " & BoldName & ",<p><p>Please contact us to discuss bringing your account in the name of " & BoldName & " up to date"

where beforehand you've set up the variable BoldName like:

BoldName = "<STRONG>" & StrConv(Left(Cells(cell.Row, "A"), InStr(1, Cells(cell.Row, "A"), ".") - 1), 3) & "</STRONG>"

2018-01-21_175712.jpg

[Shywawa]

Hi P45cal,

Works like a charm!

thank you all very much!


Kind regards,
Shywawa