I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message ...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message ...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value


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



"Chris Brown" wrote in message ...
I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message ...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

I'm still learning the VBA side. How do I load the code you provided? I
know I still need the Sub and End Sub - I just don't know where to put
...SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " &
sh.Range("b4").Value




"Ron de Bruin" wrote:

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value


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



"Chris Brown" wrote in message ...
I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message ...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

Replace the sendmail line in the code example you posted with the new line I posted

so replace

.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")


for

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value



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



"Chris Brown" wrote in message ...
I'm still learning the VBA side. How do I load the code you provided? I
know I still need the Sub and End Sub - I just don't know where to put
..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " &
sh.Range("b4").Value




"Ron de Bruin" wrote:

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value


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



"Chris Brown" wrote in message ...
I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message
...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

Sorry - I figured it out right after I clicked Post.

Last question: How can I remove the Read Only command? If I delete the
line, the macro errors out.

"Ron de Bruin" wrote:

Replace the sendmail line in the code example you posted with the new line I posted

so replace

.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")


for

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value



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



"Chris Brown" wrote in message ...
I'm still learning the VBA side. How do I load the code you provided? I
know I still need the Sub and End Sub - I just don't know where to put
..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " &
sh.Range("b4").Value




"Ron de Bruin" wrote:

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value


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



"Chris Brown" wrote in message ...
I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message
...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

Why ?

Do you want to keep the file you send

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



"Chris Brown" wrote in message ...
Sorry - I figured it out right after I clicked Post.

Last question: How can I remove the Read Only command? If I delete the
line, the macro errors out.

"Ron de Bruin" wrote:

Replace the sendmail line in the code example you posted with the new line I posted

so replace

.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")


for

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value



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



"Chris Brown" wrote in message ...
I'm still learning the VBA side. How do I load the code you provided? I
know I still need the Sub and End Sub - I just don't know where to put
..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " &
sh.Range("b4").Value




"Ron de Bruin" wrote:

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value


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



"Chris Brown" wrote in message
...
I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message
...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

I use the sheet as an approval process where each person that receives the
file is able to approve with their name on an appropriate line and send to
the next. Security is not really an issue on this - it is really just a
means to eliminate paper. There is not a need to keep the file that is sent.
Users are able to access the sent file through Outlook.

"Ron de Bruin" wrote:

Why ?

Do you want to keep the file you send

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



"Chris Brown" wrote in message ...
Sorry - I figured it out right after I clicked Post.

Last question: How can I remove the Read Only command? If I delete the
line, the macro errors out.

"Ron de Bruin" wrote:

Replace the sendmail line in the code example you posted with the new line I posted

so replace

.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")


for

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value



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



"Chris Brown" wrote in message ...
I'm still learning the VBA side. How do I load the code you provided? I
know I still need the Sub and End Sub - I just don't know where to put
..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " &
sh.Range("b4").Value




"Ron de Bruin" wrote:

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value


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



"Chris Brown" wrote in message
...
I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message
...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

You can't remove the Read Only command because it is used to delete
the activeworkbook (WB in the code) when it is open.

The file you send is not Read Only


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



"Chris Brown" wrote in message ...
I use the sheet as an approval process where each person that receives the
file is able to approve with their name on an appropriate line and send to
the next. Security is not really an issue on this - it is really just a
means to eliminate paper. There is not a need to keep the file that is sent.
Users are able to access the sent file through Outlook.

"Ron de Bruin" wrote:

Why ?

Do you want to keep the file you send

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



"Chris Brown" wrote in message ...
Sorry - I figured it out right after I clicked Post.

Last question: How can I remove the Read Only command? If I delete the
line, the macro errors out.

"Ron de Bruin" wrote:

Replace the sendmail line in the code example you posted with the new line I posted

so replace

.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")


for

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value



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



"Chris Brown" wrote in message
...
I'm still learning the VBA side. How do I load the code you provided? I
know I still need the Sub and End Sub - I just don't know where to put
..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " &
sh.Range("b4").Value




"Ron de Bruin" wrote:

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value


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



"Chris Brown" wrote in message
...
I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message
...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

I gotcha - you have been a tremendous help and I thank you for the time that
you have given me!


"Ron de Bruin" wrote:

You can't remove the Read Only command because it is used to delete
the activeworkbook (WB in the code) when it is open.

The file you send is not Read Only


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



"Chris Brown" wrote in message ...
I use the sheet as an approval process where each person that receives the
file is able to approve with their name on an appropriate line and send to
the next. Security is not really an issue on this - it is really just a
means to eliminate paper. There is not a need to keep the file that is sent.
Users are able to access the sent file through Outlook.

"Ron de Bruin" wrote:

Why ?

Do you want to keep the file you send

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



"Chris Brown" wrote in message ...
Sorry - I figured it out right after I clicked Post.

Last question: How can I remove the Read Only command? If I delete the
line, the macro errors out.

"Ron de Bruin" wrote:

Replace the sendmail line in the code example you posted with the new line I posted

so replace

.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")


for

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value



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



"Chris Brown" wrote in message
...
I'm still learning the VBA side. How do I load the code you provided? I
know I still need the Sub and End Sub - I just don't know where to put
..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " &
sh.Range("b4").Value




"Ron de Bruin" wrote:

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value


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



"Chris Brown" wrote in message
...
I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message
...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!

You are welcome

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



"Chris Brown" wrote in message ...
I gotcha - you have been a tremendous help and I thank you for the time that
you have given me!


"Ron de Bruin" wrote:

You can't remove the Read Only command because it is used to delete
the activeworkbook (WB in the code) when it is open.

The file you send is not Read Only


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



"Chris Brown" wrote in message ...
I use the sheet as an approval process where each person that receives the
file is able to approve with their name on an appropriate line and send to
the next. Security is not really an issue on this - it is really just a
means to eliminate paper. There is not a need to keep the file that is sent.
Users are able to access the sent file through Outlook.

"Ron de Bruin" wrote:

Why ?

Do you want to keep the file you send

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



"Chris Brown" wrote in message
...
Sorry - I figured it out right after I clicked Post.

Last question: How can I remove the Read Only command? If I delete the
line, the macro errors out.

"Ron de Bruin" wrote:

Replace the sendmail line in the code example you posted with the new line I posted

so replace

.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")


for

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value



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



"Chris Brown" wrote in message
...
I'm still learning the VBA side. How do I load the code you provided? I
know I still need the Sub and End Sub - I just don't know where to put
..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " &
sh.Range("b4").Value




"Ron de Bruin" wrote:

Hi Chris

Try this

..SendMail "", _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - " & sh.Range("b4").Value


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



"Chris Brown" wrote in message
...
I use sendMail. In fact, I found a macro that you wrote and am trying to
masage it for what I am doing (with some, but not total success).

Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("a1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail ActiveSheet.Range("a1").Value, _
"NISR05" & " " & "-" & " " & sh.Range("b4")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

I do not want to specify the email address - I would like for the user to
type it in.

"Ron de Bruin" wrote:

Hi Cris
Do you use sendMail or Outlook code

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



"Chris Brown" <Chris wrote in message
...
I have set up a macro that emails a sheet and retains the name of the
workbook in the subject line when Outlook is opened. I would like for it to
retain the name of the workbook, but I want to add the data from a particular
cell to the subject line as well.

Subject line example: NISR05 - Plastic Cup

Where "NISR05" is the name of the workbook and "Plastic Cup" is the data
located in cell B4.

Any help would be greatly appreciated!