Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to modify Excel spreadsheet and then email it
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 = " 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 -- Glenn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to modify Excel spreadsheet and then email it
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" wrote: 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 = " 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 -- Glenn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to modify Excel spreadsheet and then email it
Thanks Patrick,
That worked great. -- Glenn "Patrick Molloy" wrote: 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" wrote: 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 = " 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 -- Glenn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email excel spreadsheet - embedded in email | Excel Discussion (Misc queries) | |||
opened excel file from email & clicked save after modify . Where i | Excel Discussion (Misc queries) | |||
VB.NET 2005: Open an Excel Spreadsheet, modify it and then save it | Excel Programming | |||
Modify Macro Code Depending on Excel Version | Excel Discussion (Misc queries) | |||
Modify ADD ROWS Code - Excel 2003 | Excel Programming |