PDA

View Full Version : Copy data from sheet 2 variable range to sheet 3 fixed range and send it by email



Alice11
01-18-2015, 06:24 AM
Hi everyone, I've been looking for a while now how to copy a specific row from sheet 2 to sheet 3 I found some bits of codes on forums but I couldn't make it work.


Let me explain :
I created 3 forms :
-form1 to gather some data transferred into the last empty row available of Sheet2 (this works fine with this :
dim emptyrow as long
sheet2.activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 )
Cells(emptyRow, 1).Value = Textbox1.Value etc... For each cell


- form 2 to gather some more data transferred into the same row (this bit works fine with this :
dim emptyrow as long
sheet2.activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 0
Cells(emptyRow, 1).Value = Textbox1.Value etc... For each cell


- form 3 where I have a button "close" when I click on it I would like that :
- the row I just filled in sheet2 is copied and pasted onto a fixed range on sheet3 A3 to M3 ( I can't make this work)
- then i save the workbook (works fine)
- then I want to send sheet 3 only to someone by email ( the best would be to have the range A1 to M3 from sheet3 displayed on the body of the email :) ) But I can't make this work either ( I do manage to send the whole workbook though)
- then I would like the range A3 to M3 of sheet3 to be cleared and then the workbook to be saved again before unloading form3


Does anyone can help me ?
Thanks a lot
Al

gmayor
01-18-2015, 07:26 AM
The following will send your worksheet range by e-mail. You can call it from your macro e.g. as follows

If you are going to replace the entry on sheet3 after sending it, why bother putting it there at all? You can send the range from Sheet 2.




Sub YourMacro()
Dim oSource As Range
Dim oTarget As Range
Dim lastrow As Long
Dim xlSheet2 As Worksheet
Dim xlSheet3 As Worksheet
Set xlSheet2 = ActiveWorkbook.Sheets(2)
Set xlSheet3 = ActiveWorkbook.Sheets(3)
With xlSheet2
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 'The last used row (if relevant)
Set oSource = .Range("A" & lastrow & ":M" & lastrow) 'The range you want to copy
oSource.Copy 'Copy it
End With
With xlSheet3
Set oTarget = .Range("A3:M3") 'The range where you require it
oTarget.PasteSpecial xlPasteAll 'Paste it
End With
'Send the worksheet as an e-mail
SendWorksheet ActiveWorkbook.Sheets(3), _
"someone@somewhere.com", _
"Message Subject"
Set xlSheet2 = Nothing
Set xlSheet3 = Nothing
Set oSource = Nothing
Set oTarget = Nothing
lbl_Exit:
Exit Sub
End Sub




Sub SendWorksheet(xlSheet As Worksheet)
Dim oOutlookApp As Object
Dim oItem As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object

xlSheet.Range("A3", "M3").Copy
On Error Resume Next
'Get Outlook if it's running
Set oOutlookApp = GetObject(, "Outlook.Application")

'Outlook wasn't running, start it from code
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(0)
With oItem
.BodyFormat = 2
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
oRng.Collapse 1
'oRng.Text = "This is the body text of the message" & vbcr 'if required
'orng.collapse 0
oRng.Paste 'put the table after the body text but before the default signature
.To = "someone@somewhere.com" 'The intended recipient
.Subject = "This is the subject" 'Self explanatory
.Display 'This line must be included
'.Send 'Restore after testing
End With

'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
lbl_Exit:
Exit Sub
End Sub

snb
01-18-2015, 08:45 AM
Especially written for you:

http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html#H6

SamT
01-18-2015, 09:40 AM
Hi everyone, I've been looking for a while now how to copy a specific row from sheet 2 to sheet 3 I found some bits of codes on forums but I couldn't make it work.


Let me explain :
I created 3 forms :
-form1 to gather some data transferred into the last empty row available of Sheet2 (this works fine with this :
dim emptyrow as long
sheet2.activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 1).Value = Textbox1.Value etc... For each cell


- form 2 to gather some more data transferred into the same row (this bit works fine with this :
dim emptyrow as long
sheet2.activate
emptyRow LastRow = WorksheetFunction.CountA(Range("A:A")) + 0
Cells(emptyRow, 1).Value = Textbox1.Value etc... For each cell


- form 3 where I have a button "close" when I click on it I would like that :
- the row I just filled in sheet2 is copied and pasted onto a fixed range on sheet3 A3 to M3 ( I can't make this work)
- then i save the workbook (works fine)
- then I want to send sheet 3 only to someone by email ( the best would be to have the range A1 to M3 from sheet3 displayed on the body of the email :) ) But I can't make this work either ( I do manage to send the whole workbook though)
- then I would like the range A3 to M3 of sheet3 to be cleared and then the workbook to be saved again before unloading form3


Does anyone can help me ?
Thanks a lot
Al

Inre: emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 | + 0
Better:
emptyRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

In Form 3, Use Constant(s) for values in Sheet3.Range("A1:A2"), Then in email body, place

Constant & Sheet2.Range(LastRow) Note no sheet3.

When your post count >= 3, "Go Advanced" and upload your workbook for us.

Alice11
01-19-2015, 11:44 AM
Thanks for your answer, actually I realised that you are right I don’t need any sheet 3.
I couldn’t make it work unfortunately.Would you be able to advise me where or how I should input the code to send the email.

Below is the code under the button Valider of the Page1 of my form.


Private Sub Valider_Click()
‘I skip you the bit of conditions on mandatory fields
Sheet2.Activate
emptyRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(emptyRow, 1).Value = Name.Value
‘I skip the bit where I transfer the other info information
‘I skip the bit of conditions on mandatory fields
Unload Page1all
Page2All.Show
End Sub


Below is the code under the button Valider of the Page2 of my form. Then leading to page 3.



Private Sub Valider_Click()
Dim Lastrow As Long
‘I skip you the bit of conditions on mandatory fields
Sheet2.Activate
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(LastRow, 7).Value = Address.Value
' I skip the bit where I transfer the other info information
Unload Page2All
Page3All.Show
End Sub




Below is my code for the page 3 of my form.
I would like the email to be sent when I click on the button “Quitter” on my form.
And the body of the email to display 2 ranges :
-The range A1 to M2
-The range LastRow
I couldn’t figure out where to input the code you gave me. Would you be able to help me a bit more :/


Private Sub Quitter_Click()
ActiveWorkbook.Save
ActiveWorkbook.SendMail Recipients:=Array("address1", "address2"), Subject:=”Your offer”, body:= Header & Sheet2.Range(LastRow)
Unload Me
End Sub

gmayor
01-20-2015, 03:58 AM
Can you attach your workbook, so we can see what it is you are doing.

The function replaces
ActiveWorkbook.SendMailto enable the message body to be edited. This requires Outlook to send the message. Clearly in view of your latest comments it will need editing to work as required, but to save a lot of unnecessary guesswork, with respect to variable names and ranges, it would help to see what you have done.