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
> Access Vba Integrated With Outlook And Excel, Office 2010    
post Feb 24 2012, 06:40 PM

Posts: 139
Joined: 19-October 11

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...
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
nbsp;   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
Go to the top of the page
post Feb 24 2012, 07:19 PM

UtterAccess VIP
Posts: 4,712
Joined: 30-June 11

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.
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!
Go to the top of the page
post Feb 24 2012, 08:21 PM

Posts: 139
Joined: 19-October 11

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!! drunk2.gif
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    20th February 2017 - 05:37 PM