Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-20-2014, 11:22 AM
Mjpunj Mjpunj is offline Typing Mail Merge Fields instead of clicking them? Windows 7 32bit Typing Mail Merge Fields instead of clicking them? Office 2010 32bit
Novice
Typing Mail Merge Fields instead of clicking them?
 
Join Date: Jun 2014
Posts: 7
Mjpunj is on a distinguished road
Default Typing Mail Merge Fields instead of clicking them?


I am using mail merge to send out an email that contains a spreadsheet that has over 149 fields total. I would really rather use EXCEL to create the table and have mail merge fill in the fields based on the text that's in them rather than me having to click and "insert field" 149 times.

The attached photo is what I have so far, with the M_35 field being part of the table I created in Excel, and not part of the actual mail merge program.
Attached Images
File Type: jpg mail.JPG (140.4 KB, 8 views)
Reply With Quote
  #2  
Old 06-20-2014, 05:28 PM
macropod's Avatar
macropod macropod is offline Typing Mail Merge Fields instead of clicking them? Windows 7 32bit Typing Mail Merge Fields instead of clicking them? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,234
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

You can't do it that way. For starters, the chevrons (i.e. ' ') cannot be inserted as text - they're part of the actual mergefields.

Instead, you could use a macro like the following, to add the active sheet's header row names (assumed to be on row 1, as per normal mailmerge configuration) to the designated document as mergefields. You will need to change the file reference in StrDocNm to point to your folder & document. As coded, it looks for a file named 'Document Name.doc' in your Documents folder.

Code:
Sub Demo()
Application.ScreenUpdating = True
Dim wdApp As Word.Application, wdDoc As Word.Document, StrDocNm As String
Dim bStrt As Boolean, bFound As Boolean
Dim xlWkSht As Worksheet, i As Long, lCol As Long
'Check whether the document exists
StrDocNm = "C:\Users\" & Environ("Username") & "\Documents\Document Name.doc"
If Dir(StrDocNm) = "" Then
  MsgBox "Cannot find the designated document: " & StrDocNm, vbExclamation
  Exit Sub
End If
' Test whether Word is already running.
On Error Resume Next
bStrt = False ' Flag to record if we start Word, so we can close it later.
Set wdApp = GetObject(, "Word.Application")
'Start Word if it isn't running
If wdApp Is Nothing Then
  Set wdApp = CreateObject("Word.Application")
  If wdApp Is Nothing Then
    MsgBox "Can't start Word.", vbExclamation
    Exit Sub
  End If
  ' Record that we've started Word, so we can terminate it later.
  bStrt = True
End If
On Error GoTo 0
'Check if the workbook is open.
bFound = False
With wdApp
  'Hide our Word session
  If bStrt = True Then .Visible = False
  For Each wdDoc In .Documents
    If wdDoc.FullName = StrDocNm Then ' We already have it open
      bFound = True
      Exit For
    End If
  Next
  ' If not open by the current user.
  If bFound = False Then
    ' Check if another user has it open.
    If IsFileLocked(StrDocNm) = True Then
      ' Report and exit if true
      MsgBox "The Word document is in use." & vbCr & "Please try again later.", vbExclamation, "File in use"
      If bStrt = True Then .Quit
      Exit Sub
    End If
    ' The file is available, so open it.
    Set wdDoc = .Documents.Open(Filename:=StrDocNm, AddToRecentFiles:=False, ReadOnly:=False)
    If wdDoc Is Nothing Then
      MsgBox "Cannot open:" & vbCr & StrDocNm, vbExclamation
      If bStrt = True Then .Quit
      Exit Sub
    End If
  End If
  'Reference our worksheet
  Set xlWkSht = ActiveSheet
  'get the column count
  lCol = xlWkSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Column
  With wdDoc
    'Only now can we can process the document!!!
    'Add a mergefield to the document, corresponding to each 'heading' cell in our worksheet.
    For i = 1 To lCol
      If Trim(xlWkSht.Cells(1, i).Text) <> "" Then
        .Fields.Add Range:=.Characters.Last, Type:=wdFieldEmpty, _
        Text:="MERGEFIELD " & xlWkSht.Cells(1, i).Text, PreserveFormatting:=False
      End If
    Next
    If bFound = False Then .Close SaveChanges:=True
  End With
  If bStrt = True Then .Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing: Set xlWkSht = Nothing
End Sub
Function IsFileLocked(strFileName As String) As Boolean
  On Error Resume Next
  Open strFileName For Binary Access Read Write Lock Read Write As #1
  Close #1
  IsFileLocked = Err.Number
  Err.Clear
End Function
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge Directory - Combining fields officegirl Mail Merge 1 03-21-2013 11:02 PM
Typing Mail Merge Fields instead of clicking them? Mail Merge only produces 15 fields fm 22 Galceran Mail Merge 2 02-10-2013 05:47 PM
Typing Mail Merge Fields instead of clicking them? Conditional merge fields in mail merge Aude Mail Merge 1 01-06-2012 07:38 PM
Typing Mail Merge Fields instead of clicking them? Mail Merge truncating 'some' of the text fields......Not sure what to do sssb2000 Mail Merge 5 09-26-2010 09:40 AM
Mail Merge Fitting Text to Fields frnk4760 Mail Merge 1 05-18-2010 04:51 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:52 AM.


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