Reputation: 391
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
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
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
Reputation: 55
you need to Quit the Application xl.Quit
the Set "" = Nothing
isn't really necessary
Upvotes: 2