Results 1 to 2 of 2
  1. #1
    LakerDave is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2016
    Posts
    1

    VBA to import email attachments to access 2007

    Good afternoon,

    I'm new to VBA and access so I'll apologize in advance if my question is poorly worded.

    I receive 24 x 12 emails per day with csv attachments that I would like to import into access databases automatically using VBA.

    I've cobbled together code I found online (below) that works for files as received but
    1 - acts on previous message (as current message isn't displayed in the folder until the script is completed)
    2 - requires my computer to be connected as all messages are received



    As a result I would like to modify it to loop through all messages in a folder (AlgocanadaFuelToProcess);
    1 - importing attachments
    2 - moving the message to another folder(AlgocanadaFuelProcessed)

    Can anyone help me do this?

    I have to say, this is pretty amazing stuff!!

    Thank you for taking the time to read my post,

    Dave
    ___________
    Public Sub ExportFileCanada(MyMail As MailItem)

    Dim outNS As Outlook.NameSpace
    Dim outFolder As Outlook.MAPIFolder
    'Dim outItem As Outlook.Object
    Dim outNewMail As Outlook.MailItem
    Dim ns As NameSpace
    Dim inb As Folder
    Dim strDir As String
    Dim fldr As Folder
    Dim pfldr As Folder

    Set outNS = GetNamespace("MAPI")
    Set outFolder = outNS.GetDefaultFolder(olFolderInbox)

    Set ns = Outlook.GetNamespace("MAPI")
    Set inb = ns.GetDefaultFolder(olFolderInbox)

    Set fldr = inb.Folders("_FuelReports")
    Set fldr = fldr.Folders("Algocanada")
    Set pfldr = fldr.Folders("AlgocanadaFuelProcessed")
    Set fldr = fldr.Folders("AlgocanadaFuelToProcess")

    Set outNewMail = fldr.Items.GetLast


    strDir = "C:\FuelReporting\Algocanada" 'insert directory eg. "C:\Project\OutlookData"
    If outNewMail.Attachments.Count = 0 Then GoTo Err
    outNewMail.Attachments(1).SaveAsFile strDir & "Data.csv"

    Dim accApp As Access.Application

    Set accApp = New Access.Application

    accApp.OpenCurrentDatabase strDir & "Algocanada_2016.accdb" '& insert database name eg. Database1.acddb
    accApp.DoCmd.RunSavedImportExport ("Algocanada")
    ' accApp.DoCmd.OpenQuery ("Qry01_Append_Data")

    accApp.Quit

    Set accApp = Nothing
    Set outNewMail = Nothing
    Set outFolder = Nothing
    Set outNS = Nothing

    Err:
    Set outFolder = Nothing
    Set OuNewMail = Nothing
    Set outNS = Nothing

    End Sub

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Hmmm, not sure I am following your code. It seems you have more declarations than necessary.

    Starting at the post I have bookmarked in this link, there are some examples of moving emails from the inbox to a specific folder, as well as searching the subject for key words.
    https://www.accessforums.net/showthr...774#post285774

    Here is an example how I declare an object as an attachment collection.
    Dim objAttachReport As Attachments

    Here I am instantiating the object
    Set objAttachReport = objNewEmail.Attachments

    Note that there is a collection of Attachment types. So once you instantiate, you will want iterate the objects. Also you could skip creating the collection and go after the namespace.

    Something like
    Dim objAttachment As Outlook.Attachment
    for each obj in objNewEmail.Attachments
    set objAttachment = obj
    'logic to save attachement somewhere
    'use the following properties to copy to your new directory
    'objAttachment.PathName
    'objAttachment.FileName
    next obj

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

Similar Threads

  1. Access sending email attachments
    By wee irish in forum Access
    Replies: 1
    Last Post: 03-06-2012, 06:11 PM
  2. problem with importing email attachments to access
    By funi.t in forum Import/Export Data
    Replies: 1
    Last Post: 12-20-2011, 10:20 AM
  3. Replies: 0
    Last Post: 10-25-2010, 09:23 AM
  4. attachments in access 2007
    By barbhall in forum Forms
    Replies: 0
    Last Post: 08-11-2009, 01:12 PM
  5. Access 2007 Attachments - Relative reference
    By soringc in forum Programming
    Replies: 0
    Last Post: 11-22-2007, 10:47 AM

Tags for this Thread

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