My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Access Vba Integrated With Outlook And Excel, Office 2010
|
|
 Feb 24 2012, 06:40 PM
Post
#1
|
|
|
UtterAccess Addict Posts: 113  |
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 |
 |
 
|
|
Feb 24 2012, 07:19 PM
Post
#2
|
|
|
UtterAccess VIP Posts: 1,472 |
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! |
|
|
|
|
Feb 24 2012, 08:21 PM
Post
#3
|
|
|
UtterAccess Addict Posts: 113 |
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!! (IMG:style_emoticons/default/drunk2.gif)
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 25th May 2013 - 11:19 PM |