Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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! |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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! |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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! |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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! |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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! |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
emailing from Excel | Excel Discussion (Misc queries) | |||
Emailing Excel? | Excel Discussion (Misc queries) | |||
Emailing Excel | Excel Discussion (Misc queries) | |||
emailing from excel | Excel Programming | |||
Emailing from Excel | Excel Programming |