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
> Outlook Accessing Excel Workbooks, Office 2013    
post Jul 11 2017, 10:22 PM

Posts: 134
Joined: 5-August 13
From: North Texas (DFW)

I need to access Excel workbooks from an Outlook VBA macro. The general scenario is:

1. An Outlook Rule starts the VBA macro on arrival of an email addressed to a unique account (other email accounts are handled by the same Outlook).

2. The VBA macro accesses (opens and gets handles for) two Excel workbooks and an unpredictable though fairly small number of named worksheets.

3. The VBA macro authenticates the email sender by finding his address in one of those worksheets.

4. The VBA macro expands the sender's list of recipient names (Joe, Jack R, Smitty, etc) to email addresses found in one of the workbooks.

5. The VBA macro forwards the incoming email to the email address list built in the previous step.

6. The VBA macro allows certain senders (administrators) to update the Excel workbooks via commands in the body of emails they send.

Steps 1 and 2 have been coded and tested. I'm hung up though on being able to use Excel functions in the Outlook VBA code, get data from the Excel workbooks and update the workbooks when necessary. I know the Excel functions I need and how to code them in Excel but, regardless of what Microsoft claims about VBA accessing one Office application from another, I haven't been able to get that to work at all. I'm pretty certain it's a syntax problem and if I could just see one or more Outlook VBA macros that work with Excel as I need to do, I could plagiarize the code and be on my way.

All of the above, by the way, is to run in a Windows 10/Outlook 2016/Excel 2016 environment.

Go to the top of the page
post Jul 12 2017, 07:22 AM

UA Admin
Posts: 31,250
Joined: 20-June 02
From: Newcastle, WA

Have you tried recording a "macro" in Excel that does what you want? You could do that, and then use that as the basis for an equivalent Function in Outlook.

On occasion, when dealing with cross application automation, I have also done something similar, i.e. creating the necessary VBA procedures in Excel and then running those from Access. I think you should be able to do that as well.

Go to the top of the page
post Jul 12 2017, 08:28 AM

Posts: 342
Joined: 20-September 12
From: Cornwall UK

A fair bit to do there. When I have needed to process data from e-mails, I have always stripped the application file, ie excell workbook, in this instance, out of the e-mail and placed it into a working directory on a network drive first. Then done the data manipulation. Then put the file back into an e-mail and deleted the working copy of the application file. Alas I generaly do the coding via Access, so ca'nt help with the specifics in Excel.

The following code can be used, from within Outlook, to pull out the files from an e-mail and put a copy of any attached files into a network/local drive location.

Sub GetAttachments()
On Error GoTo GetAttachments_err
Dim NS As NameSpace
Dim inbox As MAPIFolder
Dim SubFolder As MAPIFolder
Dim Item As Object
Dim atm As Attachment
Dim FileName As String
Dim i As Integer
Set NS = GetNamespace("MAPI")
Set inbox = NS.GetDefaultFolder(olFolderInbox)
Set SubFolder = inbox.Folders("Production Data") ' The name of your sub folder within the Default Outlook Inbox folder
i = 0
If SubFolder.Items.Count = 0 Then
  MsgBox "There are no messages in the Folder.", vbInformation, "Nothing Found"
  Exit Sub
End If
If SubFolder.Items.Count > 0 Then
  For Each Item In SubFolder.Items
    For Each atm In Item.Attachments
      FileName = "C:\Outlook Attachments\" & atm.FileName ' You working directory goes here between the quotes
      atm.SaveAsFile FileName
      i = i + 1
    Next atm
  Next Item
End If
If i > 0 Then
    MsgBox "I found " & i & " attached files." & vbCrLf & "I have saved them into the C:\Email Attachments folder." & vbCrLf & vbCrLf & "Have a nice day.", vbInformation, "Finished!"
    MsgBox "I didn't find any attached files in your mail.", vbInformation, "Finished!"
End If
Set atm = Nothing
Set Item = Nothing
Set NS = Nothing
Exit Sub
  MsgBox "An unexpected error has occurred." _
    & vbCrLf & "Please note and report the following information." _
    & vbCrLf & "Macro Name: GetAttachments" _
    & vbCrLf & "Error Number: " & Err.Number _
    & vbCrLf & "Error Description: " & Err.Description _
    , vbCritical, "Error!"
  Resume GetAttachments_Exit
End Sub

Hope that helps with a small part of your project.

Live to Surf
Go to the top of the page
post Jul 12 2017, 11:32 AM

Posts: 134
Joined: 5-August 13
From: North Texas (DFW)

That was the plan, George. I've gone down that road already. I coded and tested the necessary statements in an Excel macro but have been unable to port them to Outlook. All I get is "__________" (nothing) or various VBA errors. The frustrating part is that neither Excel or Outlook documentation is any help.

Go to the top of the page
post Jul 12 2017, 11:37 AM

Posts: 134
Joined: 5-August 13
From: North Texas (DFW)

BuzyG: Your code uses Outlook commands to save attachments as files outside Outlook. That's not quite what I'm trying to do. I need to get data from existing Excel workbooks (not attachments) and sometimes update those workbooks from Outlook VBA routines. But thanks anyway for replying.

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 09:51 AM