Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
I have included the code, but what I am trying to do is instead of copying
the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
You were given code to copy just the cells in the pivot table.
-- Regards, Tom Ogilvy "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
Tom, sorry about that, this is the code you gave me, I have been unable to
figure out where to input it into the code, sorry. rng.Copy ActiveSheet.Range("A1").PasteSpecial xlValues rng.parent.cells.copy Activesheet.Cells.PasteSpecial xlFormats "Tom Ogilvy" wrote: You were given code to copy just the cells in the pivot table. -- Regards, Tom Ogilvy "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
Tom, I was thinking about the code you gave me, wasn't that to ensure the
column width format remained the same, I am trying to do that while copying and pasting just the text, I have tried inputing that code into a couple of different locations and have received: run-time error '424': Object required Sorry again, I literally have almost zero VBA knowledge "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
in the code you posted, you copy the entire sheet
ActiveSheet.Copy You then asked: instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. The code I gave you was ActiveSheet.PivotTables(1).TableRange2.copy which only copied the table and when you use pastespecial, only the cells and the data they display are pasted, not the entire table infrastructure you complain about. You complained that it didn't copy row heights and column widths and I gave you corrected code to do that. That all said, in the current voluminous code you have, if you uncomment ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False it should remove the pivottable and only leave the values. By the way, no offense to Ron, but nme = IntputBox("Enter email address") subj = Inputbox("enter Subject") ActiveWorkbook.Sendmail subject:=subj, recipients:=nme would work fine if the user entered a valid email address (only one - which I understand is your requirement). -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, I was thinking about the code you gave me, wasn't that to ensure the column width format remained the same, I am trying to do that while copying and pasting just the text, I have tried inputing that code into a couple of different locations and have received: run-time error '424': Object required Sorry again, I literally have almost zero VBA knowledge "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
Tom, I see exactly what you are saying, it now copies text exactly like I
wanted it to and it was quite obvious, I really do appologize that I am so unfamiliar and asking questions I really should know. I still have two small issues that I will continue to try and figure out on my own, I would like to be able to reference cell B10 in the subject because the same person will receive multiple emails with this sheet, but the difference is B10, and also I have no narrow down the range so it only displays cells with data, or only a couple pages insead of over 700. Like I said I will continue to try and figure it out and I am sure I will be able to, but again I thank you very much for your assistance. Josh "Tom Ogilvy" wrote: in the code you posted, you copy the entire sheet ActiveSheet.Copy You then asked: instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. The code I gave you was ActiveSheet.PivotTables(1).TableRange2.copy which only copied the table and when you use pastespecial, only the cells and the data they display are pasted, not the entire table infrastructure you complain about. You complained that it didn't copy row heights and column widths and I gave you corrected code to do that. That all said, in the current voluminous code you have, if you uncomment ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False it should remove the pivottable and only leave the values. By the way, no offense to Ron, but nme = IntputBox("Enter email address") subj = Inputbox("enter Subject") ActiveWorkbook.Sendmail subject:=subj, recipients:=nme would work fine if the user entered a valid email address (only one - which I understand is your requirement). -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, I was thinking about the code you gave me, wasn't that to ensure the column width format remained the same, I am trying to do that while copying and pasting just the text, I have tried inputing that code into a couple of different locations and have received: run-time error '424': Object required Sorry again, I literally have almost zero VBA knowledge "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
You are already using range("B10") here - where do you want to use it.
TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") similarly .Subject = "Schedule Requests: " & Range("B10").Text as for 700 pages, that sounds like something to do with printing. do edit=goto=special and select last cell. If it goes way past where you think it should be, then look at this: http://www.contextures.com/xlfaqApp.html#Unused -- regards, Tom Ogilvy "Josh Johansen" wrote: Tom, I see exactly what you are saying, it now copies text exactly like I wanted it to and it was quite obvious, I really do appologize that I am so unfamiliar and asking questions I really should know. I still have two small issues that I will continue to try and figure out on my own, I would like to be able to reference cell B10 in the subject because the same person will receive multiple emails with this sheet, but the difference is B10, and also I have no narrow down the range so it only displays cells with data, or only a couple pages insead of over 700. Like I said I will continue to try and figure it out and I am sure I will be able to, but again I thank you very much for your assistance. Josh "Tom Ogilvy" wrote: in the code you posted, you copy the entire sheet ActiveSheet.Copy You then asked: instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. The code I gave you was ActiveSheet.PivotTables(1).TableRange2.copy which only copied the table and when you use pastespecial, only the cells and the data they display are pasted, not the entire table infrastructure you complain about. You complained that it didn't copy row heights and column widths and I gave you corrected code to do that. That all said, in the current voluminous code you have, if you uncomment ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False it should remove the pivottable and only leave the values. By the way, no offense to Ron, but nme = IntputBox("Enter email address") subj = Inputbox("enter Subject") ActiveWorkbook.Sendmail subject:=subj, recipients:=nme would work fine if the user entered a valid email address (only one - which I understand is your requirement). -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, I was thinking about the code you gave me, wasn't that to ensure the column width format remained the same, I am trying to do that while copying and pasting just the text, I have tried inputing that code into a couple of different locations and have received: run-time error '424': Object required Sorry again, I literally have almost zero VBA knowledge "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
Tom, adding the .Text solved the issue, what I wanted to do was input that
cell into the subject line and it worked. I understand the issue with selecting the print area, what I am trying to do is only copy, paste and email the cells with data so that the users can just print without worrying about print areas or selecting a range, and also it makes the file size smaller which is nice as well. "Tom Ogilvy" wrote: You are already using range("B10") here - where do you want to use it. TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") similarly .Subject = "Schedule Requests: " & Range("B10").Text as for 700 pages, that sounds like something to do with printing. do edit=goto=special and select last cell. If it goes way past where you think it should be, then look at this: http://www.contextures.com/xlfaqApp.html#Unused -- regards, Tom Ogilvy "Josh Johansen" wrote: Tom, I see exactly what you are saying, it now copies text exactly like I wanted it to and it was quite obvious, I really do appologize that I am so unfamiliar and asking questions I really should know. I still have two small issues that I will continue to try and figure out on my own, I would like to be able to reference cell B10 in the subject because the same person will receive multiple emails with this sheet, but the difference is B10, and also I have no narrow down the range so it only displays cells with data, or only a couple pages insead of over 700. Like I said I will continue to try and figure it out and I am sure I will be able to, but again I thank you very much for your assistance. Josh "Tom Ogilvy" wrote: in the code you posted, you copy the entire sheet ActiveSheet.Copy You then asked: instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. The code I gave you was ActiveSheet.PivotTables(1).TableRange2.copy which only copied the table and when you use pastespecial, only the cells and the data they display are pasted, not the entire table infrastructure you complain about. You complained that it didn't copy row heights and column widths and I gave you corrected code to do that. That all said, in the current voluminous code you have, if you uncomment ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False it should remove the pivottable and only leave the values. By the way, no offense to Ron, but nme = IntputBox("Enter email address") subj = Inputbox("enter Subject") ActiveWorkbook.Sendmail subject:=subj, recipients:=nme would work fine if the user entered a valid email address (only one - which I understand is your requirement). -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, I was thinking about the code you gave me, wasn't that to ensure the column width format remained the same, I am trying to do that while copying and pasting just the text, I have tried inputing that code into a couple of different locations and have received: run-time error '424': Object required Sorry again, I literally have almost zero VBA knowledge "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
It seems to me we are going around in circles. I previously gave you code to
copy the pivottable alone and 8 rows above it. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, adding the .Text solved the issue, what I wanted to do was input that cell into the subject line and it worked. I understand the issue with selecting the print area, what I am trying to do is only copy, paste and email the cells with data so that the users can just print without worrying about print areas or selecting a range, and also it makes the file size smaller which is nice as well. "Tom Ogilvy" wrote: You are already using range("B10") here - where do you want to use it. TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") similarly .Subject = "Schedule Requests: " & Range("B10").Text as for 700 pages, that sounds like something to do with printing. do edit=goto=special and select last cell. If it goes way past where you think it should be, then look at this: http://www.contextures.com/xlfaqApp.html#Unused -- regards, Tom Ogilvy "Josh Johansen" wrote: Tom, I see exactly what you are saying, it now copies text exactly like I wanted it to and it was quite obvious, I really do appologize that I am so unfamiliar and asking questions I really should know. I still have two small issues that I will continue to try and figure out on my own, I would like to be able to reference cell B10 in the subject because the same person will receive multiple emails with this sheet, but the difference is B10, and also I have no narrow down the range so it only displays cells with data, or only a couple pages insead of over 700. Like I said I will continue to try and figure it out and I am sure I will be able to, but again I thank you very much for your assistance. Josh "Tom Ogilvy" wrote: in the code you posted, you copy the entire sheet ActiveSheet.Copy You then asked: instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. The code I gave you was ActiveSheet.PivotTables(1).TableRange2.copy which only copied the table and when you use pastespecial, only the cells and the data they display are pasted, not the entire table infrastructure you complain about. You complained that it didn't copy row heights and column widths and I gave you corrected code to do that. That all said, in the current voluminous code you have, if you uncomment ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False it should remove the pivottable and only leave the values. By the way, no offense to Ron, but nme = IntputBox("Enter email address") subj = Inputbox("enter Subject") ActiveWorkbook.Sendmail subject:=subj, recipients:=nme would work fine if the user entered a valid email address (only one - which I understand is your requirement). -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, I was thinking about the code you gave me, wasn't that to ensure the column width format remained the same, I am trying to do that while copying and pasting just the text, I have tried inputing that code into a couple of different locations and have received: run-time error '424': Object required Sorry again, I literally have almost zero VBA knowledge "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
Tom, you are right you did, and I am sorry about the confusion. I was told
by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses from an address book. He informed me that the only way to have a feature like that was to use a code that he gave me a link to, so I switched over to that. Everything is working great now and I just got back from presenting it and everyone was happy with what it does, the only issue I am still working on is getting the cells copied from the sheet to be smaller, so I went back and found the code you left me before, and I will try and insert into the code I have now if I am able to find the right location. Again I am sorry for the confusion, but I really do appreciate all the help you have given me. "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
If you want the email to popup in your mail program with the attachment
already there and the user makes a selection in the email as to the address, then hits send, then that is correct. Sendmail does not allow user interaction. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, you are right you did, and I am sorry about the confusion. I was told by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses from an address book. He informed me that the only way to have a feature like that was to use a code that he gave me a link to, so I switched over to that. Everything is working great now and I just got back from presenting it and everyone was happy with what it does, the only issue I am still working on is getting the cells copied from the sheet to be smaller, so I went back and found the code you left me before, and I will try and insert into the code I have now if I am able to find the right location. Again I am sorry for the confusion, but I really do appreciate all the help you have given me. "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
If you want the email to popup in your mail program with the attachment
already there and the user makes a selection in the email as to the address, then hits send, then that is correct. Sendmail does not allow user interaction. Send mail was a single line/command in what I provided. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, you are right you did, and I am sorry about the confusion. I was told by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses from an address book. He informed me that the only way to have a feature like that was to use a code that he gave me a link to, so I switched over to that. Everything is working great now and I just got back from presenting it and everyone was happy with what it does, the only issue I am still working on is getting the cells copied from the sheet to be smaller, so I went back and found the code you left me before, and I will try and insert into the code I have now if I am able to find the right location. Again I am sorry for the confusion, but I really do appreciate all the help you have given me. "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
Right, and that is my fault for not better explaining what I was trying to
do, but I really had no idea if it could be done at all much less what to try and make it do. I really do appreciate your help, I have tried using that code you gave me to copy only the cells with data and I am still getting an error, can that code work with what I have now? "Tom Ogilvy" wrote: If you want the email to popup in your mail program with the attachment already there and the user makes a selection in the email as to the address, then hits send, then that is correct. Sendmail does not allow user interaction. Send mail was a single line/command in what I provided. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, you are right you did, and I am sorry about the confusion. I was told by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses from an address book. He informed me that the only way to have a feature like that was to use a code that he gave me a link to, so I switched over to that. Everything is working great now and I just got back from presenting it and everyone was happy with what it does, the only issue I am still working on is getting the cells copied from the sheet to be smaller, so I went back and found the code you left me before, and I will try and insert into the code I have now if I am able to find the right location. Again I am sorry for the confusion, but I really do appreciate all the help you have given me. "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
activesheet.copy
produces a single sheet workbook. The code I wrote produces a single sheet workbook Substitute my code for Activesheet.copy in your code. -- regards, Tom Ogilvy "Josh Johansen" wrote: Right, and that is my fault for not better explaining what I was trying to do, but I really had no idea if it could be done at all much less what to try and make it do. I really do appreciate your help, I have tried using that code you gave me to copy only the cells with data and I am still getting an error, can that code work with what I have now? "Tom Ogilvy" wrote: If you want the email to popup in your mail program with the attachment already there and the user makes a selection in the email as to the address, then hits send, then that is correct. Sendmail does not allow user interaction. Send mail was a single line/command in what I provided. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, you are right you did, and I am sorry about the confusion. I was told by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses from an address book. He informed me that the only way to have a feature like that was to use a code that he gave me a link to, so I switched over to that. Everything is working great now and I just got back from presenting it and everyone was happy with what it does, the only issue I am still working on is getting the cells copied from the sheet to be smaller, so I went back and found the code you left me before, and I will try and insert into the code I have now if I am able to find the right location. Again I am sorry for the confusion, but I really do appreciate all the help you have given me. "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
Tom, I am really having a hard time with this, I have tride half a dozen
different things with the code you gave me. I have tried inputing this code: set rng = ActiveSheet.PivotTables(1).TableRange2 workbooks.Add Template:=xlWBATWorksheet Activesheet.Range("A1").Select rng.copy Activesheet.PasteSpecial xlValues Activesheet.PasteSpecial xlFormats into a couple of spots in this code: Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = Range("B10").Text & " " & "Schedule Requests for" & " " & Range("F3").Text .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Tom Ogilvy" wrote: activesheet.copy produces a single sheet workbook. The code I wrote produces a single sheet workbook Substitute my code for Activesheet.copy in your code. -- regards, Tom Ogilvy "Josh Johansen" wrote: Right, and that is my fault for not better explaining what I was trying to do, but I really had no idea if it could be done at all much less what to try and make it do. I really do appreciate your help, I have tried using that code you gave me to copy only the cells with data and I am still getting an error, can that code work with what I have now? "Tom Ogilvy" wrote: If you want the email to popup in your mail program with the attachment already there and the user makes a selection in the email as to the address, then hits send, then that is correct. Sendmail does not allow user interaction. Send mail was a single line/command in what I provided. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, you are right you did, and I am sorry about the confusion. I was told by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses from an address book. He informed me that the only way to have a feature like that was to use a code that he gave me a link to, so I switched over to that. Everything is working great now and I just got back from presenting it and everyone was happy with what it does, the only issue I am still working on is getting the cells copied from the sheet to be smaller, so I went back and found the code you left me before, and I will try and insert into the code I have now if I am able to find the right location. Again I am sorry for the confusion, but I really do appreciate all the help you have given me. "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
This would be my recommendation.
Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim rng as Range With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ' ActiveSheet.Copy set rng = ActiveSheet.PivotTables(1).TableRange2 workbooks.Add Template:=xlWBATWorksheet Activesheet.Range("A1").Select rng.copy Activesheet.PasteSpecial xlValues Activesheet.PasteSpecial xlFormats Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = Range("B10").Text & " " & "Schedule Requests for" & " " & Range("F3").Text .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards, Tom Ogilvy "Josh Johansen" wrote in message ... Tom, I am really having a hard time with this, I have tride half a dozen different things with the code you gave me. I have tried inputing this code: set rng = ActiveSheet.PivotTables(1).TableRange2 workbooks.Add Template:=xlWBATWorksheet Activesheet.Range("A1").Select rng.copy Activesheet.PasteSpecial xlValues Activesheet.PasteSpecial xlFormats into a couple of spots in this code: Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = Range("B10").Text & " " & "Schedule Requests for" & " " & Range("F3").Text .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Tom Ogilvy" wrote: activesheet.copy produces a single sheet workbook. The code I wrote produces a single sheet workbook Substitute my code for Activesheet.copy in your code. -- regards, Tom Ogilvy "Josh Johansen" wrote: Right, and that is my fault for not better explaining what I was trying to do, but I really had no idea if it could be done at all much less what to try and make it do. I really do appreciate your help, I have tried using that code you gave me to copy only the cells with data and I am still getting an error, can that code work with what I have now? "Tom Ogilvy" wrote: If you want the email to popup in your mail program with the attachment already there and the user makes a selection in the email as to the address, then hits send, then that is correct. Sendmail does not allow user interaction. Send mail was a single line/command in what I provided. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, you are right you did, and I am sorry about the confusion. I was told by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses from an address book. He informed me that the only way to have a feature like that was to use a code that he gave me a link to, so I switched over to that. Everything is working great now and I just got back from presenting it and everyone was happy with what it does, the only issue I am still working on is getting the cells copied from the sheet to be smaller, so I went back and found the code you left me before, and I will try and insert into the code I have now if I am able to find the right location. Again I am sorry for the confusion, but I really do appreciate all the help you have given me. "Josh Johansen" wrote: I have included the code, but what I am trying to do is instead of copying the entire pivot table which is currently 4MB, I would like to copy just the active text for what the user has selected. Also I wanted to reference a cell in the subject of the outgoing email, and I attempted to use range("B10") but it wouldnt accept that, is there a way I could do that? Thanks so much. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "Schedule Requests" .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
Tom, I copied and pasted what you gave me and I got the following error:
Run-time error '1004' PasteSpecial method of worksheet failed when i go to debug, the ActiveSheet.PasteSpecial xlValues row is highlighted. Also I need to copy the seven rows above the pivot table, can I use the same change to the code you left me before? Thanks again. "Tom Ogilvy" wrote: This would be my recommendation. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim rng as Range With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ' ActiveSheet.Copy set rng = ActiveSheet.PivotTables(1).TableRange2 workbooks.Add Template:=xlWBATWorksheet Activesheet.Range("A1").Select rng.copy Activesheet.PasteSpecial xlValues Activesheet.PasteSpecial xlFormats Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = Range("B10").Text & " " & "Schedule Requests for" & " " & Range("F3").Text .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards, Tom Ogilvy "Josh Johansen" wrote in message ... Tom, I am really having a hard time with this, I have tride half a dozen different things with the code you gave me. I have tried inputing this code: set rng = ActiveSheet.PivotTables(1).TableRange2 workbooks.Add Template:=xlWBATWorksheet Activesheet.Range("A1").Select rng.copy Activesheet.PasteSpecial xlValues Activesheet.PasteSpecial xlFormats into a couple of spots in this code: Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = Range("B10").Text & " " & "Schedule Requests for" & " " & Range("F3").Text .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Tom Ogilvy" wrote: activesheet.copy produces a single sheet workbook. The code I wrote produces a single sheet workbook Substitute my code for Activesheet.copy in your code. -- regards, Tom Ogilvy "Josh Johansen" wrote: Right, and that is my fault for not better explaining what I was trying to do, but I really had no idea if it could be done at all much less what to try and make it do. I really do appreciate your help, I have tried using that code you gave me to copy only the cells with data and I am still getting an error, can that code work with what I have now? "Tom Ogilvy" wrote: If you want the email to popup in your mail program with the attachment already there and the user makes a selection in the email as to the address, then hits send, then that is correct. Sendmail does not allow user interaction. Send mail was a single line/command in what I provided. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, you are right you did, and I am sorry about the confusion. I was told by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses from an address book. He informed me that the only way to have a feature like that was to use a code that he gave me a link to, so I switched over to |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
Thanks a lot Tom, I was able to figure it out, I appreciate all of your help.
"Tom Ogilvy" wrote: This would be my recommendation. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim rng as Range With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ' ActiveSheet.Copy set rng = ActiveSheet.PivotTables(1).TableRange2 workbooks.Add Template:=xlWBATWorksheet Activesheet.Range("A1").Select rng.copy Activesheet.PasteSpecial xlValues Activesheet.PasteSpecial xlFormats Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = Range("B10").Text & " " & "Schedule Requests for" & " " & Range("F3").Text .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards, Tom Ogilvy "Josh Johansen" wrote in message ... Tom, I am really having a hard time with this, I have tride half a dozen different things with the code you gave me. I have tried inputing this code: set rng = ActiveSheet.PivotTables(1).TableRange2 workbooks.Add Template:=xlWBATWorksheet Activesheet.Range("A1").Select rng.copy Activesheet.PasteSpecial xlValues Activesheet.PasteSpecial xlFormats into a couple of spots in this code: Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = Range("B10").Text & " " & "Schedule Requests for" & " " & Range("F3").Text .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Tom Ogilvy" wrote: activesheet.copy produces a single sheet workbook. The code I wrote produces a single sheet workbook Substitute my code for Activesheet.copy in your code. -- regards, Tom Ogilvy "Josh Johansen" wrote: Right, and that is my fault for not better explaining what I was trying to do, but I really had no idea if it could be done at all much less what to try and make it do. I really do appreciate your help, I have tried using that code you gave me to copy only the cells with data and I am still getting an error, can that code work with what I have now? "Tom Ogilvy" wrote: If you want the email to popup in your mail program with the attachment already there and the user makes a selection in the email as to the address, then hits send, then that is correct. Sendmail does not allow user interaction. Send mail was a single line/command in what I provided. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, you are right you did, and I am sorry about the confusion. I was told by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses from an address book. He informed me that the only way to have a feature like that was to use a code that he gave me a link to, so I switched over to |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program editing to copy in text format
I just used the code you posted to illustrate - I didn't look to see you had
posted the flawed code before I provided the correction - I assume you have now used the corrected code I previously provided. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Thanks a lot Tom, I was able to figure it out, I appreciate all of your help. "Tom Ogilvy" wrote: This would be my recommendation. Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim rng as Range With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ' ActiveSheet.Copy set rng = ActiveSheet.PivotTables(1).TableRange2 workbooks.Add Template:=xlWBATWorksheet Activesheet.Range("A1").Select rng.copy Activesheet.PasteSpecial xlValues Activesheet.PasteSpecial xlFormats Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = Range("B10").Text & " " & "Schedule Requests for" & " " & Range("F3").Text .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -- Regards, Tom Ogilvy "Josh Johansen" wrote in message ... Tom, I am really having a hard time with this, I have tride half a dozen different things with the code you gave me. I have tried inputing this code: set rng = ActiveSheet.PivotTables(1).TableRange2 workbooks.Add Template:=xlWBATWorksheet Activesheet.Range("A1").Select rng.copy Activesheet.PasteSpecial xlValues Activesheet.PasteSpecial xlFormats into a couple of spots in this code: Sub Mail_ActiveSheet() 'Working in 2000-2007 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 'We exit the sub when your answer is NO in the security dialog that you only 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else Select Case Sourcewb.FileFormat Case 51: FileExtStr = ".xlsx": FileFormatNum = 51 Case 52: If .HasVBProject Then FileExtStr = ".xlsm": FileFormatNum = 52 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If Case 56: FileExtStr = ".xls": FileFormatNum = 56 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 End Select End If End If End With 'Change all cells in the worksheet to values if you want With Destwb.Sheets(1).UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False 'Save the new workbook/Mail it/Delete it TempFilePath = Environ$("temp") & "\" TempFileName = Range("B10") & " " & "Schedule" & " " & "Requests" & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = Range("B10").Text & " " & "Schedule Requests for" & " " & Range("F3").Text .Body = "" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Tom Ogilvy" wrote: activesheet.copy produces a single sheet workbook. The code I wrote produces a single sheet workbook Substitute my code for Activesheet.copy in your code. -- regards, Tom Ogilvy "Josh Johansen" wrote: Right, and that is my fault for not better explaining what I was trying to do, but I really had no idea if it could be done at all much less what to try and make it do. I really do appreciate your help, I have tried using that code you gave me to copy only the cells with data and I am still getting an error, can that code work with what I have now? "Tom Ogilvy" wrote: If you want the email to popup in your mail program with the attachment already there and the user makes a selection in the email as to the address, then hits send, then that is correct. Sendmail does not allow user interaction. Send mail was a single line/command in what I provided. -- Regards, Tom Ogilvy "Josh Johansen" wrote: Tom, you are right you did, and I am sorry about the confusion. I was told by Ron that by using the code you gave me I would be unable to open an email with the sheet attatched and then select the users to email. The code you gave me worked great, but I was unable to select email addresses |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Form Text Boxes - Copy format of text boxes | Excel Discussion (Misc queries) | |||
how do I copy an entire worksheet to another with text & format? | Excel Discussion (Misc queries) | |||
Copy ranges into email - HTLM and Text format | Excel Discussion (Misc queries) | |||
copy a formatted cell to another sheet as text without format | Excel Discussion (Misc queries) | |||
How can I keep the format of cell content when I copy it to text b | Excel Programming |