UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3  (Go to first unread post)
   Reply to this topicStart new topic
> Email Query Results In Body Of Email Etc, Access 2016    
 
   
Leah
post Jul 7 2020, 08:39 AM
Post#41



Posts: 869
Joined: 22-February 00
From: New York, New York


I never know what to keep and what to get rid of when I save the HTML from Outlook.

Someone gave me the answer:

aHead(7) = "<span style='color:red'>Additional Fees To Closing</span>"

Anyway, thanks everyone. This bunch of answers to my post that you provided should be a useful one to refer to at least for me. Lots of great code.

Leah


--------------------
Leah A. Kopel
Go to the top of the page
 
MadPiet
post Jul 7 2020, 10:31 AM
Post#42



Posts: 3,796
Joined: 27-February 09



Leah,

Do the contents of the table vary by customer? (think the typical "Invoices" example...) If that's the case, I think the signature for Daniel's function would need that added. Otherwise, how do you pass the CustomerID parameter so you only get one customer's Invoice Details per letter?

Pieter
Go to the top of the page
 
Leah
post Jul 7 2020, 12:29 PM
Post#43



Posts: 869
Joined: 22-February 00
From: New York, New York


I was only focusing on the part that I did not know how to do.

I actually had a loop that grabs each person and now thanks to everyone a second loop that grabs their stuff.
CODE
...
    …  
    strduedate = Format(Me.txtduedate, "dddd"", ""mmmm dd"", ""yyyy") ' & "."
    strHTML_Message = "Blah.&nbsp; " & _
    " Blah <b>Bold blah</b>?&nbsp; Blah. " & _
    " &nbsp;Blah.&nbsp; <o:p></o:p></p><o:p><p><b>Bold Blah, " & strduedate & ".</b>&nbsp;</o:p></p>"
    
        DoCmd.OpenQuery ("Relevant People Delete")
        DoCmd.OpenQuery ("Relevant People Append")
    
    Set feecappartners = CurrentDb.OpenRecordset("Relevant People")

    Do While Not feecappartners.EOF
    
        Me.txtbpcode = feecappartners(0)
                
        DoCmd.OpenQuery ("Relevant Person Stuff List Delete")
        DoCmd.OpenQuery ("Relevant Person Stuff List Append")
                  
        First_Name = feecappartners(2)
        
        strHTML_Dear = "<p>Dear " & feecappartners(2) & ",</p>" & vbCrLf
        
        Set olApp = CreateObject("Outlook.application")
        
        Set OlMail = olApp.CreateItem(0)
        
        'Create the header row
        aHead(1) = "Client Name"
        aHead(2) = "Matter Number"
        aHead(3) = "Matter Description"
        aHead(4) = "Fee Cap"
        aHead(5) = "Billed Fees"
        aHead(6) = "Current Fee WIP"
        aHead(7) = "<span style='color:red'>Additional Fees To Closing</span>"

        lCnt = 1
        ReDim aBody(1 To lCnt)
        aBody(lCnt) = "<HTML><body><table border='2'><tr><th>" & Join(aHead, "</th><th>") & "</th></tr>"

        Set feecapmatters = CurrentDb.OpenRecordset("Relevant Person Stuff List")
    
        Do While Not feecapmatters.EOF
            lCnt = lCnt + 1
            ReDim Preserve aBody(1 To lCnt)
            
        If feecapmatters(4) = 0 Then
        
            Billed_Fees = "-"
            
        Else: Billed_Fees = Nz(Format(feecapmatters(4), "#,###"), "-")
        
        End If
        
            BuildRow = "<tr>"
            BuildRow = BuildRow & "<td>" & feecapmatters(0) & "</td>"
            BuildRow = BuildRow & "<td>" & feecapmatters(1) & "</td>"
            BuildRow = BuildRow & "<td>" & feecapmatters(2) & "</td>"
            BuildRow = BuildRow & "<td style=""text-align:Right"">" & Format(feecapmatters(3), "#,###") & "</td>"
            BuildRow = BuildRow & "<td style=""text-align:Right"">" & Billed_Fees & "</td>"
            BuildRow = BuildRow & "<td style=""text-align:Right"">" & Format(feecapmatters(5), "#,###") & "</td>"
            BuildRow = BuildRow & "<td style=""text-align:Right"">" & Format(feecapmatters(6), "#,###") & "</td>"
            BuildRow = BuildRow & "</tr>"
            aBody(lCnt) = BuildRow
            feecapmatters.MoveNext
        Loop

        aBody(lCnt) = aBody(lCnt) & "</table></body></html>"
        
    'create the email
        Set olApp = CreateObject("Outlook.application")
        
        Set OlMail = olApp.CreateItem(0)

        OlMail.To = "Leah.Kopel@CliffordChance.com"
        'OlMail.Recipients.Add "Leah.Kopel@cliffordchance.com"
        
        OlMail.Subject = "PLEASE ADVISE: Estimates of Additional Fees to Closing"
        
        OlMail.HTMLBody = Join(aBody, vbNewLine)
        
        strHTML = strHTML_Dear & strHTML_Message & Join(aBody, vbNewLine) & "<p>Thanks,</p>" & "<p>Some One<br>Billing Manager<br>Somewhere LLP <br>123 SomeStreet<br>Some City, Some State 12345-6789 <br>Direct Dial: +1 212 123 4567 <br>Switchboard: +1 212 123 8000 <br>Fax: +1 212 123 8001<br><a href='mailto:Some.One@Somewhere.com'>Some.One@Somewhere.com</a><o:p></o:p></p>"
    
        OlMail.HTMLBody = strHTML
        
        OlMail.Display
        
        strHTML = ""
        
'        OlMail.Send
      
        feecappartners.MoveNext
    
    Loop

    feecappartners.Close
    feecapmatters.Close
    
    Set feecappartners = Nothing
    Set feecapmatters = Nothing
    
    MsgBox ("The Partner Fee Cap Emails were sent.")


[EDITED by cheekybuddha to add code tags]

--------------------
Leah A. Kopel
Go to the top of the page
 
cheekybuddha
post Jul 7 2020, 03:22 PM
Post#44


UtterAccess Moderator
Posts: 13,057
Joined: 6-December 03
From: Telegraph Hill


Nice work, Leah! thumbup.gif

You don't need this line - it's covered in the next two lines anyway:
CODE
OlMail.HTMLBody = Join(aBody, vbNewLine)

--------------------


Regards,

David Marten
Go to the top of the page
 
Leah
post Jul 7 2020, 03:54 PM
Post#45



Posts: 869
Joined: 22-February 00
From: New York, New York


Ok, thanks. It was part of one of the versions, so I did not notice that it became redundant.

--------------------
Leah A. Kopel
Go to the top of the page
 
3 Pages V < 1 2 3


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 07:17 AM