Question

outlook的vba怎样读取excel的数据呢

要做一个小东西.
当某特定email地址发给我邮件时, 我要提取里面的的一些信息, 然后与我电脑里面的一个excel中的表格作比较. 如果mail里面的信息与excel里面的信息一致则把相关的数据转发给其他一些人. 现在没搞定的是怎么用outlook读取excel里面的信息? 高分!
已经解决了. 大家都没看题.不是访问数据库,是访问Excel. 其实挺简单的:
Dim myExcel
Dim myExcelPath As String
myExcelPath = "C:\1.xlsx"
Dim myExcelName As String
myExcelName = "1.xlsx"

Set myExcel = CreateObject("Excel.Application")
myExcel.workbooks.Open FileName:=myExcelPath, ReadOnly:=True
myExcel.Visible = False
myExcel.Windows(myExcelName).Activate
然后用:
myExcel.Sheets("Sheet1").Cells(1, 1)就可以访问你的excel了

Answer 1

Dim db As Database
Dim rs As Recordset
Dim sql As String

sql = "SELECT 帐单明细.品种ID "
sql = sql & "FROM (物资收支库 INNER JOIN 实物账本 ON 物资收支库.物资收支库ID = 实物账本.物资收支库ID) INNER JOIN 帐单明细 ON 实物账本.帐单ID = 帐单明细.帐单ID "
sql = sql & "WHERE (((物资收支库.物资收支库ID) =" & Me.物资收支库ID & ") And ((实物账本.日期) <= #" & Me.日期 & "#)) GROUP BY 帐单明细.品种ID;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, 2)

参考下我的代码，实际上就是将查询表的sql按行复制出来，并把其中的参数部分按上面的方式改动下即可，至于直接使用“查询表名”为参数的我也不知道，但上面的sql也看似复杂，但更改起来也很简单，你可以试试！

Answer 2

Public Function InsertToTransact(strUID As String, strText As String, strTran As String, strSpecial As String) As Boolean
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strAccess As String
strAccess = "D:\VBA Work\OutLook\DataBase\Application Record.mdb"

On Error GoTo ErrorHandle

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strAccess
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer

rst.Open Source:="Transact_Record", _
ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

rst.AddNew
rst("UID") = strUID
rst("MailDatetime") = Format(Now, "YYYY-MM-DD hh:mm:ss")
rst("MailContent") = strText
rst("bTransact") = strTran
rst("Sepecial_ID") = strSpecial
rst.Update

rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing
If ControlTxtFile("Date", g_strPath_Date, "InsertToTransact-----Success!") Then

End If
Exit Function
ErrorHandle:
'rst.Close
cnn.Close
If ControlTxtFile("Date", g_strPath_Date, "InsertToTransact-----Failure!") Then

End If
Set rst = Nothing
Set cnn = Nothing
'MsgBox "Update failure!"
End Function

这个是一个例子，可以UPDATE数据库，但是你需要查询的话更简单，你可以用上面的代码改编一下。