Excel Help Required!!`

[damienenglish]

Hello Everyone.

I have been asked to look into a solution where currently, a client of mine has a spreadsheet which contains about 200 customers details. These details have to be updated on a weekly basis which displays their pricing range. These prices are then sent out to the client via email.

The problem we have, is that it is taking 2 people half a day each to update the client info, and they want a solution which is a little more automated!

I need a simple solution which uses Excel, to which the user can simply enter the updated figures just once, which will then update all the required fields. ideally, it would have some form of 'Menu' where they can select the client from a drop-down menu and then enter the required data. Then they click a submit button which generates the data within an email.

I am not an expert in Excel and any help on this would be much appreciated. The reason I am looking for a solution in Excel, is that the users dont have access to MS Access at the moment. They do have access to MS Word though.

Any help would be much aprecciated.

 

[SkipVought]

hi,

...enter the updated figures just once...

This is definitely achievable and ought to be standard operating procedure for any similar application.

Generally speaking, the spreadsheet with 200 customers' details should have one or more columns that uniquely identify that row and no other. That data will be key.

Please describe your data. An example of actual data would be ideal. Post it here. Explain how the update process works and where your users are entering the same data multiple times.

Please be CLEAR, CONCISE & COMPLETE with your information & descriptions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[damienenglish]

Hi there

Sorry for the delay in replying to you.

I have been looking into some kind of VBS script that will extract the data I need.

Basically, I have a spreadsheet that has lots of customers data on the same sheet. Each customer has its own title, followed by its relevant data. The data is different for each customer. Each customers data is listed underneath each other.

I want to be able to extract each customers data into an email to be able to send out to the customer. I have the email addresses listed within the customers data as well.

E.G
Customer Name
Customer Email
Customer Data

Customer Name
Customer Email
Customer Data

I have been recomended by a collegue to use a 'loop' within a VBS script to extract this data, but I am quite unfamiliar with VBS scripts, so any simlar script examples would be much appreciated.

Many Thanks

 

[SkipVought]

Excel works best with TABLES as source data.

Construct a table for source data to reside, like,
[tt]
Customer Name Customer Email Customer Data
Skip Vought cvought@tt.com 4/30/2012

[/tt]
depending what you need to do with this data, it can be accessed via a lOOKUP formula, MS Query, PivotTable, VBA.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

Follow the instructions on

http://www.ehow.com/how_6931953_send-using-excel-2007-vba.html

You'll have to make a loop to do it for all of your information, but it's possible in the format you have your data. Note: it's important to get the references to the Outlook Object Library established in Step 1. I noticed that in my version ('07), I had to follow the instructions on the macro toolbar, not the main excel toolbar.

 

[damienenglish]

Hi All

Thanks for your posts so far.

So I have managed to put together the following script:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Users\desktop\CLW.xls")

IntRow = 2

Do Until objExcel.Cells(IntRow, 2).Value = "***SEND"
Wscript.Echo objExcel.Cells(IntRow, 2).Value
IntRow = IntRow + 1
Loop

objExcel.Quit

What I want to do, is to tell the script to search for a variable called: ***SEND and to them copy the proceeding data in all cells to an email until it comes across the next ***SEND in the spreadsheet.

Then I want to script to copy all data within the two ***SEND areas and paste it into an email in Outlook.

Does anyone have any suggestions on this? I have exhausted my VBA knowledge and am struggling a bit!

Many Thanks

 

[SkipVought]

The VBS forum is forum329. Your VBS questions will be best addressed there. This forum addresses Office application features that can be manipulated without the need for code.

If you want VBA answers, then please post in forum707.

What is your coding environment? If you are coding in a Microsoft Application, then you are using VBA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}