Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Mail, Excel 2002 VBA ArrrrrrGH
I'm using the following code to try and send a file as an attachment
from an Excel 2002 VBA macro. but I cannot get this to work in any way.. any ideas Dim xlapp As Object Set xlapp = CreateObject("Excel.Application") With xlapp .Visible = True .Workbooks.Open ("test.xls") .Workbooks("test.xls").SendMail ", Subject:="files for the day" End With I have to fire up and instance of excel, I've tried with and without opening it but just get Subscript out of range... and I've tried lots of different way.. I'll use anyhting to send it ... tried .sendmail butthis throws up a different but equally infuriating set of errors.... help Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Mail, Excel 2002 VBA ArrrrrrGH
You can try this: Dim xlapp As Object Dim wkbk as Object Set xlapp = CreateObject("Excel.Application") xlApp.Visible = True set wkbk = xlapp.Workbooks.Open("C:\myfolder\test.xls") wkbk.SendMail _ ", Subject:="files for the day" wkbk.Close SaveChanges:=False set wkbk = nothing xlApp.Quit set xlApp = Nothing -- Regards, Tom Ogilvy "Bobsa" wrote in message om... I'm using the following code to try and send a file as an attachment from an Excel 2002 VBA macro. but I cannot get this to work in any way.. any ideas Dim xlapp As Object Set xlapp = CreateObject("Excel.Application") With xlapp .Visible = True .Workbooks.Open ("test.xls") .Workbooks("test.xls").SendMail ", Subject:="files for the day" End With I have to fire up and instance of excel, I've tried with and without opening it but just get Subscript out of range... and I've tried lots of different way.. I'll use anyhting to send it ... tried .sendmail butthis throws up a different but equally infuriating set of errors.... help Rob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Mail, Excel 2002 VBA ArrrrrrGH
hmm...
You say you're running it from Excel VBA then why ar you opening another instance of the application? I assume you're running it from Word VBA... Sub testAPP() With CreateObject("Excel.Application") .Visible = True With .Workbooks.Open("c:\test.xls") .SendMail _ Recipients:=Array("dummy1", "dummy2"), _ Subject:="files for the day" End With End With End Sub Sub testWKS() With GetObject("c:\test.xls") .SendMail _ Recipients:=Array("dummy1", "dummy2"), _ Subject:="files for the day" End With End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool (Bobsa) wrote: I'm using the following code to try and send a file as an attachment from an Excel 2002 VBA macro. but I cannot get this to work in any way.. any ideas Dim xlapp As Object Set xlapp = CreateObject("Excel.Application") With xlapp .Visible = True .Workbooks.Open ("test.xls") .Workbooks("test.xls").SendMail ", Subject:="files for the day" End With I have to fire up and instance of excel, I've tried with and without opening it but just get Subscript out of range... and I've tried lots of different way.. I'll use anyhting to send it ... tried .sendmail butthis throws up a different but equally infuriating set of errors.... help Rob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Mail, Excel 2002 VBA ArrrrrrGH
If you are running from Excel itself, as KeepItCool said, you don't need the
xlapp bit. Dim wkbk as Workbook set wkbk = workbooks.Open("C:\MyFolder\Test.xls") wkbk.SendMail ", _ Subject:="files for the day" wkbk.Close SaveChanges:=False set wkbk = nothing some other sources of information: Ron de Bruin http://www.rondebruin.nl/sendmail.htm http://msdn.microsoft.com/library/en...odc_xlmail.asp Dick Kuselika www.dicks-clicks.com Tim Zych www.rubbershoe.com/olmail.htm -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... You can try this: Dim xlapp As Object Dim wkbk as Object Set xlapp = CreateObject("Excel.Application") xlApp.Visible = True set wkbk = xlapp.Workbooks.Open("C:\myfolder\test.xls") wkbk.SendMail _ ", Subject:="files for the day" wkbk.Close SaveChanges:=False set wkbk = nothing xlApp.Quit set xlApp = Nothing -- Regards, Tom Ogilvy "Bobsa" wrote in message om... I'm using the following code to try and send a file as an attachment from an Excel 2002 VBA macro. but I cannot get this to work in any way.. any ideas Dim xlapp As Object Set xlapp = CreateObject("Excel.Application") With xlapp .Visible = True .Workbooks.Open ("test.xls") .Workbooks("test.xls").SendMail ", Subject:="files for the day" End With I have to fire up and instance of excel, I've tried with and without opening it but just get Subscript out of range... and I've tried lots of different way.. I'll use anyhting to send it ... tried .sendmail butthis throws up a different but equally infuriating set of errors.... help Rob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Mail, Excel 2002 VBA ArrrrrrGH
Tom,
this worked perfectly and first time... and it seems to be just the fact that you've used "set Wkbk" instead of the tapping out the whole code... I never understand these little quirks but thanks save me a huuuuge amount of headaches. I'm running it from an instance of excel which a third party app starts from a IE page, and I can't seem to get things to run from within this particular instance of Excel. the only way around it seem to start another clean instance and use that. After I send the mail it pops up a box suggesting a virus might be trying to use my email ... now I want this to run automatically and not have to click on send .. seems to negate the reason for automatically sendning the mail. Is there a setting or way of turning this off, so I can send with out intervention.. We have plenty of firewalls and virus checking and seem to avoid all the problems so I don't have an issue with circumventing it. thanks again "Tom Ogilvy" wrote in message ... If you are running from Excel itself, as KeepItCool said, you don't need the xlapp bit. You can try this: Dim xlapp As Object Dim wkbk as Object Set xlapp = CreateObject("Excel.Application") xlApp.Visible = True set wkbk = xlapp.Workbooks.Open("C:\myfolder\test.xls") wkbk.SendMail _ ", Subject:="files for the day" wkbk.Close SaveChanges:=False set wkbk = nothing xlApp.Quit set xlApp = Nothing -- Regards, Tom Ogilvy "Bobsa" wrote in message om... I'm using the following code to try and send a file as an attachment from an Excel 2002 VBA macro. but I cannot get this to work in any way.. any ideas Dim xlapp As Object Set xlapp = CreateObject("Excel.Application") With xlapp .Visible = True .Workbooks.Open ("test.xls") .Workbooks("test.xls").SendMail ", Subject:="files for the day" End With I have to fire up and instance of excel, I've tried with and without opening it but just get Subscript out of range... and I've tried lots of different way.. I'll use anyhting to send it ... tried .sendmail butthis throws up a different but equally infuriating set of errors.... help Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bypass: A program is trying to send mail using Item.Send prompt | Excel Discussion (Misc queries) | |||
Send e-mail from a Excel file | Excel Worksheet Functions | |||
Can you print labels using Excel 2002 in a Word 2002 mail merge? | Excel Discussion (Misc queries) | |||
how to send e-mail from excel | Excel Programming |