Automate Your Business With Microsoft Office And Excel
Wed 23rd May 2012
A major UK organisation uses an Access database to store customer records in the sales department and uses Outlook in the marketing department to email customers newsletters, promotions and special offers. However once customer records are added or updated in Access, the sales staff have to send the changed details to the marketing staff who then add these same details their Outlook address book. Much time is taken up manually sending data between the sales Access system and the marketing Outlook system. On some occasions the same information is duplicated and on others some vital customer data is not included in the Outlook address book because the Access and Outlook data fields did not match.
Senior staff got together with the IT manager to sort this out. They needed to be able to rapidly maintain and update customer data in the sales department, so Access is ideal for them with its easy to use data entry forms and various analysis tools to create special customer lists with particular interests.
They then needed to export these special customer lists directly to a format Outlook could use, including any custom fields used in Access, to include for example customer demographics data. Lastly they needed to be able to update the Outlook address book from this data and to be able to ensure the latest data was being used. Once done the marketing staff would then be free to concentrate on email marketing using the updated lists.
These steps were achieved like this. First the IT Manager created a command button on an Access form. Then she used some VBA coding (Visual Basic for Applications) to carry out specific actions when the button was clicked. The VBA code converted the special customer list in Access to a file format Outlook could use directly. Then the code saved the file to the network server used by both departments. The code created a file name which included the saved date and a version number.
The IT manager then turned her attention to Outlook. She created a new command button on the main Outlook menu bar which when clicked would run some more VBA code. This further VBA code automatically updated the Outlook address book to the new file previously saved on the department's network server and once updated the VBA code then displayed the new address book filename, ensuring the marketing staff always updated to the latest address book file.
Once in place the VBA code worked seamlessly allowing the sales staff to update customer records in their Access database on a daily basis, and then create an updated Outlook address book on the network server. The marketing staff in turn regularly updated their Outlook address book and then focused on creating and sending tailored marketing emails.
Both groups of staff experimented with different Access address book creation and Outlook address book update frequencies, and after an initial period the IT manager added some further VBA code which created a new marketing address book every hour, with the date and creation time included in the filename. In turn she also added some VBA in Outlook for the marketing team the automatically update the Outlook address book just after the Access data file creation, with a caption confirming the update file name and time.
And what preparation did this organisation need to achieve these results? There were several staff skilled in Access and Outlook usage already in the departments, so the custom Access tables and custom Outlook address book were created in-house. And for the VBA coding, the IT manager attended a VBA coding training course which specialized in coding between Office applications, and learned all about creating actions, Outlook address book objects and automatic updating.
The results have been significant saving in time, the sales department has more time to sell and the marketing department has more time to market. The email marketing has improved significantly resulting in higher response rates and more sales!
Original article appears here: