Help with Excel to Outlook code

D

dulitul

New member
Joined
Dec 13, 2012
Messages
16
Reaction score
0
Points
0
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
 
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?

Code: 
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
 
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. :)
 
Ken Puls said:
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?

Code: 
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
Click to expand...



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!
 
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
 
Last edited:
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
 
Last edited:
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,
 
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.
 

Attachments

  • sample.xls
    13.5 KB · Views: 22
Hey there,

Try this out:

Code: 
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 = "[EMAIL="someone@somedomain.com"]someone@somedomain.com[/EMAIL]"
        '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. :)
 
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?
 
Last edited:
I'm a big fan of John Walkenbach's Power Programming With VBA series. That one is more advanced than the dummies book, and is an invaluable resource.

With regards to formatting, that one gets a little tricky. Bold is easy, but "the colour yellow" is hard, particularly if you are in Excel 2007 or higher. The issue is around tints/shades and hues, which would essentially require a full colour map to convert from Excel to HTML.

As far as spaces, do you want more spaces between the columns or the rows in the table?
 
Hey,


I would like to have more space between the columns and rows (both). The best would be if I am able to create a table with the values in excel and paste it in the outlook. Is that possible?

Cheers


Best regards
 
Unfotunatley no, it's not that easy. We have to create an HTML table on the fly and populate it.

I'll try getting to modifications for that tonight.
 
Here, give this version a go:
Code: 
Sub cond_copy()
    Dim wsSource As Worksheet
    Dim cl As Range
    Dim oApp As Object
    Dim oMail As Object
    Dim sHTML As String
    Dim cellPad As Long
    
    cellPad = 12
    
    Set wsSource = Sheets("Sheet1")
    
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    
    'Create HTML table
    sHTML = "<table cellpadding=""" & cellPad & """><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
            If cl.Offset(0, 1).Font.Bold Then
                sHTML = sHTML & "<tr><td><b>" & cl.Offset(0, 1).Value & "</b></td><td><b>" & cl.Offset(0, 2).Value & "</b></td></tr>"
            Else
                sHTML = sHTML & "<tr><td>" & cl.Offset(0, 1).Value & "</td><td>" & cl.Offset(0, 2).Value & "</td></tr>"
            End If
            cl.Value = "Check"
        End If
    Next
    sHTML = sHTML & "</table>"
    
    With oMail
        'Uncomment the line below to hard code a recipient
        .To = "[EMAIL="someone@somedomain.com"]someone@somedomain.com[/EMAIL]"
        '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

You can change the value of the CellPad to add more space around the table items. It will also make rows bold if the value in the second column is bold.
 
You must log in or register to reply here.
Back
Top