CODEWITHSUNDEEP
vbaexcel
Scott Tichenor
Scott Tichenor

Reputation: 43

Using a String with a ? from Excel cell content in visual basic

I am using 

Range("$AC" & Right(ActiveCell.Address, 2)).Value

to grab the content of the AC column in the row of the selected cell. The content of the cell is a comment, I want a user to be able to write to include in an email I am generating with my macro. For instance, as of now if you have "Comment to student 3." without the quotes in the cell, it is added to the body of the email. The comment is included in the string strBody and then incorporated in the email using the following two commands (with the target email address stored in strTo):

strURL = strTo & "&Body=" & strBody

and

ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus

No problems so far. UNLESS the user uses a special character, like a ? or a " in the content of the Excel cell. Using the ? cuts the rest of the string off while using a " creates an error and fails to even generate the email.

So here is the question: Is there a way I can code the grab to format the content of the cell so that a special character is ignored? Or is there a way I can have the user type their comment so that the ? is just treated as a ?. Note I have tried \? /? "?" "? '? and even something like Microsoft.Visual.Chr(34).

EDIT:

@stucharo 's answer below worked great! Thanks. For those asking for a minimal code to see what was going on (it's still kind of a mystery to me as I am just Frankinstein'ing this stuff together) here it is:

Private Declare PtrSafe 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 EmailStudent()
    strTo = "mailto:[email protected]"
    strSubject = "Email Subject"
    strBody1 = Range("'Sheet1'!A1")
    strBody2 = Range("'Sheet1'!A2")
    strBody3 = Range("'Sheet1'!A3")
    strBody = strBody1 & "%0D%0A%0D%0A" & strBody2 & "%0D%0A%0D%0A" & strBody3
    strBody = Replace(strBody, "?", "%3F")
    strURL = strTo & "&subject=" & strSubject & "&Body=" & strBody
    ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus
End Sub

The text in A1 A2 and A3 of Sheet1 should be included in the body of an email. You need to have an outlook client running. I included the fix for question marks, if you remove the Replace command and put a question mark in A2, any text after it, including the text in A3, will no longer show up in the email.

Upvotes: 4

Views: 211

Answers (1)

stucharo
stucharo

Reputation: 855

The first header after the address should be preceded by ? and there should be no internet unsafe characters. In VBA, the " character will end the string and there will be illegal code outside what VBA thinks is the string.

Replacing ? and " with their hex equvalents in your body string should work.

Try:

strBody = Replace(strBody, """", "%22") 'to replace the "
strBody = Replace(strBody, "?", "%3F") 'to replace the ?

before you pass strBody to the ShellExecute command.

You can read more about using the mailto: protocol here: https://msdn.microsoft.com/en-us/library/aa767737%28v=vs.85%29.aspx

Upvotes: 1

Related Questions