CODEWITHSUNDEEP
excelvbaoutlook
Lefty
Lefty

Reputation: 391

How to terminate Excel process in Outlook VBA program so Excel spreadsheet is not locked?

I've written a program in Outlook VBA which creates emails dependent upon the contents of an Excel spreadsheet.

When the program terminates I continue to have an "EXCEL.EXE" process running which locks the spreadsheet so no-one else can open it.

Within the code I have three Excel objects:

Dim xl As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet

At the end I close the workbook and set all of the variables to Nothing:

xlwb.Close

Set xlsheet = Nothing
Set xlwb = Nothing
Set xl = Nothing

This is the bare bones of the code including the new "Quit" line:

Dim xl As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim ol As Outlook.Application
Dim Mail As MailItem
Set xl = Excel.Application
Set ol = Outlook.Application
Set xlwb = xl.Workbooks.Open("C:\sheet.xlsx", ReadOnly)

For Each xlsheet In xlwb.Worksheets
    for xlrow = 1 to 5
        If xlsheet.Cells(xlRow, 1).Value = "John" Then
           msg=msg & xlsheet.Cells(xlRow, 2).Value
        end if
    next
next

Set Mail = ol.CreateItem(olMailItem)
   
Mail.To = "[email protected]"
Mail.Subject = "John's email"
Mail.Body = msg
Mail.Send

xlwb.Close

xl.Quit

Set ol = Nothing
Set xlsheet = Nothing
Set xlwb = Nothing
Set xl = Nothing

Upvotes: 1

Views: 173

Answers (3)

0m3r
0m3r

Reputation: 12495

You could try something like this

Option Explicit
Sub Excel()
    '//  Declare variables
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlStarted As Boolean


    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")

    If Err <> 0 Then
        Application.StatusBar = "Please wait while Excel source is opened ... "
        Set xlApp = CreateObject("Excel.Application")
        xlStarted = True
    End If



    ' your code here




    '// Close & SaveChanges
    xlWb.Close SaveChanges:=True
    If xlStarted Then
        xlApp.Quit
    End If

    '// clean up
    Set xlApp = Nothing
    Set xlWb = Nothing
    Set xlSheet = Nothing
 End Sub

Upvotes: 0

area9
area9

Reputation: 391

xl.quit

This will close the application (you are only closing the workbook and not the application in your code), so just put this before setting the variable to nothing.

Edit: Please change your sub to the following:

Dim xl As New Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim ol As Outlook.Application
Dim Mail As MailItem

Set ol = Outlook.Application
Set xlwb = xl.Workbooks.Open("C:\sheet.xlsx", ReadOnly)
For Each xlsheet In xlwb.Worksheets

For xlRow = 1 To 5

If xlsheet.Cells(xlRow, 1).Value = "John" Then
    msg = msg & xlsheet.Cells(xlRow, 2).Value

End If

Next

Next

Set Mail = ol.CreateItem(olMailItem)

Mail.To = "[email protected]"
Mail.Subject = "John's email"
Mail.Body = msg
Mail.Send

xlwb.Close

xl.Quit

Set ol = Nothing
Set xlsheet = Nothing
Set xlwb = Nothing
Set xl = Nothing

Upvotes: 1

Alex Gale
Alex Gale

Reputation: 55

you need to Quit the Application xl.Quit the Set "" = Nothing isn't really necessary

Upvotes: 2

Related Questions