Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Closed Thread
 
Thread Tools Display Modes
  #1  
Old 04-14-2014, 03:45 AM
Vincent Vincent is offline Using Excel data to automatically make Word documents Windows 8 Using Excel data to automatically make Word documents Office 2013
Novice
Using Excel data to automatically make Word documents
 
Join Date: Apr 2014
Location: France
Posts: 8
Vincent is on a distinguished road
Smile 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
E-mail

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  
Old 04-14-2014, 05:10 AM
macropod's Avatar
macropod macropod is offline Using Excel data to automatically make Word documents Windows 7 32bit Using Excel data to automatically make Word documents Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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  
Old 04-14-2014, 06:08 AM
Vincent Vincent is offline Using Excel data to automatically make Word documents Windows 8 Using Excel data to automatically make Word documents Office 2013
Novice
Using Excel data to automatically make Word documents
 
Join Date: Apr 2014
Location: France
Posts: 8
Vincent is on a distinguished road
Default

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  
Old 04-14-2014, 02:10 PM
macropod's Avatar
macropod macropod is offline Using Excel data to automatically make Word documents Windows 7 32bit Using Excel data to automatically make Word documents Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
Note: the above macro is coded to get the filenames from two of the data fields, via the line:
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  
Old 04-16-2014, 06:08 AM
Vincent Vincent is offline Using Excel data to automatically make Word documents Windows 8 Using Excel data to automatically make Word documents Office 2013
Novice
Using Excel data to automatically make Word documents
 
Join Date: Apr 2014
Location: France
Posts: 8
Vincent is on a distinguished road
Default

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  
Old 04-16-2014, 08:14 AM
Vincent Vincent is offline Using Excel data to automatically make Word documents Windows 8 Using Excel data to automatically make Word documents Office 2013
Novice
Using Excel data to automatically make Word documents
 
Join Date: Apr 2014
Location: France
Posts: 8
Vincent is on a distinguished road
Default

I've found where to paste macros, however nothing seems to happen when I run it.

What am I doing wrong...? Thanks
  #7  
Old 04-16-2014, 03:15 PM
macropod's Avatar
macropod macropod is offline Using Excel data to automatically make Word documents Windows 7 32bit Using Excel data to automatically make Word documents Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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  
Old 04-30-2014, 05:20 AM
Vincent Vincent is offline Using Excel data to automatically make Word documents Windows 8 Using Excel data to automatically make Word documents Office 2013
Novice
Using Excel data to automatically make Word documents
 
Join Date: Apr 2014
Location: France
Posts: 8
Vincent is on a distinguished road
Default

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  
Old 01-04-2021, 02:57 PM
Angel21 Angel21 is offline Using Excel data to automatically make Word documents Windows Vista Using Excel data to automatically make Word documents Office 2010
Novice
 
Join Date: Jan 2021
Posts: 1
Angel21 is on a distinguished road
Default Mail Merge Docs Save as pdf - Code in VBA

Quote:
Originally Posted by macropod View Post
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
Note: the above macro is coded to get the filenames from two of the data fields, via the line:
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.
Hi

Please can you let me how to change the code to save the pdfs file in a location of my choice

Thanks
  #10  
Old 01-04-2021, 03:05 PM
macropod's Avatar
macropod macropod is offline Using Excel data to automatically make Word documents Windows 10 Using Excel data to automatically make Word documents Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Closed Thread

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Excel data to automatically make Word documents How to automatically sum in excel for filtered data msi_g Excel 2 08-05-2013 05:29 AM
Using Excel data to automatically make Word documents How to create an Excel add in that will automatically build tables from data selvamariappan Excel Programming 1 12-12-2011 03:11 AM
Using Excel data to automatically make Word documents How to copy automatically data from Excel file to Word file? fuchsd Word 6 10-25-2011 05:52 AM
Using Excel data to automatically make Word documents 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:17 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft