G
Glenn Suggs
Can someone please help me find the problem with this VBA code? It seemed to
run ok the first time through but then when I tested the second time I got an
error at the indicated place in the code. Thanks in advance.
Glenn
Dim xlsApp As Excel.Application
Dim olApp As Outlook.Application
Dim olMessage As Object
Dim olRecipient As Recipient
Set xlsApp = New Excel.Application
xlsApp.Workbooks.Open Filename:=[Full path to Excel file]
Set olApp = CreateObject("Outlook.Application")
Set olMessage = olApp.CreateItem(olMailItem)
olMessage.Subject = "Subject goes here"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This is where the error occurs after the second time through the code.
' Error #91 - Object variable or With block variable not set
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
xlsApp.Range("E4").Select
ActiveCell.FormulaR1C1 = "January"
xlsApp.Range("F4").Select
ActiveCell.FormulaR1C1 = DatePart("yyyy", Now)
strEmailRecipient = "(e-mail address removed)"
Set olRecipient = olMessage.Recipients.Add(strEmailRecipient)
' Not sure why I have a second reference to the subject line, either
' I think I borrowed the code from two different sources
strSubject = "Subject goes here"
xlsApp.ActiveWorkbook.SendMail olRecipient, strSubject
xlsApp.ActiveWorkbook.Close saveChanges:=xlDoNotSaveChanges
Set xlsApp = Nothing
run ok the first time through but then when I tested the second time I got an
error at the indicated place in the code. Thanks in advance.
Glenn
Dim xlsApp As Excel.Application
Dim olApp As Outlook.Application
Dim olMessage As Object
Dim olRecipient As Recipient
Set xlsApp = New Excel.Application
xlsApp.Workbooks.Open Filename:=[Full path to Excel file]
Set olApp = CreateObject("Outlook.Application")
Set olMessage = olApp.CreateItem(olMailItem)
olMessage.Subject = "Subject goes here"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This is where the error occurs after the second time through the code.
' Error #91 - Object variable or With block variable not set
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
xlsApp.Range("E4").Select
ActiveCell.FormulaR1C1 = "January"
xlsApp.Range("F4").Select
ActiveCell.FormulaR1C1 = DatePart("yyyy", Now)
strEmailRecipient = "(e-mail address removed)"
Set olRecipient = olMessage.Recipients.Add(strEmailRecipient)
' Not sure why I have a second reference to the subject line, either
' I think I borrowed the code from two different sources
strSubject = "Subject goes here"
xlsApp.ActiveWorkbook.SendMail olRecipient, strSubject
xlsApp.ActiveWorkbook.Close saveChanges:=xlDoNotSaveChanges
Set xlsApp = Nothing