• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get Character in VBA

S

sridhar_shri03

New Member
Hiii


I need to get a percentage character in VBA


Actually i have a code which will send personalised mail for each and every one, in which i have productivity information of my team which is picking correctly but the problem is i am not able to get the "%" character after the productivity.

[pre]
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
Dim r As Integer, x As Double
For r = 2 To 4 'data in rows 2-4
' Get the email address
Email = Cells(r, 2)

' Message subject
Subj = "Your Annual Bonus"

' Compose the message
Msg = ""
Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
Msg = Msg & "I am pleased to inform you that your annual bonus is "

Msg = Msg & "Productivity = " & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
Msg = Msg & "William Rose" & vbCrLf
Msg = Msg & "President"

' 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"
Next r
End Sub
[/pre]

In the above i need to get a "%" character after the "productivity = "

i have used chr(37) code & inserted % as text in vba but both of that didn't work i am getting in output as "?D" instead of "%"

i have tried lot of things but didn't work

pls help
 
Hi Sridhar ,


I am not able to understand your problem. Instead of giving the entire procedure , can you just post the single statement which is not working the way you expect it to work ?


Have you tried this same statement in the Immediate Window , and seen what you get ?


Narayan
 
Hi Narayank991


so if my question is not clear


I need to get percentage character "%" in the output


I tried using chr(37) and Inserting "%" text in vba coding but both of that didnt work


Thanks

Sridhar
 
Hi Sridhar ,


I would like to repeat my earlier post !


Can you please indicate which statement ( line in your VBA procedure ) is not doing what you expect it to do ?


Can you copy + paste that statement / line here , in this forum ?


Can you copy + paste that same line in your VBA Project Immediate Window , and see what result it gives ? To do this , place your cursor anywhere within your code , and press the F8 key ; each press of the F8 key will advance your code execution by one step ; when you come to the statement where the chr(37) insertion is , copy + paste that statement in the Immediate Window and see what is displayed.


I have posted one statement below :


Msg = Msg & "Productivity = " & Cells(r, 3).Text & "." & vbCrLf & vbCrLf


If I insert a Chr(37) here as follows :


Msg = Msg & "Productivity = " & Chr(37) & Cells(r, 3).Text & "." & vbCrLf & vbCrLf


it will add the % character in the variable Msg ; is this not happening in your worksheet ?


Narayan
 
Hi Narayank991


I have already tried this code but it is not inserting the % character in the variable msg


It is adding ? instead of % in variable character


Pls Help


Thanks

Sridhar
 
Hi Sridhar ,


I am sorry I cannot convey my doubts ; can you please upload your worksheet and post an access link here ; it would make the solution easier , and faster to arrive at.


Narayan
 
Hi Narayank991


I have uploaded my file in to the following link


http://www.mediafire.com/download.php?p5g6ndd2i8l689i


Pls help


Thanks

Sridhar
 
Hi Sridhar ,


Your VBA Project has a password ; unless I can open the project , I cannot resolve the problem !


Can you either post the password here , or send it to me by email : narayank1026@gmail.com


Narayan
 
Hi, sridhar_shri03!

Check your another similar topic:

http://chandoo.org/forums/topic/sending-personalised-mail-from-excel

I think you got it solved there.

Regards!
 
Hi Sridhar ,


I checked your worksheet ; what I find is that when the default Mail client is Microsoft Outlook , the problem is present , but when I make Outlook Express my default Mail client , it displays the message correctly !


Can you try this at your end and see whether you still want to resolve the problem in Microsoft Outlook , or can you switch to Outlook Express ?


Narayan
 
Hi NARAYANK991


Thanks for your response


But i would like to get the same in microsoft outlook


Could you pls help me to resolve


Thanks

Sridhar
 
You must log in or register to reply here.
Back
Top