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

 

[Ben AFF]

What do you mean?

In ROWs A and B, the values are pasted after 8 rows.

 

[Akuini]

In ROWs A and B, the values are pasted after 8 rows.

The table in sheet Log, is it an actual table (not just a range)?

 

[Ben AFF]

The table in sheet Log, is it an actual table (not just a range)?

No, its just a range. From A2.

 

[Akuini]

No, its just a range. From A2.

I don't see why the code did that.
Do you have formula in col A that returns blank?

 

[Ben AFF]

I don't see why the code did that.
Do you have formula in col A that returns blank?

Hi Akuini, no , its just blank.

 

[Akuini]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[Ben AFF]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

Hi Akuini, sorry I cant

 

[Akuini]

Hi Akuini, sorry I cant

Hm.., try replacing this line:
n = Sheets("Log").Range("A" & Rows.Count).End(xlUp).Row 'last row with data in Sheets("Log") col A
with this:
n = Sheets("Log").Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[Ben AFF]

Hi Akuini, thanks, it does the same, the values are shown after 8 blank rows.

Hm.., try replacing this line:
n = Sheets("Log").Range("A" & Rows.Count).End(xlUp).Row 'last row with data in Sheets("Log") col A
with this:
n = Sheets("Log").Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[Akuini]

Sorry, without having the file I can't investigate the problem.