Programatically sending a sheet through Outlook

[Mark Scott]

I have been asked to look at a form in Excel which is automatically sent to
an email address depending on the data it contains. I have used a listbox
and a combination of IF and CONCATENATE to make the email address.

What I need to do now is to send this sheet as an attachment in Outlook
(Exchange 2003). I would like the macro ro reference an email address in
the worksheet - save a worksheet to a secified location then sent it via
Outlook as an attachment to the address referenced.

Any clues or ideas?

Regards

Mark

 

[Guest]

Everything you wanted to know but were afraid to ask... This is an excellent
reference for e-mailing...

http://www.rondebruin.nl/

HTH

 

[Mark Scott]

Thanks

I have installed sendmail and copied the macro but I keep getting 1004
errors....

Sub Mail_Workbook_2()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
wbname = "C=:/" & wb1.Name & " " & _
Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
wb1.SaveCopyAs wbname
Set wb2 = Workbooks.Open(wbname)
With wb2
..SendMail Sheets("mysheet").Range("j1").Value, _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

 

[Guest]

What line do you get the 1004 error on?

 

[Mark Scott]

wb1.SaveCopyAs wbname

What line do you get the 1004 error on?

 

[Guest]

Your path is not correct. You have C: forward slash not c: Back slach
change

C:/

to

C:\

HTH

 

[Mark Scott]

Thanks Jim, it still doesnt want to play though

 

[Guest]

Do you have Root Access to the C drive. Translation... Try saving something
into the root of your C drive. Very often administrators lock that down for
you to keep you form messing with systme files and the like...

HTH

 

[Mark Scott]

I am the administrator

Would it help if I posted the XLS file on here?

Regards

Mark

 

[Tony Steane]

Try changing the wbname = "C=:/" & etc....

to

wbname = "C:\" & etc

ie No = (equals sign) and us the backslash.

Cheers

Tony

 

[Ron de Bruin]

Hi Mark

Check out this first
http://www.rondebruin.nl/mail/problems.htm

 

[Mark Scott]

Thanks, that sorted the saving the sheet but I now get a subscript out of
range at:

Set wb2 = Workbooks.Open(wbname)
With wb2
..SendMail Sheets("mysheet").Range("j1").Value, _
"This is the Subject line"

J1 is a formula which uses concatenate to produce the required email address

Regards

Mark

 

[Tony Steane]

Mark,


Ensure that the sheet name is correct "mysheet" without any leading or
trailing spaces. I have managed to get the "subscript out of range" to
occur by having the sheet called "mysheet ".

With the sheet name corrected, the procedure works just fine.

Cheers

Tony