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

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

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

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

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

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

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

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

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

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

to

wbname = "C:\" & etc

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

Cheers

Tony

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