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.

21408


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

21413

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

21413

thank you!


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

Example:


macro code goes here




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"
21417
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>"
21418

[Shywawa]

Hi P45cal,

Works like a charm!

thank you all very much!


Kind regards,
Shywawa