Macro to send an email of specific rows

[humacdeep]

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.

Please Login or Register  to view this content.

I hope the following code needs to be modified.

Please Login or Register  to view this content.

Thanks in advance for your help!!

Regards,
Humac

[jaslake]

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.

[humacdeep]

Thanks John. I have added the sample sheet with the code. Currently this macro is just opening the outlook message file and the Subject & Message body I typed in, but I need entire Rows with NO in any of the column M or N or O. In the example file I have few Columns hided and I dont want that to be in the email Body, if possible.

I have added the sample result sheet for your reference.

Thanks in advance for your help!!

Regards,
Humac

[jaslake]

Hi Humac
Apparently your project is password protected.

[humacdeep]

Sorry about that. I have added the new file. Please have a check.

[jaslake]

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").

[humacdeep]

John,

If it can club the similar groups into single mail and send it that would be great. Other wise can we have a email in the seperate sheet with the emails defined in it?

I have attached the sample file. Please have a check.

Regards,
Humac

[jaslake]

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

[humacdeep]

Yes Sir, Email address is same for all the items under similar group.

[jaslake]

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

[humacdeep]

As number of rows are not constant, I would prefer to have an email IDs defined in another Spread Sheet. If you see that its taking more time then we can go ahead with the code that pull the Email IDs from Column P.

In the real time I can modify the Spreadsheet based on the code (Either email ID from column P or from another Spread Sheet)

Regards,
Humac

[jaslake]

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?

[humacdeep]

Do I need to key in the email ID in all the cells of Column P? For example if I have 1000 rows do I need to manually enter 1000 emails IDs based on group or VBA will take care of the email ID if I enter one entry for each group?

If VBA is going to take care of all the rows even if we enter one Email Address for group in Column P or any column in the same sheet then I am ok to have the email IDs in the same sheet.

I need subject as " SLA missed tickets of (Group name from Column C) Group.

Body of the message would be " The following are the SLA missed tickets. Please review." followed by the range of cells by group

Signature can be "Regards, Humac"

[jaslake]

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.

[humacdeep]

ok thanks!! Enjoy the game

[jaslake]

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".

[humacdeep]

Its for 2007 or later and not for earlier versions.

[jaslake]

Hi Humac

Try the attached. Let me know of issues.

[humacdeep]

Thanks John!! Its working as desired.

I have few questions, please help me.

1. If I delete Column P its not working
2. If I Insert few columns in between the existing table it is throwing error message even after changing the column from M to other value in the below code, do I need to modify anywhere else?

Please Login or Register  to view this content.

Regards,
Humac

[jaslake]

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?

[humacdeep]

Hello John,

I have another report which looks like attahced one. I have tried to change Q to Z & M to S (after inserting some columns M moved to S) in the below code.

Please Login or Register  to view this content.

In the below code Q represents 17, so I have replaced it to 26 as I mentioned Z in place of Q.

Please Login or Register  to view this content.

Thanks in advance for your help!!

Regards,
Humac

[jaslake]

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.

[humacdeep]

Hi John,

Its an additioanl file, I need the code on both the files. The only difference between two files is no of columns, in the new attachment I have additional six column than the first one.

Regards,
Humac

[jaslake]

Hi Humac

Try the attached.

[humacdeep]

Hi John,

I have tested and it working as desired, thanks for all your help & time on this request.

I appriciate your help in explaing the changes of the code.

Regards,
Humac