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

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1ToCCBCCSubjectBody 1st partBody 2nd PartAttachment 1Attachment 2
2test1@Gmail.Comtest2@Philips.ComMail Body testHi There, Please find sales details in below table.Kindly arrange payment accordingly. Thanks & Rregards, Sales TeamC:\Users\Desktop\Email\2552890.xlsx
3test3@Gmail.Comtest4@Philips.ComMail Body testHi There, Please find sales details in below table.Kindly arrange payment accordingly. Thanks & Rregards, Sales TeamC:\Users\ing13447\OneDrive - Philips\Desktop\Email\3002852.xlsx
Sheet1


Sheet2
Email with Body.xlsx
ABCDEFGHIJK
1VendorVendor CodeSr. No.DateItem CodeProduct NameQty.UnitRate/UnitAmountRemarks
2test1@Gmail.Com2552890101-01-201a100Ltr.10010000
3test3@Gmail.Com3002852101-01-202b100Packet10510500
4test3@Gmail.Com3002852202-01-203c100Box11011000
5test1@Gmail.Com2552890202-01-204d100Ltr.11511500
6test3@Gmail.Com3002852302-01-201a100Ltr.10010000
7test1@Gmail.Com2552890303-01-202b100Packet10510500
8test1@Gmail.Com2552890403-01-203c100Box11011000
9test3@Gmail.Com3002852403-01-204d100Ltr.11511500
10test3@Gmail.Com3002852503-01-201a100Ltr.10010000
Sheet2


Email (Sample) looks like this.
Outlook.jpg
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top