Microsoft Excel - one macro file updation error

Asked By anu anu on 27-Feb-14 11:24 PM

hi

i have one data file with one macro. now from this macro auto mail send to user when contract going to expire.
now i want if user is same and his more then one aacounts going to expire in this case mail should be like

Dear

The Below contract is due for notice of renewal/termination in 30 days.

Contract Number	Supplier Name	Description	Type	Contact Start Date	Contact End Date	Notice Period
1			Service	01/11/2025	31/03/2026	90
4			Service	01/10/2025	30/09/2025	120
5			Service	01/03/2026	28/02/2026	30
35			Service	01/01/2026	31/12/2025	90
41			Service	06/03/2026	05/04/2026	30
48			Service	14/01/2026	13/01/2026	0
53			Service	01/05/2026	01/05/2026	90
64			Service	25/02/2026	24/02/2026	90
76			License	01/03/2026	28/02/2026	90
89			Service	31/03/2026	30/03/2026	90
93			Service	04/03/2026	03/03/2026	30
96			Service	11/09/2025	10/09/2025	180
99			Service	01/10/2025	31/10/2025	22
103			Service	11/04/2026	30/04/2026	90
123			Service	15/04/2026	14/04/2026	90
136			Service	01/04/2026	31/03/2026	15
138			Maintenance	01/04/2026	31/03/2026	90
150			Service	24/04/2026	23/04/2026	90
156			Subscription	07/05/2026	06/05/2026	180
167			Subscription	12/02/2026	01/01/2026	14
176			Service	01/05/2026	31/10/2025	90
180			Service	01/03/2026	28/02/2026	90
182			Maintenance	01/02/2026	31/01/2026	180
184			Service	28/05/2026	27/05/2026	60
198			Service	13/07/2026	31/10/2025	90
232			Service	26/11/2025	01/11/2025	30
243			Service	24/01/2026	23/01/2026	30
251			Service	04/03/2026	03/03/2026	30
252			Goods	01/04/2026	31/03/2026	90
100d			Service	09/05/2026	08/05/2026	60
181a			Service	20/03/2026	19/03/2026	90
181b			Service	27/03/2026	26/03/2026	45
9a			Service	22/04/2026	21/04/2026	30
9b			Service	01/03/2026	28/02/2026	30

Please take the necessary action within 30 calendar days to either renew or terminate this contract.

Please note it is your responsibility to get a new contract ( if required) signed by the Financial Controller and the General Manager.

No further payments will be processed to the supplier after the contract expiry date unless the relevant paperwork is received.

Harry Boughen replied to anu anu on 01-Mar-14 04:55 PM

Hello anu,
Maybe you can use a pivot table and filters to create your content that you can then send.
Perhaps these links will help you along the way.

http://www.rondebruin.nl/win/s1/outlook/mail.htm

http://social.technet.microsoft.com/Forums/en-US/5338e403-f0e2-4b7a-927b-f0fe333650a5/mail-extacted-data-from-pivot-table?forum=excel

Regards
Harry

anu anu replied to Harry Boughen on 02-Mar-14 11:09 PM

hi harry ,

thanks for yor response .
can you edit on acttached file accourding to my req.Demo_Contract_Master_Schedule(1).zip,

thanks
Anu

Harry Boughen replied to anu anu on 03-Mar-14 12:23 AM

Hello Anu,
In a word, no.
Partly because I do not have any idea what your requirements actually are particularly with respect to the content of the e-mail that you want to send. Partly because the file that you uploaded contains external links and some other fault that might or might not affect its usability.
Did you follow up on the links that I sent to you?
Regards
Harry

anu anu replied to Harry Boughen on 03-Mar-14 12:40 AM

hi harry ,
At present if one user`s 3 aoounts has been expire 3 spreate mail to send same user . now i want is there any option in mail body 3 account s details paste and on email send to perticuller user .

thanks
anu

Harry Boughen replied to anu anu on 03-Mar-14 01:56 AM

Hello Anu,
I think I understand that. What I don't understand is which contracts belong to which user and which ones of that list would be the subject of your e-mail.
Harry

anu anu replied to Harry Boughen on 03-Mar-14 04:42 AM

hi harry ,

user in column N(Responsible Executive).now if Geeta two accounts going to expire mail should be like .

Dear geeta

The Below contract is due for notice of renewal/termination in 30 days.

Contract Number	Supplier Name	Description	Type	Contact Start Date	Contact End Date
1	VBCVB		Service	01/11/2025	31/03/2026
3	SDS		Inspection	01/04/2026	31/03/2026

Please take the necessary action within 30 calendar days to either renew or terminate this contract.

Please note it is your responsibility to get a new contract ( if required) signed by the Financial Controller and the General Manager.

No further payments will be processed to the supplier after the contract expiry date unless the relevant paperwork is received.

Demo1_Contract_Master_Schedule(1).zip

Harry Boughen replied to anu anu on 04-Mar-14 03:52 AM

Hello Anu
I don't know if this will work as I don't have Outlook to test it. There is a macro named prepare_data that 'should' insert a copy of the relevant data into an e-mail but no guarantees. I know that it works to copy the relevant data to another worksheet. The code to do that has been left in but commented out and noted.

anu_Demo_2.zip

Hope this gets you some way to what you want.
Regards
Harry