VBA program Copy Paste Email

needtoknownow · May 22, 2013

I have made a example of something similar to what i want to accomplish below. Basically I would like to have the excel file filter itself one employee at a time by employee #. Then copy and paste the filtered data to a seperate excel file and name it after the employee with the current date. I then want the file to be emailed to them as an attachment. I have 2 tabs one with the info; and the other with the emploee name, num, and email. Thank you for any assistance possible. i have been working on this and reding a book call excel 2010 power programming with VBA. But i am coming along slowly.LOL

Sheet2

Name	Emp#	Email
Jimmy Jimmy	1	jimmy@work555.com
Tommy Tommy	2	Tommy@work555.cocm
Richard Richard	3	Richard@work555.com

Sheet1

Department VP	Sales Rep	emp ID	Item	Description	trademark	week 1	week 2	week3	week4	week5	week6	week7	week8	week9	week10	week11	week12	week13	week14	week15	week16	week17	week18	week19	week20	week21	week22	week23	week24	week25	week26	week27	week28	week29	week30	week31	week32	week33	week34	week35	week36	week37	week38	week39	week40	week41	week42	week43	week44	week45	week46	week47	week48	week49	week50	week51	week52
boss of the bosses	Jimmy Jimmy	1	1	Cake 1	1	188.07	182.53	157.28	151.25	147.65	166.54	173.61	156.25	134.58	172.10	162.50	177.25	164.64	143.45	190.51	214.42	227.12	197.75	260.63	225.63	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13	264.44	298.28	320.68	369.54	0.00	0.00	0.00	0.00	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13
boss of the bosses	Jimmy Jimmy	1	2	Cake 2	2	120.55	117.24	111.07	97.64	99.40	101.04	87.52	100.99	92.83	87.10	97.42	105.57	93.34	116.59	86.55	101.76	100.17	114.08	123.70	112.29	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14	153.76	172.25	162.02	194.50	0.00	0.00	0.00	0.00	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14
boss of the bosses	Jimmy Jimmy	1	3	Cake 3	3	111.85	117.44	86.70	85.29	79.58	84.37	91.58	85.72	86.00	92.56	94.01	92.93	98.69	111.36	122.88	94.10	102.87	104.89	107.51	114.32	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88	137.90	153.72	164.93	195.89	183.50	180.91	0.00	0.00	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88
boss of the bosses	Jimmy Jimmy	1	4	Cake 4	1	124.91	154.46	119.91	129.49	126.12	127.60	122.05	120.68	128.07	141.96	148.11	129.99	143.41	139.74	180.97	165.63	176.38	180.46	171.34	173.62	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49	220.23	263.90	248.06	237.42	249.12	249.59	250.18	229.14	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49
boss of the bosses	Jimmy Jimmy	1	5	Cake 5	2	195.37	163.94	155.07	143.28	124.44	139.87	147.82	146.60	136.42	169.66	135.69	130.71	143.98	164.99	148.65	145.37	143.88	146.27	152.46	150.19	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42	217.01	235.37	0.00	0.00	0.00	0.00	0.00	0.00	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42
boss of the bosses	Jimmy Jimmy	1	6	Cake 6	3	173.57	124.69	127.02	129.02	112.01	139.45	108.62	118.23	100.10	124.96	111.05	114.42	120.49	141.41	145.05	148.68	136.41	136.69	153.94	134.24	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59	207.96	223.58	253.49	264.22	0.00	0.00	0.00	0.00	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59
boss of the bosses	Jimmy Jimmy	1	7	Cake 7	1	145.30	147.20	126.16	135.81	157.21	125.22	127.20	123.82	136.33	144.35	133.63	139.93	142.83	139.01	135.20	153.66	180.30	173.76	182.61	175.63	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69	199.61	231.04	254.15	272.94	0.00	0.00	0.00	0.00	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69
boss of the bosses	Jimmy Jimmy	1	8	Cake 8	2	186.81	160.43	135.53	107.28	152.21	157.54	172.97	170.55	173.72	182.71	159.44	194.50	209.65	195.58	215.75	193.25	226.19	237.22	265.28	220.73	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00	276.37	318.42	352.67	404.62	391.16	347.91	321.96	275.35	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00
boss of the bosses	Jimmy Jimmy	1	9	Cake 9	3	166.03	167.16	158.91	139.48	148.41	135.85	133.78	131.17	141.70	150.61	148.54	133.15	153.29	152.98	159.35	189.63	194.92	167.90	202.29	205.40	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77	250.05	269.15	318.29	335.82	315.73	309.74	0.00	0.00	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77
boss of the bosses	Jimmy Jimmy	1	10	Cake 10	1	139.28	145.22	101.39	105.43	92.60	101.33	100.80	99.29	100.51	117.01	123.04	109.04	132.16	126.19	137.76	137.94	144.65	145.77	147.05	137.92	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04	193.69	180.20	199.51	228.13	227.03	206.08	0.00	0.00	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04
boss of the bosses	Jimmy Jimmy	1	11	Cake 11	2	188.07	182.53	157.28	151.25	147.65	166.54	173.61	156.25	134.58	172.10	162.50	177.25	164.64	143.45	190.51	214.42	227.12	197.75	260.63	225.63	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13	264.44	298.28	320.68	369.54	0.00	0.00	0.00	0.00	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13
boss of the bosses	Jimmy Jimmy	1	12	Cake 12	3	120.55	117.24	111.07	97.64	99.40	101.04	87.52	100.99	92.83	87.10	97.42	105.57	93.34	116.59	86.55	101.76	100.17	114.08	123.70	112.29	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14	153.76	172.25	162.02	194.50	0.00	0.00	0.00	0.00	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14
boss of the bosses	Tommy Tommy	2	13	Cake 13	1	111.85	117.44	86.70	85.29	79.58	84.37	91.58	85.72	86.00	92.56	94.01	92.93	98.69	111.36	122.88	94.10	102.87	104.89	107.51	114.32	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88	137.90	153.72	164.93	195.89	183.50	180.91	0.00	0.00	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88
boss of the bosses	Tommy Tommy	2	14	Cake 14	2	124.91	154.46	119.91	129.49	126.12	127.60	122.05	120.68	128.07	141.96	148.11	129.99	143.41	139.74	180.97	165.63	176.38	180.46	171.34	173.62	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49	220.23	263.90	248.06	237.42	249.12	249.59	250.18	229.14	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49
boss of the bosses	Tommy Tommy	2	15	Cake 15	3	195.37	163.94	155.07	143.28	124.44	139.87	147.82	146.60	136.42	169.66	135.69	130.71	143.98	164.99	148.65	145.37	143.88	146.27	152.46	150.19	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42	217.01	235.37	0.00	0.00	0.00	0.00	0.00	0.00	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42
boss of the bosses	Tommy Tommy	2	16	Cake 16	1	173.57	124.69	127.02	129.02	112.01	139.45	108.62	118.23	100.10	124.96	111.05	114.42	120.49	141.41	145.05	148.68	136.41	136.69	153.94	134.24	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59	207.96	223.58	253.49	264.22	0.00	0.00	0.00	0.00	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59
boss of the bosses	Tommy Tommy	2	17	Cake 17	2	145.30	147.20	126.16	135.81	157.21	125.22	127.20	123.82	136.33	144.35	133.63	139.93	142.83	139.01	135.20	153.66	180.30	173.76	182.61	175.63	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69	199.61	231.04	254.15	272.94	0.00	0.00	0.00	0.00	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69
boss of the bosses	Tommy Tommy	2	18	Cake 18	3	186.81	160.43	135.53	107.28	152.21	157.54	172.97	170.55	173.72	182.71	159.44	194.50	209.65	195.58	215.75	193.25	226.19	237.22	265.28	220.73	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00	276.37	318.42	352.67	404.62	391.16	347.91	321.96	275.35	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00
boss of the bosses	Tommy Tommy	2	19	Cake 19	1	166.03	167.16	158.91	139.48	148.41	135.85	133.78	131.17	141.70	150.61	148.54	133.15	153.29	152.98	159.35	189.63	194.92	167.90	202.29	205.40	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77	250.05	269.15	318.29	335.82	315.73	309.74	0.00	0.00	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77
boss of the bosses	Tommy Tommy	2	20	Cake 20	2	139.28	145.22	101.39	105.43	92.60	101.33	100.80	99.29	100.51	117.01	123.04	109.04	132.16	126.19	137.76	137.94	144.65	145.77	147.05	137.92	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04	193.69	180.20	199.51	228.13	227.03	206.08	0.00	0.00	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04
boss of the bosses	Tommy Tommy	2	21	Cake 21	3	188.07	182.53	157.28	151.25	147.65	166.54	173.61	156.25	134.58	172.10	162.50	177.25	164.64	143.45	190.51	214.42	227.12	197.75	260.63	225.63	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13	264.44	298.28	320.68	369.54	0.00	0.00	0.00	0.00	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13
boss of the bosses	Tommy Tommy	2	22	Cake 22	1	120.55	117.24	111.07	97.64	99.40	101.04	87.52	100.99	92.83	87.10	97.42	105.57	93.34	116.59	86.55	101.76	100.17	114.08	123.70	112.29	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14	153.76	172.25	162.02	194.50	0.00	0.00	0.00	0.00	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14
boss of the bosses	Tommy Tommy	2	23	Cake 23	2	111.85	117.44	86.70	85.29	79.58	84.37	91.58	85.72	86.00	92.56	94.01	92.93	98.69	111.36	122.88	94.10	102.87	104.89	107.51	114.32	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88	137.90	153.72	164.93	195.89	183.50	180.91	0.00	0.00	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88
boss of the bosses	Tommy Tommy	2	24	Cake 24	3	124.91	154.46	119.91	129.49	126.12	127.60	122.05	120.68	128.07	141.96	148.11	129.99	143.41	139.74	180.97	165.63	176.38	180.46	171.34	173.62	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49	220.23	263.90	248.06	237.42	249.12	249.59	250.18	229.14	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49
boss of the bosses	Tommy Tommy	2	25	Cake 25	1	195.37	163.94	155.07	143.28	124.44	139.87	147.82	146.60	136.42	169.66	135.69	130.71	143.98	164.99	148.65	145.37	143.88	146.27	152.46	150.19	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42	217.01	235.37	0.00	0.00	0.00	0.00	0.00	0.00	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42
boss of the bosses	Tommy Tommy	2	26	Cake 26	2	173.57	124.69	127.02	129.02	112.01	139.45	108.62	118.23	100.10	124.96	111.05	114.42	120.49	141.41	145.05	148.68	136.41	136.69	153.94	134.24	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59	207.96	223.58	253.49	264.22	0.00	0.00	0.00	0.00	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59
boss of the bosses	Tommy Tommy	2	27	Cake 27	3	145.30	147.20	126.16	135.81	157.21	125.22	127.20	123.82	136.33	144.35	133.63	139.93	142.83	139.01	135.20	153.66	180.30	173.76	182.61	175.63	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69	199.61	231.04	254.15	272.94	0.00	0.00	0.00	0.00	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69
boss of the bosses	Tommy Tommy	2	28	Cake 28	1	186.81	160.43	135.53	107.28	152.21	157.54	172.97	170.55	173.72	182.71	159.44	194.50	209.65	195.58	215.75	193.25	226.19	237.22	265.28	220.73	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00	276.37	318.42	352.67	404.62	391.16	347.91	321.96	275.35	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00
boss of the bosses	Tommy Tommy	2	29	Cake 29	2	166.03	167.16	158.91	139.48	148.41	135.85	133.78	131.17	141.70	150.61	148.54	133.15	153.29	152.98	159.35	189.63	194.92	167.90	202.29	205.40	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77	250.05	269.15	318.29	335.82	315.73	309.74	0.00	0.00	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77
boss of the bosses	Tommy Tommy	2	30	Cake 30	3	139.28	145.22	101.39	105.43	92.60	101.33	100.80	99.29	100.51	117.01	123.04	109.04	132.16	126.19	137.76	137.94	144.65	145.77	147.05	137.92	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04	193.69	180.20	199.51	228.13	227.03	206.08	0.00	0.00	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04
boss of the bosses	Tommy Tommy	2	31	Cake 31	1	188.07	182.53	157.28	151.25	147.65	166.54	173.61	156.25	134.58	172.10	162.50	177.25	164.64	143.45	190.51	214.42	227.12	197.75	260.63	225.63	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13	264.44	298.28	320.68	369.54	0.00	0.00	0.00	0.00	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13
boss of the bosses	Tommy Tommy	2	32	Cake 32	2	120.55	117.24	111.07	97.64	99.40	101.04	87.52	100.99	92.83	87.10	97.42	105.57	93.34	116.59	86.55	101.76	100.17	114.08	123.70	112.29	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14	153.76	172.25	162.02	194.50	0.00	0.00	0.00	0.00	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14
boss of the bosses	Tommy Tommy	2	33	Cake 33	3	111.85	117.44	86.70	85.29	79.58	84.37	91.58	85.72	86.00	92.56	94.01	92.93	98.69	111.36	122.88	94.10	102.87	104.89	107.51	114.32	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88	137.90	153.72	164.93	195.89	183.50	180.91	0.00	0.00	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88
boss of the bosses	Tommy Tommy	2	34	Cake 34	1	124.91	154.46	119.91	129.49	126.12	127.60	122.05	120.68	128.07	141.96	148.11	129.99	143.41	139.74	180.97	165.63	176.38	180.46	171.34	173.62	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49	220.23	263.90	248.06	237.42	249.12	249.59	250.18	229.14	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49
boss of the bosses	Tommy Tommy	2	35	Cake 35	2	195.37	163.94	155.07	143.28	124.44	139.87	147.82	146.60	136.42	169.66	135.69	130.71	143.98	164.99	148.65	145.37	143.88	146.27	152.46	150.19	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42	217.01	235.37	0.00	0.00	0.00	0.00	0.00	0.00	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42
boss of the bosses	Tommy Tommy	2	36	Cake 36	3	173.57	124.69	127.02	129.02	112.01	139.45	108.62	118.23	100.10	124.96	111.05	114.42	120.49	141.41	145.05	148.68	136.41	136.69	153.94	134.24	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59	207.96	223.58	253.49	264.22	0.00	0.00	0.00	0.00	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59
boss of the bosses	Tommy Tommy	2	37	Cake 37	1	145.30	147.20	126.16	135.81	157.21	125.22	127.20	123.82	136.33	144.35	133.63	139.93	142.83	139.01	135.20	153.66	180.30	173.76	182.61	175.63	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69	199.61	231.04	254.15	272.94	0.00	0.00	0.00	0.00	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69
boss of the bosses	Tommy Tommy	2	38	Cake 38	2	186.81	160.43	135.53	107.28	152.21	157.54	172.97	170.55	173.72	182.71	159.44	194.50	209.65	195.58	215.75	193.25	226.19	237.22	265.28	220.73	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00	276.37	318.42	352.67	404.62	391.16	347.91	321.96	275.35	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00
boss of the bosses	Tommy Tommy	2	39	Cake 39	3	166.03	167.16	158.91	139.48	148.41	135.85	133.78	131.17	141.70	150.61	148.54	133.15	153.29	152.98	159.35	189.63	194.92	167.90	202.29	205.40	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77	250.05	269.15	318.29	335.82	315.73	309.74	0.00	0.00	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77
boss of the bosses	Tommy Tommy	2	40	Cake 40	1	139.28	145.22	101.39	105.43	92.60	101.33	100.80	99.29	100.51	117.01	123.04	109.04	132.16	126.19	137.76	137.94	144.65	145.77	147.05	137.92	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04	193.69	180.20	199.51	228.13	227.03	206.08	0.00	0.00	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04
boss of the bosses	Tommy Tommy	2	41	Cake 41	2	188.07	182.53	157.28	151.25	147.65	166.54	173.61	156.25	134.58	172.10	162.50	177.25	164.64	143.45	190.51	214.42	227.12	197.75	260.63	225.63	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13	264.44	298.28	320.68	369.54	0.00	0.00	0.00	0.00	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13
boss of the bosses	Tommy Tommy	2	42	Cake 42	3	120.55	117.24	111.07	97.64	99.40	101.04	87.52	100.99	92.83	87.10	97.42	105.57	93.34	116.59	86.55	101.76	100.17	114.08	123.70	112.29	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14	153.76	172.25	162.02	194.50	0.00	0.00	0.00	0.00	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14
boss of the bosses	Tommy Tommy	2	43	Cake 43	1	111.85	117.44	86.70	85.29	79.58	84.37	91.58	85.72	86.00	92.56	94.01	92.93	98.69	111.36	122.88	94.10	102.87	104.89	107.51	114.32	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88	137.90	153.72	164.93	195.89	183.50	180.91	0.00	0.00	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88
boss of the bosses	Tommy Tommy	2	44	Cake 44	2	124.91	154.46	119.91	129.49	126.12	127.60	122.05	120.68	128.07	141.96	148.11	129.99	143.41	139.74	180.97	165.63	176.38	180.46	171.34	173.62	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49	220.23	263.90	248.06	237.42	249.12	249.59	250.18	229.14	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49
boss of the bosses	Tommy Tommy	2	45	Cake 45	3	195.37	163.94	155.07	143.28	124.44	139.87	147.82	146.60	136.42	169.66	135.69	130.71	143.98	164.99	148.65	145.37	143.88	146.27	152.46	150.19	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42	217.01	235.37	0.00	0.00	0.00	0.00	0.00	0.00	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42
boss of the bosses	Tommy Tommy	2	46	Cake 46	1	173.57	124.69	127.02	129.02	112.01	139.45	108.62	118.23	100.10	124.96	111.05	114.42	120.49	141.41	145.05	148.68	136.41	136.69	153.94	134.24	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59	207.96	223.58	253.49	264.22	0.00	0.00	0.00	0.00	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59
boss of the bosses	Tommy Tommy	2	47	Cake 47	2	145.30	147.20	126.16	135.81	157.21	125.22	127.20	123.82	136.33	144.35	133.63	139.93	142.83	139.01	135.20	153.66	180.30	173.76	182.61	175.63	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69	199.61	231.04	254.15	272.94	0.00	0.00	0.00	0.00	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69
boss of the bosses	Tommy Tommy	2	48	Cake 48	3	186.81	160.43	135.53	107.28	152.21	157.54	172.97	170.55	173.72	182.71	159.44	194.50	209.65	195.58	215.75	193.25	226.19	237.22	265.28	220.73	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00	276.37	318.42	352.67	404.62	391.16	347.91	321.96	275.35	214.88	199.06	350.01	296.39	296.39	276.57	327.82	318.24	334.18	323.07	334.66	300.00
boss of the bosses	Tommy Tommy	2	49	Cake 49	1	166.03	167.16	158.91	139.48	148.41	135.85	133.78	131.17	141.70	150.61	148.54	133.15	153.29	152.98	159.35	189.63	194.92	167.90	202.29	205.40	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77	250.05	269.15	318.29	335.82	315.73	309.74	0.00	0.00	180.92	169.40	270.19	237.64	258.99	241.47	254.61	243.31	263.37	223.95	277.61	246.77
boss of the bosses	Tommy Tommy	2	50	Cake 50	2	139.28	145.22	101.39	105.43	92.60	101.33	100.80	99.29	100.51	117.01	123.04	109.04	132.16	126.19	137.76	137.94	144.65	145.77	147.05	137.92	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04	193.69	180.20	199.51	228.13	227.03	206.08	0.00	0.00	142.35	138.82	194.63	188.68	182.15	159.86	190.22	194.64	203.45	167.44	200.01	165.04
boss of the bosses	Richard Richard	3	51	Cake 51	3	188.07	182.53	157.28	151.25	147.65	166.54	173.61	156.25	134.58	172.10	162.50	177.25	164.64	143.45	190.51	214.42	227.12	197.75	260.63	225.63	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13	264.44	298.28	320.68	369.54	0.00	0.00	0.00	0.00	194.03	190.43	318.33	327.05	305.45	298.51	317.79	310.08	275.37	287.71	323.96	274.13
boss of the bosses	Richard Richard	3	52	Cake 52	1	120.55	117.24	111.07	97.64	99.40	101.04	87.52	100.99	92.83	87.10	97.42	105.57	93.34	116.59	86.55	101.76	100.17	114.08	123.70	112.29	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14	153.76	172.25	162.02	194.50	0.00	0.00	0.00	0.00	119.59	110.52	189.07	174.21	179.99	164.96	181.22	192.38	180.45	167.37	175.45	154.14
boss of the bosses	Richard Richard	3	53	Cake 53	2	111.85	117.44	86.70	85.29	79.58	84.37	91.58	85.72	86.00	92.56	94.01	92.93	98.69	111.36	122.88	94.10	102.87	104.89	107.51	114.32	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88	137.90	153.72	164.93	195.89	183.50	180.91	0.00	0.00	104.59	106.34	184.01	196.04	170.98	158.45	176.79	167.11	154.59	148.94	158.63	140.88
boss of the bosses	Richard Richard	3	54	Cake 54	3	124.91	154.46	119.91	129.49	126.12	127.60	122.05	120.68	128.07	141.96	148.11	129.99	143.41	139.74	180.97	165.63	176.38	180.46	171.34	173.62	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49	220.23	263.90	248.06	237.42	249.12	249.59	250.18	229.14	159.04	173.85	256.64	230.27	232.79	213.72	236.56	227.43	241.09	248.15	265.10	208.49
boss of the bosses	Richard Richard	3	55	Cake 55	1	195.37	163.94	155.07	143.28	124.44	139.87	147.82	146.60	136.42	169.66	135.69	130.71	143.98	164.99	148.65	145.37	143.88	146.27	152.46	150.19	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42	217.01	235.37	0.00	0.00	0.00	0.00	0.00	0.00	164.86	148.09	238.89	215.49	245.24	196.91	250.38	213.55	243.81	223.64	240.87	209.42
boss of the bosses	Richard Richard	3	56	Cake 56	2	173.57	124.69	127.02	129.02	112.01	139.45	108.62	118.23	100.10	124.96	111.05	114.42	120.49	141.41	145.05	148.68	136.41	136.69	153.94	134.24	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59	207.96	223.58	253.49	264.22	0.00	0.00	0.00	0.00	149.73	156.81	276.83	221.83	235.63	219.67	247.12	235.46	266.79	207.07	242.31	202.59
boss of the bosses	Richard Richard	3	57	Cake 57	3	145.30	147.20	126.16	135.81	157.21	125.22	127.20	123.82	136.33	144.35	133.63	139.93	142.83	139.01	135.20	153.66	180.30	173.76	182.61	175.63	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69	199.61	231.04	254.15	272.94	0.00	0.00	0.00	0.00	157.00	165.99	255.10	202.67	189.37	190.50	210.82	225.59	235.77	199.05	232.67	228.69

apo · May 25, 2013

Hi..

I enjoyed doing this..

Funnily enough.. I actually got more than half way though creating this and accidently closed the Workbook (before saving it)... had to write the code again... one of those 'doh' moments..

I have shown below how part of my test data looked.. although my entire test data had all 52 weeks in it (out to column BF).

This Sub will do everything you asked..

It will:

* Filter your employees one by one and then copy the data for each employees to sheet 3.
* It will then create a New Workbook and copy the data from Sheet3 to this new workbook.
* It will then save the New Workbook with the Employees name and the date appended to it..
* It will then create a new Outlook email.. addressed to the Employee (as per that employees email address on Sheet2).
* It will also attach the newly created workbook ( the one that was named as per the Employees name)..

Let me know how it goes for you...

Code:

Sub a()


Dim temp As String
Dim strTo As String
Dim LastRow As Long
Dim LastRow2 As Long
Dim LastRow3 As Long
Dim j As Long
Dim Crit1 As Long
Dim Crit2 As Long
Dim Flname As String
Dim Fval As String
Dim DateToday As String
Dim WBClose As String


DateToday = Format(Date, "mmm-dd-yy")
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row


For j = 2 To LastRow
If Sheets("Sheet1").Cells(j, 3) = Sheets("Sheet1").Cells(j, 3).Offset(-1, 0) Then
GoTo 1
End If
Crit1 = Sheets("Sheet1").Cells(j, 3)
Sheets("Sheet1").Range("A1:C1").AutoFilter Field:=3, Criteria1:=Crit1


LastRow2 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Activate
Sheets("Sheet1").Range("A1:BF" & LastRow2).Select
  Selection.Copy
  Flname = Sheets("Sheet1").Cells(j, 2).Value
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
 
         
  Sheets("Sheet3").Activate
  Sheets("Sheet3").Cells(1, 1).Select
  Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
  False, Transpose:=False
 
    If Flname <> "" Then
        Set NewWkbk = Workbooks.Add
        ThisWorkbook.Sheets(3).Copy Before:=NewWkbk.Sheets(1)
        NewWkbk.SaveAs ThisWorkbook.Path & "\" & Flname & "-" & DateToday


        End If
        WBClose = Flname & "-" & DateToday & ".xlsx"
        Workbooks(WBClose).Close SaveChanges:=False
        
Sheets("Sheet3").Range("A1:BF" & LastRow2).ClearContents




strTo = ""
Sheets("Sheet2").Range("A1:C1").AutoFilter Field:=2, Criteria1:=Crit1
LastRow3 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row




strTo = Sheets("Sheet2").Cells(LastRow3, 3).Value


'Declare and establish the Object variables for Outlook.
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)
        


'Display the email message
With objMailItem
.To = strTo
.Subject = temp
.Body = "Blah Blah (enter body text here)"
.Attachments.Add ThisWorkbook.Path & "\" & WBClose
.Display 'Change to .Send if you want to just send it.
End With


'Release object variables from system memory.
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing


1
Next j


End Sub

Sheet1

*	A	B	C	D	E	F	G	H	I	J	K
1	Department VP	Sales Rep	emp ID	Item	Description	trademark	week 1	week 2	week 3	week 4	week 5
2	boss of the bosses	Jimmy	1	1	Cake 1	1	188.07	188.07	188.07	188.07	188.07
3	boss of the bosses	Jimmy	1	2	Cake 2	2	120.55	120.55	120.55	120.55	120.55
4	boss of the bosses	Jimmy	1	2	Cake 2	2	120.55	120.55	120.55	120.55	120.55
5	boss of the bosses	Tommy	2	3	Cake 3	3	111.85	111.85	111.85	111.85	111.85
6	boss of the bosses	Tommy	2	4	Cake 4	1	124.91	124.91	124.91	124.91	124.91
7	boss of the bosses	Richard	3	5	Cake 5	2	195.37	195.37	195.37	195.37	195.37
8	boss of the bosses	Richard	3	6	Cake 6	3	173.57	173.57	173.57	173.57	173.57

Excel tables to the web - Excel Jeanie Html 4

VBA program Copy Paste Email

needtoknownow

New Member

Excel Facts

apo

Well-known Member

Forum statistics

Share this page

VBA program Copy Paste Email

needtoknownow

New Member

Excel Facts

apo

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock