Access 2013 VBA：查询收件人电子邮件地址 [英] Access 2013 VBA: Query recipient email address

问题描述

如何添加以下SQL查询作为Outlook电子邮件的收件人？

How do I add the below SQL query AS the recipient of an Outlook email?

SELECT 
     E.Email
FROM 
     Employee E
          INNER JOIN ProposalTracking P ON E.Initials = P.UW

这里是到目前为止仅在Outlook中打开新电子邮件的VBA代码：

Here's the VBA code which merely opens a new email in Outlook so far:

Private Sub btnEmail_Click()

Dim varName As Variant
Dim varSubject As Variant
Dim varBody As Variant

varName = "[e-mail]"

varSubject = "TPS Report"

varBody = "Did you see the memo? We use the new cover sheets now."

DoCmd.SendObject , , , varName, , varSubject, varBody, True, False

End Sub

推荐答案

通过DAO做到这一点的一种方法：

One way of doing it through DAO:

Dim email as string
Dim sqlSTR as string
Dim r As DAO.Recordset
Dim dbs as DAO.database
set dbs = currentDB()
sqlSTR = "SELECT E.Email FROM  Employee E INNER JOIN ProposalTracking P ON E.Initials = P.UW"

Set r = dbs.OpenRecordset(Name:=sqlSTR , Type:=dbOpenSnapshot)
varName = r![Email]
r.close

但是，如果这样做这样，我将在where子句中添加变量。看起来像这样：

Although, if doing it this way, I would add variable in the where clause. It would look like this:

Dim varSubject As Variant
Dim varBody As Variant
Dim email as string
Dim sqlSTR as string
Dim r As DAO.Recordset
Dim varInitials as string
Dim dbs as DAO.database

set dbs = currentDB()

varInitials = "P.UW"
sqlSTR = "SELECT E.Email FROM  Employee E INNER JOIN ProposalTracking P ON E.Initials = P.UW WHERE " & someField & " = & " & """" & someVariable & """"

Set r = dbs.OpenRecordset(Name:=sqlSTR , Type:=dbOpenSnapshot)
varName = r![Email]
r.close

varSubject = "TPS Report"

varBody = "Did you see the memo? We use the new cover sheets now."

DoCmd.SendObject , , , varName, , , varSubject, varBody, True, False

在sqlSTR中，用您要匹配的内容（例如P.UW）替换someField，并用您希望匹配的某个值（例如 John）替换someVariable。如果是int值，请删除&来自 someVariable值的

In the sqlSTR, replace someField with whatever your matching is (for example, P.UW) and someVariable with some value you wish to match (e.g. "John"). If it's an int value, remove the & """" from around the someVariable value

这篇关于Access 2013 VBA：查询收件人电子邮件地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！