Access Vba Integrated With Outlook And Excel, Office 2010
Feb 24 2012, 06:40 PM
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, _
For Each item In inbox.Items
For Each atmt In item.Attachments
If Right(atmt.filename, 4) = "xlsx" Then
filename = "C:\temp\" & atmt.filename
i = i + 1
MsgBox "Attachments have been saved.", vbInformation, "Finished"
Set atmt = Nothing
Set item = Nothing
Set ns = Nothing
Feb 24 2012, 07:19 PM
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!
Feb 24 2012, 08:21 PM
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!!
|Search Top Lo-Fi||20th February 2017 - 05:37 PM|