Solved

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

Posted on 2016-08-28
3
31 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 48

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now