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

 

[smozgur]

Thank you so much Akuini, it works perfect.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers.
No further action is required for this thread.

 

[Akuini]

Thank you so much Akuini, it works perfect. I dont know how to thank you, wish I could by you a coffee or beer

You're welcome, glad to help & thanks for the feedback.

 

[Ben AFF]

Hi Akuini

Hope you are doing well.

Im needing to do a change where the user does not need to select rows in column E:F to send the mails.
The idea is to create and additional button to action the macro for bulk send without doing any selection, so the selection is automatically done for all ROWs for range E:F containing data if the value in column N for each row is blank.

Can you help me please modify the code?

Thank you.

PS: I modified a bit the order of the columns in the previous code you help me with.

Column E	Column F	Column N
PO ABCDE	Item 001	Sent
PO ABCDF	Item 001	(blank) -> send mail
PO ABCDG	Item 002	(blank) -> send mail

[TABLE] [TR] [TD]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 Dim d As Object, f As Object Dim tx As String Dim x, ary, g If Not Intersect(Selection, Range("E:F")) Is Nothing And Intersect(Selection, Range("G:XFD")) Is Nothing Then n = Range("E" & Rows.Count).End(xlUp).Row 'get last row with data in col E If Not Intersect(Selection, Range("E2:E" & n)) Is Nothing Then Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare Set f = CreateObject("scripting.dictionary"): f.CompareMode = vbTextCompare For Each r In Intersect(Selection, Range("E2:E" & n)) f(r.Value) = Empty Next For Each r In Range("E2:E" & n) tx = r.Value If f.Exists(tx) Then If Not d.Exists(tx) Then d(tx) = r.Row Else d(tx) = d(tx) & " " & r.Row End If End If Next For Each x In d ary = Split(d.Item(x), " ") SendToMail = Range("M" & ary(0)) MailSubject = Range("K" & ary(0)) tx = "" For Each g In ary tx = tx & vbLf & Range("L" & g) Next mMailBody = Mid(tx, 2) ' Debug.Print mMailBody Set mApp = CreateObject("Outlook.Application") Set mMail = mApp.CreateItem(0) With mMail .to = SendToMail .Subject = MailSubject .Body = "" .Display Selection.Copy ThisWorkbook.Worksheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues End With Next MsgBox "@mails are sent" Else MsgBox "Please, select cells with data in column E&F only." End If Else MsgBox "Please, select cells with data in column E&F only." End If End Sub[/TD] [/TR] [/TABLE]

 

[Akuini]

The idea is to create and additional button to action the macro for bulk send without doing any selection, so the selection is automatically done for all ROWs for range E:F containing data if the value in column N for each row is blank.

What should happen if there are duplicate in col E but in col N one is "Sent" & the other is blank?

Column E	Column F	Column N
PO ABCDE	Item 001	Sent
PO ABCDE	Item 001	(blank) -> send mail
PO ABCDG	Item 002	(blank) -> send mail

 

[Ben AFF]

What should happen if there are duplicate in col E but in col N one is "Sent" & the other is blank?

Column E	Column F	Column N
PO ABCDE	Item 001	Sent
PO ABCDE	Item 001	(blank) -> send mail
PO ABCDG	Item 002	(blank) -> send mail

Hi Akuin, thanks. As is now when item in column F repeats a single mail is consolidated based on the value of column E. In case this happens only 1 mail should be sent.

 

[Akuini]

As is now when item in column F repeats a single mail is consolidated based on the value of column E.

I don't understand, what do this have to do with col F? In post #24, I've change your example, so that E2 = E3.

In case this happens only 1 mail should be sent.

What should mMailBody consist of? just L2 or both L2 & L3?

Can you provide new example showing more variations & explain what should happen on each variations?

 

[Ben AFF]

I don't understand, what do this have to do with col F? In post #24, I've change your example, so that E2 = E3.

What should mMailBody consist of? just L2 or both L2 & L3?

Can you provide new example showing more variations & explain what should happen on each variations?

Hi Akuini

Sure.

Column E is the Purchase Order Number
Column F is the Purchase Order Item
A purchase order can have various items.
An scenario like in the example in your table where rows 2 & 3 for columns E & F are the same, its not posible, because it would be a duplicate.
The as-is code (working well) consolidates in a single mail the items in column F when the same value occurs in column E (rows 4&5 in the Table below)
For example.

Purchase Order Number (E)	Purchase Order Item (F)	Status (N)	Action
ABCDE	001	SENT	-> Do not send mail
ABCDE	001		(duplicate with Row 2 - not a posible scenario)
ABCDF	001	(blank)	Send a single mail with items 1 & 2
ABCDF	002	(blank)	(see above)
ABCDG	001	(blank)	Send a single mail with item 1
etc...

 

[Akuini]

Is this scenario possible?

Purchase Order Number (E)	Purchase Order Item (F)	Status (N)	Action
ABCDE	1​	SENT	-> Do not send mail
ABCDE	2​	blank

 

[Ben AFF]

Is this scenario possible?

Purchase Order Number (E)	Purchase Order Item (F)	Status (N)	Action
ABCDE	1​	SENT	-> Do not send mail
ABCDE	2​	blank

No. If the status in item 1 is sent, the status in item 2 would be also sent.

 

[Akuini]

What about col K:M, are they still in the same order?
The "mMailBody" still get values from col L, right?