Mail Merge using only Excel
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Mail Merge using only Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Mail Merge using only Excel

    I know this is possible, I just don't know how to do it.

    I have a form made in excel and data in another excel file.

    What I would like to have happen is there are 3 boxes, Course Number, Course, and Instructor, that change for each course and instructor. If we could get those three cells to read the data correctly from the data excel file and populate on the form excel file that would decrease the amount of time it takes for us to print up these evaluations.

    I made "mock" files that are exactly like what I'm working at. If anyone would like these files to play around and provide some help then you can email me at jessica.west@logan.edu

    Thank you.

  2. #2
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,531
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using only Excel

    Here is my solution. Place in a standard module of your source data, and run doMerge().



    Code:
    ' array list of fields to merge
    Dim strMergeFields() As String
    ' range where merge data comes from
    Dim rngSourceRange As Excel.Range
    
    ' path to workbook containing template
    Dim strTemplatePath As String
    ' name of merge sheet on template
    Dim strSheetName As String
    ' track user cancellation
    Dim cancelled As Boolean
    
    Private Sub initGlobals()
      Dim rngTemp As Excel.Range
      Dim wkbTemp As Excel.Workbook
    
      Dim iSize As Long
      Dim iCount As Long
      
      ' get source range
      On Error Resume Next
      Set rngSourceRange = Application.InputBox( _
        Prompt:="Select source data range. Include headers.", _
        Title:="Merge: Select Source Data", _
        Type:=8)
      On Error GoTo 0
      
      If rngSourceRange Is Nothing Then
        cancelled = True
        Exit Sub
      End If
      
      If (rngSourceRange.Rows.Count < 2) Then
        cancelled = True
        Call MsgBox("You must select a range with at least two rows.", _
                  vbOKOnly + vbExclamation, "Merge: Error")
        Exit Sub
      End If
      
      ' resize array as needed
      iSize = rngSourceRange.Columns.Count
      ReDim strMergeFields(1 To iSize)
      
      ' get template file name
      With Application.FileDialog(Office.MsoFileDialogType.msoFileDialogFilePicker)
        .AllowMultiSelect = False
        With .Filters
          .Clear
          .Add "Excel Files", "*.xl*"
        End With
        If .Show = False Then
          cancelled = True
          Exit Sub
        End If
        strTemplatePath = .SelectedItems(1)
      End With
      
      Set wkbTemp = Application.Workbooks.Open(strTemplatePath)
      wkbTemp.Activate
      
      ' get ranges to populate
      For iCount = LBound(strMergeFields) To UBound(strMergeFields)
        On Error Resume Next
        Set rngTemp = Application.InputBox( _
            Prompt:="Select range(s) to populate with " & _
                    rngSourceRange.Rows(1).Cells(iCount) & ". " & vbCrLf & _
                    "Hold Ctrl to select multiple cells.", _
            Title:="Merge: Select Merge Fields", _
            Type:=8)
        On Error GoTo 0
        If rngTemp Is Nothing Then
          cancelled = True
          Exit Sub
        End If
        strMergeFields(iCount) = rngTemp.Address
        If Len(strSheetName) = 0 Then
          strSheetName = Application.ActiveWorkbook.ActiveSheet.Name
        Else
          If (strSheetName <> Application.ActiveWorkbook.ActiveSheet.Name) Then
            cancelled = True
            Call MsgBox("Merge fields must be on the same sheet.", _
                vbOKOnly + vbCritical, "Merge: Error")
            wkbTemp.Close (False)
            Exit Sub
          End If
        End If
      Next iCount
      
      wkbTemp.Close (False)
    End Sub
    
    Public Sub doMerge()
      Dim iSourceRow As Long
      Dim iFieldNum As Long
      
      Dim wkbTemp As Excel.Workbook
      Dim wshTemp As Excel.Worksheet
      Dim strTemp As String
      
      Call initGlobals
      If (cancelled) Then Exit Sub
      
      Dim answer As VBA.VbMsgBoxResult
      
      answer = MsgBox("Create separate workbook for each record?", _
                vbYesNoCancel, "How you wanna rip it?")
      
      If answer = vbCancel Then Exit Sub
      
      Application.ScreenUpdating = False
      
      If answer = vbNo Then
        Set wkbTemp = Application.Workbooks.Add(strTemplatePath)
      End If
      ' go through all row records
      For iSourceRow = 2 To rngSourceRange.Rows.Count
        ' make a new workbook based on template
        If answer = vbYes Then
          Set wkbTemp = Application.Workbooks.Add(strTemplatePath)
          Set wshTemp = wkbTemp.Worksheets(strSheetName)
        Else
          wkbTemp.Worksheets(strSheetName).Copy _
              after:=wkbTemp.Worksheets(wkbTemp.Worksheets.Count)
          Set wshTemp = wkbTemp.Worksheets(wkbTemp.Worksheets.Count)
        End If
        
        ' populate fields
        For iFieldNum = LBound(strMergeFields) To UBound(strMergeFields)
          wshTemp.Range(strMergeFields(iFieldNum)).Value = _
              rngSourceRange.Cells(iSourceRow, iFieldNum).Value
        Next iFieldNum
        
        If answer = vbYes Then
          ' make a name for the new merge
          strTemp = ThisWorkbook.Path
          If Right$(strTemp, 1) <> "\" Then
            strTemp = strTemp & "\"
          End If
          strTemp = strTemp & Format(Now(), "yyyy-mm-dd_hhmmss_") & "merge_" & iSourceRow - 1
          
        ' save the file and close
          wkbTemp.SaveAs strTemp, ThisWorkbook.FileFormat
          wkbTemp.Close False
        End If
      Next iSourceRow
      
      If answer = vbNo Then
          ' make a name for the new merge
          strTemp = ThisWorkbook.Path
          If Right$(strTemp, 1) <> "\" Then
            strTemp = strTemp & "\"
          End If
          strTemp = strTemp & Format(Now(), "yyyy-mm-dd_hhmmss_") & "merge"
          
          Application.DisplayAlerts = False
          wkbTemp.Worksheets(strSheetName).Delete
          Application.DisplayAlerts = True
        ' save the file and close
          wkbTemp.SaveAs strTemp, ThisWorkbook.FileFormat
          wkbTemp.Close False
      End If
      
      Application.ScreenUpdating = False
      
      Call MsgBox("Merge completed!", vbOKOnly + vbInformation, "Merge: Completed")
    End Sub

  3. #3
    New Member
    Join Date
    Dec 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using only Excel

    Hi there,

    I keep trying to use this code but it always ends in an error (using Excel2007).

    The debugger highlights this code fragment:
    For iSourceRow = 2 To rngSourceRange.Rows.Count

    Any suggestions for how to correct it?

    I am simply using a test with 6 columns and 5 rows of data.

    Many thanks!

  4. #4
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,531
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using only Excel

    What is the error? What are the values for iSourceRow and rngSourceRange.Rows.Count when you get it?

  5. #5
    New Member
    Join Date
    Dec 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using only Excel

    That is the problem...I don't think any values are coming from the initGlobal...as when I put in a value to get past that error... the next error is another value that should be coming from initGlobal. Are the values passing from initGlobal if not declared as globals?

  6. #6
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,531
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using only Excel

    Nothing is passing to or from initGlobals, it just gets the data needed for the global declarations. I did it that way to avoid a large number of ByRef arguments, and didn't want to do the whole thing in a single proc.

    It's been a long time, but back when Jessica and I were working this out, it was tested with both 2003 and 2007 versions of Office, and worked as intended.

  7. #7
    New Member
    Join Date
    Dec 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using only Excel

    That is the problem...I don't think any values are coming from the initGlobal...as when I put in a value to get past that error... the next error is another value that should be coming from initGlobal. Are the values passing from initGlobal if not declared as globals?

    It says 'Object Required' and gives no value.

    If I replace:
    For iSourceRow = 2 To rngSourceRange.Rows.Count
    with:
    For iSourceRow = 2 To 6

    It gets past that error... but then the next 'Object required' comes...and

    If I then replace
    Set wkbTemp = Application.Workbooks.Add(strTemplatePath)
    with:
    'Set wkbTemp = Application.Workbooks.Add("Q:\Temp\test.xls")

    It gets past that error...

    and so on... the next error comes at:

    strSheetName = Application.ActiveWorkbook.ActiveSheet.Name


    When I look at the variables...they are all in the initGlobal... but I am not good enough to know why they aren't being passed.

    My next step was to try to declare each of those as globals in initGlobal, but was wondering if I am missing something.

    Thanks for the quick reply!

  8. #8
    New Member
    Join Date
    Dec 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using only Excel

    Quote Originally Posted by iliace View Post
    Nothing is passing to or from initGlobals, it just gets the data needed for the global declarations. I did it that way to avoid a large number of ByRef arguments, and didn't want to do the whole thing in a single proc.

    It's been a long time, but back when Jessica and I were working this out, it was tested with both 2003 and 2007 versions of Office, and worked as intended.

    Ok man... I understand... I'll try to find a workaround...thanks again though for replying.

  9. #9
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    3,531
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using only Excel

    I don't mind revisiting, I just can't recreate your error. When I copy the code exactly as above in its entirety, it works like it's supposed to. So if you're using only portions of the code, post what you have and we'll go from there.
    "A problem well-stated is a problem half-solved." -Charles F. Kettering

  10. #10
    New Member
    Join Date
    Dec 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Mail Merge using only Excel

    Well.. took the files home and they work perfectly! Must have been some sort of corruption due to Windows NT/XP/networked system at work for some reason. Looks like I now have a reason to work from home! LOL

    Quite frustrating though the amount of time I spent today trying to get it work at work.

    Thanks again.
    Last edited by Specialized; Dec 17th, 2010 at 02:20 PM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •