We help IT Professionals succeed at work.
Get Started

take current email text PLUS recordset and make it into HTML

309 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
Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
This problem has been solved!
Unlock 1 Answer and 12 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE