Vba to copy cells and paste into outlook

[thedeadzeds]

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

[Ken Puls]

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?)

[thedeadzeds]

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

[Ken Puls]

I'll see what I can cobble together for you tomorrow on this.

[thedeadzeds]

Thanks v much its really appreciated

[Ken Puls]

Okay, try this. Email addresses need to be placed in column Q (although you can easily change that in the 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"

[thedeadzeds]

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

Very much appreciated
Thanks

[ankit_kuchhal]

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

Okay, try this. Email addresses need to be placed in column Q (although you can easily change that in the 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]

ankit, are you still looking for help with this?

[awillard006]

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!