Solved

adding currency and  percentage formatting to an email created in access VBA

Posted on 2016-08-28
3
46 Views
Last Modified: 2016-08-29
Access 201 vba
creating an email in Access to send in outlook.;
I need to format the table and have certain columns with currenty and percetage formatting.. see below.
strhtml = "<HTML><Body><table border='1' width='50%'><tr><th>Sku</th><th>QTY</th>" & _
"<th>TARGET_PRICE</th><th>TARGET_GP</th><th>CURRENT_PRICE</th><th>CURRENT_GP</th><th>VENDOR_GUIDELINE_GP</th><th>APPROVED_PRICE</th><th>APPROVED_GP</th></tr>"

' Initialize Mailbody
MAILBODY = strhtml

With rst
  Do While Not .EOF
   ' Mailbody = Mailbody & ![SKU] & " | " & ![QTY] & " | " & ![TARGET_PRICE] & ![TARGET_GP] & " | " & ![CURRENT_PRICE] & " | " & ![CURRENT_GP] & " | " & ![VENDOR_GUIDELINE_GP] & " | " & ![APPROVED_PRICE] & " | " & ![APPROVED_GP] & vbCrLf
    MAILBODY = MAILBODY & "<tr><td>" & rst("SKU") & "</td><td>" & _
        rst("QTY") & "</td><td>" & rst("TARGET_PRICE") & "</td><td>" & _
        rst("TARGET_GP") & "</td><td>" & rst("CURRENT_PRICE") & "</td><td>" & rst("CURRENT_GP") & "</td><td>" & rst("VENDOR_GUIDELINE_GP") & "</td><td>" & rst("APPROVED_PRICE") & "</td><td>" & rst("APPROVED_GP") & "</td></tr>"
   .MoveNext
  Loop
  
End With

Open in new window


Fields that need money formatting and percentage formatting
I.E.
TARGET_PRICE   TARGET_GP   CURRENT_PRICE   CURRENT_GP       : SHOULD BE
$2.00                      65%              $4.89                      45%
$5.67                      35%              $3.21                       32%


Thanks
fordraiders
0
Comment
Question by:fordraiders
3 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 300 total points
ID: 41774299
Hi,

pls try

strhtml = "<HTML><Body><table border='1' width='50%'><tr><th>Sku</th><th>QTY</th>" & _
"<th>TARGET_PRICE</th><th>TARGET_GP</th><th>CURRENT_PRICE</th><th>CURRENT_GP</th><th>VENDOR_GUIDELINE_GP</th><th>APPROVED_PRICE</th><th>APPROVED_GP</th></tr>"

' Initialize Mailbody
MAILBODY = strhtml

With rst
  Do While Not .EOF
   ' Mailbody = Mailbody & ![SKU] & " | " & ![QTY] & " | " & ![TARGET_PRICE] & ![TARGET_GP] & " | " & ![CURRENT_PRICE] & " | " & ![CURRENT_GP] & " | " & ![VENDOR_GUIDELINE_GP] & " | " & ![APPROVED_PRICE] & " | " & ![APPROVED_GP] & vbCrLf
    MAILBODY = MAILBODY & "<tr><td>" & rst("SKU") & "</td><td>" & _
        rst("QTY") & "</td><td>" & format(rst("TARGET_PRICE"), "Currency") & "</td><td>" & _
        format(rst("TARGET_GP"),"0%") & "</td><td>" & format(rst("CURRENT_PRICE"), "Currency") & "</td><td>" & Format(rst("CURRENT_GP"),"0%") & "</td><td>" & rst("VENDOR_GUIDELINE_GP") & "</td><td>" & rst("APPROVED_PRICE") & "</td><td>" & rst("APPROVED_GP") & "</td></tr>"
   .MoveNext
  Loop
  
End With

Open in new window

Regards
0
 
LVL 24

Assisted Solution

by:chaau
chaau earned 200 total points
ID: 41774300
Use the Format for this. For currency and percent there are predefined formats.
So, the code will be:
    MAILBODY = MAILBODY & "<tr><td>" & rst("SKU") & "</td><td>" & _
        rst("QTY") & "</td><td>" & Format(rst("TARGET_PRICE"), "Currency") & "</td><td>" & _
        Format(rst("TARGET_GP"), "Percent") & "</td><td>" & rst("CURRENT_PRICE") & "</td><td>" & rst("CURRENT_GP") & "</td><td>" & rst("VENDOR_GUIDELINE_GP") & "</td><td>" & rst("APPROVED_PRICE") & "</td><td>" & rst("APPROVED_GP") & "</td></tr>"

Open in new window

0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41774898
Thanks Liked both methods.!!
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question