VB Compile Error: Expected End of Statement

[jerzes11]

Hi,

I've used a pre-existing macro to create mine and it keeps telling me at the Cells on line 2 that it is an Expected End of Statement error but no one else I know who has VB experience can seem to figure this out either.

Please help!?!?

strbody = "Hello " & Cells(cell.Row, "A")&vbNewLine & vbNewLine& _
Cells(cell.Row,"M") & vbNewLine & _
"<B>First Name:</B> " & Cells(Cells(cell.Row, "A") & vbNewLine & _
"<b>Last Name:</b> " & Cells(Cells(cell.Row, "B") & vbNewLine & _
"<b>Username:</b> " & Cells(Cells(cell.Row, "C") & vbNewLine & _
"<B>Password:</B> " & Cells(cell.Row, "D") & vbNewLine & _
"Cells(cell.Row,"N") & vbNewLine & _

 

[RoryA]

You need to separate your ampersands from the constants:

strbody = "Hello " & Cells(cell.Row, "A") & vbNewLine & vbNewLine & _
Cells(cell.Row,"M") & vbNewLine & _
"<B>First Name:</B> " & Cells(Cells(cell.Row, "A") & vbNewLine & _
"<b>Last Name:</b> " & Cells(Cells(cell.Row, "B") & vbNewLine & _
"<b>Username:</b> " & Cells(Cells(cell.Row, "C") & vbNewLine & _
"<B>Password:</B> " & Cells(cell.Row, "D") & vbNewLine & _
"Cells(cell.Row,"N") & vbNewLine & _

 

[andiemac]

Cells is looking for the columnindex, not the alphabetic letter. Take out the "M" and enter 13.

 

[RoryA]

Actually, Cells will accept either a column letter or number.

 

[jerzes11]

Thank you!

I noticed that it wasn't initially working because of the space and the duplication of typing Cells that I was clearly over-seeing. Then for some reason my Excel VB would accept but not execute the vbNewLine command so I ended up getting it to work with this:

.HTMLBody = "Hello " & Cells(cell.Row, "A") & ",</p>" & _
Cells(cell.Row, "M") & _
"<p><B>First Name:</B> " & Cells(cell.Row, "A") & "</p>" & _
"<p><b>Last Name:</b> " & Cells(cell.Row, "B") & "</p>" & _
"<p><b>Username:</b> " & Cells(cell.Row, "C") & "</p>" & _
"<p><B>Password:</B> " & Cells(cell.Row, "D") & "</p>" & _
Cells(cell.Row, "N")

 

[jsotola]

delete the red highlighted sections in line 2,3,4,5

note line 5 - delete leading double_quote mark

to debug a multiline statement like this, work from top to bottom

delete the " & _ " at end of line and see if the command becomes valid

work your way down, line by line

another note: line 2,3,4 the double cells command is not the problem
it is a missing closing parenthesis that is causing the error
the first cells command is just redundant

strbody = "Hello " & Cells(cell.Row, "A")&vbNewLine & vbNewLine & _
Cells(cell.Row,"M") & vbNewLine & _
"<B>First Name:</B> " & [U][SIZE=4][COLOR=#ff0000]Cells([/COLOR][/SIZE][/U]Cells(cell.Row, "A") & vbNewLine & _
"<b>Last Name:</b> " & [U][SIZE=4][COLOR=#ff0000]Cells([/COLOR][/SIZE][/U]Cells(cell.Row, "B") & vbNewLine & _
"<b>Username:</b> " & [U][SIZE=4][COLOR=#ff0000]Cells([/COLOR][/SIZE][/U]Cells(cell.Row, "C") & vbNewLine & _
"<B>Password:</B> " & Cells(cell.Row, "D") & vbNewLine & _
[U][SIZE=4][COLOR=#ff0000]"[/COLOR][/SIZE][/U]Cells(cell.Row,"N") & vbNewLine[SIZE=4][COLOR=#FF0000] & _[/COLOR][/SIZE]