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,

I'd be pleased to help if I can, but I think you need to take it a step at a time.

First loading the data into you SS via a form. I would need to know the name of all the fields you want to records, and what order you want them recorded in your SS.

ColinKJ

 

[chalcott]

Colin thanks,

Sorry what is "ss"?

I don't use Office 2007 much, only Word, and am really only familiar with the basics of that which i do use,

Do you mean you want a list of all the records that i want to enter in the Form?

The Excel workbook has many fields, i would't mind creating a template/form that would add all the info for each record from a form. I could then create another template/form for the invoice, as i wouldn't need all the records for the invoice to send to the customer.

Another thing, to simply entering date into the Excel Worksheet for the invoices, i have made many of the columns a drop down list, so that i only have to use the drop down list to choose the answer. Not all the records though, some need typing manually.

 

[VoG]

You might want to take a look at Mail Merge in Word, using your Excel workbook as the data source.

 

[chalcott]

VOg

thank you,

i have used mail merge in the past, to create a list to send emails too. They were all sent out at the same time,

Do you know if it would work, each time i wanted to print a specific invoice? ie, each time i dont' want to print all the records, just the ones newly added, so that the customer gets a copy if you know what i mean,

 

[ColinKJ]

Hi chalcott,

SS is just Spredsheet.

In your first post you listed some of them.

Those you did lis, all but one of them are one liners, except for "Customer address", this would normally run into multiple lines, how do you manage this in your Spredsheet, do you use multiple columns.

Regards

Colin

 

[chalcott]

Colin,

At the moment i am just creating the spreadsheet, i havn't started using it yet.

The spreadsheet, called "Customer Data" is where i enter all the main data for each customer.

The rows all begin with an invoice number let's say

10000
10001
10002

The invoice number is the first column field,

The other column fields are

Invoice Number<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Day Ordered<o></o>
Name<o></o>
Shipping Address<o></o>
Shipping Country<o></o>
Email<o></o>
Telephone<o></o>
Email<o></o>
Currency Code<o></o>
RBS Worldpay<o></o>
Paypal<o></o>
Google Checkout<o></o>
Cheque No.<o></o>
Bank Transfer<o></o>
Cash<o></o>
No. of cartons<o></o>
Shipped Weekday<o></o>
Shipped Date<o></o>
Shipped Month<o></o>
Shipped Year<o></o>
Posting/Delivery Used<o></o>
Google Checkout Fees GBP<o></o>
Paypal Fees GBP<o></o>
Paypal Fees EUR<o></o>
Paypal Fees USD<o></o>
Weight kg

Please note, that i created these records for my own calculations (accounting). It doesn't mean that i use all of them for the customers, for example some customers pay using Paypal, so for them i will record what amount they paid in their currency, they won't have an entry in cheque, or google checkout , so i leave those blank.


Yes you are right, most are one liners and i have created drop down lists for most of the answers to make it easy to fill in.

The "Customer Address" would have at least 3 separate lines of data. I thought i could copy and paste the address in 3 or 4 lines. But now you mention it, I don't know if I can enter separate lines here for example like this

19 High St,
London
NW2

If i cannot do that in Excel, then I will have to change the columns to show

Address 1,
Address 2,
City,
Post Code

Do you think I should edit my records to show the address in the latter style in Excel? This would mean, that each line gets it's own column name. I think this might be better?

 

[ColinKJ]

chalcott,

I think with the address you are right, you should use consecutive columns.

A couple of questions from the list of column headers:

It appears you have used Email twice?.

With regards to the Shipping date,

You've got Date / Month / Year, is that correct, or should it be Day / Month / Year.

Can you let me know which ones you want drop down selections for, and what you want in each list.

with regards to Payment Type, do you enter values, or what

RBS Worldpay<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Paypal<o></o>
Google Checkout<o></o>
Cheque No.<o></o>
Bank Transfer<o></o>
Cash<o></o>

Regards

ColinKJ

 

[chalcott]

Colin,

OK i will change the address fields. Sorry yes, the email column only has one column, i made a mistake above. (Ideally id like to be able to click on the customers email and it opens a prefilled email in outlook , ready to send.) Regarding the date, i have created drop down lists, with the weekdays, day number, month name and year. I'm happy with the list, they're ready to use, with drop down lists. I mean ready to use in the Excel form. I don't know if i can use them in the email and invoice forms i want to create.

The payment types, each have a drop down list with their own name. This is a silly way it may seem to you, but the reason i did it, was because i designed this spreadsheet to have each column tot up the totals in the list at the end of the year either by number as some fields or text. So i'll know how many people used Paypal and how many used Google checkout and how many paid cheque etc. I know it may seem silly and unecessary to you because if someone pays by Paypal, all the other payment columns will remain blank, so you're questiong why not have a single column with a drop down list of all payment types.

I think it may be better for me to add a nother page to my spreadsheet, soley for invoices. If you think this is better, i'll do that as it may not be possible to create an invoice from the style i'm using now. I mean unless there is a way to sift out the columns with data to create the invoice, for example how would the invoice form know what to put in the "payment type" if each payment type has its own column?

Most of the column fields have drop down lists, with data ready to choose from. I used the "data validation" to create drop down lists. Thus if a customer pays £30 by Paypal, for 3 cartons,

I will enter "paypal" in the paypal column from the drop down list. and 30 in the "total paid" column and GBP in the Currency code column. Looking at the list i forgot to add it above, but it's there.

This spreadsheet layout may actually not be ideal for creating an invoice from,

Let me now if you think i should create a separate one for invoices. I could perhaps have the data copy automatically to the "invoice spreadsheet" if i have to create one

 

[walden]

As Vog says: use Mailmerge in Word, picking the appropriate columns from the Excel sheet. Please don't overcomplicate matters by setting up more Worksheets. The way I do this (and I've done it for years) is to have a "flag" column in my Excel sheet which Word uses to decide to print the invoice - just follow the Word wizard "Tools-Letters and Mailings-Mailmerge..."
For the email use Google to find one of the many posts which automate this - it will mean using VBA but the steps are well explained. Ron de Bruin, in particular, has many posts on this subject.