send all data for selected code by auto mail

[kv.singh]

I have a excel file having two sheets 1)Name 2)DATA
I want vba code for sending auto mails one after another to mail IDs in col. C of "NAME" sheet having "yes" remark in col. D
Code should fetch all corresponding rows from DATA sheet for Code(col.B of sheet "Name") and convert to html table
format in mail and send mail to corresponding mail address against code shown in "Name" sheet. body of mail and sample
mail result is shown in sheet "result"
I am using Microsoft outlook 2016 for sending and receiving mail.


I will be very thankful if anyone can provide code.

[maniacb]

So you are trying to send one email to at least two people? in the given scenario?

[kv.singh]

No. I want to send mail to single person only. work of code
1. select first "code" from Name sheet.If col.D has "yes" remark then search all rows in sheet "DATA" for selected code.
2. Convert rows searched rows for first "code"in html table format with heading shown in sheet "RESULT"
3. send auto mail to "code" mail Id shown in "NAME" sheet.
4. body to mail is shown in "result" sheet.


Then again continues above process for second "code" (i.e. B2,B3,B4.... of "NAME"sheet.
Sheet "RESULT" is for your reference(result I expect) in Mail.

[maniacb]

Here is a pass at creating your requirements. I added one column E on the NAME tab to track what was sent. A 1 denotes that the email has been sent. Let me know if you have any questions.

Please Login or Register  to view this content.

[FDibbins]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

[kv.singh]

cross post link : https://chandoo.org/forum/threads/au...ba-code.44646/

[kv.singh]

@Manicab

Out Mail is as under:-
Respected YYYY Please find hereunder your lapse list.

code number name fup prem doc
123456722 2 x 22020 987 20200808
123456722 14 f 32017 602 20200985
123456722 23 g 32014 305 20201861
123456722 32 u 32011 8 20202737
123456722 41 w 32008 -289 20203613
123456722 50 i 32005 -586 20204489

yours faithfully, kvv
=============================================================================
I want "Please find hereunder your lapse list." in next new line (i.e. below "Respected yyyy")
I want "kvv" in next new line (i.e. below "yours faithfully")
I want table data bordered in mail with BOLD fonts .
Refer attachment (sample-14)for expected output.

[maniacb]

To update the formatting, change the format of the sheet that holds the data. As for the breaks, I am still working on that

[maniacb]

Here is an update with the formatted cells and HTMLbody working

Please Login or Register  to view this content.

[kv.singh]

I have downloaded your final xls file and try to run code but nothing is happening when I click "SendEmail" button.
kindly check at your end.

[kv.singh]

sorry, I forgot to delete '1' in "sent" col. Code is giving desired results.
Thanks for your valuable efforts.
One more favor from your side. Can we attach created sheet to respective mail ID before deleting.(send table as attachment)

[maniacb]

Here is the update you've requested. It took me some time to make the changes. Now an attachment is added to the email.

Please Login or Register  to view this content.

[kv.singh]

Excellent !! Thanks for your efforts. Your code is giving desired results. Your are great.

[maniacb]

If that takes care of your original question, please select*Thread Tools*from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.