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!

 

[VoG]

Your question is almost indecipherable. Which line of code isgiving the problem?

 

[Norie]

If you want the value of a cell as it's formatted on the worksheet use its Text property.

 

[mole999]

can you force an apostrophe to the front of that string making it text, then maybe to programme won't try and interpret as web address spacing

 

[jazzcar99]

If you want the value of a cell as it's formatted on the worksheet use its Text property.

Can you elaborate a little on this? I'm not sure how to accomplish this in VBA. Sorry for the ignorance, again I'm new to VBA. Possibly an example of how the code above should be changed.

Also to be more clear, the line of code that applies to that cell is in paragraph 4, line 6. The line below is "descriptions:"

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")

 

[mole999]

maybe

"Descriptions: " & "'" & Range("AR2") & vbCrLf & </pre>

 

[jazzcar99]

maybe

"Descriptions: " & "'" & Range("AR2") & vbCrLf &

Nope it still gives me the question marks in the email

 

[jazzcar99]

Mole999, can you be more specific? Again, sorry I am very new to VBA. Can you define "string" and also possibly post a code example of what you are referring to?

 

[Norie]

Which cells have %s?

 

[jazzcar99]

Which cells have %s?

The column with the Naming Variations. In the code it is Cell Range AR2.