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!