PDA

View Full Version : Macro to generate multiple documents and send specific ones to specific emails



Storm85
12-21-2012, 07:32 AM
I will have to start my request for help with a note, as most do on these forums, that I am new to VB and to forgive my ignorance. Using various examples of code cobbled together I have managed to get relatively far in my project. However I have come to a grinding halt. I saw a related post on this forum and thought I would stick my head above the parapet and ask for help.

To date i have been generating single invoices using a word template and data from a table with a macro. The macro opens Word and the invoice template, which has several bookmarks. The macro copies data from certain cells and pastes values into the correct locations/bookmarks. All the text is then copied and pasted into a new document with a unique filename: standard text "invoice", the name of the client (taken from a cell value) and date stamp in the filename. this is then saved into the Invmoice sub folder in a main folder with the name name as the client. Each client has their own folder. the macro uses the same cell value for client name to determine the save location and saves it there as a PDF.
The problem is twofold: one is that the data from which the macro runs is taken from a sheet containing data looked up from a master table (on a separate sheet), with each client having their own worksheet of summarised data. The user has to go to each client's sheet and run the macro. This takes time.
The second is that I want to email all of these invoices to the clients - obviously the correct invoice needs attaching to the correct email for that client.
So I need a macro which can generate all the invoices using the bookmark method, up to 30, save them in the right folders, and then Email them to the right people. For this example assume column A has the client name, B has the clients email and columns C to G info for the five bookmarks. If I can add in standard text for the email subject and then standard text in the body with a reference to the invoice period date, say taken from column H. Outlook account is no. 2.

I hope that's clear and not asking too much! Any help would be much appreciated.

mdmackillop
12-24-2012, 11:45 AM
Check out this thread

http://www.vbaexpress.com/forum/showthread.php?p=242546