UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> View / Store Emails In Access, Office 2013    
 
   
lex
post Jul 3 2017, 09:26 AM
Post#1



Posts: 671
Joined: 20-October 05



I have found the code here

Visit My Website

that shows how to do this, but it is not updated. Does anyone have any updated VBA code?

I could go through the errors, if you're interested...

Thanks

FYI - the 'version" dropdown for the post doesn't have 2016.

Lex
Go to the top of the page
 
Doug Steele
post Jul 3 2017, 10:34 AM
Post#2


UtterAccess VIP
Posts: 21,494
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Updated in what way? I don't see anything in that code that shouldn't work with Outlook 2016.

I assume you've set a reference to the appropriate version of Outlook in your Access code.

--------------------
Go to the top of the page
 
theDBguy
post Jul 3 2017, 10:35 AM
Post#3


Access Wiki and Forums Moderator
Posts: 71,217
Joined: 19-June 07
From: SunnySandyEggo


Hi Lex,

I don't, but it might be worth it to contact the author directly. There is a link on the page to do so. Good luck.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
lex
post Jul 3 2017, 12:51 PM
Post#4



Posts: 671
Joined: 20-October 05



I'm on Outlook and Access versions 1705 (see below)

Attached File  2017_07_03_13_46_53.jpg ( 132.05K )Number of downloads: 3


Access version

Attached File  2017_07_03_13_48_47.jpg ( 128.83K )Number of downloads: 1



Below is the error

Attached File  2017_07_03_13_45_12.jpg ( 290.88K )Number of downloads: 8


and referencing Outlook 16.0 Object Library.

I don't ref

Attached File  2017_07_03_13_50_16.jpg ( 63.29K )Number of downloads: 2


Do you see anything?

Thanks, Doug
Go to the top of the page
 
lex
post Jul 3 2017, 12:52 PM
Post#5



Posts: 671
Joined: 20-October 05



DBGuy... I thought of that, but saw a post with someone asking a question a couple years ago, which went left unanswered.

Thanks for the thought!

Lex
Go to the top of the page
 
theDBguy
post Jul 3 2017, 01:12 PM
Post#6


Access Wiki and Forums Moderator
Posts: 71,217
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I didn't mean to add a post but actually contact the author (owner) directly using the many links they provided on the page.

Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Jul 3 2017, 02:35 PM
Post#7



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


A few months ago I created a relatively simple DB Application (Access 2016) for a friend that Downloaded all E-Mails for a User that were in their Inbox. There is a User-Defined Section in the Code that indicates both From and To Date Ranges in order to limit the number of E-Mails Imported. It loops thru all E-Mails in the Inbox, and if they are in the specified Range, Imports and Appends them to a Table. Once this Data is in Table Format, there are many options available to you. The Properties Imported and Appended to the Table are: SentOn, Subject, Body, SenderName, Attachments (count of), Importance (Low, Normal, High), and Sensitivity (Normal, Personal, Private, and Confidential). I am not really sure if this suits your needs or not, but if you are interested, just let me know and I'm sure that I can dig it up.
Go to the top of the page
 
lex
post Jul 3 2017, 03:27 PM
Post#8



Posts: 671
Joined: 20-October 05



ADezii, that would be GREAT!!

You're fab.

Thanks

Lex

PS - have a great 4th!!
Go to the top of the page
 
ADezii
post Jul 3 2017, 03:59 PM
Post#9



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. The Code uses Early Binding (my preference) and contains a Reference to the Outlook 16.0 Object Library. You will probably have to change the Reference to point to the Outlook 15.0 Object Library.
  2. As it stands now, the Code will Import all E-Mails in the Inbox for a User for 2017. To change this Date Range, modify the Constants in the USER DEFINED SECTION as indicated below:
    CODE
    '******* USER DEFINED SECTION *******
    Const conFROM As Date = #1/1/2017#
    Const conTO As Date = #12/31/2017#
    '************************************
  3. The Code should be easily adaptable to suit your specific needs.
  4. Good Luck with your Project!

Attached File(s)
Attached File  Import_EMails.zip ( 40.11K )Number of downloads: 6
 
Go to the top of the page
 
Doug Steele
post Jul 3 2017, 10:47 PM
Post#10


UtterAccess VIP
Posts: 21,494
Joined: 8-January 07
From: St. Catharines, ON (Canada)


OutlookFolderNames is not something built into Outlook: it's a custom function. Looking at Juan's code, I see that function defined on page 5. Did you remember to copy it into your project? If so, where did you put it?

--------------------
Go to the top of the page
 
lex
post Jul 5 2017, 06:51 AM
Post#11



Posts: 671
Joined: 20-October 05



Thanks ADezii

Why did you think I might need to change to the 15.0 library? I only see 16.0 (see above screenshot)

I hate to be a pain, but got error Type mismatch at the below line

Attached File  2017_07_05_7_46_07.jpg ( 142.29K )Number of downloads: 5


I commented out all the assignment statements and still got the error. Maybe it has to do with the library you mentioned? See post Jul 3 2017, 06:51 PM for what I have.

Any thoughts are appreciated.

Lex
Go to the top of the page
 
ADezii
post Jul 5 2017, 07:19 AM
Post#12



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Very strange Error given the context that you are describing. Please post the Code in its 'entirety' so I can see the complete picture. The Code was thoroughly tested prior to the Upload and was fully operational. Not really sure what is going on, but we will find out. Have a hunch is has to do with the Object Variable msg's Declaration, but not really sure. Again, kindly post all the Code. iconfused.gif

P.S. - In your previous posting, I see no reference to the Microsoft Outlook XX.X Object Library.
Go to the top of the page
 
lex
post Jul 5 2017, 11:51 AM
Post#13



Posts: 671
Joined: 20-October 05



So, I downloaded again and got the same error. Sorry, I thought I had pasted a screenshot of the Object Library. Please find below

Attached File  2017_07_05_12_41_23.jpg ( 422.91K )Number of downloads: 5


and below, you should see I don't have 15.0


Attached File  2017_07_05_12_41_59.jpg ( 432.42K )Number of downloads: 3


and here is the form code as downloaded

CODE
Option Compare Database
Option Explicit

Private Sub cmdTest_Click()
On Error GoTo Err_cmdTest_Click
'Use Early Binding for Outlook Declarations
Dim myOlApp As New outlook.Application
Dim myOlItems As outlook.Items
Dim msg As outlook.MailItem
Dim objNS As outlook.NameSpace
Dim oMAPIFldr As outlook.MAPIFolder
Dim intCtr As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblEMails", dbOpenDynaset, dbAppendOnly)

Set myOlItems = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items

'Set Outlook Folders to use (Inbox)
Set objNS = myOlApp.GetNamespace("MAPI")
Set oMAPIFldr = objNS.GetDefaultFolder(olFolderInbox) 'my inbox

'******* USER DEFINED SECTION *******
Const conFROM As Date = #7/4/2017#
Const conTO As Date = #7/5/2017#
'************************************

CurrentDb.Execute "DELETE * FROM tblEMails", dbFailOnError      'Clear Table

DoCmd.Hourglass True

Set msg = oMAPIFldr.Items(1)

For Each msg In myOlItems
  If TypeName(msg) = "MailItem" Then    'An E-Mail
    If msg.SentOn >= conFROM And msg.SentOn <= conTO Then       'in Date Range?
     intCtr = intCtr + 1
      With msg
        rst.AddNew
          rst![Sent] = .SentOn
          rst![Subject] = .Subject
          rst![Body] = .Body
          rst![Sender] = .SenderName
          rst![Attachments] = .Attachments.Count
          rst![Importance] = IIf(.Importance = olImportanceLow, "Low", IIf(.Importance = olImportanceNormal, _
                                   "Normal", "High"))
          rst![Sensitivity] = IIf(.Sensitivity = olNormal, "Normal", IIf(.Sensitivity = olPersonal, "Personal", _
                               IIf(.Sensitivity = olPrivate, "Private", "Confidential")))
        rst.Update
      End With
    End If
  End If
Next

rst.Close
Set rst = Nothing
Set MyDB = Nothing
Set myOlApp = Nothing
Set objNS = Nothing
Set oMAPIFldr = Nothing

DoCmd.Hourglass False

If intCtr > 0 Then
  MsgBox intCtr & " E-Mails between the Date Range of " & conFROM & " to " & conTO & _
        " have been Imported into tblEMails", vbInformation, "E-Mail Import Status"
Else
  MsgBox "No E-Mails were Imported into tblEMails", vbExclamation, "Import Failure"
End If

With DoCmd
  .OpenTable "tblEMails", acViewNormal, acReadOnly
  .Maximize
End With

Exit_cmdTest_Click:
  Exit Sub

Err_cmdTest_Click:
  DoCmd.Hourglass False
    MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
      Resume Exit_cmdTest_Click
End Sub
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

  DoCmd.Quit

Exit_cmdExit_Click:
    Exit Sub

Err_cmdExit_Click:
    MsgBox Err.Description
    Resume Exit_cmdExit_Click
    
End Sub

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Call GenerateBBCodesForTable("Month")

Exit_Command2_Click:
  Exit Sub

Err_Command2_Click:
  MsgBox Err.Description
    Resume Exit_Command2_Click
End Sub


and the Module code as downloaded

CODE
Option Compare Database
Option Explicit
Public Sub GenerateBBCodesForTable(strDataSource As String)
Const sCell As String = "[cell]"    'Start Cell
Const eCell As String = "[/cell]"   'End Cell
Const sRow As String = "[row]"      'Start Row
Const eRow As String = "[/row]"     'End Row
Dim strRowData As String
Dim strTable As String
Dim intFldCtr As Integer
Dim varRetVal As Variant

With CurrentDb.OpenRecordset(strDataSource, dbOpenForwardOnly)
  'Build Header Row
  strRowData = ""
  For intFldCtr = 0 To .Fields.Count - 1
    strRowData = strRowData & sCell & .Fields(intFldCtr).Name & eCell
  Next
  strRowData = sRow & strRowData & eRow
        
  strTable = strTable & strRowData
    
  Do While Not .EOF
    'Append each Row
    strRowData = ""
    For intFldCtr = 0 To .Fields.Count - 1
      strRowData = strRowData & sCell & .Fields(intFldCtr) & eCell
    Next
    strRowData = sRow & strRowData & eRow
      strTable = strTable & strRowData
            'Go to the next Row
        .MoveNext
  Loop
    
End With

'Add Tags to encapsulate the Table
strTable = "[stdtbl]" & strTable & "[/stdtbl]"

Open CurrentProject.Path & "\BBCodes.txt" For Output As #1
  Print #1, strTable
    Close #1

varRetVal = Shell("Notepad.exe " & CurrentProject.Path & "\BBCodes.txt", vbMaximizedFocus)
End Sub


Thanks again for your help,
Lex
Go to the top of the page
 
ADezii
post Jul 5 2017, 12:32 PM
Post#14



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


I downloaded the Attachment that I sent you and all works fine. Can you Upload your Database, stripped of any sensitive information? You can DELETE the GenerateBBCodesForTable() Routine since it has nothing to do with the context of the Code.
Go to the top of the page
 
lex
post Jul 5 2017, 01:54 PM
Post#15



Posts: 671
Joined: 20-October 05



Here ya go!

Thanks

Attached File  Import_EMails.zip ( 22.58K )Number of downloads: 1


Uh - wait a minute. I ran it from your Import_EMails. Shouldn't it work from there?

The attached is basically what you uploaded.
Go to the top of the page
 
ADezii
post Jul 5 2017, 02:33 PM
Post#16



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Plan "B":
I threw together a Version using strictly 'Late Binding". Remove the Reference to the Microsoft Outlook 16.0 Object Library, keep your fingers crossed, and Open the Revised Database. I set it to return all E-Mails in 2017 in the Inbox. Until you know for sure that it is working, please keep it that way. Get back to me.
Attached File(s)
Attached File  Import_EMails___Late_Binding.zip ( 34.04K )Number of downloads: 4
 
Go to the top of the page
 
lex
post Jul 5 2017, 02:50 PM
Post#17



Posts: 671
Joined: 20-October 05



It worked! You're fabulous. Thank you!!

Any idea why Early Binding would work for you, and only Late would work for me?

Again ... thanks

Lex
Go to the top of the page
 
ADezii
post Jul 5 2017, 02:53 PM
Post#18



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Any idea why Early Binding would work for you, and only Late would work for me?

None whatsoever, especially when the 'same' Outlook Library is referenced in each case! iconfused.gif Good Luck with your Project and it has been a pleasure working with you on this Thread.
Go to the top of the page
 
lex
post Jul 5 2017, 03:04 PM
Post#19



Posts: 671
Joined: 20-October 05



The pleasure is mine.

Lex
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 06:43 PM