Limit user selected range

[Ben AFF]

Hi, I have a Macro that allows the user to send email based on selection of rows on a spreadsheet.
I want to limit the range within which the user selection is valid to columns A:B only.
Please can you help me? Thank you.

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
For Each r In Selection
SendToMail = Range("M" & r.Row)
MailSubject = Range("K" & r.Row)
mMailBody = Range("L" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.Subject = MailSubject
.Body = mMailBody
.Display
End With
Next r
End Sub

 

[eiloken]

you can try this

Sub ExcelToOutlookSR()
    Dim r As Range
    Dim mApp As Object
    Dim mMail As Object
    Dim SendToMail As String
    Dim MailSubject As String
    Dim mMailBody As String
    For Each r In Selection
        If r.Column = 1 Or r.Column = 2 Then  'limit selection range
            SendToMail = Range("M" & r.Row)
            MailSubject = Range("K" & r.Row)
            mMailBody = Range("L" & r.Row)
            Set mApp = CreateObject("Outlook.Application")
            Set mMail = mApp.CreateItem(0)
            With mMail
                .To = SendToMail
                .Subject = MailSubject
                .Body = mMailBody
                .Display
            End With
        Else
            MsgBox "invalid selection address."
        End If
    Next r
End Sub

 

[Akuini]

I want to limit the range within which the user selection is valid to columns A:B only.

What should happen, for example if the user select cells A2:C3?

 

[Ben AFF]

What should happen, for example if the user select cells A2:C3?

Thank you Akuini, in that case the mails are cannot be sent. Only when the user selects cells in range A:B.

 

[Akuini]

When you allow users to select the range, it could end up messy. For example, a user could select the entire column A and then run the code, which would cause the code to loop through all the cells in the column. So, I've modified the code to process only within the data range. In this part:

    n = Range("A" & Rows.Count).End(xlUp).Row 'get last row with data in col A
    For Each r In Selection.Columns(1).Cells
        If r.Row > n Then Exit For  'if the selection include rows below the last row with data in col A then exit

In your original code, if user selects col A:B at once then each selected row will be processed twice which I don't think that's what you want. So, I modified the code to process only within 1 column, in this part:
For Each r In Selection.Columns(1).Cells
Maybe this:

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
Dim r  As Range, n As Long

If Not Intersect(Selection, Range("A:B")) Is Nothing And Intersect(Selection, Range("C:XFD")) Is Nothing Then
    n = Range("A" & Rows.Count).End(xlUp).Row 'get last row with data in col A
    For Each r In Selection.Columns(1).Cells
        If r.Row > n Then Exit For  'if the selection include rows below the last row with data in col A then exit
        SendToMail = Range("M" & r.Row)
        MailSubject = Range("K" & r.Row)
        mMailBody = Range("L" & r.Row)
        Set mApp = CreateObject("Outlook.Application")
        Set mMail = mApp.CreateItem(0)
        With mMail
            .To = SendToMail
            .Subject = MailSubject
            .Body = mMailBody
            .Display
        End With
    Next r
Else
    MsgBox "Please, select cells in column A or B"
End If
End Sub

 

[Ben AFF]

When you allow users to select the range, it could end up messy. For example, a user could select the entire column A and then run the code, which would cause the code to loop through all the cells in the column. So, I've modified the code to process only within the data range. In this part:

    n = Range("A" & Rows.Count).End(xlUp).Row 'get last row with data in col A
    For Each r In Selection.Columns(1).Cells
        If r.Row > n Then Exit For  'if the selection include rows below the last row with data in col A then exit

In your original code, if user selects col A:B at once then each selected row will be processed twice which I don't think that's what you want. So, I modified the code to process only within 1 column, in this part:
For Each r In Selection.Columns(1).Cells
Maybe this:

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
Dim r  As Range, n As Long

If Not Intersect(Selection, Range("A:B")) Is Nothing And Intersect(Selection, Range("C:XFD")) Is Nothing Then
    n = Range("A" & Rows.Count).End(xlUp).Row 'get last row with data in col A
    For Each r In Selection.Columns(1).Cells
        If r.Row > n Then Exit For  'if the selection include rows below the last row with data in col A then exit
        SendToMail = Range("M" & r.Row)
        MailSubject = Range("K" & r.Row)
        mMailBody = Range("L" & r.Row)
        Set mApp = CreateObject("Outlook.Application")
        Set mMail = mApp.CreateItem(0)
        With mMail
            .To = SendToMail
            .Subject = MailSubject
            .Body = mMailBody
            .Display
        End With
    Next r
Else
    MsgBox "Please, select cells in column A or B"
End If
End Sub

Thank you so much Akuini, this is perfect the only problem is that when I action the macro, nothing happens? (the mails are not displayed)

 

[Akuini]

Thank you so much Akuini, this is perfect the only problem is that when I action the macro, nothing happens? (the mails are not displayed)

When you used your original code, did it work?

 

[Ben AFF]

When you used your original code, did it work?

Sorry Akuini it works now, not sure when I tried first it did not . Please one more help if you are so kind, I want to consolidate in a single mail all the rows when the value repeat in column A, is this possible you help me please? Thank you.

 

[Akuini]

Do you mean you have duplicate values in col A?
And when the duplicate values are selected then you want to sent only 1 mail?
So, the mMailBody will consist of values from multiple rows in col L? in this part mMailBody = Range("L" & r.Row)

 

[Ben AFF]

Do you mean you have duplicate values in col A?
And when the duplicate values are selected then you want to sent only 1 mail?
So, the mMailBody will consist of values from multiple rows in col L? in this part mMailBody = Range("L" & r.Row)

Yes, precisely.