By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Extract Email Attachment into MS database

P: 16
I am working on simple task monitoring tool build in MS Access 2016. I have link the outlook inbox into my access db. but I am only getting text, I am looking hit how I could get attachment from the emails into the db as well.

Is it possible to do this through vba? I have wrote this code but its not working as intended. Any help will be greatly appreciated.

Expand|Select|Wrap|Line Numbers
  1. Private Sub GetAttachments()
  2.  
  3.     Dim ns As Namespace
  4.     Dim Inbox As Outlook.MAPIFolder
  5.     Dim Item As Object
  6.     Dim Atmt As Outlook.Attachment
  7.     Dim FileName As String
  8.  
  9.     Set ns = GetNamespace("MAPI")
  10.  
  11.     Set Inbox = ns.GetDefaultFolder(olFolderInbox)
  12.     Set Inbox = ns.Folders("MailboxName").Folders("Inbox")
  13.  
  14.     If Inbox.Items.Count = 0 Then
  15.         MsgBox "There are no messages in the Inbox.", vbInformation, _
  16.                 "Nothing Found"
  17.         Exit Sub
  18.     End If
  19.  
  20.     For Each Item In Inbox.Items
  21.         For Each Atmt In Item.Attachments
  22.             If Atmt.Type = 1 And InStr(Atmt, "xlsx") > 0 Then
  23.                 FileName = "C:\attachments\" & Atmt.FileName
  24.                 Atmt.SaveAsFile FileName
  25.             End If
  26.         Next Atmt
  27.     Next Item
  28.  
  29. End Sub
  30.  
1 Week Ago #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,308
Adict:
not working as intended
Not sure that's very helpful if I'm honest.

Bear in mind we aren't at your computer and only have what you tell us to go in. So, not much then.

What happens? Where does it break? How does it break?
1 Week Ago #2

P: 16
So the code above runs well but its looking at all the emails in the outlook folder and extracting emails, what i need is something like every email received gets registered into my internal table called Inbox with the rest of the text and attachment from that single email. Search much but didn't come across any . appreciate if i can be pointed to right direction
1 Week Ago #3

zmbd
Expert Mod 5K+
P: 5,331
Adict,
1) Rarely a good idea to store files in an access database. The file size of the database is fairly small and the bloat is horrendous.

2) You have no reference to the database in your code.

3) Your follow-up is still a bit obscure, maybe it's the late hour/long day I have had?
Perhaps, try to explain what you want in a step by step fashion?

4) Are you running this code from Outlook or from Access?
1 Week Ago #4

P: 16
Thank you, i wish to run the code from my access database.
1. I have link the particular outlook mail box into my database but i only get text which since into my table called email.

2. What i intend to achieve is to extract outlook attachment into each records with a click event from Access Database.

3.Extract the specific email into a shared network folder and have the link inserted into my table.

Thanks in advance
1 Week Ago #5

NeoPa
Expert Mod 15k+
P: 31,308
NeoPa:
What happens? Where does it break? How does it break?
Sorry. Let's try again.

I'm happy you're described what you want from the code. I'm also happy you've started the code already and posted what you have. All good. I'm less happy that you haven't explained what's actually happening when you try to run it.

I hope it's now very clear what it is I'm asking for.

PS. I agree with Zmbd about the advisability of storing files in a database. Even in bigger ones (than Access) it's a something that would need careful consideration. Nevertheless, it never hurts to learn how to do things. Your judgement as to what you use when you know how is up to you. We're here to help you to know how.
1 Week Ago #6

zmbd
Expert Mod 5K+
P: 5,331
1. I have link the particular outlook mail box into my database but i only get text which since into my table called email.
Did you create this link by:
Ribbon>External Data>Import & Link/More>Outlook Folder
(x)Link to the data source....

The problem with this method is that there is typically no message_id field which will make it difficult to associate any attachments with the corresponding email.
1 Week Ago #7

ADezii
Expert 5K+
P: 8,616
If you wish to avoid and Link and save the Attached Files to a Folder on your Hard Drive, the following Code will work well (assuming you have a Folder named 'C:\Attachments'). You could then simply store the PATH to these Files within your DB.
Expand|Select|Wrap|Line Numbers
  1. Dim ns As Outlook.Namespace
  2. Dim appOutlook As Outlook.Application
  3. Dim Inbox As Outlook.MAPIFolder
  4. Dim Item As Outlook.MailItem
  5. Dim Atmt As Outlook.Attachment
  6. Dim FileName As String
  7.  
  8. Set appOutlook = New Outlook.Application
  9. Set ns = appOutlook.GetNamespace("MAPI")
  10. Set Inbox = ns.GetDefaultFolder(olFolderInbox)
  11.  
  12. If Inbox.Items.Count = 0 Then
  13.   MsgBox "There are no messages in the Inbox.", vbInformation, _
  14.          "Nothing Found"
  15.     Exit Sub
  16. End If
  17.  
  18. For Each Item In Inbox.Items
  19.   For Each Atmt In Item.Attachments
  20.     If Atmt.Type = 1 And InStr(Atmt, "xlsx") > 0 Then
  21.       FileName = "C:\attachments\" & Atmt.FileName
  22.         Atmt.SaveAsFile FileName
  23.     End If
  24.   Next
  25. Next Item
1 Week Ago #8

zmbd
Expert Mod 5K+
P: 5,331
ADezii,
Essentially where I was going with this...

My thought was:
Table with the email text information
Table with a [FK] to the email table with would store the path information for the saved attachments to handle emails with multiple attachments.
1 Week Ago #9

P: 16
Thanks Zmdb, I did try that by inserting a code on the outlook to run a insert function when a new mail arrived into the Inbox. However it didn't run as intended. Here the code i wrote in outlook that I ended up getting error with
Expand|Select|Wrap|Line Numbers
  1. Dim HRSWorkflow As MailItem
  2. Set HRSWorkflow = Application.Session.GetRecipientFromID(EntryIDCollection)
  3.  
  4. Dim WkfSubject As String, WkfSender As String, wrfSenderEmail As String, wkfrequestdate As Date, wkfdescription As String, wkfcc As String
  5.  
  6.  
  7. WkfSender = HRSWorkflow.Sender
  8. wrfSenderEmail = HRSWorkflow.SenderEmailAddress
  9. WkfSubject = HRSWorkflow.Subject
  10. wkfcc = HRSWorkflow.CC
  11. wkfrequestdate = HRSWorkflow.ReceivedTime
  12. wkfdescription = HRSWorkflow.Body
  13. '_______________________________________________________________
  14. Dim str As String
  15.  
  16. str = "INSERT INTO TABLE tblTaskCollector(Sender,SenderEmail,Subject, CC,Description,DateRecieved) VALUES(" & "'" & WkfSender & "'" & "," & "'" & wrfSenderEmail & "'" & "," & "'" & WkfSubjec & "'" & "," & "'" & wkfcc & "'" & "," & "'" & wkfrequestdate & "'" & "," & "'" & wkfdescription & "'" & ", )"
  17. Dim cnx As ADODB.Connection
  18. Set cnx = New ADODB.Connection
  19.  
  20. cnx.Provider = "Microsoft.ACE.OLEDB.12.0"
  21. cnx.ConnectionString = "C:\Users\PosiaE\Documents\HR Transacts.accdb"
  22. cnx.Open
  23. cnx.Execute (str)
1 Week Ago #10

P: 16
How can i get a unique id to identify this attachment belong to this email? Apology, I ma having my head cracked by this. Really appreciate all your feedback.
1 Week Ago #11

ADezii
Expert 5K+
P: 8,616
  1. In line with zmbd's Post #9, I am making some assumptions here strictly to illustrate a point. These assumptions are:
  2. You have a Table named tblAttachments with two Fields ([FK]-{TEXT}, [FPath]-{TEXT}). [FK] is a Foreign Key Field and relates to a Primary Key Field in a MASTER Table.
  3. The Foreign Key Value can somehow be derived from the E-Mail. In this case, I simply used the first six characters of the Subject Line as the Foreign Key.
  4. The above being said, the following Code will:
    1. Process every E-Mail in the Inbox Folder.
    2. If the first 6 characters of the Subject Line is Numeric ([FK]) AND it was Sent after yesterday (5/12/2019) then we loop thru all Attachments in that E-Mail with a *.xlsx Extension.
    3. The Foreign Key is written to the [FK] Field in tblAttachments and the Absolute Path (Filename] is written to the [FPath] Field in tblAttachments.
    4. The actual Attachment(s) is/are saved to the Filename Path.
  5. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim ns As Outlook.Namespace
    2. Dim appOutlook As Outlook.Application
    3. Dim Inbox As Outlook.MAPIFolder
    4. Dim Item As Outlook.MailItem
    5. Dim Atmt As Outlook.Attachment
    6. Dim FileName As String
    7.  
    8. Set appOutlook = New Outlook.Application
    9. Set ns = appOutlook.GetNamespace("MAPI")
    10. Set Inbox = ns.GetDefaultFolder(olFolderInbox)
    11.  
    12. DoCmd.Hourglass True
    13.  
    14. If Inbox.Items.Count = 0 Then
    15.   MsgBox "There are no messages in the Inbox.", vbInformation, _
    16.          "Nothing Found"
    17.     Exit Sub
    18. End If
    19.  
    20. For Each Item In Inbox.Items
    21.   If IsNumeric(Left$(Item.Subject, "6")) And Item.SentOn > #5/12/2019# Then
    22.     For Each Atmt In Item.Attachments
    23.       If Atmt.Type = 1 And InStr(Atmt, ".xls") > 0 Then
    24.         FileName = "C:\attachments\" & Atmt.FileName
    25.           CurrentDb.Execute "INSERT INTO tblAttachments ([FK], [FPath]) VALUES ('" & _
    26.                              Left$(Item.Subject, "6") & "','" & FileName & "')", _
    27.                              dbFailOnError
    28.             Atmt.SaveAsFile FileName
    29.        End If
    30.     Next
    31.    End If
    32. Next Item
    33.  
  6. The Code has been tested and is fully operational under certain conditions. The only problem that I see is that this should all be wrapped in some form of Transaction since you would not want tblAttachments being updated and the SaveAsFile operation fail.
P.S. - If you wish to execute similar Code from Outlook when the E-Mails first arrive, then we are talking about a totally different situation.
1 Week Ago #12

Post your reply

Sign in to post your reply or Sign up for a free account.