Macro to send an email of specific rows

Hello,

I am new to programming and looking for help in the below code.

In the below code it opens the outlook email, getting subject & body of the mail however I am looking for certain rows which has NO in either column M, N or O to be the body of the email.

It need to send an email to email defined in Column P of that row.

Sub EmailMe()
    Dim oEmail      As Object
    Dim oMailItem   As Object
    Dim sRcpt       As String
    Dim sSbjct      As String
    Dim sMsg        As String
        
    sRcpt = "abc@gmail.com"
    sSbjct = "Here is the network number " & Range("A1")
    sMsg = sSbjct
    
    Set oEmail = CreateObject("Outlook.Application")
    Set oMailItem = oEmail.CreateItem(olMailItem)
    
    With oMailItem
        .Recipients.Add sRcpt
        .Subject = sSbjct
        .Body = sMsg
        .Display
    End With
    
    Set oMailItem = Nothing
    Set oEmail = Nothing
End Sub

I hope the following code needs to be modified.

 .Body = sMsg

Thanks in advance for your help!!

Regards,
Humac

Hi Humac
If you'll post a sanitized copy of your file WITH CURRENT CODE (confidential information modified) I'll be glad to look at it.

Hi Humac
Apparently your project is password protected.

Hi Humac

If you have multiple items of a given group is the email address the same for all items within the group or can the email address be different for items within the group? Your sample file infers they're the same within the group (but then...all email addresses are "email ID").

Hi Humac

So, it would appear the answer is yes??????

If you have multiple items of a given group is the email address the same for all items within the group

Hi Humac

OK...so in your sample file the Email address is NOT in column P "Email ID". Is this the case in the "real world" or in the "real world" will the "Email ID" be in column P?

Or do we need

we have a email in the separate sheet with the emails defined in it

I'm asking this because I need to know from where to pull the Email address. If they're in column P and they're the same for the group it's rather simple. If they're not in column P but in a different worksheet...not as simple but not complicated. Just need to know where the email addresses live.

We can do this

If it can club the similar groups into single mail and send it that would be great

Hi Humac

Number of rows is not an issue...the procedure will handle that. I'm going to assume for the moment that email addresses live in a separate worksheet...if you change your mind let me know as it affects how the code is written.

I'm going to be signing off soon so will probably not finish tonight. In the meantime...what else do you want in the body of the message besides the range of cells by group? What kind of greeting? What other verbiage before and after the range of cells? Any signature line?

Hi Humac

I see the issue. We'll deal with it with the separate worksheet...not a problem. I'll work on this later today...have a pee wee football game to attend this afternoon.

Hi Humac

What version of Excel are we developing for? Your profile indicates Excel 2007. Is 2007 (or later) all we need be concerned about? No earlier versions? I'd like to hear "No...no earlier versions".

Hi Humac

Try the attached. Let me know of issues.

Hi Humac

Can't really say without knowing what changes you made to the structure of the file. You can either explain what changes you made to the structure of the file or attach a new version so I can look at it.

Are you planning further structure changes?

Hi Humac

You say

I have another report which looks like attached one

So, is this an ADDITIONAL file for which you need code or is this the ORIGINAL file with the structure substantially changed? If it's an ADDITIONAL file I'll try to indicate the code changes in the code.

If it's the ORIGINAL file then the code needs to be rewritten to the file structure of the changed file. Settle on a file structure then I'll modify the code.

So...what is it...an ADDITIONAL file or the ORIGINAL file...moving targets are hard for me to hit.

Hi Humac

Try the attached.