Copy excel range to outlook mail body based on filtered criteria

vaibhav106

New Member
Joined
Jan 28, 2009
Messages
40
Hi Experts,
I am on project to generate auto-mails based on filtered email id column.

in Excel, I am filtering 1 email id in column M, copy filtered data from column B to J & paste it in outlook body. Copy that filtered email ID in To field of outlook. Send the email.

Again I filter for next email id in column M, copy filtered data from column B to J & paste it in outlook body. Copy that filtered email ID in To field of outlook. Send the email.

Now I have 500+ email Ids to whom I want to send mail. Above process is very time consuming.

Considering the capabilities of macros to handle repetitive work very easily, can we have some simple VBA code to generate auto-mails base on filtered criteria?

sample table given below.....
[TABLE="width: 1641"]
<tbody>[TR]
[TD]SR NO[/TD]
[TD]Reaceived Date[/TD]
[TD]POD Number[/TD]
[TD]Location[/TD]
[TD]File Barcode[/TD]
[TD]Stockist Name[/TD]
[TD]Chemist Name / Patient name[/TD]
[TD]Invoice Number[/TD]
[TD]Invoice Date[/TD]
[TD]Remark[/TD]
[TD]DEO[/TD]
[TD]mail sent on[/TD]
[TD]stockist mail id[/TD]
[TD]depo mail id[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]08-Mar-18[/TD]
[TD]161820083172[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063360[/TD]
[TD]ALLIED TRADE CORPORATION[/TD]
[TD]AMRUT PHARMACY-AKOTA[/TD]
[TD]DB-T/245603[/TD]
[TD="align: right"]2-Feb-18[/TD]
[TD] [/TD]
[TD]SHRINATH[/TD]
[TD] [/TD]
[TD]allied.firoz@gmail.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]08-Mar-18[/TD]
[TD]161820083172[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063361[/TD]
[TD]ALLIED TRADE CORPORATION[/TD]
[TD]AMRUT PHARMACY-AKOTA[/TD]
[TD]DB-T/272912[/TD]
[TD="align: right"]24-Feb-18[/TD]
[TD] [/TD]
[TD]SHRINATH[/TD]
[TD] [/TD]
[TD]allied.firoz@gmail.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]08-Mar-18[/TD]
[TD]161820083172[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063362[/TD]
[TD]ALLIED TRADE CORPORATION[/TD]
[TD]SANJIV PHARMACY-AKOTA[/TD]
[TD]DB-T/264736[/TD]
[TD="align: right"]17-Feb-18[/TD]
[TD] [/TD]
[TD]SHRINATH[/TD]
[TD] [/TD]
[TD]allied.firoz@gmail.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]08-Mar-18[/TD]
[TD]161820083172[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063363[/TD]
[TD]ALLIED TRADE CORPORATION[/TD]
[TD]SANJIV PHARMACY-AKOTA[/TD]
[TD]DB-T/271777[/TD]
[TD="align: right"]23-Feb-18[/TD]
[TD] [/TD]
[TD]SHRINATH[/TD]
[TD] [/TD]
[TD]allied.firoz@gmail.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]162[/TD]
[TD="align: right"]8-Mar-18[/TD]
[TD]1043308645[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063511[/TD]
[TD]AHMEDABAD MEDICAL CORPORATION[/TD]
[TD]RAJ MEDICAL AND PRO STORES[/TD]
[TD]T/11068[/TD]
[TD="align: right"]1-Mar-2018[/TD]
[TD] [/TD]
[TD]PRAVIN[/TD]
[TD]15-03-2018[/TD]
[TD]amcmanil@yahoo.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]163[/TD]
[TD="align: right"]8-Mar-18[/TD]
[TD]1043308645[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063512[/TD]
[TD]AHMEDABAD MEDICAL CORPORATION[/TD]
[TD]RAJ MEDICAL AND PRO STORES[/TD]
[TD]T/11042[/TD]
[TD="align: right"]1-Mar-2018[/TD]
[TD] [/TD]
[TD]PRAVIN[/TD]
[TD]15-03-2018[/TD]
[TD]amcmanil@yahoo.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]164[/TD]
[TD="align: right"]8-Mar-18[/TD]
[TD]1043308645[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063513[/TD]
[TD]AHMEDABAD MEDICAL CORPORATION[/TD]
[TD]RAJ MEDICAL AND PRO STORES[/TD]
[TD]T/11067[/TD]
[TD="align: right"]1-Mar-2018[/TD]
[TD] [/TD]
[TD]PRAVIN[/TD]
[TD]15-03-2018[/TD]
[TD]amcmanil@yahoo.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

Thanks in advance.

Vaibhav
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi Experts,
I am on project to generate auto-mails based on filtered email id column.

in Excel, I am filtering 1 email id in column M, copy filtered data from column B to J & paste it in outlook body. Copy that filtered email ID in To field of outlook. Send the email.

Again I filter for next email id in column M, copy filtered data from column B to J & paste it in outlook body. Copy that filtered email ID in To field of outlook. Send the email.

Now I have 500+ email Ids to whom I want to send mail. Above process is very time consuming.

Considering the capabilities of macros to handle repetitive work very easily, can we have some simple VBA code to generate auto-mails base on filtered criteria?

sample table given below.....
[TABLE="width: 1641"]
<tbody>[TR]
[TD]SR NO[/TD]
[TD]Reaceived Date[/TD]
[TD]POD Number[/TD]
[TD]Location[/TD]
[TD]File Barcode[/TD]
[TD]Stockist Name[/TD]
[TD]Chemist Name / Patient name[/TD]
[TD]Invoice Number[/TD]
[TD]Invoice Date[/TD]
[TD]Remark[/TD]
[TD]DEO[/TD]
[TD]mail sent on[/TD]
[TD]stockist mail id[/TD]
[TD]depo mail id[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]08-Mar-18[/TD]
[TD]161820083172[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063360[/TD]
[TD]ALLIED TRADE CORPORATION[/TD]
[TD]AMRUT PHARMACY-AKOTA[/TD]
[TD]DB-T/245603[/TD]
[TD="align: right"]2-Feb-18[/TD]
[TD] [/TD]
[TD]SHRINATH[/TD]
[TD] [/TD]
[TD]allied.firoz@gmail.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]08-Mar-18[/TD]
[TD]161820083172[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063361[/TD]
[TD]ALLIED TRADE CORPORATION[/TD]
[TD]AMRUT PHARMACY-AKOTA[/TD]
[TD]DB-T/272912[/TD]
[TD="align: right"]24-Feb-18[/TD]
[TD] [/TD]
[TD]SHRINATH[/TD]
[TD] [/TD]
[TD]allied.firoz@gmail.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]08-Mar-18[/TD]
[TD]161820083172[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063362[/TD]
[TD]ALLIED TRADE CORPORATION[/TD]
[TD]SANJIV PHARMACY-AKOTA[/TD]
[TD]DB-T/264736[/TD]
[TD="align: right"]17-Feb-18[/TD]
[TD] [/TD]
[TD]SHRINATH[/TD]
[TD] [/TD]
[TD]allied.firoz@gmail.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]08-Mar-18[/TD]
[TD]161820083172[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063363[/TD]
[TD]ALLIED TRADE CORPORATION[/TD]
[TD]SANJIV PHARMACY-AKOTA[/TD]
[TD]DB-T/271777[/TD]
[TD="align: right"]23-Feb-18[/TD]
[TD] [/TD]
[TD]SHRINATH[/TD]
[TD] [/TD]
[TD]allied.firoz@gmail.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]162[/TD]
[TD="align: right"]8-Mar-18[/TD]
[TD]1043308645[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063511[/TD]
[TD]AHMEDABAD MEDICAL CORPORATION[/TD]
[TD]RAJ MEDICAL AND PRO STORES[/TD]
[TD]T/11068[/TD]
[TD="align: right"]1-Mar-2018[/TD]
[TD] [/TD]
[TD]PRAVIN[/TD]
[TD]15-03-2018[/TD]
[TD]amcmanil@yahoo.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]163[/TD]
[TD="align: right"]8-Mar-18[/TD]
[TD]1043308645[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063512[/TD]
[TD]AHMEDABAD MEDICAL CORPORATION[/TD]
[TD]RAJ MEDICAL AND PRO STORES[/TD]
[TD]T/11042[/TD]
[TD="align: right"]1-Mar-2018[/TD]
[TD] [/TD]
[TD]PRAVIN[/TD]
[TD]15-03-2018[/TD]
[TD]amcmanil@yahoo.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
[TR]
[TD="align: right"]164[/TD]
[TD="align: right"]8-Mar-18[/TD]
[TD]1043308645[/TD]
[TD]AHMEDABAD[/TD]
[TD]MUMCL063513[/TD]
[TD]AHMEDABAD MEDICAL CORPORATION[/TD]
[TD]RAJ MEDICAL AND PRO STORES[/TD]
[TD]T/11067[/TD]
[TD="align: right"]1-Mar-2018[/TD]
[TD] [/TD]
[TD]PRAVIN[/TD]
[TD]15-03-2018[/TD]
[TD]amcmanil@yahoo.com[/TD]
[TD]ahm@cipla.com[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

Thanks in advance.

Vaibhav

hi experts, can anyone help me for this query.....need help plsss.
 
Upvote 0

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

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