VBA code to modify Excel spreadsheet and then email it

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
 
P

Patrick Molloy

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
 
G

Glenn Suggs

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top