Excel doc locked for read only after Outlook VBA code updates worksheet

Question

I have adapted code that checks the subject line of new Outlook emails for a keyword, opens a workbook and pastes certain information into this workbook:

Option Explicit

Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()

  Dim olApp As Outlook.Application
  Dim objNS As Outlook.NameSpace
  Set olApp = Outlook.Application
  Set objNS = olApp.GetNamespace("MAPI")

  ' default local Inbox
  Set Items = objNS.GetDefaultFolder(olFolderInbox).Items

End Sub

Private Sub Items_ItemAdd(ByVal item As Object)

  On Error GoTo ErrorHandler

  Dim Msg As Outlook.MailItem

  If TypeName(item) = "MailItem" Then
    Set Msg = item
    If InStr(Msg.Subject, "Re:") > 0 Then
        Exit Sub
    ElseIf InStr(Msg.Subject, "MDI Board") > 0 Then '// Keyword goes here

        '// Declare all variables needed for excel functionality and open appropriate document
        Dim oXL As Object
        Dim oWS As Object
        Dim lngRow As Long

        Set oXL = CreateObject("Excel.Application")
        oXL.Workbooks.Open FileName:="T:\Capstone Proj\TimeStampsOnly.xlsx", AddTOMRU:=False, UpdateLinks:=False

        '// Change sheet name to suit
        Set oWS = oXL.Sheets("TimeStamps")
        lngRow = oWS.Range("A" & oXL.Rows.Count).End(-4162).Offset(1).Row '// -4162 = xlUp. not available late bound

        With oWS
            .cells(lngRow, 1).Value = Msg.SenderName
            .cells(lngRow, 2).Value = Msg.ReceivedTime
            .cells(lngRow, 3).Value = Msg.ReceivedByName
            .cells(lngRow, 4).Value = Msg.Subject
            .cells(lngRow, 5).Value = Msg.Body

        '// And others as needed - you will have Intellisense

        End With

        With oXL
            .activeworkbook.Save
            .activeworkbook.Close SaveChanges:=2   '// 2 = xlDoNotSaveChanges but not availabe late bound
            .Application.Quit
        End With

        Set oXL = Nothing
        Set oWS = Nothing

    End If
  Else
    Exit Sub
  End If

ExitPoint:

  Exit Sub

ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ExitPoint

  '// Debug only
  Resume

End Sub

I was having issues with being able to access the workbook after the Outlook VBA code ran. It would give multiple errors such as 'the workbook is already open' even though I had no instance of Excel running on my machine or 'this file is read-only' etc.

I tried to circumvent this issue by using another workbook with an update macro that would update a dashboard using the information in the problematic workbook however I am getting a 'subscript out of range' error when I try to set a variable to the workbook with the Outlook data.

Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Set wkb = Excel.Workbooks("T:\Capstone Proj\TimeStampsOnly.xlsx")
Set wks = wkb.Worksheets("Timestamps")

Answer 1

Wagner Braga!

I have had a similar problem in the past. In my case, I was not looking for subjects containing certain characters but rather subjects equal to a string. Either way, that is irrelevant to your issue.

I found that, like yours, my code errored when trying to put info from the email into Excel. I did read the comments on your question and know that you don't want to use unneccessary computing power. My method is not the most efficient way to accomplish what you want to do, but it was the only way I could do it.

First of all, I did not edit the Excel workbook from the Outlook VBA. I tried to do it, but this is where my code errored. Instead, I set the email object as a variable's value (to make it easier to reference). Then I read the information from the email I wanted into an array by using the Split(...) function. The code created a text file and wrote the data to it so that it would be accessible by Excel. Before writing the data from the email, I also wrote the text "!NEWDATA!" on the first line. You could use any string you want, as long as there is a unique identifier at the top so that Excel recognizes that it should get data from the file. I then opened the workbook, just like I would open any other file using VBA.

Now, the Excel workboook requires some VBA code as well for my method to work. In the Workbook_Open() VBA sub in the workbook code, Excel should read the first line or first x number of characters. You can use either method, but this is should point to the part of the file that has your "!NEWDATA!" or other string. If this string is the one you wrote from Outlook, continue reading the file. If it's not, Exit Sub. From here you can have Excel read the rest of the file (which you separated by a delimeter of your choice via Outlook VBA) and put the data into the corresponding cells. Then change the "!NEWDATA!" and the rest of the file so that if you start Excel manually (and you don't want to import any data) the Workbook_Open() sub will stop and not error. You can change it to anything like a blank file, "No new data", or any other string you like. After this, use VBA to save the workbook and close it.

As you probably know, you could set the Excel window's Visible property to False if you don't want the user seeing the workbook.

If you have any questions or comments, let me know. I'll be happy to answer any questions you may have.