Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.outlook.program_vba,microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel as a data source for Outlook to email Word docs
Hello
Each fortnight I am given a report (in Excel) containing the details of all NEW team leaders and managers for our organisation. This report contains, amongst other things, the User ID and First Name of each person listed. I also have a standard letter (in Word) which needs to be emailed (via Outlook) to each of these people. WHAT I NEED In summary, I need a macro which will do the following: 1. Grab the first name of the person from the Excel spreadsheet and place it in the salutation of the letter in Word (i.e. Dear "first name") 2. Send this letter as an attachment in an email via Outlook, which in turn uses the User ID from the Excel spreadsheet as the email address. My preference would be to have a separate email for each person listed in the spreadsheet, and for the macro to process each name one by one until it hits a blank cell. If at all possible, I would also like to have the person's first name used in the actual email text. As such, I imagine the macro (or macros) would have to save the Word document with some sort of unique name (perhaps using the User ID in the filename) in a particular location before then using Outlook to open an existing email template, inserting the first name in the email text, attaching the appropriate letter and then sending it off to the correct recipient. Basically, we are trying to create a personalised experience for these people. I have cross-posted this to a group in each of the three applications (Excel, Word and Outlook) as I wasn't sure where to start? Any help would be greatly appreciated. I really hope this is possible... Joe. -- If you can measure it, you can improve it! |
#2
Posted to microsoft.public.excel.programming,microsoft.public.outlook.program_vba,microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel as a data source for Outlook to email Word docs
See the article "Mail Merge to E-mail with Attachments" at
http://word.mvps.org/FAQs/MailMerge/...ttachments.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Monomeeth" wrote in message ... Hello Each fortnight I am given a report (in Excel) containing the details of all NEW team leaders and managers for our organisation. This report contains, amongst other things, the User ID and First Name of each person listed. I also have a standard letter (in Word) which needs to be emailed (via Outlook) to each of these people. WHAT I NEED In summary, I need a macro which will do the following: 1. Grab the first name of the person from the Excel spreadsheet and place it in the salutation of the letter in Word (i.e. Dear "first name") 2. Send this letter as an attachment in an email via Outlook, which in turn uses the User ID from the Excel spreadsheet as the email address. My preference would be to have a separate email for each person listed in the spreadsheet, and for the macro to process each name one by one until it hits a blank cell. If at all possible, I would also like to have the person's first name used in the actual email text. As such, I imagine the macro (or macros) would have to save the Word document with some sort of unique name (perhaps using the User ID in the filename) in a particular location before then using Outlook to open an existing email template, inserting the first name in the email text, attaching the appropriate letter and then sending it off to the correct recipient. Basically, we are trying to create a personalised experience for these people. I have cross-posted this to a group in each of the three applications (Excel, Word and Outlook) as I wasn't sure where to start? Any help would be greatly appreciated. I really hope this is possible... Joe. -- If you can measure it, you can improve it! |
#3
Posted to microsoft.public.excel.programming,microsoft.public.outlook.program_vba,microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel as a data source for Outlook to email Word docs
Hi Doug,
Thanks very much for your help - I really do appreciate it. I have followed your link and have read the instructions a number of times. I'm not sure I have correctly created the Directory type mail merge main document, as I haven't done this before, so I'm a little uncertain. Nevertheless, I have created the macro in Word using your code. I've attached the macro to the actual mailmerge document. However, when I try to run it I get a Microsoft Visual Basic Compile error: "User-defined type not defined". When I click OK it highlights the following code: oOutlookApp As Outlook.Application This appears in row 6 of the macro code, after "Dim ". Are you able to suggest where I may be going wrong? I've definitely checked the Microsoft Outlook 11.0 Object Library checkbox under the TOOLS REFERENCES menu option in the Visual Basic Editor. Hope this makes sense. Thanks for your help! Joe. -- If you can measure it, you can improve it! "Doug Robbins - Word MVP" wrote: See the article "Mail Merge to E-mail with Attachments" at http://word.mvps.org/FAQs/MailMerge/...ttachments.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Monomeeth" wrote in message ... Hello Each fortnight I am given a report (in Excel) containing the details of all NEW team leaders and managers for our organisation. This report contains, amongst other things, the User ID and First Name of each person listed. I also have a standard letter (in Word) which needs to be emailed (via Outlook) to each of these people. WHAT I NEED In summary, I need a macro which will do the following: 1. Grab the first name of the person from the Excel spreadsheet and place it in the salutation of the letter in Word (i.e. Dear "first name") 2. Send this letter as an attachment in an email via Outlook, which in turn uses the User ID from the Excel spreadsheet as the email address. My preference would be to have a separate email for each person listed in the spreadsheet, and for the macro to process each name one by one until it hits a blank cell. If at all possible, I would also like to have the person's first name used in the actual email text. As such, I imagine the macro (or macros) would have to save the Word document with some sort of unique name (perhaps using the User ID in the filename) in a particular location before then using Outlook to open an existing email template, inserting the first name in the email text, attaching the appropriate letter and then sending it off to the correct recipient. Basically, we are trying to create a personalised experience for these people. I have cross-posted this to a group in each of the three applications (Excel, Word and Outlook) as I wasn't sure where to start? Any help would be greatly appreciated. I really hope this is possible... Joe. -- If you can measure it, you can improve it! |
#4
Posted to microsoft.public.excel.programming,microsoft.public.outlook.program_vba,microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel as a data source for Outlook to email Word docs
The problem is arising because the reference to the Outlook Object Library
is not being retained. I would suggest that you place the macro in the Normal.Dot template or in another template that you save in the Word\Startup folder so that it is available to any document that you create. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Monomeeth" wrote in message ... Hi Doug, Thanks very much for your help - I really do appreciate it. I have followed your link and have read the instructions a number of times. I'm not sure I have correctly created the Directory type mail merge main document, as I haven't done this before, so I'm a little uncertain. Nevertheless, I have created the macro in Word using your code. I've attached the macro to the actual mailmerge document. However, when I try to run it I get a Microsoft Visual Basic Compile error: "User-defined type not defined". When I click OK it highlights the following code: oOutlookApp As Outlook.Application This appears in row 6 of the macro code, after "Dim ". Are you able to suggest where I may be going wrong? I've definitely checked the Microsoft Outlook 11.0 Object Library checkbox under the TOOLS REFERENCES menu option in the Visual Basic Editor. Hope this makes sense. Thanks for your help! Joe. -- If you can measure it, you can improve it! "Doug Robbins - Word MVP" wrote: See the article "Mail Merge to E-mail with Attachments" at http://word.mvps.org/FAQs/MailMerge/...ttachments.htm -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Monomeeth" wrote in message ... Hello Each fortnight I am given a report (in Excel) containing the details of all NEW team leaders and managers for our organisation. This report contains, amongst other things, the User ID and First Name of each person listed. I also have a standard letter (in Word) which needs to be emailed (via Outlook) to each of these people. WHAT I NEED In summary, I need a macro which will do the following: 1. Grab the first name of the person from the Excel spreadsheet and place it in the salutation of the letter in Word (i.e. Dear "first name") 2. Send this letter as an attachment in an email via Outlook, which in turn uses the User ID from the Excel spreadsheet as the email address. My preference would be to have a separate email for each person listed in the spreadsheet, and for the macro to process each name one by one until it hits a blank cell. If at all possible, I would also like to have the person's first name used in the actual email text. As such, I imagine the macro (or macros) would have to save the Word document with some sort of unique name (perhaps using the User ID in the filename) in a particular location before then using Outlook to open an existing email template, inserting the first name in the email text, attaching the appropriate letter and then sending it off to the correct recipient. Basically, we are trying to create a personalised experience for these people. I have cross-posted this to a group in each of the three applications (Excel, Word and Outlook) as I wasn't sure where to start? Any help would be greatly appreciated. I really hope this is possible... Joe. -- If you can measure it, you can improve it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VBA toggle Outlook '03 Word email editor on/off | Excel Programming | |||
Creating Word Docs from Excel Data | Excel Discussion (Misc queries) | |||
Import cells from excel into word and create multiple word docs | Excel Programming | |||
Embedding Word Docs into Excel Worksheets and Then Printing The Word Docs | Excel Worksheet Functions | |||
converting excel docs to word docs | New Users to Excel |