Dear Pascal Thanks for the responses. I am reply each response separately so as not to confuse other readers
I can NOT use Paper mail / snail mail or postal mail as you mail call it Unfortunately postal mail is NOT fast enough, takes many days to reach (customers at) other places, and IF the customer faces a discrepancy it takes again a lot of time for him to respond via another postal mail ....so I am forced to use fax or e mail for instant reminders; I explained the problem with fax earlier, so the only option left is e mail I think others have understood this correctly. Yes I am requesting a way to e mail these letters I shall answer your other suggestion to change the VBA in the next mail -- Regards Subu mail_to_s...@yahoo.com ________________________________ From: bpascal123 <bpascal...@gmail.com> To: excel-macros@googlegroups.com Sent: Wednesday, 26 December 2012, 23:38 Subject: $$Excel-Macros$$ Re: Mail merge and E mail : Reminder email to customers using VBA Hi, Happy new year and all... About your query it seems you have 2 queries, one related to mail merge (paper) and one to email. I have been working on these for my own needs, with more or less access to the content... For mail merge using email, it seems there are many resources available online to automate emails from Excel to Outlook with customs email addresses, subject, body and so on For mail merge using paper (for faxing or post mailing), most available infos is about Word and its mail merge tool. For that, at this time, I have just managed to make the data "fit" the mail merge tools, like Customer# Customer_name invoiceNum_1 invoiceDate_1 invoiceDueDate_1 InvoiceDueAmt_1 invoiceNum_2 invoiceDate_2 invoiceDueDate_2 InvoiceDueAmt_2 … invoiceItem_8 address email Total Dues 12345 The fancy paint shop AX4562 2012-01-10 30/11/2012 11289.6 AX4653 2012-02-10 30/11/2012 45678.45 PO Box 322,Madras fancy_pa...@hotmail.com 68203.83 For one customer, using Word Mail Merge, the data for one customer must be like above... Your data is also a bit difficult to deal with, to implement it there might be changes in the account info sheet as invoice AX4653 comes with multiple invoice date, due date and amount due. Maybe it's a typo because for one invoice, there should only be be one invoice date... unless you have specific requirements... Once your data is laid like that you can start the mail merge from Word. The header fields as shown above should help Word match its mail merge fields as there may be some confusion using an incremental number invoiceNum_1, invoiceDate_2... in relation to the column position of each field... My issue using mail merge comes when invoice information are displayed in a table or in a tabular way following your letter layout. There Word mail merge decides of the number of rows in the table from the customer that has the maximum number of due invoices. Everyone else will have a table size of this customer. I'm not an expert of Mail Merge but maybe there is an option somewhere that makes Word adjusts the number of rows in the mail merge tool accordingly to the number of real invoices for one customer, in your data, it seem there is the same number of invoice for each customer ... if one day a customer has ten invoices, there will be 10 rows on every table... At this stage, I have deleted manually all empty rows while checking every letters... If the number of letters is significative, this might be difficult... Is anyone aware of something to do to "fix" this? I would think of a macro that would loop through every table and delete empty rows once the mail merge is complete. Else, coding with vba the Word mail merge instead of doing it manually from the tool bar might allow more flexibility... Can someone advise? Using Word mail merge is the fastest possible option. Else copy paste tables from Excel would require a significative amount of time and cpu activity. But it's then easy to copy only the needed number of rows. One last thing is that tables are of Excel range objects... instead of html or xml (loss of word document style). Excel range object in Word also make files heavier. If you could follow me until here, I can help you to the point where I am now... presenting the data for Word mail merge tool. Using emails, things are a little different since most actions can be automated from within Excel to Outlook or most email clients. For this, I'm also facing an issue... is it possible to code an email html table from Excel using vba rather than pasting an Excel object range? I spent some time looking for anything related to this on web sites without any success. It seems this is possible doing so from Excel to Word as there is a specific table object/method available from referencing Word object collections in Excel. Is there anything similar with email, Outlook? Thanks Pascal -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
