Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: Vba to copy cells and paste into outlook

  1. #1

    Vba to copy cells and paste into outlook



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

    Hi all,

    I have a spreadsheet that I would like to copy and paste information into an email and then send that email to a specific person based on a value that is entered in that cell. Not sure if this is possible but the attached spreadsheet should help explain this. Cell L4 populates with the text 'Urgent' if cell K4 is equal to A4.

    When Cell L4 shows 'Urgent', can excel copy cells A4 to Q4 and paste them in an email, and then send it to a specific email address.

    The only other thing to consider is that I would want this to work every time Urgent is populated so for example:

    When Cell L5 shows 'Urgent', can excel copy cells A5 to Q5 and paste them in an email, and then send it to a specific email address.
    When Cell L6 shows 'Urgent', can excel copy cells A6 to Q6 and paste them in an email, and then send it to a specific email address.
    When Cell L7 shows 'Urgent', can excel copy cells A7 to Q7 and paste them in an email, and then send it to a specific email address.
    AND SO ON.........

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Hi there, and welcome to the forum.

    Yes, absolutley. A few questions though...
    1) How would you like this triggered? Press a button and it sends an email to everyone that is URGENT, or did you want to do it one person at a time?
    2) Are you Microsoft Outlook as your email client, or something else?
    3) What version of Excel are you using?
    4) Where will the email addresses of the person/people to send to be stored? (Hopefully in a column, but which one?)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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
    Thaks Vrey much

    Yes, absolutley. A few questions though...
    1) How would you like this triggered? Press a button and it sends an email to everyone that is URGENT, or did you want to do it one person at a time?

    One person at a time

    2) Are you Microsoft Outlook as your email client, or something else?

    Micorsoft Outlook

    3) What version of Excel are you using?

    2003
    4) Where will the email addresses of the person/people to send to be stored? (Hopefully in a column, but which one?)


    Any column is fine as long as its not got anything esle in it

    Many thanks for this

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    I'll see what I can cobble together for you tomorrow on this.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  5. #5
    Thanks v much its really appreciated

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Okay, try this. Email addresses need to be placed in column Q (although you can easily change that in the code):

    Code:
    Sub SendEmail()
    'Macro Purpose: To send an email through Outlook
        Dim objOL As Object
        Dim objMail As Object
        Dim sEmail As String
        Dim sEmailColumn As String
        Dim sSubject As String
        Dim sBody As String
        Dim lDataRow As Long
        Dim cl As Range
        'Set column with email address
        sEmailColumn = "Q"
        For Each cl In Selection.Resize(, 1)
            'Generate required info
            lDataRow = cl.Row
            'Check if remediation required
            If cl.Parent.Range("L" & lDataRow).Value = "Urgent" Then
                With cl.Parent
                    sEmail = .Range(sEmailColumn & lDataRow)
                    sSubject = "Agreement " & .Range("B" & lDataRow) & " requires urgent remediation!"
                    sBody = "Remediation Required:" & vbNewLine & .Range("H" & lDataRow) & _
                            vbNewLine & vbNewLine & "Advisors Comments:" & vbNewLine & .Range("N" & lDataRow) & _
                            vbNewLine & vbNewLine & "Management Comments:" & vbNewLine & .Range("O" & lDataRow)
                End With
                'Turn on error handling
                On Error GoTo Cleanup
                'Bind to Outlook
                Set objOL = CreateObject("Outlook.Application")
                'Create a new email and send it
                Set objMail = objOL.CreateItem(0)    '0=olmailitem
                With objMail
                    .To = sEmail
                    .Subject = sSubject
                    .Body = sBody
                    .Display
                End With
            End If
        Next cl
    Cleanup:
        'Release all objects
        Set objMail = Nothing
        Set objOL = Nothing
        On Error GoTo 0
    End Sub
    Once that code is pasted into a standard module, you can select any cell, press Alt+F8 and run the macro. It should create the email for you. If you select a group of cells, it will create an email for each line where remediation is Urgent.

    If you'd like it to send automatically, without creating the preview first, change ".Display" to ".Send"
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  7. #7
    works like a treat.....I cant thank you enough.

    Very much appreciated
    Thanks

  8. #8

    Vba to copy cells and paste into outlook

    Hi Ken,

    Can you please help me with a similar problem? I want to write a code where predefined cells are copied and sent in a mail. Attached in the code which i have written:
    Dim objOL As Object
    Dim objMail As Object
    Dim sEmail As String
    Dim sSubject As String
    Dim sBody As String
    Dim lDataRow As Long
    Dim cl As Range
    Set b = Range("B2:B100").Find("WHITE", LookIn:=xlValues)
    MyRow = b.Row
    sSubject = "Test Execution status as of " & Cells(6, 3) & " for " & Cells(7, 3)
    sBody = Range("B2:K" & MyRow).Select

    'Turn on error handling
    On Error GoTo Cleanup
    'Bind to Outlook
    Set objOL = CreateObject("Outlook.Application")
    'Create a new email and send it
    Set objMail = objOL.CreateItem(0) '0=olmailitem
    With objMail
    .Subject = sSubject
    .Body = sBody
    .Display
    End With
    Cleanup:
    'Release all objects
    Set objMail = Nothing
    Set objOL = Nothing
    On Error GoTo 0
    End Sub

    Here when i execute the code, "True" is coming in body of mail while i want the copied cells to be in the mail. What should i do to copy my selected range.

    Thanks,
    Ankit



    Quote Originally Posted by Ken Puls View Post
    Okay, try this. Email addresses need to be placed in column Q (although you can easily change that in the code):

    Code:
    Sub SendEmail()
    'Macro Purpose: To send an email through Outlook
        Dim objOL As Object
        Dim objMail As Object
        Dim sEmail As String
        Dim sEmailColumn As String
        Dim sSubject As String
        Dim sBody As String
        Dim lDataRow As Long
        Dim cl As Range
        'Set column with email address
        sEmailColumn = "Q"
        For Each cl In Selection.Resize(, 1)
            'Generate required info
            lDataRow = cl.Row
            'Check if remediation required
            If cl.Parent.Range("L" & lDataRow).Value = "Urgent" Then
                With cl.Parent
                    sEmail = .Range(sEmailColumn & lDataRow)
                    sSubject = "Agreement " & .Range("B" & lDataRow) & " requires urgent remediation!"
                    sBody = "Remediation Required:" & vbNewLine & .Range("H" & lDataRow) & _
                            vbNewLine & vbNewLine & "Advisors Comments:" & vbNewLine & .Range("N" & lDataRow) & _
                            vbNewLine & vbNewLine & "Management Comments:" & vbNewLine & .Range("O" & lDataRow)
                End With
                'Turn on error handling
                On Error GoTo Cleanup
                'Bind to Outlook
                Set objOL = CreateObject("Outlook.Application")
                'Create a new email and send it
                Set objMail = objOL.CreateItem(0)    '0=olmailitem
                With objMail
                    .To = sEmail
                    .Subject = sSubject
                    .Body = sBody
                    .Display
                End With
            End If
        Next cl
    Cleanup:
        'Release all objects
        Set objMail = Nothing
        Set objOL = Nothing
        On Error GoTo 0
    End Sub
    Once that code is pasted into a standard module, you can select any cell, press Alt+F8 and run the macro. It should create the email for you. If you select a group of cells, it will create an email for each line where remediation is Urgent.

    If you'd like it to send automatically, without creating the preview first, change ".Display" to ".Send"

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    ankit, are you still looking for help with this?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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
    Ken,
    I know this thread is pretty old but it references exactly the problem I am trying to solve. Basically I would like to open a new email in Outlook (Outlook will be open already) and then copy in a specified range of cells into the email (ie range("A1:F60")). The cells do not need to be selected beforehand. I do have some merged cells within this range. Any help would be greatly appreciated, thanks!

Page 1 of 3 1 2 3 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
  •