How to automate emails and invoices from Excel datasheet

[chalcott]

I would appreciate any help or tips please. I'm not an expert Excel user, and can just about make the most simple worksheet with references to Excel 2007 bookds,

I have the office 2007 Suite, in case anyone needs to know. what want to do is this.

I have a small business, where i sell online mostly. I'm creating a simple spreadsheet using Excel 2007, a workbook that has simple financial statements.

I'm creating a list of customers along with what they order, what they paid, how they paid, (paypal, cheque) etc,

The first column is the invoice number, and each is unique to each customer. I add as i get orders. so my list of invoices has the following fields as column headers


<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Quote:<o></o>
<TABLE style="WIDTH: 100%; mso-cellspacing: 0cm; mso-yfti-tbllook: 1184; mso-padding-alt: 4.5pt 4.5pt 4.5pt 4.5pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0 1pt inset; BORDER-LEFT: #f0f0f0 1pt inset; PADDING-BOTTOM: 4.5pt; PADDING-LEFT: 4.5pt; PADDING-RIGHT: 4.5pt; BACKGROUND: #f8f8f8; BORDER-TOP: #f0f0f0 1pt inset; BORDER-RIGHT: #f0f0f0 1pt inset; PADDING-TOP: 4.5pt; mso-border-alt: inset windowtext .75pt">Invoice Number Customer Name, Paid by Paypal, Paid by Cheque, Amount, Cusotmer email, Customer address, <o></o>

</TD></TR></TBODY></TABLE>
There are more and i won't list them all, but you get the idea.

Now each time i send an order, i have to fill in the excel worksheet. I then create the invoice separately in Word 2007 which i print to send to the customer with the order in the mail. The third step is sending an email to the customer upon despatch, using Outlook 2007.

I don't want to use accounting software as that is too complicated for my simple little business.

What i do want to do however is combine all three tasks instead of having to open different programs and copying and pasting between them. I'd like to be able to add the data to Excel either using a form (ideally) or direct, then i'd like to be able to click on the "customer email" field to send a confirmation email to the customer, and also to be able to have an automated invoice, either in excel or Word, which draws the information automatically for each invoice, so that i only have to print it.

At the moment i'm doing all 3 tasks separately, when i know they can be automated.

In short, can anyone give me clear steps in simple language how to do the following

1. I have about 20 columns of data for each invoice, ideally i'd like to be able to enter them from a form as it gets cumbersome going through them all in Excel. The form could be either in Word or Excel, which would be easier?

2. Using the invoice number as the unique field, how can i create an invoice that would draw out the required fields from the Excel data automatically, just by clicking a button? I dont' mind using either Excel or Word, whichever is easier, and i do have Access too, but i don't know if i should use that, or not. I don't need to draw any other data. I only want to print invoices and send confirmation emails.

3. After printing the invoice and posting the order to the customer i sent them an email confirming. Again i have to manually enter or copy and paste data in the email, which i know could be automated, if i create a template, and have some fields that are automaitcally populated using the fields in the date already entered.

These fields ususally include the total paid, currency paid, address, name and how many ordered. All of this could be automated, but i'm not sure how to do it.

----------------

Thanks in advance for any help. <o></o>

 

[ColinKJ]

Hi chalcott,

Lets go one step at a time, we'll get the record form sorted first then go on to the invoice and autoemail.

I've put together an example of the Workbook called "Records" with a dummy set of data on sheet1 but with the headings we've discussed, just to see if the layout and headers are as you want them. I've also created in the WB an input form, again, at this stage, to see if it has all the fields you want.

You can download a copy from:

http://www.box.net/shared/yyuzgkly1r

When you open it, if you select a cell in row 1 or any blank cell, then right click, you should get an input form displayed. There are no controls over what you can enter at present.

Just see if it's looking like what you want. Also if all the fields you want are there.

Enter some dummy data in any of the field, and click on SAVE, the data entered should be placed in the next free row.

Let me have your feed back before I go any further.

Not sure where you're based, but I'm in Spain, and it's getting late, so I'm off to bed.

Be in touch tomorrow.

Regards

ColinKJ

 

[chalcott]

Colin thank you, ill see you tomorrow then, i'm in the UK,

yes i did download your Workseet, added a test data in a field and it saved OK

If i had known that you can upload excel files for others to see, at this website you uploaded to, i could have perhaps added my own worksheet for you, Would you like me to do that, so that you can see how i've done the drop down lists?

Also, in the paypal sections, i only showed you the GBP, USD, and EUR columns but i have about 19 other currencies too. So if you need to see or know what ALL the columns are, i can upload the Excel worksheet for you if it makes it easier,

Thanks a lot

 

[chalcott]

No hurry Colin, i was just checking that you did receive my Excel file which i sent to your aol address? I know that aol. sometimes rejects googlemail, so just checking.

thanks

 

[berigud]

Hi guys,

I would like to know how this project ended. I have a similar challenge and would like to have a copy of your working sheet.

Thank you in advance.

 

[ColinKJ]

Hi berigud,

Your going back a bit now, but you can download a copy of the WBs:

Records.xls
&
Invoice_Template.xls

From:

http://www.box.com/s/8vuhcoy263zs5574agqx

"chalcott" was the one who was going to use it, if he dosn't respond to your post, try sending him a Private Message.