在Access中创建Outlook收件箱电子邮件表 [英] Creating table of Outlook Inbox emails in Access

问题描述

更新:

以下符合推荐的SQL构造的当前代码:SqlString =

Current code below in accordance with recommended SQL construct: error in SqlString =

运行时错误"3011":Microsoft Access数据库引擎找不到对象.确保对象存在，并且您正确拼写了它的名称和路径名.如果不是本地对象，请检查您的网络连接或与服务器管理员联系.

Run-time error '3011': The Microsoft Access database engine could not find the object ". Make sure the object exists and that you spell its name and the path name correctly. If " is not a local object, check your network connection or contact the server administrator.

值得注意的是，我正在使用USAF未分类的网络系统，并通过CAC登录.

Of note, I am working on a USAF unclassified network system, and log in via CAC.

Sub InboxImport
    Dim SqlString As String
    Dim ConnectionString As String
    Dim EmailTableName As String
    Dim UserIdNum As String
    Dim EmailAddr As String
    Dim olNS As Outlook.NameSpace
    Dim olFol As Outlook.Folder`

    Set ol = CreateObject("Outlook.Application")
    Set olNS = ol.GetNamespace("MAPI")
    Set olFol = olNS.GetDefaultFolder(olFolderInbox)

    EmailTableName = "MyInbox" 'My table name
    UserIdNum = Environ("USERNAME")  '1277523A... acct #
    EmailAddr = olFol.Parent.name 'user's email address
    ConnectionString = "Outlook 9.0;MAPILEVEL=" & EmailAddr & "|;PROFILE=Default Outlook Profile;TABLETYPE=0;TABLENAME=MyInbox;COLSETVERSION=12.0;DATABASE=C:\Users\" & UserIdNum & "\AppData\Local\Temp\"

    SqlString = "SELECT [From] As [Sender], [Sender Name] As SenderName, [Subject Prefix] & [Normalized Subject] As Subject, [Contents] As [Body], [Received] As [ReceivedTime]" & _
                " INTO [Email]" & _
                " From [" & ConnectionString & "].[MyInbox]"
    DoCmd.RunSQL SqlString
end sub

---

原始文本:

我正在尝试将默认的Outlook收件箱电子邮件拉到Access中的表中.我能够使用向导成功检索电子邮件并填充各个列，并通过名为收件箱"的Access表查看我当前的收件箱.

I am attempting to pull default Outlook inbox emails into a table within Access. I am able to use the wizard to successfully retrieve emails and populate the various columns and view my current inbox via Access table named "Inbox".

我的Access数据库将由多个员工同时使用，我不能要求他们为他们登录的每台不同计算机运行向导.

My Access database will be used by several employees at the same time, and I can't ask them to run the wizard for every different computer they log into.

我正在使用从页面中间复制的代码... .

I am using code copied from middle of the page..."Export Outlook Emails to Access table - VBA".

我正在尝试使用

DoCmd.RunSQL "INSERT INTO [Email] " & _
     "([Sender], [SenderName], [Subject], [Body], [ReceivedTime])" & _
     "VALUES " & _
     "'" & objProp(i).Sender & "', '" & _     'ERROR!
     objProp(i).SenderName & "', " & _  'ERROR!
     objProp(i).Subject & "', '" & _
     objProp(i).Body & "', '" & _       'ERROR!
     objProp(i).ReceivedTime & "';"

代码偶然发现除.ReceivedTime或.Subject之外的任何MailItem属性，并且这些属性引发错误...

The code stumbles looking at any MailItem property other than .ReceivedTime or .Subject, and those properties throw an error of...

运行时错误'287':应用程序定义或对象定义的错误

Run-time error '287': Application-defined or object-defined error

供我参考-数据库:

1. 用于应用程序的Visual Basic
2. Microsoft Access 15.0对象库
3. OLE自动化
4. Microsoft Office 15.0 Access数据库引擎对象库
5. Microsoft Internet控件
6. Microsoft Outlook 15.0对象库

推荐答案

我强烈建议您从Outlook导入邮件时不要尝试使用这种方法. Access可以与SQL查询中的Outlook数据文件一起使用.当然，您可以使用VBA执行这些查询.但这会更加优化.

I strongly recommend you don't take the tried approach when importing mail from Outlook. Access can natively work with Outlook Data Files in SQL queries. You can, of course, execute these queries using VBA. But it will be way more optimized.

技巧是获取正确的连接字符串.您可以通过以下过程轻松获得连接字符串:

The trick is getting the proper connection string. You can easily obtain the connection string by using the following process:

1. 在外部数据"->更多"->"Outlook"文件夹下，创建到所需Outlook文件夹的链接表，选择链接表，然后选择文件夹
2. 使用Debug.Print CurrentDb.TableDefs!MyLinkedOutlookFolder.Connect获取连接字符串，使用Debug.Print CurrentDb.TableDefs!MyLinkedOutlookFolder.SourceTableName获取外部表名称

3. 使用获得的变量执行以下查询:

1. Create a linked table to the desired outlook folder under External Data -> More -> Outlook folder, choose linked table, select the folder
2. Use Debug.Print CurrentDb.TableDefs!MyLinkedOutlookFolder.Connect to obtain the connection string, and Debug.Print CurrentDb.TableDefs!MyLinkedOutlookFolder.SourceTableName to obtain the external table name

3. Execute the following query, using your obtained variables:

SELECT [From] As [Sender], [Sender Name] As SenderName, [Subject Prefix] & [Normalized Subject] As Subject, [Contents] As [Body], [Received] As [ReceivedTime]
INTO [Email]
FROM [ThatConnectionString].[ThatSourceTableName]

示例连接字符串:

Outlook 9.0;MAPILEVEL=[e-mail]|;PROFILE=Default Outlook Profile;TABLETYPE=0;TABLENAME=Inbox;COLSETVERSION=12.0;DATABASE=C:\Users\Me\AppData\Local\Temp\

示例源表名称:

Inbox

这就是您所需要的，不需要复杂的VBA.

That's all you need, no complex VBA needed.

这篇关于在Access中创建Outlook收件箱电子邮件表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！