| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ron's e-mail won't work when I change the send to:
Hi all - can someone review this (******) and see what I'm missing?
Thanks very much! T Sub Send_WR_Est() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Set Source = Nothing On Error Resume Next Set Source = Range("C156:C160").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ Sheets("Sheet1").Range("C116").Value .SendMail ", _ "This is a Test - Work Request Estimate" ****It will work with the above line, but won't with the line above it. There is no error message. C110 is a valid e-mail and the worksheet/workbook is not protected. Instead of "Sheet1, I've tried the name of the worksheet and the name in Ron's code (mysheet) - any other suggestions?***** On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ron's e-mail won't work when I change the send to:
Hi Theo
'.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ Sheets("Sheet1").Range("C116").Value Did you copy the macro in the same workbook as the sheet with the mail address ? Note: you use ThisWorkbook As it is now it is looking for the mail address in the workbook with the code and it is looking for the subject in the activeworkbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Theo" wrote in message ... Hi all - can someone review this (******) and see what I'm missing? Thanks very much! T Sub Send_WR_Est() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Set Source = Nothing On Error Resume Next Set Source = Range("C156:C160").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ Sheets("Sheet1").Range("C116").Value .SendMail ", _ "This is a Test - Work Request Estimate" ****It will work with the above line, but won't with the line above it. There is no error message. C110 is a valid e-mail and the worksheet/workbook is not protected. Instead of "Sheet1, I've tried the name of the worksheet and the name in Ron's code (mysheet) - any other suggestions?***** On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ron's e-mail won't work when I change the send to:
Hi Ron - yes, the macro and the sheet where the e-mail to and subject are
located are in the same workbook. But I'm not sure where that leaves me ... do I add This Workbook like this? (I'm at home and don't have Outlook Explorer, so I can't test this ....) '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ ThisWorkbook.Sheets("Sheet1").Range("C116").Value Thanks Ron - I use your information/web-site ALL the time. Thank you very much. T "Ron de Bruin" wrote: Hi Theo '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ Sheets("Sheet1").Range("C116").Value Did you copy the macro in the same workbook as the sheet with the mail address ? Note: you use ThisWorkbook As it is now it is looking for the mail address in the workbook with the code and it is looking for the subject in the activeworkbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Theo" wrote in message ... Hi all - can someone review this (******) and see what I'm missing? Thanks very much! T Sub Send_WR_Est() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Set Source = Nothing On Error Resume Next Set Source = Range("C156:C160").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ Sheets("Sheet1").Range("C116").Value .SendMail ", _ "This is a Test - Work Request Estimate" ****It will work with the above line, but won't with the line above it. There is no error message. C110 is a valid e-mail and the worksheet/workbook is not protected. Instead of "Sheet1, I've tried the name of the worksheet and the name in Ron's code (mysheet) - any other suggestions?***** On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ron's e-mail won't work when I change the send to:
This is working OK .SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ ThisWorkbook.Sheets("Sheet1").Range("C116").Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Theo" wrote in message ... Hi Ron - yes, the macro and the sheet where the e-mail to and subject are located are in the same workbook. But I'm not sure where that leaves me ... do I add This Workbook like this? (I'm at home and don't have Outlook Explorer, so I can't test this ....) '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ ThisWorkbook.Sheets("Sheet1").Range("C116").Value Thanks Ron - I use your information/web-site ALL the time. Thank you very much. T "Ron de Bruin" wrote: Hi Theo '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ Sheets("Sheet1").Range("C116").Value Did you copy the macro in the same workbook as the sheet with the mail address ? Note: you use ThisWorkbook As it is now it is looking for the mail address in the workbook with the code and it is looking for the subject in the activeworkbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Theo" wrote in message ... Hi all - can someone review this (******) and see what I'm missing? Thanks very much! T Sub Send_WR_Est() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Set Source = Nothing On Error Resume Next Set Source = Range("C156:C160").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ Sheets("Sheet1").Range("C116").Value .SendMail ", _ "This is a Test - Work Request Estimate" ****It will work with the above line, but won't with the line above it. There is no error message. C110 is a valid e-mail and the worksheet/workbook is not protected. Instead of "Sheet1, I've tried the name of the worksheet and the name in Ron's code (mysheet) - any other suggestions?***** On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ron's e-mail won't work when I change the send to:
Thanks Ron - that did it.
Have a wonderful week-end. T "Ron de Bruin" wrote: This is working OK .SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ ThisWorkbook.Sheets("Sheet1").Range("C116").Value -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Theo" wrote in message ... Hi Ron - yes, the macro and the sheet where the e-mail to and subject are located are in the same workbook. But I'm not sure where that leaves me ... do I add This Workbook like this? (I'm at home and don't have Outlook Explorer, so I can't test this ....) '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ ThisWorkbook.Sheets("Sheet1").Range("C116").Value Thanks Ron - I use your information/web-site ALL the time. Thank you very much. T "Ron de Bruin" wrote: Hi Theo '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ Sheets("Sheet1").Range("C116").Value Did you copy the macro in the same workbook as the sheet with the mail address ? Note: you use ThisWorkbook As it is now it is looking for the mail address in the workbook with the code and it is looking for the subject in the activeworkbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Theo" wrote in message ... Hi all - can someone review this (******) and see what I'm missing? Thanks very much! T Sub Send_WR_Est() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Set Source = Nothing On Error Resume Next Set Source = Range("C156:C160").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next '.SendMail ThisWorkbook.Sheets("Sheet1").Range("C110").Value, _ Sheets("Sheet1").Range("C116").Value .SendMail ", _ "This is a Test - Work Request Estimate" ****It will work with the above line, but won't with the line above it. There is no error message. C110 is a valid e-mail and the worksheet/workbook is not protected. Instead of "Sheet1, I've tried the name of the worksheet and the name in Ron's code (mysheet) - any other suggestions?***** On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Send an e-mail | Excel Discussion (Misc queries) | |||
| Send worksheet by E-Mail | Excel Discussion (Misc queries) | |||
| Send e-mail | Excel Discussion (Misc queries) | |||
| Send To - E-mail Recipient | Setting up and Configuration of Excel | |||
| send to mail all the context | Excel Discussion (Misc queries) | |||
Hybrid Mode
