Programatically sending a sheet through Outlook
 

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

Programatically sending a sheet through Outlook
 

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" wrote:

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

Programatically sending a sheet through Outlook
 

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



"Jim Thomlinson" wrote in message
...
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" wrote:

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

Programatically sending a sheet through Outlook
 

What line do you get the 1004 error on?

"Mark Scott" wrote:

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

Programatically sending a sheet through Outlook
 

wb1.SaveCopyAs wbname

"Jim Thomlinson" wrote in message
...
What line do you get the 1004 error on?

"Mark Scott" wrote:

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

Programatically sending a sheet through Outlook
 

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

C:/

to

C:\

HTH
"Jim Thomlinson" wrote:

What line do you get the 1004 error on?

"Mark Scott" wrote:

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

Programatically sending a sheet through Outlook
 

Thanks Jim, it still doesnt want to play though :(

"Jim Thomlinson" wrote in message
...
Your path is not correct. You have C: forward slash not c: Back slach
change

C:/

to

C:\

HTH
"Jim Thomlinson" wrote:

What line do you get the 1004 error on?

"Mark Scott" wrote:

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

Programatically sending a sheet through Outlook
 

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" wrote:

Thanks Jim, it still doesnt want to play though :(

"Jim Thomlinson" wrote in message
...
Your path is not correct. You have C: forward slash not c: Back slach
change

C:/

to

C:\

HTH
"Jim Thomlinson" wrote:

What line do you get the 1004 error on?

"Mark Scott" wrote:

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

Programatically sending a sheet through Outlook
 

I am the administrator :)

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

Regards

Mark

"Jim Thomlinson" wrote in message
...
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" wrote:

Thanks Jim, it still doesnt want to play though :(

"Jim Thomlinson" wrote in
message
...
Your path is not correct. You have C: forward slash not c: Back slach
change

C:/

to

C:\

HTH
"Jim Thomlinson" wrote:

What line do you get the 1004 error on?

"Mark Scott" wrote:

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

Programatically sending a sheet through Outlook
 

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

to

wbname = "C:\" & etc

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

Cheers

Tony

Programatically sending a sheet through Outlook
 

Hi Mark

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mark Scott" wrote in message ...
I am the administrator :)

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

Regards

Mark

"Jim Thomlinson" wrote in message
...
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" wrote:

Thanks Jim, it still doesnt want to play though :(

"Jim Thomlinson" wrote in message
...
Your path is not correct. You have C: forward slash not c: Back slach
change

C:/

to

C:\

HTH
"Jim Thomlinson" wrote:

What line do you get the 1004 error on?

"Mark Scott" wrote:

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

Programatically sending a sheet through Outlook
 

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" wrote in message
om...
Try changing the wbname = "C=:/" & etc....

to

wbname = "C:\" & etc

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

Cheers

Tony