[SOLVED] VBA concatenation with & cause issues as string also has &

VBA concatenation with & cause issues as string also has &

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"

Solution

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