#1
|
|||
|
|||
Using Excel data to automatically make Word documents
Hello everyone,
This is my first post on this forum I am not totally sure I'm posting in the right section but hopefully you guys will tell me and move this thread if need be. I have tried searching past threads for an answer to my question but couldn't find anything. To be honest I am not really sure how to phrase my question either, so please feel free to modify this thread's title so it can help others too Now here's my question: I am currently looking for a job and I have gathered data about people and companies I would like to get in touch with. I have a fairly large number of people to contact now (80) and I really don't want to go through the hassle of updating my cover letter 80 times manually, especially since I want to tell them exactly the same thing! So I thought to myself, there must be a way to automate that process. I have an Excel document with 8 columns: First name Name Company Address line 1 Postcode City Phone I'd like this data to be sucked automatically into the "recipient" field of my cover letter and, ideally saved as a PDF, then even more ideally if it could be sent via e-mail automatically then it'd be WONDERFUL! It would also be truly fantastic if the e-mail could be customised to each recipient! No idea how to do that though. I can't code, I don't know macros, I didn't do computer science at uni. Can you guys think of a clever yet simple way to go about this task? Thank you so much for your attention! Kind regards Vincent |
#2
|
||||
|
||||
You could create the letters via mailmerge, then email them. A standard email mailmerge can send your letter in the email body, but not as an attachment (which is what you need to send a PDF). For that, see: http://word.mvps.org/FAQs/MailMerge/...ttachments.htm
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks for your reply Macropod.
From what I understand I should get my letters ready first and then start e-maling them. Right? I should be able to get my letters ready by following these steps: http://office.microsoft.com/en-001/w...102809678.aspx , however that won't make a PDF copy of each of them. How do I do that? Once my letters are all ready to be sent, how do I automate the process of sending them all? Thanks again |
#4
|
||||
|
||||
To create the individual PDFs, you could drive the merge with a macro like:
Code:
Sub Merge_To_Individual_Files() 'Merges one record at a time to the chosen output folder Application.ScreenUpdating = False Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j as long Set MainDoc = ActiveDocument With MainDoc StrFolder = .Path & "\" For i = 1 To .MailMerge.DataSource.RecordCount With .MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = i .LastRecord = i .ActiveRecord = i If Trim(.DataFields("Last_Name")) = "" Then Exit For StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name") End With .Execute Pause:=False End With For j = 1 To 255 Select Case j Case 1 To 31, 33 To 45, 47, 58 To 64, 91 To 94, 96, 123 To 141, 143 To 149, 152 To 157, 160 To 180, 182 To 191 StrName = Replace(StrName, Chr(j), "") End Select Next StrName = Trim(StrName) With ActiveDocument .SaveAs2 FileName:=StrPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False .Close SaveChanges:=False End With Next i End With Application.ScreenUpdating = True End Sub StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name") You will need to change that lines to reference the data field(s) you want to use. The output PDFs will be saved in the same folder you keep your mailmerge main document in.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Dear Paul,
Thank you for your reply... However as I said I don't know how to use macros, how do I use this? Thank you so much for your help |
#6
|
|||
|
|||
I've found where to paste macros, however nothing seems to happen when I run it.
What am I doing wrong...? Thanks |
#7
|
||||
|
||||
Did you check the folder containing your mailmerge main document for the PDFs after running the macro? Did you add the macro to your mailmerge main document? Did you change the field names to suit your data, as indicated? Does your mailmerge main document generate an SQL prompt when you open it?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
Dear Macropod, dear forumers,
Thank you for your help! I did not what folder to check, so eventually I decided to just do this: -Clean up a bit my list in Excel -Write a message in Word; pair this file with my list; insert a few fields/variables in the text (so that "GENDER" "NAME" eventually read "Mr. A.", "Ms. B", etc.) -Click on Send (=Outlook sent my 80 mails). The text featured a line that went like "Feel free to ask me for a CV" + had a link to my LinkedIn page in it. I don't know much about mass-mailing, spams etc (I don't know much about computers in general compared to you!), but it also occurred to me that an e-mail from a stranger with 2 attachments would make it likely to end up in the junk folder. I'm getting replies now Thanks for your help. Macropod will you let me know what folder I should have looked into? I'd be curious to know if the macro worked all right, I might even try to make it work and learn something. Cheers |
#9
|
|||
|
|||
Mail Merge Docs Save as pdf - Code in VBA
Quote:
Please can you let me how to change the code to save the pdfs file in a location of my choice Thanks |
#10
|
||||
|
||||
Please don't resurrect ancient threads - this one was last active nearly 6 years ago.
For the general answer to your question, see Split Merged Output to Separate Documents or, better still, Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum: https://www.msofficeforums.com/mail-...ps-tricks.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to automatically sum in excel for filtered data | msi_g | Excel | 2 | 08-05-2013 05:29 AM |
How to create an Excel add in that will automatically build tables from data | selvamariappan | Excel Programming | 1 | 12-12-2011 03:11 AM |
How to copy automatically data from Excel file to Word file? | fuchsd | Word | 6 | 10-25-2011 05:52 AM |
Old Excel Document folder automatically created in My Documents | slickcondo | Office | 6 | 07-13-2011 02:31 AM |
Automatically entering/fill data in cells in Excel 2003 | dipdog | Excel | 0 | 08-17-2006 08:37 AM |