Solved Display data as percentage using VBA code into an email

July 19, 2016 at 10:02:46
Specs: Windows 7
I have a macro that emails sales people their monthly volume. I would like to show them their referral source in a percentage. I have the data and code working well but it displays it as a long number rather than a percentage. The cell where the data is coming from in excel shows properly as a percentage but when it copies it into the email it is formatted wrong.

It currently displays as "YTD Referral Source % = 0.556045696188214

I would it to display as "YTD Referral Source % = 56%

Here is my code:
Set SourceRng = Worksheets("Source").Range("A:A").Find(What:=myVal, LookAt:=xlWhole)

RefSource = SourceRng.Offset(0, 8).Value

strbody = "<br />" & ActiveSheet.Range("A1") & " " & ActiveSheet.Range("B1") & "<br />" & ActiveSheet.Range("A2") & "YTD Referral Source % = " & RefSource & "<br />"

message edited by mecerrato


See More: Display data as percentage using VBA code into an email

Report •

#1
July 19, 2016 at 10:05:54
Set SourceRng = Worksheets("Source").Range("A:A").Find(What:=myVal, LookAt:=xlWhole)

RefSource = SourceRng.Offset(0, 8).Value

strbody = "<br />" & ActiveSheet.Range("A1") & " " & ActiveSheet.Range("B1") & "<br />" & ActiveSheet.Range("A2") & "YTD Referral Source % = " & RefSource & "<br />" 


Report •

#2
July 19, 2016 at 12:59:27
✔ Best Answer
Edit:

Just multiple your value by 100, round to 2 0 digits and concatenate a percent sign onto it.

Do this before passing the strBody text string to the email.

e.g.

RefSource = SourceRng.Offset(0, 8).Value
  "YTD Referral Source % = " & Round(RefSource * 100, 0) & "%"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •
Related Solutions


Ask Question