Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Hi All,
I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not whole workbook. Is there some code I can put in that will email me just the last user input and not the previous inputs? Please help I hope I made it clear Thanks in Advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Hi Sam
How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not whole workbook. Is there some code I can put in that will email me just the last user input and not the previous inputs? Please help I hope I made it clear Thanks in Advance __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Thanks for your reply Ron,
I have 26 cells of data in a row that I need to mail, from A to Z, I have to send it as an excel attachment. Thanks in advance "Ron de Bruin" wrote: Hi Sam How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not whole workbook. Is there some code I can put in that will email me just the last user input and not the previous inputs? Please help I hope I made it clear Thanks in Advance __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Hi Sam
Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have to send it as an excel attachment. Thanks in advance "Ron de Bruin" wrote: Hi Sam How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not whole workbook. Is there some code I can put in that will email me just the last user input and not the previous inputs? Please help I hope I made it clear Thanks in Advance __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Change the mail address to yours before you test it
"Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have to send it as an excel attachment. Thanks in advance "Ron de Bruin" wrote: Hi Sam How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not whole workbook. Is there some code I can put in that will email me just the last user input and not the previous inputs? Please help I hope I made it clear Thanks in Advance __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Hey Ron, This worked out great. Thanks a Lot for your help.
One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to mail that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have to send it as an excel attachment. Thanks in advance "Ron de Bruin" wrote: Hi Sam How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not whole workbook. Is there some code I can put in that will email me just the last user input and not the previous inputs? Please help I hope I made it clear Thanks in Advance __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Use this then and read this page about the warning
http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to mail that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have to send it as an excel attachment. Thanks in advance "Ron de Bruin" wrote: Hi Sam How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not whole workbook. Is there some code I can put in that will email me just the last user input and not the previous inputs? Please help I hope I made it clear Thanks in Advance __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Thank you for all your help, it worked out great. I have to tweak the code a
little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to mail that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have to send it as an excel attachment. Thanks in advance "Ron de Bruin" wrote: Hi Sam How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not whole workbook. Is there some code I can put in that will email me just the last user input and not the previous inputs? Please help I hope I made it clear Thanks in Advance __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
You are welcome
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to mail that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have to send it as an excel attachment. Thanks in advance "Ron de Bruin" wrote: Hi Sam How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not whole workbook. Is there some code I can put in that will email me just the last user input and not the previous inputs? Please help I hope I made it clear Thanks in Advance __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________ The message was checked by ESET Smart Security. __________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Hey Ron, Is this approach possible?
1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to mail that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have to send it as an excel attachment. Thanks in advance "Ron de Bruin" wrote: Hi Sam How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Hi Sam
That it is doing now It create a new workbook Copy the header and last row in it Save it Mail it delete it Your workbook with the userform is still the same -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Hey Ron, Is this approach possible? 1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to mail that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have to send it as an excel attachment. Thanks in advance "Ron de Bruin" wrote: Hi Sam How many cells with data have a row ? Do you want to send it in the body or in a workbook "sam" schreef in bericht ... Hi All, I have a userform that accepts user inputs and emails it to me once users click "Submit" on the userform. Before being emailed they are stored in a workbook on a shared drive. What is happening is: this workbook that is emailed to me stores the user inputs of each user in a separate row, and hence I am receiving the whole workbook with inputs of all the users. What I want: I want to receive just the current users data. I dont want to receive the entire workbook, but the data that has been inputted by the last user only. (For e.g. only the last row updated in the workbook) and not __________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
I think I have messed up your code a litte.. will work on it from start,
I am getting a "run time error, Object required" on this line: ------ Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With I have declared everything and also set my workbook, Here is the code: Dim Source1 As Range Dim Source2 As Range Dim Lrow As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim Dest As Workbook Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:W1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":W" & Lrow) On Error GoTo 0 Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") Am i missing something? Thanks in Advance "Ron de Bruin" wrote: Hi Sam That it is doing now It create a new workbook Copy the header and last row in it Save it Mail it delete it Your workbook with the userform is still the same -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Hey Ron, Is this approach possible? 1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to mail that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Why you open a workook ???
Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") The only thing that you must do is that your sheet with data is active The code will create a new workbook for you save/mail/delete it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... I think I have messed up your code a litte.. will work on it from start, I am getting a "run time error, Object required" on this line: ------ Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With I have declared everything and also set my workbook, Here is the code: Dim Source1 As Range Dim Source2 As Range Dim Lrow As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim Dest As Workbook Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:W1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":W" & Lrow) On Error GoTo 0 Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") Am i missing something? Thanks in Advance "Ron de Bruin" wrote: Hi Sam That it is doing now It create a new workbook Copy the header and last row in it Save it Mail it delete it Your workbook with the userform is still the same -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Hey Ron, Is this approach possible? 1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Thanks for your reply Ron, I have 26 cells of data in a row that I need to mail, from A to Z, I have __________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Hey Ron, This is working now. It took some time but I got it working. But, I
didnt mention you about macro in this workbook where user inputs are saved. I want the current users input only- which Is working fine, But I also want the macro that is in the workbook once it is mailed to me. Rite now, It is saving the workbook with user inputs, then copying the first and the last row in a seperate book and emailing it to me. What I want is, Once the workbook(containing macro) is saved with user inputs(which is working fine). I want to save a copy of the same workbook(containing macro) as a temp file, then I want to copy the first and the last rows(current user input), mail it to me and delete it. I hope I made it clear, sorry for the confusion.. I am not that good with VBA. Thanks a lot "Ron de Bruin" wrote: Why you open a workook ??? Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") The only thing that you must do is that your sheet with data is active The code will create a new workbook for you save/mail/delete it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... I think I have messed up your code a litte.. will work on it from start, I am getting a "run time error, Object required" on this line: ------ Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With I have declared everything and also set my workbook, Here is the code: Dim Source1 As Range Dim Source2 As Range Dim Lrow As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim Dest As Workbook Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:W1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":W" & Lrow) On Error GoTo 0 Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") Am i missing something? Thanks in Advance "Ron de Bruin" wrote: Hi Sam That it is doing now It create a new workbook Copy the header and last row in it Save it Mail it delete it Your workbook with the userform is still the same -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Hey Ron, Is this approach possible? 1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
I am confused and I am not sure what you want
Why do you want the macro in the workbook with two lines ?? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... Hey Ron, This is working now. It took some time but I got it working. But, I didnt mention you about macro in this workbook where user inputs are saved. I want the current users input only- which Is working fine, But I also want the macro that is in the workbook once it is mailed to me. Rite now, It is saving the workbook with user inputs, then copying the first and the last row in a seperate book and emailing it to me. What I want is, Once the workbook(containing macro) is saved with user inputs(which is working fine). I want to save a copy of the same workbook(containing macro) as a temp file, then I want to copy the first and the last rows(current user input), mail it to me and delete it. I hope I made it clear, sorry for the confusion.. I am not that good with VBA. Thanks a lot "Ron de Bruin" wrote: Why you open a workook ??? Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") The only thing that you must do is that your sheet with data is active The code will create a new workbook for you save/mail/delete it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... I think I have messed up your code a litte.. will work on it from start, I am getting a "run time error, Object required" on this line: ------ Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With I have declared everything and also set my workbook, Here is the code: Dim Source1 As Range Dim Source2 As Range Dim Lrow As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim Dest As Workbook Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:W1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":W" & Lrow) On Error GoTo 0 Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") Am i missing something? Thanks in Advance "Ron de Bruin" wrote: Hi Sam That it is doing now It create a new workbook Copy the header and last row in it Save it Mail it delete it Your workbook with the userform is still the same -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Hey Ron, Is this approach possible? 1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this Sub Mail_Range() 'Working in 2000-2007 Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) Set Source = Nothing On Error Resume Next Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Sorry for the confusion. I want the macro because there is a button designed
in that workbook which on clicking would do some updates and generate reports. "Ron de Bruin" wrote: I am confused and I am not sure what you want Why do you want the macro in the workbook with two lines ?? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... Hey Ron, This is working now. It took some time but I got it working. But, I didnt mention you about macro in this workbook where user inputs are saved. I want the current users input only- which Is working fine, But I also want the macro that is in the workbook once it is mailed to me. Rite now, It is saving the workbook with user inputs, then copying the first and the last row in a seperate book and emailing it to me. What I want is, Once the workbook(containing macro) is saved with user inputs(which is working fine). I want to save a copy of the same workbook(containing macro) as a temp file, then I want to copy the first and the last rows(current user input), mail it to me and delete it. I hope I made it clear, sorry for the confusion.. I am not that good with VBA. Thanks a lot "Ron de Bruin" wrote: Why you open a workook ??? Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") The only thing that you must do is that your sheet with data is active The code will create a new workbook for you save/mail/delete it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... I think I have messed up your code a litte.. will work on it from start, I am getting a "run time error, Object required" on this line: ------ Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With I have declared everything and also set my workbook, Here is the code: Dim Source1 As Range Dim Source2 As Range Dim Lrow As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim Dest As Workbook Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:W1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":W" & Lrow) On Error GoTo 0 Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") Am i missing something? Thanks in Advance "Ron de Bruin" wrote: Hi Sam That it is doing now It create a new workbook Copy the header and last row in it Save it Mail it delete it Your workbook with the userform is still the same -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Hey Ron, Is this approach possible? 1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
How many sheets are there in the workbook
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... Sorry for the confusion. I want the macro because there is a button designed in that workbook which on clicking would do some updates and generate reports. "Ron de Bruin" wrote: I am confused and I am not sure what you want Why do you want the macro in the workbook with two lines ?? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... Hey Ron, This is working now. It took some time but I got it working. But, I didnt mention you about macro in this workbook where user inputs are saved. I want the current users input only- which Is working fine, But I also want the macro that is in the workbook once it is mailed to me. Rite now, It is saving the workbook with user inputs, then copying the first and the last row in a seperate book and emailing it to me. What I want is, Once the workbook(containing macro) is saved with user inputs(which is working fine). I want to save a copy of the same workbook(containing macro) as a temp file, then I want to copy the first and the last rows(current user input), mail it to me and delete it. I hope I made it clear, sorry for the confusion.. I am not that good with VBA. Thanks a lot "Ron de Bruin" wrote: Why you open a workook ??? Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") The only thing that you must do is that your sheet with data is active The code will create a new workbook for you save/mail/delete it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... I think I have messed up your code a litte.. will work on it from start, I am getting a "run time error, Object required" on this line: ------ Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With I have declared everything and also set my workbook, Here is the code: Dim Source1 As Range Dim Source2 As Range Dim Lrow As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim Dest As Workbook Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:W1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":W" & Lrow) On Error GoTo 0 Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") Am i missing something? Thanks in Advance "Ron de Bruin" wrote: Hi Sam That it is doing now It create a new workbook Copy the header and last row in it Save it Mail it delete it Your workbook with the userform is still the same -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Hey Ron, Is this approach possible? 1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Hey Ron, I finally figured it out. Your email code is working great. I had to
change my code a little to make it work. I wouldnt have accomplished this without your code. I would like to explain you how I got it to work. You can let me know if there are any flaws/inconsistencies with the process I am following. Will email you if you dont mind Thanks again "Ron de Bruin" wrote: I am confused and I am not sure what you want Why do you want the macro in the workbook with two lines ?? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... Hey Ron, This is working now. It took some time but I got it working. But, I didnt mention you about macro in this workbook where user inputs are saved. I want the current users input only- which Is working fine, But I also want the macro that is in the workbook once it is mailed to me. Rite now, It is saving the workbook with user inputs, then copying the first and the last row in a seperate book and emailing it to me. What I want is, Once the workbook(containing macro) is saved with user inputs(which is working fine). I want to save a copy of the same workbook(containing macro) as a temp file, then I want to copy the first and the last rows(current user input), mail it to me and delete it. I hope I made it clear, sorry for the confusion.. I am not that good with VBA. Thanks a lot "Ron de Bruin" wrote: Why you open a workook ??? Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") The only thing that you must do is that your sheet with data is active The code will create a new workbook for you save/mail/delete it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... I think I have messed up your code a litte.. will work on it from start, I am getting a "run time error, Object required" on this line: ------ Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With I have declared everything and also set my workbook, Here is the code: Dim Source1 As Range Dim Source2 As Range Dim Lrow As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim Dest As Workbook Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:W1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":W" & Lrow) On Error GoTo 0 Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") Am i missing something? Thanks in Advance "Ron de Bruin" wrote: Hi Sam That it is doing now It create a new workbook Copy the header and last row in it Save it Mail it delete it Your workbook with the userform is still the same -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Hey Ron, Is this approach possible? 1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
email issue: need last user input only
Hi Sam
You can mail me your file and when I have time I will take a look at it You can find my mail address on my site -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... Hey Ron, I finally figured it out. Your email code is working great. I had to change my code a little to make it work. I wouldnt have accomplished this without your code. I would like to explain you how I got it to work. You can let me know if there are any flaws/inconsistencies with the process I am following. Will email you if you dont mind Thanks again "Ron de Bruin" wrote: I am confused and I am not sure what you want Why do you want the macro in the workbook with two lines ?? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... Hey Ron, This is working now. It took some time but I got it working. But, I didnt mention you about macro in this workbook where user inputs are saved. I want the current users input only- which Is working fine, But I also want the macro that is in the workbook once it is mailed to me. Rite now, It is saving the workbook with user inputs, then copying the first and the last row in a seperate book and emailing it to me. What I want is, Once the workbook(containing macro) is saved with user inputs(which is working fine). I want to save a copy of the same workbook(containing macro) as a temp file, then I want to copy the first and the last rows(current user input), mail it to me and delete it. I hope I made it clear, sorry for the confusion.. I am not that good with VBA. Thanks a lot "Ron de Bruin" wrote: Why you open a workook ??? Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") The only thing that you must do is that your sheet with data is active The code will create a new workbook for you save/mail/delete it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... I think I have messed up your code a litte.. will work on it from start, I am getting a "run time error, Object required" on this line: ------ Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With I have declared everything and also set my workbook, Here is the code: Dim Source1 As Range Dim Source2 As Range Dim Lrow As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object Dim Dest As Workbook Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:W1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":W" & Lrow) On Error GoTo 0 Set Dest = Workbooks.Open(Filename:="H:\Book11.xlsm") Am i missing something? Thanks in Advance "Ron de Bruin" wrote: Hi Sam That it is doing now It create a new workbook Copy the header and last row in it Save it Mail it delete it Your workbook with the userform is still the same -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Hey Ron, Is this approach possible? 1. Saving all User inputs(accepted through userform) in a workbook template, lets say: UserWb.xls 2. Now, Saving a copy of the same workbook(UserWb.xls) as UserWbCpy.xls with just he current user entry. 3. Mailing the copy that includes current user entry only(UserWbCpy.xls) 4. Deleting the copy(UserWbCpy.xls). But the original template still remains(UserWb.xls) as I want to keep a backup file of all user inputs. Thanks in advance "Ron de Bruin" wrote: You are welcome -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" schreef in bericht ... Thank you for all your help, it worked out great. I have to tweak the code a little to make it work my way, thank you for your sources available online. "Ron de Bruin" wrote: Use this then and read this page about the warning http://www.rondebruin.nl/mail/prevent.htm If you use Outlook you can also add body text if you want See http://www.rondebruin.nl/sendmail.htm Sub Mail_Range() 'Working in 2000-2007 Dim Source1 As Range Dim Source2 As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Lrow As Long Lrow = LastRow(ActiveSheet) On Error Resume Next Set Source1 = Range("A1:K1") On Error GoTo 0 On Error Resume Next Set Source2 = Range("A" & Lrow & ":K" & Lrow) On Error GoTo 0 With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source1.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With Source2.Copy With Dest.Sheets(1) .Cells(2, 1).PasteSpecial Paste:=8 .Cells(2, 1).PasteSpecial Paste:=xlPasteValues .Cells(2, 1).PasteSpecial Paste:=xlPasteFormats .Cells(2, 1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Selection of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function "sam" schreef in bericht ... Hey Ron, This worked out great. Thanks a Lot for your help. One thing I also wanted was to mail column headers with the last updated row.. The first row contains column headers and I would like to that as well. I tried updaing this part of the code: Set Source = Range("A" & Lrow & ":K" & Lrow).SpecialCells(xlCellTypeVisible) to: Set Source = Range("A" & Lrow & ":K" & Lrow & Frow).SpecialCells(xlCellTypeVisible) But it didnt mail the first row, ALso when I it mails, it asks me to allow or deny the macro to mail the book, should I set Application.DisplayAlerts = False ? Thanks a lot again. "Ron de Bruin" wrote: Change the mail address to yours before you test it "Ron de Bruin" schreef in bericht ... Hi Sam Try this |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt the start of an email...user fills in email address | Excel Programming | |||
Email issue | Excel Discussion (Misc queries) | |||
Prompt user for input and utilize that input | Excel Worksheet Functions | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |