Help with Excel to Outlook code

[dulitul]

Hey,

I would like to ask you if you could help me out with the following macro. I want to copy excel rows based on a certain value in a column and paste them in a outlook mail. After having done that I want to change the cells value in the unique rows to sth else - for example - "check" being in a green colour. This is the code I produced so far. But it doesnt display the rows..it just opens the mailbox in outlook..Pls help. Thank you

Sub cond_copy()
Sheets("Sheet1").Select
RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 1 To RowCount
'assuming the true statment is in column a
Range("a" & i).Select
check_value = ActiveCell
If check_value = "True" Or check_value = "true" Then
ActiveCell.EntireRow.Copy
End with
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "someone@somedomain.com"
'Uncomment the line below to hard code a subject
'.Subject = paste

.display
End with
End If
Next


End Sub

[Ken Puls]

Hi there,

FYI, I've split your question into it's own thread.

I've cleaned up your code a bit here, but I'm not quite sure I follow what you want to happen. At this point, it should copy the row, and will create your outlook email. You want the data in the Outlook email though? And then just highlight the entire row in the spreadsheet in green?

Sub cond_copy()
    Dim i As Integer
    Dim wsSource As String
    
    wsSource = Sheets("Sheet1")
    
    For i = 1 To wsSource.Cells(wsSource.Cells.Rows.Count, "a").End(xlUp).Row
       
        If UCase(CStr(wsSource.Range("a" & i).Value)) = "TRUE" Then
            wsSource.Range("a" & i).EntireRow.Copy
            
            Set oApp = CreateObject("Outlook.Application")
            Set oMail = oApp.CreateItem(0)
            With oMail
                'Uncomment the line below to hard code a recipient
                .To = "someone@somedomain.com"
                'Uncomment the line below to hard code a subject
                '.Subject =  paste
            
              .display
            End With
        End If
    Next
End Sub

[Ken Puls]

Just noticed that you also created a new thread in addition to posting this on another. I've removed the one you originally created since I've already answered this one.

[dulitul]

Hi there,

FYI, I've split your question into it's own thread.

I've cleaned up your code a bit here, but I'm not quite sure I follow what you want to happen. At this point, it should copy the row, and will create your outlook email. You want the data in the Outlook email though? And then just highlight the entire row in the spreadsheet in green?

Sub cond_copy()
    Dim i As Integer
    Dim wsSource As String
    
    wsSource = Sheets("Sheet1")
    
    For i = 1 To wsSource.Cells(wsSource.Cells.Rows.Count, "a").End(xlUp).Row
       
        If UCase(CStr(wsSource.Range("a" & i).Value)) = "TRUE" Then
            wsSource.Range("a" & i).EntireRow.Copy
            
            Set oApp = CreateObject("Outlook.Application")
            Set oMail = oApp.CreateItem(0)
            With oMail
                'Uncomment the line below to hard code a recipient
                .To = "someone@somedomain.com"
                'Uncomment the line below to hard code a subject
                '.Subject =  paste
            
              .display
            End With
        End If
    Next
End Sub

Hey Ken,


Thank you for your quick response. However, I do get an error when I run the code you posted. It says - "Compile error Invalid qualifier" and marks my sub macro in yellow (the first row I mean).

And second, I would like to run the macro, automatically paste the rows containing "True" in column A in an outlook mail. After that I want automatically to rename the labels "True" from the relevant rows (I pasted) in the excel sheets to sth else - for example - the text "check" instead "True" and being coloured green. I would be very thankful if you could create that macro for me. I am yet a beginner and getting involved in the world of vba.

Thank you in advance!

[dulitul]

Hey again,


Just wanted to tell you that I adjusted your code a bit to remove the error to:

Sub cond_copy()
Dim i As Integer


For i = 1 To Application.Sheets("Sheet1").Cells(Application.Sheets("Sheet1").Cells.Rows.Count, "a").End(xlUp).Row

If UCase(CStr(Application.Sheets("Sheet1").Range("a" & i).Value)) = "TRUE" Then
Application.Sheets("Sheet1").Range("a" & i).EntireRow.Copy

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "someone@somedomain.com"
'Uncomment the line below to hard code a subject
.Subject = paste

.display
End With
End If
Next
End Sub


However, it opens a new message box, but does not display the selected rows? Any ideas?

Furthermore, I cannot create the code afterwards to change the "True" labels to "check" of the selected rows in the excel sheet. Pls help..

Best regards

[dulitul]

Sorry for posting again..


Further to my previous post..I just found out that a new mail is generated for every "True" in the column. Could you adjust it somehow so as to all "True" rows to be loaded in one email and not separately. And also, in your code you wrote - .subject = paste
I changed it to .body = paste
but still it doesnt display the excel rows in the mail box...

Thank you

[Ken Puls]

Okay, I get what you're after now.

For reference, I just cleaned up your code above and made some adjustments to to, but never tested it as I didn't have clear sample data. There were definitely some errors.

Moving forward here, we've got a bit of an issue in that you can't just paste a row of data into an OUtlook email body. It's going to be more complicated than that.

What would be very helpful for me is to see a sample of the data that you're working with. Mocked up is fine, as long as the worksheet setup and data characteristics are the same. You can attach a copy to this post by double clicking the reply button at the bottom.

Cheers,

[dulitul]

Here you go. I want the rows starting with true to be displayed in an outlook mail which I am going to send. Then I want to change the label True to "check" in the excel sheet so that I know that I have sent them successfully. Then I am going to add new data rows with True in column A, send them and they should also be changed afterwards to check etc.

[Ken Puls]

Hey there,

Try this out:

Sub cond_copy()
    Dim wsSource As Worksheet
    Dim cl As Range
    Dim oApp As Object
    Dim oMail As Object
    Dim sHTML As String
    
    Set wsSource = Sheets("Sheet1")
    
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    
    'Create HTML table
    sHTML = "<table><tr><td>Name</td><td>Commission</td></tr>"
    For Each cl In wsSource.Range("A1:A" & wsSource.Range("A" & wsSource.Rows.Count).End(xlUp).Row)
        If UCase(cl.Value) = "TRUE" Then
            sHTML = sHTML & "<tr><td>" & cl.Offset(0, 1).Value & "</td><td>" & cl.Offset(0, 2).Value & "</td></tr>"
            cl.Value = "Check"
        End If
    Next
    sHTML = sHTML & "</table>"
    
    With oMail
        'Uncomment the line below to hard code a recipient
        .To = "someone@somedomain.com"
        'Uncomment the line below to hard code a subject
        .Subject = "Commission report"
        .HTMLBody = sHTML
        .display
    End With
    
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

Be aware that this runs MUCH faster if Outlook is already open.

[dulitul]

This is just bloody awesome!!! You are absolutely great! Unfortunately, I cant find these codes in the book I am reading - John Walkenbach - VBA for dummies. I am still a beginner. Can you recommend some other books for VBA experts?

1)I ve got just one more thing in mind. If I want to paste the rows in the respective formatting? For instance if some of the cells are yellow coloured or in bold format what should I do?

2) how can I add more space between the cells in the outlook mail?