I'm reading a value from a cell, the contents has the & as part of the string. For example the cell value is "BAU Dev & Production Support-Partner"
xCellValue = xRg.Cells(i, 2).Value
xMsg = " This is a test of the concatenation "
xMsg = xMsg & xCellValue & vbCrLf & vbCrLf
When I print xCellValue it shows "This is a test of the concatenation BAU Dev".
Hence the & inside the cell has become part of the concatenation. How do I tell VBA not to interpret the & inside the cell?
Thanks to all those who responded. I've Debug.Print and further identified the issue:
xURL = "mailto:" & xEmail & "?subject=" & xSubj & "&body=" & xMsg
Debug.Print xURL
ShellExecute 0, vbNullString, xURL, vbNullString, vbNullString, vbNormalFocus
Up to the Debug.Print xURL, the & is still showing in the string. However, the string terminates after the & in the ShellExecute xMsg. The problem is not in reading the cell value as I had previous thought. If I set
xMsg = " This is a test & of the concatenation "
the body message in the ShellExecute will only show " This is a test"
Ok, I found the solution. I needed
' Replace & with %26 (hex)
xMsg = Application.WorksheetFunction.Substitute(xMsg, "&", "%26")
It was the same reason I needed
' Replace spaces with %20 (hex)
xSubj = Application.WorksheetFunction.Substitute(xSubj, " ", "%20")
xMsg = Application.WorksheetFunction.Substitute(xMsg, " ", "%20")
' Replace carriage returns with %0D%0A (hex)
xMsg = Application.WorksheetFunction.Substitute(xMsg, vbCrLf, "%0D%0A")
For more details, check URL encoding