4

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:Address@bmail.com"
    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.

Scott Tichenor
  • 43
  • 4
  • 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 Jan 05 '16 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 Jan 05 '16 at 19:09
  • Try using one of the functions from here: http://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba – Tim Williams Jan 05 '16 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 Jan 05 '16 at 22:13
  • `~?` did not work. Sorry. – Scott Tichenor Jan 06 '16 at 22:11
  • It appears the nitty gritty of what is going on is described on page 4 here: [link](http://www.ietf.org/rfc/rfc2368.txt) This article was linked to in the link provided at the end of @stucharo 's solution. It will hopefully make sense to all. I appreciate the help and hope this is usefull to others at some point. – Scott Tichenor Jan 06 '16 at 22:32

1 Answers1

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

stucharo
  • 865
  • 5
  • 19