Preserve percent sign values in VBA code?

[jazzcar99]

Hello All,

I have a code that opens outlook and pulls data from cells to send an email. There are cells that contain a percent sign (%). I want that to be preserved when it transfers to email, but it doesn't. I've noticed it also does that with the ampersand(sp?) sign. I realize these are part of the coding nature so my theory is that has something to do with it.

How can I preserve the values to read exactly with the percent sign (%) remaining? See below examples:


Here is my code:
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long


Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Email = Range("C2")


Subj = Range("C2") & "_" & _
Range("AO2") & "_" & _
Range("AP2") & "_" & _
"Reactive" & "_" & _
Range("AM2") & "_" & _
Range("AK2")


Msg = "Customer Name: " & Range("C2") & vbCrLf & _
"Requestor Contact Name: " & Range("AL2") & vbCrLf & _
"Requestor Contact Email: " & Range("AN2") & vbCrLf & _
"Theatre: " & Range("AO2") & vbCrLf & _
"Segment: " & Range("AP2") & vbCrLf & _
"Descriptions: " & Range("AR2") & vbCrLf & _
"Cisco Expiring Quarter/Month: " & Range("AM2")



'Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

'Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

'Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

'Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'Wait two seconds before sending keystrokes
'Application.Wait (Now + TimeValue("0:00:02"))
'Application.SendKeys "%s"
End Sub


Here is what the data looks like:
Note the "Naming Variations" column is what I need the percent sign preserved in

Here is the result:
Note the descriptions line does not keep the formatting. Instead it has ? and no %.

Please note I am newer to VBA. I can figure out the response, but step-by-step answers are much appreciated! Thank you for your time!

 

[Norie]

Try this for starters.

"Descriptions: " & Range("AR2").Text  & vbCrLf & _

 

[jazzcar99]

Try this for starters.

"Descriptions: " & Range("AR2").Text  & vbCrLf & _

Nope, still the same results Sorry it took a bit to get back on the forum, took a long weekend.

 

[Norie]

I think the problem is the way you are creating the email.

What you are doing is essentially creating a hyperlink and in hyperlinks the % sign is normally used to represent characters like space.

 

[jazzcar99]

I think the problem is the way you are creating the email.

What you are doing is essentially creating a hyperlink and in hyperlinks the % sign is normally used to represent characters like space.

So what's the solution?

 

[Norie]

Replace % with %25.