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

Question

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.

welcome to StackOverflow, please format you code in future questions and add relevant information that help to solve tu problem. Also, leave thanks are not required. — hcarrasko
– hcarrasko, Commented Jan 5, 2016 at 19:06
I think that " is making the code crashing because it's a special character, while I'm not sure why ? is cutting the rest of the string (it might be related to the Body property of your email object, but it's the first time I hear about it). However, if you store these characters from the cell to a variable, you shouldn't have any issue so I think we're missing a relevant part of code. Can we please have a minimal example to reproduce the error? — Matteo NNZ
– Matteo NNZ, Commented Jan 5, 2016 at 19:09
Try using one of the functions from here: stackoverflow.com/questions/218181/… — Tim Williams
– Tim Williams, Commented Jan 5, 2016 at 19:22
Do you have to use the ShellExecute command to generate the email? I've had lots of success by creating an Outlook object and then creating an email object and adding the body and attachments and such. Working this angle would probably step around your problem here. — PeterT
– PeterT, Commented Jan 5, 2016 at 22:13

stucharo · Accepted Answer · 2016-01-06 20:45:37Z

1

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

edited Jan 6, 2016 at 20:45

answered Jan 6, 2016 at 20:39

stucharo

8655 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related