Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Help with Excel to Outlook code

  1. #1

    Help with Excel to Outlook code



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Quote Originally Posted by Ken Puls View Post
    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


    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!

  5. #5
    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 by dulitul; 2012-12-13 at 08:37 PM.

  6. #6
    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 by dulitul; 2012-12-13 at 08:52 PM.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    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.
    Attached Files Attached Files

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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 = "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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    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 by dulitul; 2012-12-16 at 11:32 AM.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •