Email Pivot Table to Distribution list changing Pivot Table Filter

[DarkNight]

Hello,

I'm trying to use vba to mass email out a pivot table. The email_address table in column A:B includes a list of states and an associated email. I'm looking to email out the pivot table located in column D:E to each of the emails located in the email_addres table. In each email i need to change the filter on the pivot table to match the associated state.

For example: the first email that should be sent would go to Test_AZ@gmail.com. The body of this email needs to include the pivot table in column D:E with a filter applied for the state located in column A (in this case AZ).
this loop needs to continue for each row in the email_address table. apply the filter in column a to the pivot table. send the email with the pivot table in the body to teh email address located in column B.


let me know if you have any questions. thank you!!!!

Question.xlsx
	A	B	C	D	E
1	State	Email Address		State	(All)
2	AZ	Test_AZ@gmail.com
3	IA	Test_IA@gmail.com		Row Labels	Count of City
4	IN	Test_IN@gmail.com		Beckley	18
5	KY	Test_KY@gmail.com		Danville	18
6	MI	Test_MI@gmail.com		Elko	18
7	MT	Test_MT@gmail.com		Gallatin	17
8	NC	Test_NC@gmail.com		Jeffersonville	17
9	NC	Test_NC@gmail.com		Johnston	17
10	NC	Test_NC@gmail.com		Johnstown	17
11	NM	Test_NM@gmail.com		Kennewick	18
12	NM	Test_NM@gmail.com		Lake Havasu City	18
13	NV	Test_NV@gmail.com		Las Cruces	17
14	OR	Test_OR@gmail.com		Los Alamos	17
15	PA	Test_PA@gmail.com		Marquette	18
16	PA	Test_PA@gmail.com		Martinsville	18
17	TN	Test_TN@gmail.com		McMinnville	18
18	TX	Test_TX@gmail.com		Missoula	18
19	VA	Test_VA@gmail.com		Ottumwa	17
20	VA	Test_VA@gmail.com		Paris	18
21	VA	Test_VA@gmail.com		Pasco	18
22	WA	Test_WA@gmail.com		Roxboro	17
23	WA	Test_WA@gmail.com		Somerset	17
24	WV	Test_WV@gmail.com		Warrenton	17
25				Wilson	35
26				Grand Total	403
Email List

 

[DarkNight]

One note - the pivot table should be within the email body not as an attachment to the email. thank you!

 

[DarkNight]

bump