Fordraiders
asked on
take current email text PLUS recordset and make it into HTML
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 ?
Thanks
fordraiders
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"
Thanks
fordraiders
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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>"
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>"
ASKER
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.
Yes, specifically, The "salutation" is embedded in the last cell of the table in the email.
It is not outside the table.
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
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
ASKER
code for table:
Sorry i do not know much html.
fordraiders
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
Sorry i do not know much html.
fordraiders
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
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>
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>
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>
ASKER
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.
so i put the </table> tag here:
mybody2 = "</table><p>Sincerely,<br>
mybody2 = mybody2 & "The CSP Quotations Team.</p>"
worked great.
ASKER
Thanks for sticking with the question.
ASKER