UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Access Vba Integrated With Outlook And Excel, Office 2010    
 
   
Csharp821
post Feb 24 2012, 06:40 PM
Post #1

UtterAccess Addict
Posts: 139



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
        
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
 
+
DanielPineault
post Feb 24 2012, 07:19 PM
Post #2

UtterAccess VIP
Posts: 2,907



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
 
+
Csharp821
post Feb 24 2012, 08:21 PM
Post #3

UtterAccess Addict
Posts: 139



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
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 23rd November 2014 - 07:02 AM