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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Email Query Results In Body Of Email Etc, Access 2016    
 
   
Leah
post Jun 29 2020, 08:30 PM
Post#1



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


I have done automatic emails from Access before, however I usually use a template for the body with the query or report results as an attachment. I was asked if instead of attaching a query, it could be included in the body.

Someone at my office referred me to this answer to a post made by Richard Kusleika on Stackoverflow on how to put the table in the body.

https://stackoverflow.com/questions/3074173...in-table-format

I was able to adapt what I learned and was able to recreate the table I wanted. Now. I want to add stuff before and after. Also if there is a way to control sections of the body of the email, then I could skip templates and just put what I need to in the code.

I want to add a "Dear First_Name,

(New Line)Blah blah blah, see below.

(New Line)Please respond by Me.txtduedate"

After the table is presented, I want a blank line and then:

"Thanks

Jane Doe
Jane.Doe@Somewhere.com"

...

'Table Section

Set feecapmatters = CurrentDb.OpenRecordset("Partners with Fee Caps Matters")

Do While Not feecapmatters.EOF
lCnt = lCnt + 1
ReDim Preserve aBody(1 To lCnt)
aRow(1) = feecapmatters(0)
aRow(2) = feecapmatters(1)
aRow(3) = feecapmatters(2)
aRow(4) = feecapmatters(3)
aRow(5) = feecapmatters(4)
aRow(6) = feecapmatters(5)
aRow(7) = feecapmatters(6)
aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
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 = "First_Name.Last_Name@Somewhere.com"

OlMail.Subject = "PLEASE ADVISE: Estimates of Additional Fees"

'OlMail.HTMLBody = "<HTML><BODY>Blah blah blah, see below. etc </BODY></HTML>"

'OlMail.Display

OlMail.HTMLBody = Join(aBody, vbNewLine)


Cherry on top would be to right align standard format the numbers in the table and have one column header in Red font.

Thanks, any bits on how to string everything together with new lines would be appreciated. I think the hardest part was done, but this is all new to me.

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
theDBguy
post Jun 29 2020, 09:12 PM
Post#2


UA Moderator
Posts: 78,438
Joined: 19-June 07
From: SunnySandyEggo


Hi Leah. It's just a matter of knowing the HTML tags you need to use. As a test, you could try creating a HTML page, using an HTML or a WYSYWIG editor, with a sample message you want to see in the email. You can then look at the Page Source and maybe use the same HTML tags in your VBA.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
DanielPineault
post Jun 29 2020, 10:06 PM
Post#3


UtterAccess VIP
Posts: 7,373
Joined: 30-June 11



You may find https://www.devhut.net/2017/02/14/generate-...erys-recordset/ useful.

--------------------
Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Leah
post Jun 30 2020, 07:52 AM
Post#4



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


Wow, I think I like the version you provided better as I could understand the code better.

I am planning to play with that and see how far I get before I get stuck again.

Thanks

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
Leah
post Jun 30 2020, 06:41 PM
Post#5



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


Thanks DBGuy.

Shortly after I read this, someone at work told me about saving an Outlook as an HTML and I finally know what you were talking about.

I am playing with that now. It has a lot of code for what seems to be rather simple thing before and after the table. So far I am getting an error message.

Earlier I was able to do the message before the table without HTML but I does not look like I can mix the too so I guess I have to learn HTML and how to get it to work with Access.

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
theDBguy
post Jun 30 2020, 07:10 PM
Post#6


UA Moderator
Posts: 78,438
Joined: 19-June 07
From: SunnySandyEggo


Hi Leah. HTML is simple. I think Outlook and/or Word complicates it. All that stuff they add to the document when you save an email or doc to HTML is not really necessary for a simple HTML document. See if you can find out which tags you can clean out. Or better yet, just get a WYSIWYG editor and construct a mockup of your email content. You can then see the simpler version of the HTML tags you need for your project. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
DanielPineault
post Jun 30 2020, 07:33 PM
Post#7


UtterAccess VIP
Posts: 7,373
Joined: 30-June 11



I recently help a person who wanted to send e-mails from Access. They started by creating a HTML template in Word. Word's HTML was a disaster!!! Long story short, I reduced Word's HTML a whopping 1237 lines of code, by manually creating the HTML file myself with only 57 lines of code. And mine was better and much easier to manage moving forward.

All this to say, stay very, very far away from Microsoft when it comes to anything HTML related!!!!!!!

--------------------
Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
MadPiet
post Jun 30 2020, 07:59 PM
Post#8



Posts: 3,769
Joined: 27-February 09



If you have to learn at least basic HTML, try https://www.w3schools.com/. The cool part is that you can play with your HTML there, and then just copy & paste it into something like Notepad++, save it as HTML and run it.
Go to the top of the page
 
Leah
post Jun 30 2020, 08:50 PM
Post#9



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


Thanks everyone. Since I wanted to create an Outlook email. Someone told me to format it the way, I wanted and then just save as HTML. When I did, I was horrified. To be continued.

--------------------
Leah A. Kopel
Go to the top of the page
 
stevep
post Yesterday, 10:13 AM
Post#10



Posts: 114
Joined: 9-November 14



I have used Daniels GenHTMLTable function for a few years now and it is pretty great. My usage, with late binding, is below.

My only quibble is formatting numbers to right align but since this is 457 times better than what I was using before, I just let it slide.

I forget how I did it but it was easy to put a little text before and after the table.

CODE
Public Sub EMailHTMLQuery(ByRef strQry As String)

Dim oApp As Object
Dim oMail As Object

Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.createitem(0)

With oMail
    .Subject = fNow & " " & strQry
    .htmlbody = GenHTMLTable(strQry)
    .display
End With

Set oMail = Nothing
Set oApp = Nothing

End Sub


Example usage

Public Sub EMailHTMLNewItems()
EMailHTMLQuery ("New Items")

End Sub
Go to the top of the page
 
MadPiet
post Yesterday, 12:26 PM
Post#11



Posts: 3,769
Joined: 27-February 09



Given that the function call GenHTMLTable(strQry) can access the fields collection of the query, you should be able to programmatically identify the fields in the query that are numeric, and add some formatting tags to the table cells that should be right aligned. much easier in CSS, but not available in e-mail. =(

Where did you find the code? I wanted to play with it, but I didn't find it on Daniel's website.
This post has been edited by MadPiet: Yesterday, 12:27 PM
Go to the top of the page
 
theDBguy
post Yesterday, 12:55 PM
Post#12


UA Moderator
Posts: 78,438
Joined: 19-June 07
From: SunnySandyEggo


I think the link is posted in Post #3.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Yesterday, 02:15 PM
Post#13



Posts: 3,086
Joined: 4-February 07
From: USA, Florida, Delray Beach


I do apologize for jumping in on this Thread so very, very late but I created a Demo for you that hopefully will help you out. The Code Outputs a Query in *.html Format, an E-Mail is generated in Outlook with the proper Attributes, the Query results are read using the Scripting Runtime, then embedded in the Body of the E-Mail, and Text is placed after the Query results. The E-Mail is then displayed (see the Image for a partial view of the E-Mail). Download the Demo and see if it helps - it is relatively simple.
Attached File(s)
Attached File  Outlook.JPG ( 106.98K )Number of downloads: 1
Attached File  Query_Results_In_Body_Of_EMail.zip ( 40.88K )Number of downloads: 3
 
Go to the top of the page
 
stevep
post Yesterday, 02:33 PM
Post#14



Posts: 114
Joined: 9-November 14



Hi MadPiet. The getting of field types and CSS is beyond my skills. I don't know how to quote replies.

I looked up how I added stuff before and after the table. Primitive I guess but it works for me.

CODE
strHTMLStart = "Here is some text to go before the table<br><br>"

strHTMLEnd = "<br><br>Here is some text to go after the table<br>"

oMail.htmlbody = strHTMLStart & GenHTMLTable(strQry) & strHTMLEnd


Edit: I see ADezii put up a demo while I was typing this. I downloaded it but it had an error on opening on my Access 2010 setup. Something about strPath.

I did not explicitly set my body type to olHTMLBody, I guess I have been lucky to get away with that.
This post has been edited by stevep: Yesterday, 02:44 PM
Go to the top of the page
 
ADezii
post Yesterday, 03:16 PM
Post#15



Posts: 3,086
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
had an error on opening on my Access 2010 setup. Something about strPath.

I must have inadvertently forgot to Declare, or deleted, the Variable Reference:
CODE
Dim strPath As String
Go to the top of the page
 
cheekybuddha
post Yesterday, 04:19 PM
Post#16


UtterAccess Moderator
Posts: 12,987
Joined: 6-December 03
From: Telegraph Hill


See this old thread for some ideas on how to incorporate CSS styles into your table html

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


Regards,

David Marten
Go to the top of the page
 
Leah
post Yesterday, 04:36 PM
Post#17



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


Thanks everyone. I got pulled away from this. I hope to take a look at this tomorrow.

Leah
This post has been edited by Leah: Yesterday, 04:38 PM

--------------------
Leah A. Kopel
Go to the top of the page
 
MadPiet
post Yesterday, 07:17 PM
Post#18



Posts: 3,769
Joined: 27-February 09



Some ugly HTML4 or something table formatting <shudder>... I basically inserted this in the inline style stuff of any column should be right-aligned:

CODE
text-align:right;


(Makes me miss CSS and nth child!)

CODE
<table style='border-collapse:collapse; border: 1px solid LightGrey;'>
  <tbody>
    <tr style='font-weight:bold;'>
      <td style='border: 1px solid LightGrey;'>Description</td>
      <td style='border: 1px solid LightGrey;'>Hire (£)</td>
      <td style='border: 1px solid LightGrey;'>Sale (£)</td>
    </tr>
    <tr>
      <td style='border: 1px solid LightGrey;'>Test1</td>
      <td style='border: 1px solid LightGrey;text-align:right;'>10.00</td>
      <td style='border: 1px solid LightGrey;text-align:right;'>5.00</td>
    </tr>
    <tr>
      <td style='border: 1px solid LightGrey;'>Test2</td>
      <td style='border: 1px solid LightGrey;text-align:right;'>3.00</td>
      <td style='border: 1px solid LightGrey;text-align:right;'>5.00</td>
    </tr>
  </tbody>
</table>
Go to the top of the page
 
DanielPineault
post Today, 08:40 AM
Post#19


UtterAccess VIP
Posts: 7,373
Joined: 30-June 11



I try, as much as possible, to build use CSS classes and only use inline CSS for very specific cases. Typically, classes results in cleaner code and it is much more manageable.

--------------------
Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    2nd July 2020 - 10:54 AM