This document will walk you through the necessary procedures to perform the following functions.
1. Create an email campaign in QuickBooks Point of Sale and export the information into an Excel spreadsheet.
2. Modify the data in the Excel spreadsheet into the proper format to allow it to be imported into Constant Contact.
3. Import the Excel spreadsheet into your contacts in Constant Contact.
This process is a workaround to the current issue with QuickBooks Point of Sale not being able to natively export your email campaign information to Constant Contact. Please note that this document will not walk you through each type of customer list that you can prepare in QuickBooks Point of Sale, it will only show you how to get the data from QuickBooks Point of Sale into an Excel spreadsheet, prepare the data in the Excel spreadsheet and then import the spreadsheet into Constant Contact.
1. Open QuickBooks Point of Sale.
2. Click on the Customers menu bar and then click on “Create an E-mail Campaign”.
3. Click “Next”.
4. Choose whichever type of customer list you would like to use and then click “Next”.
5. Continue through the application until you have prepared your customer list and are ready to export it to an Excel spreadsheet.
6. Once you are ready to export the data to an Excel spreadsheet, click the radio button next to “Export customer list to a file” and also the checkbox next to “Open Excel file after exporting” and then click “Finish”.
7. Select the location that you want to save the Excel spreadsheet in and then click “OK”.
8. You will be notified that the Excel spreadsheet was successfully created. Click “Close”.
9. The Excel spreadsheet that was created will now open and you will be presented with two columns of data (Full Name and Email Address).
10. Put your mouse cursor over row #1 in the header, right click and then click on “Insert”.
11. This will now insert a blank row into our spreadsheet at the top of the document.
12. Enter “Full Name” in cell A1, “Email Address” in cell B1, “First Name” in cells C1 and F1, and “Last Name” in cells D1 and G1.
13. Double click on the lines between each of the columns to have Excel automatically expand the columns to the width of the data entered.
14. Enter “=LEFT(A2,FIND(" ",A2,1)-1)” without the quotes in cell C2 and press enter. It will now extract the First Name from the data in cell A2 and place it in cell C2.
15. Enter “=RIGHT(A2,(LEN(A2)-FIND(" ",A2,1)))” without the quotes in cell D2 and press enter. It will now extract the Last Name from the data in cell A2 and place it in cell D2.
16. Now we need to expand the formulas in cells C2 and D2 to go all the way down to the end of the data.
17. To do this, first click on cell C2 to highlight it.
18. Now put your mouse cursor over the lower right hand corner of cell C2 until the mouse cursor becomes a small plus sign, then press and hold the mouse button.
19. With the mouse button pressed, drag the cursor down to the last row that has data.
20. Once the cursor has been dragged down to the last row of data, release the mouse button and you will now notice that the data for column C which shows First Name is now correctly populated with the first name from column A.
21. Repeat steps #16 through #20 to extract the Last Name into column D from column A.
22. Now we need to select columns C and D by clicking in column C, dragging over to column D and then releasing the mouse button.
23. Now that we have used formulas to separate both First and Last Names from the Full Name column, we need to copy the values of the cells into separate columns that we will use to import into Constant Contact.
24. With columns C and D selected, right click and then click on “Copy”.
25. Now we need to select columns F and G by clicking in column F, dragging over to column G and then releasing the mouse button.
26. With columns F and G selected, click on the Paste button, then click on Values.
27. This will copy the values of the results of the formulas into their own new columns of F for “First Name” and G for “Last Name”.
28. Select columns A, C and D and then delete those three columns.
29. Now select column B and delete it.
30. The only columns that have data now are column A (Email Address), column B (First Name) and column C (Last Name).
31. Save the Excel spreadsheet on your workstation and note both the name and location of the spreadsheet as it will be needed to import into Constant Contact.
32. Log into your Constant Contact account by visiting their website at http://www.constantcontact.com/index.jsp.
33. Once logged into your Constant Contact account, click on “Contacts”.
34. Click on “Add contacts”, then click on “Add from file” in the drop down menu.
35. Click “Choose File”.
36. You will now be presented with a window that will allow you to find and select your Excel spreadsheet that you will be importing. Please locate and select your Excel spreadsheet and then click on “Continue”.
37. You will now be shown a short preview of contacts from your Excel spreadsheet to verify that the correct columns of data from the spreadsheet are mapped to the correct fields of data in Constant Contact. If the Excel spreadsheet is prepared properly according to this document, the first field of data from the spreadsheet will automatically be mapped to the Constant Contact field named “Email address – other”. The second field of data from the Excel spreadsheet will automatically be mapped to the Constant Contact field named “First name”. Finally the third field of data from the Excel spreadsheet will automatically be mapped to the Constant Contact field named “Last name”. Once you have verified that the field mapping are correct, click “Continue”.
38. First make sure that the check mark for “I have permission to email these contacts” is checked and then click “Import”.
39. If there are any email addresses that Constant Contact finds that are not formatted correctly, it will notify you and ask you to either correct the email address or remove the field from the import. Once either process has been completed, the import will continue.
40. In this instance, there is an invalid character in the email address (“,”) and it needs to be corrected. Correct the email address and then click “Save Changes”.
41. The import of data from the Excel spreadsheet will have now completed successfully and you will be shown your list of contacts in Constant Contact.