Remove From My Forums

Query Results in Outlook Email Body

Question
0

Sign in to vote

Hello,

I have a simple question (I hope). I have MS Access 2010 and Outlook 2010 on Win 7 PC. In my Access Db I have a query named ParcelShipments that contain fields named TrackingNum (Text), CustName (Text), PO_Num (Text) and ShipDate (Date/Time). Currently I copy/paste these results in a HTML email.

I want to know if anyone have any VB code that will insert the query results automatically in the body of the Outlook HTML email. I'm looking for the query results to look the same as if I copy/pasted it. This way my co-workers can add any missing PO_Num directly in the cell on the email when they reply.

The email should open so I can choose the recipient.

Thanks for your help. Kevin

Friday, April 7, 2017 1:56 PM

Answers

Below is the VB code. This code does not contain any controls for font and font size.

Function EmailShipment()

    Dim olApp As Object
    Dim olItem As Variant
    Dim rst As DAO.Recordset
    Dim strQry As String
    Dim strBody As String

    strQry = "SELECT [TableName].FieldName, [TableName].FieldName, [TableName].FieldName, " & _
             "[TableName].FieldName " & _
             "FROM [TableName] "
    Set rst = CurrentDb.OpenRecordset(strQry)

    'Create the HTML table
    strBody = fHTMLBodyTable(rst, 2)

    'create the email
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.createitem(0)

    olItem.Display
    'olItem.To = "xxx@yyyy.net"
    olItem.Subject = "Enter subject here"
    olItem.HTMLBody = strBody
    olItem.Display

End Function


Function fHTMLBodyTable(objRst As Object, Optional intBorder As Integer = 1) As String
    
    Dim strHTML As String
    Dim varFld
    
    'Header row based on field names
    strHTML = "<table border='" & intBorder & "'><tr>"
    For Each varFld In objRst.Fields
        strHTML = strHTML & "<th>" & varFld.Name & "</th>"
    Next
    strHTML = strHTML & "</tr>" & vbCrLf
    
    'Iterate Child data rows
    Do Until objRst.EOF
        strHTML = strHTML & "<tr>"
        For Each varFld In objRst.Fields
            strHTML = strHTML & "<td>" & varFld.Value & "</td>"
        Next
        strHTML = strHTML & "</tr>" & vbCrLf
        objRst.MoveNext
    Loop
    strHTML = strHTML & "</table>"
    
    fHTMLBodyTable = strHTML

End Function

Marked as answer by KevinATF Monday, April 10, 2017 8:10 PM

Monday, April 10, 2017 8:10 PM

All replies

0

Sign in to vote

Hi Kevin,

Check out the link Leigh posted at UtterAccess.

Hope it helps...

Friday, April 7, 2017 2:52 PM
0

Sign in to vote

Hi DBguy,

Always good to hear from you. I checked out the link. Leigh has several options posted. Not being much of a VB person myself, can you recommend which one of Leigh's post would work for my situation?

Thanks, Kevin

Friday, April 7, 2017 3:28 PM
0

Sign in to vote

Hi Kevin,

Leigh recommends checking out the fHTMLBodyTable() function. You pass the recordset object to this function, and it returns a HTML table with all the data from the recordset.

Hope it helps...

Friday, April 7, 2017 3:49 PM

Thanks. Here are the results I'm getting. I got a error on "Dim rst As DAO.rstordset" so I changed it to "Dim rst As DAO.Recordset". Now when I run the code, an email opens but the data in the body is missing. When I debug, the "olItem.HTMLBody = Join(strBody, vbNewLine)" line is high

Function EmailShipment()

    Dim olApp As Object
    Dim olItem As Variant
    Dim rst As DAO.Recordset
    Dim strQry As String
    Dim strBody As String

    strQry = "SELECT [ParcelShipments].TrackingNum, [ParcelShipments].PO_Num, [ParcelShipments].ShipDate " & _
             "FROM [ParcelShipments] " & _
             "WHERE [ParcelShipments].Matched=False"
    Set rst = CurrentDb.OpenRecordset(strQry)

    'Create the HTML table
    strBody = fHTMLBodyTable(rst, 2)

    'create the email
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.CreateItem(0)

    olItem.Display
    olItem.To = "xxx@yyyy.net"
    olItem.Subject = "Upcoming Expirations"
    olItem.HTMLBody = Join(strBody, vbNewLine)
    olItem.Display

End Function


Function fHTMLBodyTable(objRst As Object, Optional intBorder As Integer = 1) As String
    
    Dim strHTML As String
    Dim varFld
    
    'Header row based on field names
    strHTML = "<table border='" & intBorder & "'><tr>"
    For Each varFld In objRst.Fields
        strHTML = strHTML & "<th>" & varFld.Name & "</th>"
    Next
    strHTML = strHTML & "</tr>" & vbCrLf
    
    'Iterate Child data rows
    Do Until objRst.EOF
        strHTML = strHTML & "<tr>"
        For Each varFld In objRst.Fields
            strHTML = strHTML & "<td>" & varFld.Value & "</td>"
        Next
        strHTML = strHTML & "</tr>" & vbCrLf
        objRst.MoveNext
    Loop
    strHTML = strHTML & "</table>"
    
    fHTMLBodyTable = strHTML

End Function

lighted. I'm also sending my code. Thanks for your help.

Friday, April 7, 2017 5:45 PM

0

Sign in to vote
Hi,

What happens if you simply use?

olItem.HTMLBody = strBody

Would the data show up?
- Proposed as answer by Chenchen Li Monday, April 10, 2017 2:38 AM
Friday, April 7, 2017 6:56 PM
0

Sign in to vote

Beautiful! Worked great! Is there anyway to setup the font and font size?

Friday, April 7, 2017 7:21 PM
0

Sign in to vote

Beautiful! Worked great! Is there anyway to setup the font and font size?

You should be able to. Either try using CSS styles or plain HTML font tags.

Cheers!

Friday, April 7, 2017 7:41 PM

Cannot get the font to work. What am I doing wrong? Thanks.

Function EmailShipment()

    Dim olApp As Object
    Dim olItem As Variant
    Dim rst As DAO.Recordset
    Dim strQry As String
    Dim strBody As String

    strQry = "SELECT [ParcelShipments].TrackingNum, [ParcelShipments].PO_Num, [ParcelShipments].ShipDate " & _
             "FROM [ParcelShipments] " & _
             "WHERE [ParcelShipments].Matched=False"
    Set rst = CurrentDb.OpenRecordset(strQry)

    'Create the HTML table
    strBody = "<html><b><font size=9><face=Times New Roman><p>" & fHTMLBodyTable(rst, 2)

    'create the email
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.CreateItem(0)

    olItem.Display
    olItem.To = "xxx@yyyy.net"
    olItem.Subject = "Upcoming Expirations"
    olItem.HTMLBody = strBody
    olItem.Display

End Function


Function fHTMLBodyTable(objRst As Object, Optional intBorder As Integer = 1) As String
    
    Dim strHTML As String
    Dim varFld
    
    'Header row based on field names
    strHTML = "<table border='" & intBorder & "'><tr>"
    For Each varFld In objRst.Fields
        strHTML = strHTML & "<th>" & varFld.Name & "</th>"
    Next
    strHTML = strHTML & "</tr>" & vbCrLf
    
    'Iterate Child data rows
    Do Until objRst.EOF
        strHTML = strHTML & "<tr>"
        For Each varFld In objRst.Fields
            strHTML = strHTML & "<td>" & varFld.Value & "</td>"
        Next
        strHTML = strHTML & "</tr>" & vbCrLf
        objRst.MoveNext
    Loop
    strHTML = strHTML & "</table>"
    
    fHTMLBodyTable = strHTML

End Function

Friday, April 7, 2017 7:59 PM

0

Sign in to vote
Hi,

Try it this way:

strBody = "<html>" & fHTMLBodyTable(rst, 2)

Hope it helps...
- Proposed as answer by Chenchen Li Monday, April 10, 2017 2:38 AM
Friday, April 7, 2017 8:56 PM
0

Sign in to vote

Hello Kevin,

I suggest you mark helpful posts or your solution as answer to close the thread if your issue has been resolved.

If you have any additional issues, I suggest you post new threads. Thanks for your understanding.

Regards,

Celeste
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, April 10, 2017 2:40 AM
0

Sign in to vote

Hi DBguy,

That didn't work. Any other suggestions?

Thanks, Kevin

Monday, April 10, 2017 2:44 PM
0

Sign in to vote

Hello Celeste,

I wasn't finished with this thread. Please be mindful of the day of the week of the last reply when posting your comments. This is a work related thread and we closed on the weekends. I work for a good company that encourages us not to take our work home.

Regards, Kevin

Monday, April 10, 2017 2:49 PM
0

Sign in to vote

Hi Kevin,

What actually happened? Did you get an error message? Or, did the tags show up as plain text in the email body? My work does not allow HTML formatted email, so I can't really check or do any testing. Can you post an image of what the email looks like? Thanks.

Monday, April 10, 2017 3:26 PM
0

Sign in to vote

No errors appear. Each time I run the code, all the tables in the body of the email are font Times New Roman, size 12. The table header is Bold. I tried changing the font to Arial, size 9. No luck. Thanks.

Monday, April 10, 2017 3:35 PM
0

Sign in to vote

Can you view the HTML source for the email? Just wondering if the code is making it but the email client (Outlook) is ignoring it.

Monday, April 10, 2017 3:54 PM
0

Sign in to vote

My apologies DB guy, that's a little over my head. How do I go about checking if I can view the HTML source for the email? Thank you.

Monday, April 10, 2017 5:02 PM
0

Sign in to vote

Let's see... If you select File > Save As, what options do you get? Is there one for HTML?

Monday, April 10, 2017 5:42 PM
0

Sign in to vote

Yes, the email can be saved in html. See screenshot.

Monday, April 10, 2017 5:53 PM
0

Sign in to vote

Okay, try saving it as HTML and then open the file using a Text editor and show us the code behind it. Thanks.

Monday, April 10, 2017 6:08 PM

When I save the email in HTML format, I get a copy of the email with a .htm extension. It also saves a folder with three files. The first file is themedata.thmx. A text reader cannot read the code. The next file is colorschememapping.xml followed by filelist.xml. Code is below. Thanks.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<a:clrMap xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" bg1="lt1" tx1="dk1" bg2="lt2" tx2="dk2" accent1="accent1" accent2="accent2" accent3="accent3" accent4="accent4" accent5="accent5" accent6="accent6" hlink="hlink" folHlink="folHlink"/>





<xml xmlns:o="urn:schemas-microsoft-com:office:office">
 <o:MainFile HRef="../Upcoming%20Expirations.htm"/>
 <o:File HRef="themedata.thmx"/>
 <o:File HRef="colorschememapping.xml"/>
 <o:File HRef="filelist.xml"/>
</xml>

Monday, April 10, 2017 6:27 PM

Here the .htm file when I read it in a text reader:

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:w="urn:schemas-microsoft-com:office:word"
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Word.Document>
<meta name=Generator content="Microsoft Word 14">
<meta name=Originator content="Microsoft Word 14">
<link rel=File-List href="Upcoming%20Expirations_files/filelist.xml">
<link rel=Edit-Time-Data href="Upcoming%20Expirations_files/editdata.mso">
<!--[if gte mso 9]><xml>
 <o:DocumentProperties>
  <o:Template>NormalEmail.dotm</o:Template>
  <o:Revision>1</o:Revision>
  <o:Created>2017-04-10T18:17:00Z</o:Created>
  <o:Pages>1</o:Pages>
  <o:Words>66</o:Words>
  <o:Characters>382</o:Characters>
  <o:Company>Ralph Lauren Corporation</o:Company>
  <o:Lines>3</o:Lines>
  <o:Paragraphs>1</o:Paragraphs>
  <o:CharactersWithSpaces>447</o:CharactersWithSpaces>
  <o:Version>14.00</o:Version>
 </o:DocumentProperties>
 <o:OfficeDocumentSettings>
  <o:AllowPNG/>
 </o:OfficeDocumentSettings>
</xml><![endif]-->
<link rel=themeData href="Upcoming%20Expirations_files/themedata.thmx">
<link rel=colorSchemeMapping
href="Upcoming%20Expirations_files/colorschememapping.xml">
<!--[if gte mso 9]><xml>
 <w:WordDocument>
  <w:Zoom>0</w:Zoom>
  <w:TrackMoves/>
  <w:TrackFormatting/>
  <w:ValidateAgainstSchemas/>
  <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
  <w:IgnoreMixedContent>false</w:IgnoreMixedContent>
  <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
  <w:DoNotPromoteQF/>
  <w:LidThemeOther>EN-US</w:LidThemeOther>
  <w:LidThemeAsian>ZH-CN</w:LidThemeAsian>
  <w:LidThemeComplexScript>HI</w:LidThemeComplexScript>
  <w:Compatibility>
   <w:DoNotExpandShiftReturn/>
   <w:BreakWrappedTables/>
   <w:SplitPgBreakAndParaMark/>
   <w:EnableOpenTypeKerning/>
   <w:UseFELayout/>
  </w:Compatibility>
  <m:mathPr>
   <m:mathFont m:val="Cambria Math"/>
   <m:brkBin m:val="before"/>
   <m:brkBinSub m:val="&#45;-"/>
   <m:smallFrac m:val="off"/>
   <m:dispDef/>
   <m:lMargin m:val="0"/>
   <m:rMargin m:val="0"/>
   <m:defJc m:val="centerGroup"/>
   <m:wrapIndent m:val="1440"/>
   <m:intLim m:val="subSup"/>
   <m:naryLim m:val="undOvr"/>
  </m:mathPr></w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
 <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
  DefSemiHidden="true" DefQFormat="false" DefPriority="99"
  LatentStyleCount="267">
  <w:LsdException Locked="false" Priority="0" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
  <w:LsdException Locked="false" Priority="9" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
  <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
  <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
  <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
  <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
  <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
  <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
  <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
  <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
  <w:LsdException Locked="false" Priority="39" Name="toc 1"/>
  <w:LsdException Locked="false" Priority="39" Name="toc 2"/>
  <w:LsdException Locked="false" Priority="39" Name="toc 3"/>
  <w:LsdException Locked="false" Priority="39" Name="toc 4"/>
  <w:LsdException Locked="false" Priority="39" Name="toc 5"/>
  <w:LsdException Locked="false" Priority="39" Name="toc 6"/>
  <w:LsdException Locked="false" Priority="39" Name="toc 7"/>
  <w:LsdException Locked="false" Priority="39" Name="toc 8"/>
  <w:LsdException Locked="false" Priority="39" Name="toc 9"/>
  <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
  <w:LsdException Locked="false" Priority="10" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Title"/>
  <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
  <w:LsdException Locked="false" Priority="11" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
  <w:LsdException Locked="false" Priority="22" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
  <w:LsdException Locked="false" Priority="20" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
  <w:LsdException Locked="false" Priority="59" SemiHidden="false"
   UnhideWhenUsed="false" Name="Table Grid"/>
  <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
  <w:LsdException Locked="false" Priority="1" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
  <w:LsdException Locked="false" Priority="60" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Shading"/>
  <w:LsdException Locked="false" Priority="61" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light List"/>
  <w:LsdException Locked="false" Priority="62" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Grid"/>
  <w:LsdException Locked="false" Priority="63" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 1"/>
  <w:LsdException Locked="false" Priority="64" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 2"/>
  <w:LsdException Locked="false" Priority="65" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 1"/>
  <w:LsdException Locked="false" Priority="66" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 2"/>
  <w:LsdException Locked="false" Priority="67" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 1"/>
  <w:LsdException Locked="false" Priority="68" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 2"/>
  <w:LsdException Locked="false" Priority="69" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 3"/>
  <w:LsdException Locked="false" Priority="70" SemiHidden="false"
   UnhideWhenUsed="false" Name="Dark List"/>
  <w:LsdException Locked="false" Priority="71" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Shading"/>
  <w:LsdException Locked="false" Priority="72" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful List"/>
  <w:LsdException Locked="false" Priority="73" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Grid"/>
  <w:LsdException Locked="false" Priority="60" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
  <w:LsdException Locked="false" Priority="61" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light List Accent 1"/>
  <w:LsdException Locked="false" Priority="62" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
  <w:LsdException Locked="false" Priority="63" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
  <w:LsdException Locked="false" Priority="64" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
  <w:LsdException Locked="false" Priority="65" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
  <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
  <w:LsdException Locked="false" Priority="34" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
  <w:LsdException Locked="false" Priority="29" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
  <w:LsdException Locked="false" Priority="30" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
  <w:LsdException Locked="false" Priority="66" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
  <w:LsdException Locked="false" Priority="67" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
  <w:LsdException Locked="false" Priority="68" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
  <w:LsdException Locked="false" Priority="69" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
  <w:LsdException Locked="false" Priority="70" SemiHidden="false"
   UnhideWhenUsed="false" Name="Dark List Accent 1"/>
  <w:LsdException Locked="false" Priority="71" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
  <w:LsdException Locked="false" Priority="72" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
  <w:LsdException Locked="false" Priority="73" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
  <w:LsdException Locked="false" Priority="60" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
  <w:LsdException Locked="false" Priority="61" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light List Accent 2"/>
  <w:LsdException Locked="false" Priority="62" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
  <w:LsdException Locked="false" Priority="63" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
  <w:LsdException Locked="false" Priority="64" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
  <w:LsdException Locked="false" Priority="65" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
  <w:LsdException Locked="false" Priority="66" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
  <w:LsdException Locked="false" Priority="67" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
  <w:LsdException Locked="false" Priority="68" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
  <w:LsdException Locked="false" Priority="69" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
  <w:LsdException Locked="false" Priority="70" SemiHidden="false"
   UnhideWhenUsed="false" Name="Dark List Accent 2"/>
  <w:LsdException Locked="false" Priority="71" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
  <w:LsdException Locked="false" Priority="72" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
  <w:LsdException Locked="false" Priority="73" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
  <w:LsdException Locked="false" Priority="60" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
  <w:LsdException Locked="false" Priority="61" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light List Accent 3"/>
  <w:LsdException Locked="false" Priority="62" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
  <w:LsdException Locked="false" Priority="63" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
  <w:LsdException Locked="false" Priority="64" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
  <w:LsdException Locked="false" Priority="65" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
  <w:LsdException Locked="false" Priority="66" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
  <w:LsdException Locked="false" Priority="67" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
  <w:LsdException Locked="false" Priority="68" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
  <w:LsdException Locked="false" Priority="69" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
  <w:LsdException Locked="false" Priority="70" SemiHidden="false"
   UnhideWhenUsed="false" Name="Dark List Accent 3"/>
  <w:LsdException Locked="false" Priority="71" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
  <w:LsdException Locked="false" Priority="72" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
  <w:LsdException Locked="false" Priority="73" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
  <w:LsdException Locked="false" Priority="60" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
  <w:LsdException Locked="false" Priority="61" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light List Accent 4"/>
  <w:LsdException Locked="false" Priority="62" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
  <w:LsdException Locked="false" Priority="63" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
  <w:LsdException Locked="false" Priority="64" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
  <w:LsdException Locked="false" Priority="65" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
  <w:LsdException Locked="false" Priority="66" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
  <w:LsdException Locked="false" Priority="67" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
  <w:LsdException Locked="false" Priority="68" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
  <w:LsdException Locked="false" Priority="69" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
  <w:LsdException Locked="false" Priority="70" SemiHidden="false"
   UnhideWhenUsed="false" Name="Dark List Accent 4"/>
  <w:LsdException Locked="false" Priority="71" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
  <w:LsdException Locked="false" Priority="72" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
  <w:LsdException Locked="false" Priority="73" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
  <w:LsdException Locked="false" Priority="60" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
  <w:LsdException Locked="false" Priority="61" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light List Accent 5"/>
  <w:LsdException Locked="false" Priority="62" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
  <w:LsdException Locked="false" Priority="63" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
  <w:LsdException Locked="false" Priority="64" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
  <w:LsdException Locked="false" Priority="65" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
  <w:LsdException Locked="false" Priority="66" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
  <w:LsdException Locked="false" Priority="67" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
  <w:LsdException Locked="false" Priority="68" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
  <w:LsdException Locked="false" Priority="69" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
  <w:LsdException Locked="false" Priority="70" SemiHidden="false"
   UnhideWhenUsed="false" Name="Dark List Accent 5"/>
  <w:LsdException Locked="false" Priority="71" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
  <w:LsdException Locked="false" Priority="72" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
  <w:LsdException Locked="false" Priority="73" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
  <w:LsdException Locked="false" Priority="60" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
  <w:LsdException Locked="false" Priority="61" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light List Accent 6"/>
  <w:LsdException Locked="false" Priority="62" SemiHidden="false"
   UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
  <w:LsdException Locked="false" Priority="63" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
  <w:LsdException Locked="false" Priority="64" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
  <w:LsdException Locked="false" Priority="65" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
  <w:LsdException Locked="false" Priority="66" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
  <w:LsdException Locked="false" Priority="67" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
  <w:LsdException Locked="false" Priority="68" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
  <w:LsdException Locked="false" Priority="69" SemiHidden="false"
   UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
  <w:LsdException Locked="false" Priority="70" SemiHidden="false"
   UnhideWhenUsed="false" Name="Dark List Accent 6"/>
  <w:LsdException Locked="false" Priority="71" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
  <w:LsdException Locked="false" Priority="72" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
  <w:LsdException Locked="false" Priority="73" SemiHidden="false"
   UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
  <w:LsdException Locked="false" Priority="19" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
  <w:LsdException Locked="false" Priority="21" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
  <w:LsdException Locked="false" Priority="31" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
  <w:LsdException Locked="false" Priority="32" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
  <w:LsdException Locked="false" Priority="33" SemiHidden="false"
   UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
  <w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
  <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
 </w:LatentStyles>
</xml><![endif]-->
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:SimSun;
	panose-1:2 1 6 0 3 1 1 1 1 1;
	
	
	
	
	}
@font-face
	{font-family:Mangal;
	panose-1:2 4 5 3 5 2 3 3 2 2;
	
	
	
	}
@font-face
	{font-family:Mangal;
	panose-1:2 4 5 3 5 2 3 3 2 2;
	
	
	
	}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;
	
	
	
	}
@font-face
	{font-family:"\@SimSun";
	panose-1:2 1 6 0 3 1 1 1 1 1;
	
	
	
	}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{
	
	"";
	margin:0in;
	margin-bottom:.0001pt;
	
	font-size:12.0pt;
	font-family:"Times New Roman","serif";
	
	}
a:link, span.MsoHyperlink
	{
	
	color:blue;
	
	text-decoration:underline;
	text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
	{
	
	color:purple;
	
	text-decoration:underline;
	text-underline:single;}
p
	{
	
	
	margin-right:0in;
	
	margin-left:0in;
	
	font-size:12.0pt;
	font-family:"Times New Roman","serif";
	
	}
span.EmailStyle18
	{
	
	
	font-weight:bold;}
.MsoChpDefault
	{
	
	font-family:"Calibri","sans-serif";
	
	
	
	
	
	
	
	}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;
	
	
	}
div.WordSection1
	{page:WordSection1;}
-->
</style>
<!--[if gte mso 10]>
<style>
 /* Style Definitions */
 table.MsoNormalTable
	{"Table Normal";
	
	
	
	
	"";
	
	
	
	
	font-size:11.0pt;
	
	font-family:"Calibri","sans-serif";
	
	
	
	}
</style>
<![endif]--><!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext="edit" spidmax="1026"/>
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext="edit">
  <o:idmap v:ext="edit" data="1"/>
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=EN-US link=blue vlink=purple style='tab-interval:.5in'>

<div

<p style='margin-left:120.0pt;text-indent:-120.0pt;tab-stops:
120.0pt;text-autospace:none'><b><span
style='font-family:"Calibri","sans-serif";color:black'>To:<span style='                                   </span></span></b><span
style='font-family:"Calibri","sans-serif";color:black'>xxx@yyyy.net<o:p></o:p></span></p>

<p style='margin-left:120.0pt;text-indent:-120.0pt;tab-stops:
120.0pt;text-autospace:none'><b><span
style='font-family:"Calibri","sans-serif";color:black'>Subject:<span style='                          </span></span></b><span
style='font-family:"Calibri","sans-serif";color:black'>Upcoming Expirations<o:p></o:p></span></p>

<p

<table border=1 cellpadding=0 style='
 border:outset 1.5pt;
 <tr style='
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>TrackingNum<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>FedExAcctNum<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>FedExAcctName<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>ShipDate<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>3PL<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>ShipToName<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>ShipToAddress1<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>ShipToAddress2<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>ShipToCity<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>ShipToState<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>ShipToZip<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>PO_Reference<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>SIREF<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>EstimatedDeliveryDate<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>LastStatus<o:p></o:p></b></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p align=center style='text-align:center'><b>LastStatusUpdate<o:p></o:p></b></p>
  </td>
 </tr>
 <tr style='
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'></td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p CHAMPIONSHIP FNDN INC<o:p></o:p></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p SUGARLOAF CLUB DR.<o:p></o:p></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'></td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p 2000494132<o:p></o:p></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p FedEx location<o:p></o:p></p>
  </td>
  <td style='padding:.75pt .75pt .75pt .75pt'>
  <p - 0326<o:p></o:p></p>
  </td>
 </tr>
</table>

<p

</div>

</body>

</html>

Monday, April 10, 2017 6:30 PM

0

Sign in to vote

Hi DBguy,

FYI, I changed the font to Arial. I searched for it in the text editor and it looks as if it isn't getting passed from the code in VB:

strBody = "<html>" & fHTMLBodyTable(rst, 2)

Any suggestions?

Thanks for your help.

Monday, April 10, 2017 7:00 PM
0

Sign in to vote
Hi,

This is hard to troubleshoot without actually being there to see it. Try the following just to see if anything changes:

strBody = "THIS IS JUST A TEST..." & fHTMLBodyTable(rst, 2)
Monday, April 10, 2017 7:06 PM
0

Sign in to vote

Here are the results: It added the line "THIS IS JUST A TEST" with Times New Roman font and font size 36. The table was unchanged (Times New Roman font and font size 12).

I performed a second test with your code using Arial font and font size 11. The font changed to Arial, but the size is still 36. The table was unchanged (Times New Roman font and font size 12).

Monday, April 10, 2017 7:15 PM
0

Sign in to vote
Well many HTML tags are incorrect for one thing.



<table border=1 cellpadding=0 style='
border:outset 1.5pt;

missing closing ' and >

<tr style='

missing content and ' and >

and so on. So the basic html generation function need to be address before troubleshooting other things.

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net
- Edited by Daniel Pineault (MVP)MVP Monday, April 10, 2017 7:24 PM
Monday, April 10, 2017 7:22 PM
0

Sign in to vote
Here are the results: It added the line "THIS IS JUST A TEST" with Times New Roman font and font size 36. The table was unchanged (Times New Roman font and font size 12).

I performed a second test with your code using Arial font and font size 11. The font changed to Arial, but the size is still 36. The table was unchanged (Times New Roman font and font size 12).

Hi Kevin,

Yes, I was just testing the font size and type with the text "THIS IS A TEST" before applying it to the table, if it works.

So, it looks like we can affect the font type but still need to fix the size. Try the following now...

strBody = "THIS IS JUST A TEST..." & fHTMLBodyTable(rst, 2)
Monday, April 10, 2017 7:27 PM

Hi Daniel,

Below is the VB code that DBguy and I working on. If you have any suggestions on how to get get the html generation function to work, please list it.

DBguy, any thoughts on our last test would be very welcome.

Thanks, Kevin

Function EmailShipment()

    Dim olApp As Object
    Dim olItem As Variant
    Dim rst As DAO.Recordset
    Dim strQry As String
    Dim strBody As String

    strQry = "SELECT [FedEx MASTER].TrackingNum, [FedEx MASTER].FedExAcctNum, [FedEx MASTER].FedExAcctName, " & _
             "[FedEx MASTER].ShipDate, [FedEx MASTER].[3PL], [FedEx MASTER].ShipToName, " & _
             "[FedEx MASTER].ShipToAddress1, [FedEx MASTER].ShipToAddress2, [FedEx MASTER].ShipToCity, " & _
             "[FedEx MASTER].ShipToState, [FedEx MASTER].ShipToZip, [FedEx MASTER].PO_Reference, " & _
             "[FedEx MASTER].SIREF, [FedEx MASTER].EstimatedDeliveryDate, [FedEx MASTER].LastStatus, " & _
             "[FedEx MASTER].LastStatusUpdate " & _
             "FROM [FedEx MASTER] " & _
             "WHERE [FedEx MASTER].Matched=False"
    Set rst = CurrentDb.OpenRecordset(strQry)

    'Create the HTML table
    strBody = "<p><font size='11' face='Arial'>THIS IS JUST A TEST...</font></p>" & fHTMLBodyTable(rst, 2)

    'create the email
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.createitem(0)

    olItem.Display
    olItem.To = "xxx@yyyy.net"
    olItem.Subject = "Upcoming Expirations"
    olItem.HTMLBody = strBody
    olItem.Display

End Function


Function fHTMLBodyTable(objRst As Object, Optional intBorder As Integer = 1) As String
    
    Dim strHTML As String
    Dim varFld
    
    'Header row based on field names
    strHTML = "<table border='" & intBorder & "'><tr>"
    For Each varFld In objRst.Fields
        strHTML = strHTML & "<th>" & varFld.Name & "</th>"
    Next
    strHTML = strHTML & "</tr>" & vbCrLf
    
    'Iterate Child data rows
    Do Until objRst.EOF
        strHTML = strHTML & "<tr>"
        For Each varFld In objRst.Fields
            strHTML = strHTML & "<td>" & varFld.Value & "</td>"
        Next
        strHTML = strHTML & "</tr>" & vbCrLf
        objRst.MoveNext
    Loop
    strHTML = strHTML & "</table>"
    
    fHTMLBodyTable = strHTML

End Function

Monday, April 10, 2017 7:32 PM

0

Sign in to vote
DBguy, any thoughts on our last test would be very welcome.

Hi Kevin,

I posted a new code earlier using "px" for testing. Did it show any change to the resulting email body?

Here's what I said:

strBody = "THIS IS JUST A TEST..." & fHTMLBodyTable(rst, 2)
Just trying to see if the font changes from 36 to something else...
Monday, April 10, 2017 7:54 PM
0

Sign in to vote

My apologies for missing your post DBguy. It must have come in at the same time as Daniel's and I missed it.

The code you posted changed the font on the email to size 12. The font is Times New Roman. The table was unchanged (Times New Roman font and font size 12).

My my opinion, we achieved the goal of this thread. That is to pass query results to the body of a email. I'm going to post that working code (minus the font code) and mark it as my answer.

DBguy, always a pleasure working with you my friend. Thanks for all your help.

Monday, April 10, 2017 8:03 PM
0

Sign in to vote

Hi Kevin,

You're welcome. Glad Daniel and I were able to assist. Good luck with your project.

Monday, April 10, 2017 8:05 PM

Below is the VB code. This code does not contain any controls for font and font size.

Function EmailShipment()

    Dim olApp As Object
    Dim olItem As Variant
    Dim rst As DAO.Recordset
    Dim strQry As String
    Dim strBody As String

    strQry = "SELECT [TableName].FieldName, [TableName].FieldName, [TableName].FieldName, " & _
             "[TableName].FieldName " & _
             "FROM [TableName] "
    Set rst = CurrentDb.OpenRecordset(strQry)

    'Create the HTML table
    strBody = fHTMLBodyTable(rst, 2)

    'create the email
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.createitem(0)

    olItem.Display
    'olItem.To = "xxx@yyyy.net"
    olItem.Subject = "Enter subject here"
    olItem.HTMLBody = strBody
    olItem.Display

End Function


Function fHTMLBodyTable(objRst As Object, Optional intBorder As Integer = 1) As String
    
    Dim strHTML As String
    Dim varFld
    
    'Header row based on field names
    strHTML = "<table border='" & intBorder & "'><tr>"
    For Each varFld In objRst.Fields
        strHTML = strHTML & "<th>" & varFld.Name & "</th>"
    Next
    strHTML = strHTML & "</tr>" & vbCrLf
    
    'Iterate Child data rows
    Do Until objRst.EOF
        strHTML = strHTML & "<tr>"
        For Each varFld In objRst.Fields
            strHTML = strHTML & "<td>" & varFld.Value & "</td>"
        Next
        strHTML = strHTML & "</tr>" & vbCrLf
        objRst.MoveNext
    Loop
    strHTML = strHTML & "</table>"
    
    fHTMLBodyTable = strHTML

End Function

Marked as answer by KevinATF Monday, April 10, 2017 8:10 PM

Monday, April 10, 2017 8:10 PM

Answered by:

Query Results in Outlook Email Body

Question

Answers

All replies