VBA code to modify Excel spreadsheet and then email it

  • Thread starter Thread starter Glenn Suggs
  • Start date Start date
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
 
add
DIM wb as Object
DIM ws as Object
then change this
xlsApp.Workbooks.Open Filename:=[Full path to Excel file]
to
SET wb = xlsApp.Workbooks.Open Filename:=[Full path to Excel file]
SET ws = wb.Activesheet

change this
xlsApp.Range("E4").Select
ActiveCell.FormulaR1C1 = "January"

to
ws.Range("E4") = "January"

change this
xlsApp.Range("F4").Select
ActiveCell.FormulaR1C1 = DatePart("yyyy", Now)
to

ws.Range("F4") = YEAR(now)


change these

xlsApp.ActiveWorkbook.SendMail olRecipient, strSubject
xlsApp.ActiveWorkbook.Close saveChanges:=xlDoNotSaveChanges

to

wb.SendMail olRecipient, strSubject
wb.Close FALSE
xlsApp.QUIT
 
Thanks Patrick,
That worked great.
--
Glenn


Patrick Molloy said:
add
DIM wb as Object
DIM ws as Object
then change this
xlsApp.Workbooks.Open Filename:=[Full path to Excel file]
to
SET wb = xlsApp.Workbooks.Open Filename:=[Full path to Excel file]
SET ws = wb.Activesheet

change this
xlsApp.Range("E4").Select
ActiveCell.FormulaR1C1 = "January"

to
ws.Range("E4") = "January"

change this
xlsApp.Range("F4").Select
ActiveCell.FormulaR1C1 = DatePart("yyyy", Now)
to

ws.Range("F4") = YEAR(now)


change these

xlsApp.ActiveWorkbook.SendMail olRecipient, strSubject
xlsApp.ActiveWorkbook.Close saveChanges:=xlDoNotSaveChanges

to

wb.SendMail olRecipient, strSubject
wb.Close FALSE
xlsApp.QUIT




Glenn Suggs said:
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
Click to expand...
Click to expand...
 
Back
Top