How to insert line break in message in VBA

[pei]

Hello, this is my first time using VBA.

I am using Excel VBA to send messages via Whatsapp.
In my message in excel, there are paragraphs, however, when i try to send the message via whatapp there is no linebreak and I have to Alt+Enter manually.
Is there a code I can add into my VBA to create line breaks?
Please advise. Thank you.

VBA CODE:
Sub Customer()
On Error GoTo errorhandler:
mobilenumber = Selection.Value
Set myrange = Sheet1.Range("B:B")
rowno = Application.WorksheetFunction.Match(mobilenumber, myrange, 0)
Description = Sheet1.Cells(rowno, 3).Value
Rs = Sheet1.Cells(rowno, 4).Value
ActiveWorkbook.FollowHyperlink Address:="Open WhatsApp" & mobilenumber & "?text=" & Description & Rs & ""
End
errorhandler:
Err.Clear
MsgBox "Select Proper Mobile Number "
End Sub


Description:
Hello Gina, How is everything?

We did a video like this and targeted the right type of buyers on Facebook and the outcome was way better than just in guru. Not sure if this can help you but *ALL COSTS* are on us for this!

Maybe we can see if it can apply to your place as well if it's still for sale?

 

[maabadi]

You Can Add Line Break at VBA with VBNewline. example:

"Hello Gina, How is everything?" & vbnewline & "We did a video like this and targeted the right type of buyers on Facebook" & vbnewline & " and the outcome was way better than just in guru." & vbnewline & " Not sure if this can help you but *ALL COSTS* are on us for this!" & vbnewline & "Maybe we can see if it can apply to your place as well if it's still for sale?" & vbnewline & Vbnewline & "Gina (PA to Joel Li R044384D)"

 

[pei]

You Can Add Line Break at VBA with VBNewline. example:

"Hello Gina, How is everything?" & vbnewline & "We did a video like this and targeted the right type of buyers on Facebook" & vbnewline & " and the outcome was way better than just in guru." & vbnewline & " Not sure if this can help you but *ALL COSTS* are on us for this!" & vbnewline & "Maybe we can see if it can apply to your place as well if it's still for sale?" & vbnewline & Vbnewline & "Gina (PA to Joel Li R044384D)"

In which area should i insert this VBA code in?

Also, the name of the person changes in every message.

 

[pei]

You Can Add Line Break at VBA with VBNewline. example:

"Hello Gina, How is everything?" & vbnewline & "We did a video like this and targeted the right type of buyers on Facebook" & vbnewline & " and the outcome was way better than just in guru." & vbnewline & " Not sure if this can help you but *ALL COSTS* are on us for this!" & vbnewline & "Maybe we can see if it can apply to your place as well if it's still for sale?" & vbnewline & Vbnewline & "Gina (PA to Joel Li R044384D)"

Let me show you my excel.

Excel to Whatapp.xltm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	TYPE	PROJECT	ADDRESS	NAME	NUMBER	MESSAGE	CONTACTED ON				HDB		CONDO
2	CONDO			Gina		Hello Gina, How is everything? Are you also facing issues for the listing of your property? No viewings or low offers? Before we found the perfect formula, we used to face these problems as well. Now, we did a video like this https://youtu.be/xc_9gdYqutg and targeted the right type of buyers on Facebook and the outcome was way better than just in guru. Not sure if this can help you but *ALL COSTS* are on us for this! Perhaps we can see if it can be applied to your place if it's still for sale? Gina (PA to Joel Li R044384D) www.home-link.sg	24/2/2021
3	CONDO			Joel		Hello Joel, How is everything? Are you also facing issues for the listing of your property? No viewings or low offers? Before we found the perfect formula, we used to face these problems as well. Now, we did a video like this https://youtu.be/xc_9gdYqutg and targeted the right type of buyers on Facebook and the outcome was way better than just in guru. Not sure if this can help you but *ALL COSTS* are on us for this! Perhaps we can see if it can be applied to your place if it's still for sale? Gina (PA to Joel Li R044384D) www.home-link.sg
Sheet2

 

[maabadi]

First Add Specific Character (e.g. # ) each place you want to add LineBreak to your text within Cell then Replace it within VBA.
Example:

Sub Customer()
On Error GoTo errorhandler:
mobilenumber = Selection.Value
Set myrange = Sheet1.Range("B:B")
rowno = Application.WorksheetFunction.Match(mobilenumber, myrange, 0)
Description = Sheet1.Cells(rowno, 3).Value
Description.Replace What:="#", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Rs = Sheet1.Cells(rowno, 4).Value
ActiveWorkbook.FollowHyperlink Address:="Open WhatsApp" & mobilenumber & "?text=" & Description & Rs & ""
End
errorhandler:
Err.Clear
MsgBox "Select Proper Mobile Number "
End Sub

 

[pei]

Sub Customer() On Error GoTo errorhandler: mobilenumber = Selection.Value Set myrange = Sheet1.Range("B:B") rowno = Application.WorksheetFunction.Match(mobilenumber, myrange, 0) Description = Sheet1.Cells(rowno, 3).Value Description.Replace What:="#", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Rs = Sheet1.Cells(rowno, 4).Value ActiveWorkbook.FollowHyperlink Address:="Open WhatsApp" & mobilenumber & "?text=" & Description & Rs & "" End errorhandler: Err.Clear MsgBox "Select Proper Mobile Number " End Sub

I am not sure what is the reason but it shows me error messages from the MsgBox.

 

[maabadi]

Try one of this 2 method. (with your uploaded file should be description & Rs column number changed to 6 & 7.
Method 1. First add # symbole each place you want line break.

Sub Customer()
Dim mobilenumber As String, myRange As Range, rowno As Long, Description As String, Rs As String
On Error GoTo errorhandler:
mobilenumber = Selection.Value
Set myRange = Sheet1.Range("B:B")
rowno = Application.WorksheetFunction.Match(mobilenumber, myRange, 0)
Description = Replace(Sheet1.Cells(rowno, 3), "#", vbNewLine)
Rs = Sheet1.Cells(rowno, 4).Value
ActiveWorkbook.FollowHyperlink Address:="Open WhatsApp" & mobilenumber & "?text=" & Description & Rs & ""
End
errorhandler:
Err.Clear
MsgBox "Select Proper Mobile Number "
End Sub

2. Second Method. Because your description is same for all customers except their names you can use this method also. (don't need Column F)
you can change Newline Place with change vbnewline place in code.

Sub Customer2()
Dim mobilenumber As String, myRange As Range, rowno As Long, Description As String, Rs As String, CustName As String
On Error GoTo errorhandler:
mobilenumber = Selection.Value
Set myRange = Sheet1.Range("B:B")
'rowno = Application.WorksheetFunction.Match(mobilenumber, myRange, 0)
CustName = Sheet1.Cells(rowno, 2)
Description = "Hello" & CustName & ", How is everything?" & vbNewLine & vbNewLine & "Are you also facing issues for the" & vbNewLine & _
"listing of your property?" & vbNewLine & "No viewings or low offers?" & vbNewLine & "Before we found the perfect" _
& vbNewLine & "formula, we used to face these" & vbNewLine & "problems as well." & vbNewLine & vbNewLine & _
"Now, we did a video like this" & vbNewLine & "https://youtu.be/xc_9gdYqutg" & vbNewLine & "and targeted the right type of buyers on" _
& vbNewLine & "Facebook and the outcome was" & vbNewLine & "way better than just in guru." & vbNewLine & "Not sure if this can" _
& "help you but" & vbNewLine & "*ALL COSTS* are on us for this!" & vbNewLine & vbNewLine & "Perhaps we can see if it can be" & vbNewLine & _
"applied to your place if it's still" & vbNewLine & "for sale?" & vbNewLine & vbNewLine & "Gina (PA to Joel Li R044384D)" & vbNewLine & _
"www.home -link.sg"
Rs = Sheet1.Cells(rowno, 4).Value
ActiveWorkbook.FollowHyperlink Address:="Open WhatsApp" & mobilenumber & "?text=" & Description & Rs & ""
End
errorhandler:
Err.Clear
MsgBox "Select Proper Mobile Number "
End Sub

 

[pei]

Try one of this 2 method. (with your uploaded file should be description & Rs column number changed to 6 & 7.
Method 1. First add # symbole each place you want line break.

Sub Customer()
Dim mobilenumber As String, myRange As Range, rowno As Long, Description As String, Rs As String
On Error GoTo errorhandler:
mobilenumber = Selection.Value
Set myRange = Sheet1.Range("B:B")
rowno = Application.WorksheetFunction.Match(mobilenumber, myRange, 0)
Description = Replace(Sheet1.Cells(rowno, 3), "#", vbNewLine)
Rs = Sheet1.Cells(rowno, 4).Value
ActiveWorkbook.FollowHyperlink Address:="Open WhatsApp" & mobilenumber & "?text=" & Description & Rs & ""
End
errorhandler:
Err.Clear
MsgBox "Select Proper Mobile Number "
End Sub

2. Second Method. Because your description is same for all customers except their names you can use this method also. (don't need Column F)
you can change Newline Place with change vbnewline place in code.

Sub Customer2()
Dim mobilenumber As String, myRange As Range, rowno As Long, Description As String, Rs As String, CustName As String
On Error GoTo errorhandler:
mobilenumber = Selection.Value
Set myRange = Sheet1.Range("B:B")
'rowno = Application.WorksheetFunction.Match(mobilenumber, myRange, 0)
CustName = Sheet1.Cells(rowno, 2)
Description = "Hello" & CustName & ", How is everything?" & vbNewLine & vbNewLine & "Are you also facing issues for the" & vbNewLine & _
"listing of your property?" & vbNewLine & "No viewings or low offers?" & vbNewLine & "Before we found the perfect" _
& vbNewLine & "formula, we used to face these" & vbNewLine & "problems as well." & vbNewLine & vbNewLine & _
"Now, we did a video like this" & vbNewLine & "https://youtu.be/xc_9gdYqutg" & vbNewLine & "and targeted the right type of buyers on" _
& vbNewLine & "Facebook and the outcome was" & vbNewLine & "way better than just in guru." & vbNewLine & "Not sure if this can" _
& "help you but" & vbNewLine & "*ALL COSTS* are on us for this!" & vbNewLine & vbNewLine & "Perhaps we can see if it can be" & vbNewLine & _
"applied to your place if it's still" & vbNewLine & "for sale?" & vbNewLine & vbNewLine & "Gina (PA to Joel Li R044384D)" & vbNewLine & _
"www.home -link.sg"
Rs = Sheet1.Cells(rowno, 4).Value
ActiveWorkbook.FollowHyperlink Address:="Open WhatsApp" & mobilenumber & "?text=" & Description & Rs & ""
End
errorhandler:
Err.Clear
MsgBox "Select Proper Mobile Number "
End Sub

I feel so stressed and disheartened. What am I doing wrong?
Option 1 show same error from MsgBox.
Option 2 said can't run macro.
Omg....

 

[maabadi]

1. I don't see your example mobile number at uploaded file. Please upload complete example file.
2. at your macro, Column number for Describtion & Rs is 3 & 4, but at your uploaded file their see at column 6 & 7.
After you uploaded your complete example file, I work on it.

 

[pei]

1. I don't see your example mobile number at uploaded file. Please upload complete example file.
2. at your macro, Column number for Describtion & Rs is 3 & 4, but at your uploaded file their see at column 6 & 7.
After you uploaded your complete example file, I work on it.

VBA CODE:

Sub Customer()
On Error GoTo errorhandler:
mobilenumber = Selection.Value
Set myRange = Sheet1.Range("E:E")
rowno = Application.WorksheetFunction.Match(mobilenumber, myRange, 0)
Description = Sheet1.Cells(rowno, 6).Value
Rs = Sheet1.Cells(rowno, 7).Value
ActiveWorkbook.FollowHyperlink Address:="Share on WhatsApp" & mobilenumber & "?text=" & Description & ""
End
errorhandler:
Err.Clear
MsgBox "Select Proper Mobile Number "
End Sub