Results 1 to 11 of 11
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Help getting Email Information


    I have the following code that works great at getting all the attachments from a specific folder in my Outlook email. After it saves all the attachments to a certain file location it moves the emails to another folder. What i am wanting to do is get the email date, time, and the attachment file name and put them in a table (tbl_EmailInfo). The reason for this is because the current code will get all the attachments every time and overwrite the other ones. I am wanting this information so i can check if a file has already been used so if changes were made to the file there is a new file saved with an appended name. I compare the files with other code that gives me the changes. Can anyone point me in the direction to get the email information that i am looking for. I hope i explained this as well as it was in my head. Thank you in advance for your help.

    Code:
    Private Sub GetAttachments()
    
        Dim db As DAO.Database, _
            olNameSpace As Outlook.Namespace, _
            olInbox As Outlook.Folder, _
            olSubFolder As Outlook.Folder, _
            olDestFolder As Outlook.Folder, _
            Item As Object, _
            Attachment As Outlook.Attachment, _
            FileName As String, _
            sql As String, _
            rsOpen As Recordset
        
        Set db = CurrentDb
        Set olNameSpace = GetNamespace("MAPI")
        Set olInbox = olNameSpace.GetDefaultFolder(olFolderInbox)
        Set olSubFolder = olInbox.Folders("3-Incoming Inspection").Folders("SyQwest")
        Set olDestFolder = olInbox.Folders("3-Incoming Inspection").Folders("SyQwest").Folders("Imported Files")
        
        If olSubFolder.Items.Count = 0 Then
            MsgBox "There are no messages in the Inbox.", vbInformation, _
                    "Nothing Found"
            Exit Sub
        End If
    
        For Each Item In olSubFolder.Items
            For Each Attachment In Item.Attachments
                If Attachment.Type = 1 And (InStr(Attachment, "xlsx") Or InStr(Attachment, "xls")) > 0 Then
                    FileName = networkFilepath & Attachment.FileName
                    'Debug.Print FileName
                    'Debug.Print Attachment
                    Attachment.SaveAsFile FileName
                    
                End If
            Next Attachment
            
        Next Item
        
           Set xlApp = New Excel.Application
                xlApp.Visible = True
                xlApp.WindowState = xlMaximized
    
        Set olDestFolder = Nothing
        Set olInbox = Nothing
        
    End Sub

  2. #2
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Sorry I added a part in my explanation that doesn't work yet. I have not gotten the part working that moves the email to another folder yet. --Walker

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's a line of code from a process of mine:

    outItem.Move (outTargetFolder)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Paul,

    Thank you. Do you have any leads for getting the email info? The date and time of the email? i got the attachment name now.

    --Walker

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I thought the move was the last item needed based on your second post. Does this help?

    https://docs.microsoft.com/en-us/off...m.receivedtime
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at my free utility http://forestbyte.com/ms-access-util...ook-companion/ that does that and has the move message feature.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Paul,

    I got all the info from the email that i need and it goes to the correct fields in the correct table. The problem i am getting now is i get an error on the code line.
    Code:
     Item.Move (olDestFolder)
    the error i am getting is run-time error 13 type mismatch

    do you know what that cause of this could be?

    Thank you.
    --Walker

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How are the various variables declared and set?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Here is the whole Subroutine

    Code:
    Private Sub GetAttachments()
    
        Dim db As DAO.Database, _
            olNameSpace As Outlook.Namespace, _
            olInbox As Outlook.Folder, _
            olSubFolder As Outlook.Folder, _
            olDestFolder As Outlook.Folder, _
            olConv As Outlook.Conversation, _
            olMail As MailItem, _
            objItems As Outlook.Items, _
            Item As Object, _
            Attachment As Outlook.Attachment, _
            FileName As String, _
            sql As String, _
            strReceivedTime As String, _
            rsOpen As Recordset
        
        Set db = CurrentDb
        Set olNameSpace = GetNamespace("MAPI")
        Set olInbox = olNameSpace.GetDefaultFolder(olFolderInbox)
        Set olSubFolder = olInbox.Folders("3-Incoming Inspection").Folders("SyQwest")
        Set olDestFolder = olInbox.Folders("3-Incoming Inspection").Folders("SyQwest").Folders("Imported Files")
        
        If olSubFolder.Items.Count = 0 Then
            MsgBox "There are no messages in the Inbox.", vbInformation, _
                    "Nothing Found"
            Exit Sub
        End If
    
        For Each Item In olSubFolder.Items
            Set rsOpen = db.OpenRecordset("tbl_EmailInfo")
    
            For Each Attachment In Item.Attachments
                If Attachment.Type = 1 And (InStr(Attachment, "xlsx") Or InStr(Attachment, "xls")) > 0 Then
                    rsOpen.AddNew
                    rsOpen![DateEmailRcvd] = Item.ReceivedTime
                    rsOpen![AttachmentName] = Attachment.FileName
                    rsOpen.Update
     '               Debug.Print strReceivedTime
                    
                    FileName = networkFilepath & Attachment.FileName
                    'Debug.Print FileName
                    Debug.Print Attachment.FileName
    '                Debug.Print Item
                    Attachment.SaveAsFile FileName
                    
                End If
            Next Attachment
            If TypeOf Item Is Outlook.MailItem Or Item.Class = 43 Or TypeOf Item Is Outlook.ReportItem Then
            Set olMail = Item
            olMail.Move (olDestFolder)
            End If
        Next Item
    '    For Each olMail In olSubFolder.Items
           Set xlApp = New Excel.Application
                xlApp.Visible = True
                xlApp.WindowState = xlMaximized
    
        Set olDestFolder = Nothing
        Set olInbox = Nothing
        
    End Sub

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I've got 2 toddlers yelling, but my outItem came from:

    For Each outItem In outFolder.Items

    so in your code it would be

    Item.Move (olDestFolder)

    See if that works while I go wrangle grandkids.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I get the same error with that. I will be back in the morning. Have fun with the grandkids. Thank you for your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-25-2016, 09:47 AM
  2. Replies: 1
    Last Post: 10-08-2015, 04:27 AM
  3. Replies: 5
    Last Post: 05-07-2014, 09:25 AM
  4. Replies: 3
    Last Post: 05-02-2014, 09:27 AM
  5. Replies: 1
    Last Post: 07-16-2013, 12:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums