UtterAccess Discussion Forums > Access Vba Integrated With Outlook And Excel

Full Version: Access Vba Integrated With Outlook And Excel

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

Csharp821

Feb 24 2012, 06:40 PM

I've got a general question which I think is probably above my expertise, but I just wanted to know if what I'm thinking about doing is even possible. I've been using VBA in MS Access for the past couple years, but that's about the extent of my programming experience. Okay, enough of the disclaimer, here's what I'm thinking about doing. At work, we use Outlook 2010 and receive emails with excel attachments throughout the day. The way we handle them right now is manually saving the attachment in a sub-folder that we create within a divisional folder on a network drive. What I'm curious about is if it's possible to use code to check incoming emails to see if they have an attachment, then check the attachment to see if it's an .XLSX, and if so, open the attachment, check the value of a particular cell, then store the account name and account number as a string and a variable and then use those to create the sub-folders in the appropriate Windows directory. If that's even possible, what would be the best way to accommodate what I'm trying to do? This portion of it seems like it's more work that just saving the files, but once I get past this part, I can also feed data into two or three other databases that will end up saving us a tremendous amount of time later. Please let me know if there's any other information that you need to understand what I'm trying to do. Here's what I've got so far...

CODE

Private Sub cmdConnectToOutlook_Click()
    Dim appOutlook As Outlook.Application
    Dim ns As Outlook.Namespace
    Dim inbox As Outlook.MAPIFolder
    Dim item As Object
    Dim atmt As Outlook.Attachment
    Dim filename As String
    Dim i As Integer


    Set appOutlook = GetObject(, "Outlook.Application")
    Set ns = appOutlook.GetNamespace("MAPI")
    Set inbox = ns.GetDefaultFolder(olFolderInbox)
    i = 0


    If inbox.Items.Count = 0 Then
        MsgBox "There are no messages in the Inbox.", vbInformation, _
               "Nothing Found"
        Exit Sub
    End If


    For Each item In inbox.Items
      For Each atmt In item.Attachments

        If Right(atmt.filename, 4) = "xlsx" Then
            filename = "C:\temp\" & atmt.filename
            atmt.SaveAsFile filename
           i = i + 1
        End If

    Next atmt
    Next item

      MsgBox "Attachments have been saved.", vbInformation, "Finished"

    Set atmt = Nothing
    Set item = Nothing
    Set ns = Nothing

End Sub

DanielPineault

Feb 24 2012, 07:19 PM

Yes it can be done. But I'd would do this directly in Outlook. You can create routine in Outlook to do all of this.

I would however be concerned with this because you'd be opening incoming e-mails automatically, so in theory, if someone send you an infected file (virus) your code might in fact automatically activated it. Perhaps you should associate it to a button which you'd click on to process e-mail that you can trust. It will still do all the work, but you'd remain in control as to when it should run or not.

Post your question in an Outlook forum. The gurus there will be more qualified on the exact type of code required.

And it is only by pushing ourselves that we can expand our knowledge! By the time you are done, you will be able to help many others!

Good luck and have fun!

Csharp821

Feb 24 2012, 08:21 PM

Daniel, thank you for the direction! The security side of it hadn't even crossed my mind. Let me mess around with it and if I do figure out something, I'll come back and update the post. Thank you and have a great weekend!!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.