Mailmerge Excel to Excel

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
Hi everyone,

I need to create a mailmerge from Excel and use Excel for the mailmerge.

I discovered the code below from Mail Merge in Excel - Microsoft: Office FAQ - Tek-Tips

Code:
Function RangeName(sName As String) As String
    RangeName = Application.Substitute(sName, " ", "_")
End Function
Sub MergePrint()
'set up your merge form by naming the merge fields _
 with the same name as the data fields you are importing.
'if any data field contains spaces, then substitute an _
 UNDERSCORE character for each space in the name.
    Dim wsForm As Worksheet, wsData As Worksheet
    Dim sRngName As String, r As Long, c As Integer
    Set wsForm = Worksheets("My Form") 'change to your sheet name
    Set wsData = Worksheets("My Data") 'change to your sheet name
    With wsData.Cells(1, 1).CurrentRegion
        For r = 2 To .Rows.Count
            If Not wsData.Cells(r, 1).EntireRow.Hidden Then
                For c = 1 To .Columns.Count
                    sRngName = wsData.Cells(1, c).Value
                    Range(RangeName(sRngName)).Value = wsData.Cells(r, c)
                Next
                wsForm.PrintOut
            End If
        Next
    End With
End Sub

which works great and I have amended to save each sheet as the mailmerged file.

However it has a drawback where it expects a named range for every header name in the Data sheet.
I have more headers than named ranges required on the Excel form, as I need extra info on the mailmerge, date created, combining several columns etc.

I know how to hide the columns I am not using in the merge, but what would I need to change to only process visible columns?

TIA
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
Really surprised, no one chipped in on this one.
I've found a solution, not particularly elegant, but it works.

Code:
                strColName = fnColumnToLetter(c)
                    If ActiveSheet.Range(strColName & ":" & strColName).EntireColumn.Hidden = False Then
                        sRngName = wsData.Cells(1, c).Value
                        Workbooks(strTemplate).Sheets("Invoice").Range(RangeName(sRngName)).Value = wsData.Cells(r, c)
                    End If
                Next
and
Code:
' Get column letter from number
Function fnColumnToLetter(ByVal ColumnNumber As Integer)
fnColumnToLetter = Replace(Replace(Cells(1, ColumnNumber).Address, "1", ""), "$", "")
End Function
The function was obtained from VBA Column Number to Name-Explained with Excel Examples
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
Late to the party, but you don't need those extra functions:

Code:
Sub MergePrint()
  ' set up your merge form by naming the merge fields _
    with the same name as the data fields you are importing.
  ' if any data field contains spaces, then substitute an _
    UNDERSCORE character for each space in the name.

  Dim wksForm       As Worksheet
  Dim wksData       As Worksheet
  Dim rInp          As Range
  Dim iRow          As Long
  Dim iCol          As Long

  Set wksForm = Worksheets("My Form")  'change to your sheet name
  Set wksData = Worksheets("My Data")  'change to your sheet name

  With wksData
    Set rInp = .Range("A1").CurrentRegion
    For iRow = 2 To rInp.Rows.Count
      If Not .Rows(iRow).Hidden Then
        For iCol = 1 To rInp.Columns.Count
          If Not Columns(iCol).Hidden Then
            Range(Replace(.Cells(1, iCol).Value, " ", "_")).Value = .Cells(iRow, iCol).Value
          End If
        Next iCol
        wksForm.PrintOut
      End If
    Next
  End With
End Sub

EDIT: This reference
Code:
Range(Replace(....
... is not qualified. Does the named range have workbook scope?
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
shg,
I'm always up for tidying code. :)
The original code expected the merge and data sheets to be in the same workbook.
I realised I needed a template due to formatting and pictures in the output workbook.

So for that reason I created a template which I open at the start and save with different names each time.
So, if I understand you correctly, yes it does, the scope is the workbook whose name is in strTemplate.
Code:
Workbooks(strTemplate).Sheets("Invoice").Range(RangeName(sRngName)).Value = wsData.Cells(r, c)

I'm off on holiday Wednesday, so doubt I will get a chance to test your version until my return.

Thank you for posting.
 

MALEPINZON

New Member
Joined
Apr 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi, I'm so much interested in this case, I'd like to see the columns I am not using in the merge so I really need this colums remains in de Data sourse,

Late to the party, but you don't need those extra functions:

Code:
Sub MergePrint()
  ' set up your merge form by naming the merge fields _
    with the same name as the data fields you are importing.
  ' if any data field contains spaces, then substitute an _
    UNDERSCORE character for each space in the name.

  Dim wksForm       As Worksheet
  Dim wksData       As Worksheet
  Dim rInp          As Range
  Dim iRow          As Long
  Dim iCol          As Long

  Set wksForm = Worksheets("My Form")  'change to your sheet name
  Set wksData = Worksheets("My Data")  'change to your sheet name

  With wksData
    Set rInp = .Range("A1").CurrentRegion
    For iRow = 2 To rInp.Rows.Count
      If Not .Rows(iRow).Hidden Then
        For iCol = 1 To rInp.Columns.Count
          If Not Columns(iCol).Hidden Then
            Range(Replace(.Cells(1, iCol).Value, " ", "_")).Value = .Cells(iRow, iCol).Value
          End If
        Next iCol
        wksForm.PrintOut
      End If
    Next
  End With
End Sub

EDIT: This reference
Code:
Range(Replace(....
... is not qualified. Does the named range have workbook scope?

Hello, I'm not an expert, so I don't understand why this error happen . Could you give me a more detailed example?


Miguel P



1618719786479.png
 
Last edited by a moderator:

MALEPINZON

New Member
Joined
Apr 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Really surprised, no one chipped in on this one.
I've found a solution, not particularly elegant, but it works.

Code:
                strColName = fnColumnToLetter(c)
                    If ActiveSheet.Range(strColName & ":" & strColName).EntireColumn.Hidden = False Then
                        sRngName = wsData.Cells(1, c).Value
                        Workbooks(strTemplate).Sheets("Invoice").Range(RangeName(sRngName)).Value = wsData.Cells(r, c)
                    End If
                Next
and
Code:
' Get column letter from number
Function fnColumnToLetter(ByVal ColumnNumber As Integer)
fnColumnToLetter = Replace(Replace(Cells(1, ColumnNumber).Address, "1", ""), "$", "")
End Function
The function was obtained from VBA Column Number to Name-Explained with Excel Examples
Hello, I'm interest in the solution to this problen, could yo send me back the complete code ? Thanks

Miguel P
 
Last edited by a moderator:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
Wow!, talk about a blast from the past. :)

Sorry no, that when I was working at a particular firm. I am now retired.
The best I can suggest is use the links I posted. I can't even remember what that was for either. :(
 

Watch MrExcel Video

Forum statistics

Threads
1,129,467
Messages
5,636,456
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top