VBA to copy/Paste dynamic table range in outlook email body

[sanket_sk]

Hi There,

I am looking for a solution (VBA) that will help send multiple emails with specific attachments and copy the filtered range ( Based on vendor email id) from Excel and paste it into the body of the email.

I have got multiple codes for my first requirement (I mean sending multiple mails with attachments) however I did not get any solution for copy / pasting filtered range based on vendor.

Sharing use case here…

Sheet1 has Emailid, subject, Body Part1 & Body part 2, attachment details etc.

Sheet2 has Emailid, vendor & sales details.

I look for excel to do following

1. Create new email ( in Outlook).
2. Put Email id (To,CC,BCC) as per details available
3. Attach file as per details available
4. Put Body part 1
5. look for Email id in Sheet1 “A” Column, Filter range in Sheet2.
6. Copy details from column “C:K” to end of the data ( Dynamic filtered range). with proper width setting.
7. Put Body part 2 and send the email.

Refer below sample data..

Sheet1

Email with Body.xlsx
	A	B	C	D	E	F	G	H
1	To	CC	BCC	Subject	Body 1st part	Body 2nd Part	Attachment 1	Attachment 2
2	test1@Gmail.Com	test2@Philips.Com		Mail Body test	Hi There, Please find sales details in below table.	Kindly arrange payment accordingly. Thanks & Rregards, Sales Team	C:\Users\Desktop\Email\2552890.xlsx
3	test3@Gmail.Com	test4@Philips.Com		Mail Body test	Hi There, Please find sales details in below table.	Kindly arrange payment accordingly. Thanks & Rregards, Sales Team	C:\Users\ing13447\OneDrive - Philips\Desktop\Email\3002852.xlsx
Sheet1

Sheet2

Email with Body.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Vendor	Vendor Code	Sr. No.	Date	Item Code	Product Name	Qty.	Unit	Rate/Unit	Amount	Remarks
2	test1@Gmail.Com	2552890	1	01-01-20	1	a	100	Ltr.	100	10000
3	test3@Gmail.Com	3002852	1	01-01-20	2	b	100	Packet	105	10500
4	test3@Gmail.Com	3002852	2	02-01-20	3	c	100	Box	110	11000
5	test1@Gmail.Com	2552890	2	02-01-20	4	d	100	Ltr.	115	11500
6	test3@Gmail.Com	3002852	3	02-01-20	1	a	100	Ltr.	100	10000
7	test1@Gmail.Com	2552890	3	03-01-20	2	b	100	Packet	105	10500
8	test1@Gmail.Com	2552890	4	03-01-20	3	c	100	Box	110	11000
9	test3@Gmail.Com	3002852	4	03-01-20	4	d	100	Ltr.	115	11500
10	test3@Gmail.Com	3002852	5	03-01-20	1	a	100	Ltr.	100	10000
Sheet2

Email (Sample) looks like this.