Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from Excel with Outlook address book to mail bills
I'm trying to setup an automated way to send computed bills to a list of
people in an organization (about 300). We collect data (water usage) and use Excel to compute the bill based on usage (current reading - previous times rate). Each row is for a particular meter, and has a number. We have a seperate list of people, with mailing addresses. Some people will have more than one meter (we could make it one-to-one by creating duplicate entries for these people as there are so few). I'd like a way to combine the address list and the excel data and generate bills - sounds like a mail merge but with two data sources. Presumably Access could do this, but I don't have any experience there and I want people after me to be able to understand what I've done. Anyways, I'm open to suggestings. The addresses do not have to be in Outlook, but it is convenient since Outlook Address Book does a good job at managing this data. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from Excel with Outlook address book to mail bills
If you are in contacts, you can select File and then export to Excel,
Personally I would do that and then vlookup from the meter sheet to get the name and addresses from the outlook sheet. Then you can sort and see how many have multiple entries since you say there are so few, and then use that Excel sheet to setup your mail merge. -- -John Please rate when your question is answered to help us and others know what is helpful. "Joe Porkka [MSFT]" wrote: I'm trying to setup an automated way to send computed bills to a list of people in an organization (about 300). We collect data (water usage) and use Excel to compute the bill based on usage (current reading - previous times rate). Each row is for a particular meter, and has a number. We have a seperate list of people, with mailing addresses. Some people will have more than one meter (we could make it one-to-one by creating duplicate entries for these people as there are so few). I'd like a way to combine the address list and the excel data and generate bills - sounds like a mail merge but with two data sources. Presumably Access could do this, but I don't have any experience there and I want people after me to be able to understand what I've done. Anyways, I'm open to suggestings. The addresses do not have to be in Outlook, but it is convenient since Outlook Address Book does a good job at managing this data. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from Excel with Outlook address book to mail bi
HM...I'd like it more automatic.
I can "link" the contacts from Outlook into Access - but this has a number of problems (you cannot cusomize the link at all and the defaults won't work for me). I will probably have to write some VBA to get this to work the way I want it. Related Question: I have a sheet that looks like Meter# 1/10/2007 1/17/2007 1/21/2007 1 0 21 44 2 0 45 66 3 0 22 76 4 0 13 45 5 0 45 90 So basically, every week the number of columns will increase as I collect more data -- my table continues getting bigger and bigger. Someplace then I will have an column of equations for the current amount due for each meter that looks like = (D2-c2) * RATE Where "D2" and "C2" should be the two most recent columns in the above table (and RATE is of course the multiplier to convert from meter units to dollars). This seems like such an obvious thing, yet I've rarely seen a sample spreadsheet that deals with a constantly growing table. I have struggled with this problem in various forms for a long time and have never found a good solution. The alternative here would be to manually shift the data over each week when entering the new data - but then I loose the history of readings. "John Bundy" wrote: If you are in contacts, you can select File and then export to Excel, Personally I would do that and then vlookup from the meter sheet to get the name and addresses from the outlook sheet. Then you can sort and see how many have multiple entries since you say there are so few, and then use that Excel sheet to setup your mail merge. -- -John Please rate when your question is answered to help us and others know what is helpful. "Joe Porkka [MSFT]" wrote: I'm trying to setup an automated way to send computed bills to a list of people in an organization (about 300). We collect data (water usage) and use Excel to compute the bill based on usage (current reading - previous times rate). Each row is for a particular meter, and has a number. We have a seperate list of people, with mailing addresses. Some people will have more than one meter (we could make it one-to-one by creating duplicate entries for these people as there are so few). I'd like a way to combine the address list and the excel data and generate bills - sounds like a mail merge but with two data sources. Presumably Access could do this, but I don't have any experience there and I want people after me to be able to understand what I've done. Anyways, I'm open to suggestings. The addresses do not have to be in Outlook, but it is convenient since Outlook Address Book does a good job at managing this data. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from Excel with Outlook address book to mail bi
WRT the growing tables problem I asked about, I found "structured
references", which seems to just about solve the problem for me. "Joe Porkka [MSFT]" wrote: HM...I'd like it more automatic. I can "link" the contacts from Outlook into Access - but this has a number of problems (you cannot cusomize the link at all and the defaults won't work for me). I will probably have to write some VBA to get this to work the way I want it. Related Question: I have a sheet that looks like Meter# 1/10/2007 1/17/2007 1/21/2007 1 0 21 44 2 0 45 66 3 0 22 76 4 0 13 45 5 0 45 90 So basically, every week the number of columns will increase as I collect more data -- my table continues getting bigger and bigger. Someplace then I will have an column of equations for the current amount due for each meter that looks like = (D2-c2) * RATE Where "D2" and "C2" should be the two most recent columns in the above table (and RATE is of course the multiplier to convert from meter units to dollars). This seems like such an obvious thing, yet I've rarely seen a sample spreadsheet that deals with a constantly growing table. I have struggled with this problem in various forms for a long time and have never found a good solution. The alternative here would be to manually shift the data over each week when entering the new data - but then I loose the history of readings. "John Bundy" wrote: If you are in contacts, you can select File and then export to Excel, Personally I would do that and then vlookup from the meter sheet to get the name and addresses from the outlook sheet. Then you can sort and see how many have multiple entries since you say there are so few, and then use that Excel sheet to setup your mail merge. -- -John Please rate when your question is answered to help us and others know what is helpful. "Joe Porkka [MSFT]" wrote: I'm trying to setup an automated way to send computed bills to a list of people in an organization (about 300). We collect data (water usage) and use Excel to compute the bill based on usage (current reading - previous times rate). Each row is for a particular meter, and has a number. We have a seperate list of people, with mailing addresses. Some people will have more than one meter (we could make it one-to-one by creating duplicate entries for these people as there are so few). I'd like a way to combine the address list and the excel data and generate bills - sounds like a mail merge but with two data sources. Presumably Access could do this, but I don't have any experience there and I want people after me to be able to understand what I've done. Anyways, I'm open to suggestings. The addresses do not have to be in Outlook, but it is convenient since Outlook Address Book does a good job at managing this data. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I access Outlook Express email address book using Excel | Excel Discussion (Misc queries) | |||
Editing e-mail address brings up Outlook in Excel cell | New Users to Excel | |||
Using Outlook address book in Excel | Excel Discussion (Misc queries) | |||
How do I import Office address book to Outlook Express address bo. | Excel Discussion (Misc queries) | |||
How do i export a Excel database a Outlook address book? | Excel Discussion (Misc queries) |