我正在制作一個格式化電子郵件正文的功能。它一次創建一個 HTMLtable 并用 db 表中的資料回圈/填充它。它從 Onclick 事件中呼叫，并且所有內容都使用 Outlook.Application 正確調暗。
以下代碼有效，但僅提取 InputWalls 表資料。我不知道如何在 VBA 中將 2 個 SQL 陳述句連接在一起，這樣我就可以從兩個表中提取資料并在Do While Not ws.EOF中回圈。
這個左連接陳述句位于我資料庫中其他地方的查詢中，但我似乎無法對其進行逆向工程和回圈，它非常適合我需要的東西
FROM InputWalls LEFT JOIN SpecSheet ON (InputWalls.Client = SpecSheet.Client) AND (InputWalls.Code = SpecSheet.Code)
因此結合 InputWalls SpecSheet，這將在電子郵件正文中輸出一個資料表。它將被格式化為一個表格，其中包含來自兩個作業表的混合值，但關系是與將它們鏈接在一起的“客戶”和“代碼”。
下面只是顯示它輸出正確。它是空的，但我已經用變數對其進行了測驗。現在您可以使用 ws!FIELDNAME 拉取表資料
功能：
Public Sub SendHtmlMailFromAccess()
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olMessage As Outlook.MailItem
Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olMessage = olApp.CreateItem(olMailItem)
With olMessage
.subject = ""
.HTMLBody = BuildHtmlBody()
.bodyFormat = 2
.Display
End With
End Sub
我對 EOF 線的問題：
Function BuildHtmlBody()
Dim html, ID, Plot, Ref, SpecCodes, Series, SIZE, BOXQTY, RateBox, Ext
html = "<!DOCTYPE html><html><body>"
html = html & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
html = html & "Dear {name}, <br /><br />This is a test email from MS Access using VBA. <br />"
html = html & "Here is current recordset data:<br /><br />"
html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
'table
Dim ws, WAsql
Set ws = CreateObject("ADODB.Recordset")
WAsql = "Select * from InputWalls WHERE SITE = """ & Me.SITE.Value & """ AND PLOTNO = """ & Me.PLOTNO.Value & """ AND CLIENT = """ & Me.CLIENT.Value & """"
ws.Open WAsql, CurrentProject.Connection
ws.MoveFirst
Do While Not ws.EOF
Plot = Trim(ws!PLOTNO)
Ref = Trim(ws!Ref)
SpecCodes = "" 'Here is where I want to pull SpecSheet!Code from SpecSheet table where SpecSheet!Code = InputWalls.Code
Series = Trim(ws!Series)
SIZE = "" 'Also from spec Sheet
BOXQTY = "" 'Also from spec Sheet
RateBox = "" 'Also from spec Sheet
Ext = "" 'Also from spec Sheet
html = html & "<tr>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & Plot & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & Ref & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & SpecCodes & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & Series & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & SIZE & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & BOXQTY & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & RateBox & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & Ext & "</td>"
html = html & "</tr>"
ws.MoveNext
Loop
html = html & "</table></div></body></html>"
BuildHtmlBody = html
End Function
uj5u.com熱心網友回復：
未經測驗，
嘗試改變這一點：
WAsql = "Select * from InputWalls WHERE SITE = """ & Me.SITE.Value & """ AND PLOTNO = """ & Me.PLOTNO.Value & """ AND CLIENT = """ & Me.CLIENT.Value & """"
對此：
WAsql = "Select * from InputWalls LEFT JOIN SpecSheet ON (InputWalls.Client = SpecSheet.Client) AND (InputWalls.Code = SpecSheet.Code) WHERE InputWalls.SITE = """ & Me.SITE.Value & """ AND InputWalls.PLOTNO = """ & Me.PLOTNO.Value & """ AND InputWalls.CLIENT = """ & Me.CLIENT.Value & """"
并使用：
ws!SpecSheet.SIZE
ws!SpecSheet.BOXQTY
ws!SpecSheet.RateBox
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/488979.html
下一篇：結果合并兩個具有獨立列的表