troubleshooting Question

take current email text PLUS recordset and make it into HTML

Avatar of Fordraiders
FordraidersFlag for United States of America asked on
Microsoft AccessHTMLOutlook
12 Comments1 Solution311 ViewsLast Modified:
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"

Thanks
fordraiders
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros