Does anyone have code to get a table from either excel or access into the body of an outlook email?

P

peter_z

Board Regular
Joined
Feb 27, 2011
Messages
87
Hey guys, as per the title would be much appreciated if you could share your code that does this.

Thanks heaps in advance!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
I'm assuming you've managed to create an email including the body. You sound as though you're just asking how to get the table data into the body.


Perhaps something along these lines:-

Code: 
[FONT=Fixedsys]  Dim iRow As Long
  Dim iColumn As Long[/FONT]
[FONT=Courier New][/FONT] 

[FONT=Fixedsys]  .HTMLBody = .HTMLBody _[/FONT]
[FONT=Fixedsys]      & "< table border='1' bordercolor='#000000' cellpadding='4' cellspacing='2'>"
  For iRow = 1 To 10
    .HTMLBody = .HTMLBody & "< tr>"
    For iColumn = 1 To 6
      .HTMLBody = .HTMLBody & "< td>" & Cells(iRow, iColumn) & "< /td> "
    Next iColumn
    .HTMLBody = .HTMLBody & "< /tr>"
  Next iRow
  .HTMLBody = .HTMLBody & "< /table>"
[/FONT]

(Remove the spaces immediately after each < character: they're only there to prevent the forum software from throwing a wobbler.)


That would take rows 1-10 and columns 1-6 and stuff them into a table in the body of the email.


Is that something you can use?



You end up with HTML like this:-


HTML: 
< table border='1' bordercolor='#000000' cellpadding='4' cellspacing='2'>
< tr>
< td>UPN< /td> < td>Surname< /td> < td>Forename< /td>
< td>DOB< /td> < td>Gender< /td> < td>Class< /td>
< /tr>
< tr>
< td>D916110408240< /td> < td>Aquinas< /td> < td>Thomas< /td> < td>08/01/1997< /td> < td>M< /td> < td>RA< /td>
< /tr>
< /table>



and a table like this:-



<TABLE style="BORDER-BOTTOM: black 1pt outset; BORDER-LEFT: black 1pt outset; BORDER-TOP: black 1pt outset; BORDER-RIGHT: black 1pt outset; mso-cellspacing: 1.5pt; mso-border-alt: outset black .75pt; mso-yfti-tbllook: 1184; mso-padding-alt: 3.0pt 3.0pt 3.0pt 3.0pt" class=MsoNormalTable border=1 cellPadding=0><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">UPN<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Surname<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Forename<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">DOB<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Gender<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Class<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">D916110408240<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Aquinas<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Thomas<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">08/01/1997<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">M<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">RA<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">E916110008153<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Beardsley<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Dromio<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">25/10/1996<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">M<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">RH<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">F916110108331<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Brand
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Rebecca<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">27/03/1997<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">F<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">JG<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">G916110208349<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Captain<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Taylor<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">03/02/1997<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">M<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">FV<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">H916110308281<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Chatterton<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Thomas<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">07/02/1997<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">M<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">LR<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">I916110408246<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Cottley<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Antiochus<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">10/09/1996<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">M<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">WC<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 7"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">J916110008109<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Fothergill<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Simon<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">11/02/1997<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">M<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">CL<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 8"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">K916110108102<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Daniels<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Shylock<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">20/02/1997<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">M<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">SG<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 9; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">L916110208291<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Daniels<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">Hamlet<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">20/02/1997<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">M<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: black 1pt inset; BORDER-LEFT: black 1pt inset; PADDING-BOTTOM: 3pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 3pt; PADDING-RIGHT: 3pt; BORDER-TOP: black 1pt inset; BORDER-RIGHT: black 1pt inset; PADDING-TOP: 3pt; mso-border-alt: inset black .75pt">RR<o:p></o:p>
</TD></TR></TBODY></TABLE>




Obviously you can tweak the HTML to suit.
 
Last edited:
Upvote 0
Hi Guys, thanks a lot for your responses.

Ruddles - Sorry should have said looking for VBA, I have no idea how to read the code that you have kindly shared lol...

Michael M - Thanks for that, I have been scorung Ron's site trying to adapt something that might work. Think i'm onto something will post shortly if I have any difficulty.

- Peter
 
Upvote 0
Hey guys I have found some code from Rons site which is almost doing exactly what I need.

Just need some help making some adjustments:

The named range in the code is from excel.
I need to make the range a table from access.

Any ideas on how to modify the code to include named ranges from access?

Code: 
Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim rng As Range
    
    'Declare variables for ado connection and sql command
    Dim oCon, strBuild, strSql
    'These variables will store data from excel spreadsheet
    Dim StrUser As String
    
    StrUser = Environ("Username")
    
    'Instantiate the connection object and specify connection properties
    Set oCon = CreateObject("adodb.connection")
    oCon.Provider = "Microsoft.ACE.OLEDB.12.0"
    
    'Open the connection
    'Use a UNC to a file on a server or the path & filename name
    oCon.Open "c\\tempfolder"
    strSql = "SELECT u373091.Client_Name, u373091.Product, u373091.Policy_Number, u373091.Net_Amount " _
    & "FROM u373091 " _
    & "WHERE (((u373091.Status) = 'cancelled')) " _
    & "GROUP BY u373091.Client_Name, u373091.Product, u373091.Policy_Number, u373091.Net_Amount;"
    
    
    On Error GoTo StopMacro
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    'Fill in the Worksheet/range you want to mail
    'Note: if you use one cell it will send the whole worksheet
    Set Sendrng = Worksheets("STATEMENT").Range("A1:B5")
    'Remember the activesheet
    Set AWorksheet = ActiveSheet
    'Create the mail and send it
    With Sendrng
        ' Select the worksheet with the range you want to send
        .Parent.Select
        'Remember the ActiveCell on that worksheet
        Set rng = ActiveCell
        'Select the range you want to mail
        .Select
        ' Create the mail and send it
        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope
            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = "This is a test mail."
            ' In the "With .Item" part you can add more options
            ' See the tips on this Outlook example page.
            ' [URL]http://www.rondebruin.nl/mail/tips2.htm[/URL]
            With .Item
                .To = "[EMAIL="peter_z@mrexcel.com"]peter_z@mrexcel.com[/EMAIL]"
                .Subject = "My subject"
                .Send
            End With
        End With
        'select the original ActiveCell
        rng.Select
    End With
    'Activate the sheet that was active before you run the macro
    AWorksheet.Select
StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False
End Sub
 
Upvote 0
Peter

Ruddles code is VBA.

It's for Excel but should be possible to adapt it for Access.

There are no named ranges in Access though, what you need and what you almost have is a recordset with the data from the query/table you want.

In the posted code you seem to have stopped just before getting that.

You've got the connection, you've got the SQL you just need to get the recordset.
Code: 
    rst.Open strSQL, cnn
    rst.MoveFirst
 
    strHTML = "< table border=1>"
 
    While Not (rst.EOF)
        For Each fld In rst.Fields
            strCells = strCells & "< td>" & fld.Value & "< /td>"
        Next fld
        strHTML = strHTML & "< tr>" & strCells & "< /tr>"
        strCells = ""
        rst.MoveNext
    Wend
 
    strHTML = strHTML & "< /table>"

This will create the HTML for a simple table with the data form your recordset (query).

To include it in the body of the email you'll need to use HTMLBody instead of Body.

Note, like Ruddles code I've used a space after the less than so the board will display the HTML code.
 
Last edited:
Upvote 0
Almost there!

I have got some code producing the recordset data into the email however the format is all wrong. If you know how to fix this please let me know...

relevant code is:

Code: 
 'Set Body for mail
aEmail.Body = vbLf & strClientSQL & strPolicySQL _
& vbLf & strProductSQL _ 'Change Body Message
aEmail.Body = aEmail.Body 'Set attachment
aEmail.Attachments.Add StrOutput
'Send Mail
aEmail.Display
 
Upvote 0
Are you using the code that has been posted to create HTML from your range/recordset?

Or some other code that we haven't seen?
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
MS Excel VBA Meeting Request Code to Set Meeting to Property
Replies
4
Views
267
OilEconomist
O
L
  • Question Question
Sending Email with Table in Body
Replies
3
Views
738
Domenic
D
H
  • Solved
Macro to extract text to Body in Email and Table at end of Body
Replies
1
Views
621
howard
H
G
  • Question Question
Modify Single Email in Outlook Drafts subfolder
Replies
1
Views
359
g_speran
G
T
  • Solved
VBA- Copy & Paste Range into Outlook as a Picture
Replies
6
Views
4K
jolivanes
jolivanes

Forum statistics

Threads
1,224,585
Messages
6,179,696
Members
452,938
Latest member
babeneker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top