Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

take current email text PLUS recordset and make it into HTML

Posted on 2016-08-24
12
Medium Priority
?
111 Views
Last Modified: 2016-08-25
Access 2010 vba
outlook vba via access

based on previous q:
https://www.experts-exchange.com/questions/28965360/Sending-email-using-a-query-based-on-a-value-from-a-subform-produces-error.html

The following is grabbing  text I create from values on my MAIN form.
AND
displaying values from a subform that is in a recordset.

What I need:
I would like to display the whole thing now in html:
BUT
specifically put the data from the subform(rs)  into  a HTML table to make it look better ?

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Dim price_escNum As String
Dim esc_Quote As String
Dim esc_AccountNumber As String
Dim esc_AccountName As String
Dim SELLER_NAME As String
Dim DSM_NAME As String


' outlook parameters set
price_escNum = Me!PRICING_ESCALATION_ID
esc_Quote = Me!QUOTE_NUMBER
esc_AccountNumber = Me!ACCOUNT_NUMBER
esc_AccountName = Me!ACCOUNT_NAME
SELLER_NAME = Me!SELLER_NAME
DSM_NAME = Me!DSM_NAME


' get the subform data now
Set MyDB = CurrentDb
Set qd = MyDB.QueryDefs("Query_FOR_HTML")
qd.Parameters!ENTERVALUE = [Forms]![Main]![dbo_t_redbook_pricing_escalation_detail_subform]![RBP_MASTER_ID]
Set rst = qd.OpenRecordset


With rst
  Do While Not .EOF
    Mailbody = Mailbody & ![SKU] & " | " & ![QTY] & " | " & ![TARGET_PRICE] & vbCrLf
   .MoveNext
  Loop
End With

' create the email with the subform data
Dim appOutLook As Outlook.Application
   Dim MailOutLook As Outlook.MailItem
   Dim strPath As String
   Dim strFileName As String
   Set appOutLook = CreateObject("Outlook.Application")
   Set MailOutLook = appOutLook.CreateItem(olMailItem)
      
   With MailOutLook
     .BodyFormat = olFormatRichText
     .To = "xxxxell@xxxxxer.com"
     '.CC = ""
     '.bcc = ""
     .Subject = "Action Required - Pricing Escalation #'" & escNum & "'"
     
     .Body = "Please review the request and advise if you support." & vbCrLf & " " & vbCrLf & _
       "Request #:'" & price_escNum & "'" & vbCrLf & _
       "Quote #:'" & esc_Quote & "'" & vbCrLf & _
       "Account #:'" & esc_AccountNumber & "'" & vbCrLf & _
       "Account Name:'" & esc_AccountName & "'" & vbCrLf & _
       "Seller Name:'" & SELLER_NAME & "'" & vbCrLf & _
       "DSM Name:'" & DSM_NAME & "'" & vbCrLf & _
       "Please Note one or more of the Target Prices is below cost" & vbCrLf _
        & " " & vbCrLf _
        & Mailbody
              
     .Send
End With
rst.Close
Set rst = Nothing
  
MsgBox "done"

Open in new window


Thanks
fordraiders
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 41769433
I'll just start top level

  With MailOutLook
     .BodyFormat = olFormatRichText
     .To = "xxxxell@xxxxxer.com"
     '.CC = ""
     '.bcc = ""
     .Subject = "Action Required - Pricing Escalation #'" & escNum & "'"
     
     .Body =


Instead of .Body you use .HTMLBody and you fill it with a string that would be between the
<body></body> tags of a standard HTML4 file.

so
.BodyFormat = olFormatHTML
.HTMLBody = "<p>Hi Nick67"
.HTMLBody = .HTMLBody & "<br>Is that all there is to it?"
.HTMLBody = .HTMLBody & "<br>yes, no javascript, vbscript or fancyness"
.HTMLBody = .HTMLBody & "<br>Just straight HTML</p>"

Open in new window


You are just building a straight-up whack of syntactically correct HTML.

Production code:
   Dim mybody As String
   mybody = "Hello,<br><br>"
   
   mybody = mybody & "A webpage with links to the reports you have requested has been created at " & _
    "<a href = " & Chr(34) & "http://www.nowhere.com/pdf/" & mywo & "/index.htm" & Chr(34) & " >" & _
    "www.nowhere.com/pdf/" & mywo & "/index.htm</a><br />" & vbCrLf & _
    "Click each link to view the report.<br>" & _
    "Right click and Save Target As... to save the report to your hard drive.<br>" & _
    "This is an automated sending by us as requested.<br><br>" & _
    "Please respond to me with any inquiries"
    
    mybody = mybody & "<br><br>Please note that this page is temporary and may be taken down 4 to 6 weeks after you receive this message."
   '.Importance = olImportanceHigh  'High importance
    .HTMLBody = mybody

Open in new window


Your loop here
Mailbody = Mailbody & ![SKU] & " | " & ![QTY] & " | " & ![TARGET_PRICE] & vbCrLf
can minimally be altered to
Mailbody = Mailbody & ![SKU] & " | " & ![QTY] & " | " & ![TARGET_PRICE] & <br>

For general prettiness you can build tables

 .HTMLBody = "<table>"
With rst
  Do While Not .EOF
     .HTMLBody =  .HTMLBody & "<tr><td>"  & ![SKU] &  "</td><td>"   & ![QTY] &  "</td><td>" & ![TARGET_PRICE]  & "</td></tr>" 
   .MoveNext
  Loop
End With
.HTMLBody =  .HTMLBody &  "</table>"

Open in new window


and then there's your size tags font tags bold tags italic tags and ALL the pure HTML4 you can muster.

Got the idea?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41770364
yes, Thanks will let you know how it goes.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41770447
nick67, One small piece left.

I'm trying to put a salutation after my table

I have "text"  ,  table , then i need a salutation under the html table.
Cant get it to work.

MAILBODY is my html table...fyi


mybody = "Please Review the request and advise if you support.<br><br>"
   
   mybody = mybody & "Request #:'" & price_escNum & "' <br>" & _
    "Quote #:'" & esc_Quote & "'<br>" & _
    "Account #:'" & esc_AccountNumber & "'<br>" & _
    "Account Name:'" & esc_AccountName & "'<br>" & _
    "Seller Name:'" & SELLER_NAME & "'<br>" & _
    "DSM Name:'" & DSM_NAME & "'<br><br>" & _
    "Please Note one or more of the Target Prices is below cost.<br><br>"
   
       
   mybody2 = "Sincerely,"
   mybody2 = mybody2 & "The CSP Quotations Team."


 .BodyFormat = olFormatHTML
     .HTMLBody = mybody & MAILBODY
     .HTMLBody = .HTMLBody & mybody2
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:Nick67
ID: 41770604
Cant get it to work.

That's fairly generic for a problem description <grin>
Remember it ALL has to be syntactically correct HTML

  mybody2 = "Sincerely,"
    mybody2 = mybody2 & "The CSP Quotations Team."

I don't see any tags in there, do you?
How's about:
  mybody2 = "<p>Sincerely,<br><br>"
    mybody2 = mybody2 & "The CSP Quotations Team.</p>"
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41770642
Thanks..."cant get it to work"..
Yes, specifically, The "salutation" is embedded in the last cell of the table in the email.

It is not outside the table.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41770649
here is a pic
table with salutation embedded.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41770656
Ah.
Well it's HTML.
So if your salutation is in a cell then the part you posted is NOT where the problem lies
The HTML for a table
<table>
<tr><td> Table opens it, tr starts a row, td starts a cell </td></td>
<tr><td>  /tr ends a row, /td ends a cell and /table closes it </td></td>
</table>

Before your loop starts you need a
<table>
Your loop running through the recordset is going to create successive
<tr><td>stuff</td></td>
lines.  After the loop completes, you need
</table>

I think you don't have the closing table tag or perhaps the final three </td></tr></table> constructed quite right.
Post the code from where "<table>" to "</table>" is
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41770674
code for table:

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>" & _
        vbCrLf
   .MoveNext
  Loop
End With

Open in new window



Sorry i do not know much html.
fordraiders
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41770699
Ok.
Only with creating HTML for Outlook these tags are superfluous
<HTML><Body>
Here you open the table
strhtml = "<HTML><Body><table border='1' width='50%'>

And here you are building rows
MAILBODY = MAILBODY & "<tr><td>" ...  "</td></tr>"
The VBCrLf is superfluous.

But now, at the end you don't have a closing table tag

So try

strhtml = "<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
</table>

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 41770701
Sorry i do not know much html.
http://www.w3schools.com/html/html_tables.asp

Probably the very best place to learn enough HTML to be dangerous.
Which is all you need for Outlook <grin>
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41770801
nick , throws an error.
so i put the </table> tag  here:

mybody2 = "</table><p>Sincerely,<br><br>"
    mybody2 = mybody2 & "The CSP Quotations Team.</p>"

worked great.
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41770803
Thanks for sticking with the question.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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