SUMMARY: I've got a sheet of data that I need to have exported to Word via a mail merge, and this specific task happens regularly enough that I want to automate it, especially for other, not particularly tech-savvy, people who have to do this from time to time when I'm not available. Is there a way to use VBA to open a mail merge template from a specified location and then run the merge based on the currently-open spreadsheet?
Context and details:
I run the peer feedback surveys at my company, which is a series of either 6 or 9 competencies (depending on role) that you rate the person on, and optionally provide comments on each as well. I have a macro that I wrote to clean up the data that I get out of SurveyMonkey, part of which splits the comments out from the numerical ratings and puts them on their own worksheet.
The resulting comments worksheet has a first column for the name of the respondent (or Anon if they chose not to put their name), then the next 6-9 columns are headed with the competency names, and each row consists of one respondent's name and then their comments in each area. Some people have comments in all columns, some don't have comments at all but they still get their own row, and some leave comments in one area but not another, so there's an unpredictable number of blank cells in the mix.
Senior management has asked if there's a way to export the peer feedback comments to a word document, arranged by competency, with each respondent's name and comment on that area if there was one. So it would look like:
Comments for Sandra S
Standards of Excellence
and so on. If I rearrange the comments sheet so that the rows are the competencies, and the columns are Topic, Name1, Comment1, Name2, Comment2, etc. I can use mail merge to get what they're asking for. And I can probably get the VBA to reformat the comments into that layout worked out on my own. I think.
What I'm hoping to find out, though, is if there's a way to use VBA to open a mail merge template document from a specified folder and run the merge based on the currently open spreadsheet? I'm looking to minimize the number of active steps required, since there are people other than me who have to take this process over when I'm not available, and not all of those people are particularly tech-savvy. The less complicated I can make this, the better.
Thanks!
Context and details:
I run the peer feedback surveys at my company, which is a series of either 6 or 9 competencies (depending on role) that you rate the person on, and optionally provide comments on each as well. I have a macro that I wrote to clean up the data that I get out of SurveyMonkey, part of which splits the comments out from the numerical ratings and puts them on their own worksheet.
The resulting comments worksheet has a first column for the name of the respondent (or Anon if they chose not to put their name), then the next 6-9 columns are headed with the competency names, and each row consists of one respondent's name and then their comments in each area. Some people have comments in all columns, some don't have comments at all but they still get their own row, and some leave comments in one area but not another, so there's an unpredictable number of blank cells in the mix.
Senior management has asked if there's a way to export the peer feedback comments to a word document, arranged by competency, with each respondent's name and comment on that area if there was one. So it would look like:
Comments for Sandra S
Standards of Excellence
- Eric T: [blah blah blah]
- Anon: [blah blah blah]
- Anon: [blah blah blah]
- Rosa K: [blah blah blah]
- Anon: [blah blah blah]
- Eric T: [blah blah blah]
- Rosa K: [blah blah blah]
- Anon: [blah blah blah]
and so on. If I rearrange the comments sheet so that the rows are the competencies, and the columns are Topic, Name1, Comment1, Name2, Comment2, etc. I can use mail merge to get what they're asking for. And I can probably get the VBA to reformat the comments into that layout worked out on my own. I think.
What I'm hoping to find out, though, is if there's a way to use VBA to open a mail merge template document from a specified folder and run the merge based on the currently open spreadsheet? I'm looking to minimize the number of active steps required, since there are people other than me who have to take this process over when I'm not available, and not all of those people are particularly tech-savvy. The less complicated I can make this, the better.
Thanks!
