Send Mail, Excel 2002 VBA ArrrrrrGH

[Bobsa]

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
Recipients:="(e-mail address removed)", 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

 

[Tom Ogilvy]

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 _
Recipients:="(e-mail address removed)", Subject:="files for the day"
wkbk.Close SaveChanges:=False
set wkbk = nothing
xlApp.Quit
set xlApp = Nothing

 

[keepitcool]

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 >

 

[Tom Ogilvy]

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 Recipients:="(e-mail address removed)", _
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-us/odc_xl2003_ta/html/odc_xlmail.asp

Dick Kuselika
www.dicks-clicks.com

Tim Zych
www.rubbershoe.com/olmail.htm

 

[Bobsa]

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