Mail Merge with Grouping Key Field from Excel or SQL Databases

Question

I have searched the internet to find a solution, but have, so far, been unsuccessful. I have intermediate skills in VBA and SQL but have been unable to create what I need.

I have a Database with the following info.

  Co.Name |Email        | Product  |    Q    |
    SH1   |info@sh.com  | Orange   | 105.566 |
    SH1   |info@sh.com  | Berries  | 200.000 |
    BT1   |info@bt.com  | Orange   | 300.000 |
    BT4   |info@bt.com  | Apple    | 101.700 |
    WT1   |info@wt.com  | Berries  | 201.040 |
    WT6   |info@wt.com  | Apple    | 204.000 |

Co.Name refers to Company_Name, and Q for quantity.

I need a mail merge that inserts an exclusive table using a key field to form the table.

In the dummy table before, the key field could be the company name, or the company mail.

**Email**
To: <emailcompany1>
Subject: Enquiry of <productname1>, <productname2>, ..., <productnamei>

Dear sirs from <company_name1>

In name of StackOverflow Inc, I'm requesting a price quotation and
availability of the following products so we can feed this huge 
and awesome online community:

!-- Here it has to insert an specific table for the company name
!-- so it only shows the information regarding that company.!

Table<CompanyName1>
| Product     |    Q       |
| Product 1   | Q. Prod 1  |
| Product 2   | Q. Prod 2  |
| Product ... | Q. Prod ...|
| Product i   | Q. Prod  i |


Regards
StackOverflow Supply Manager
<end mail, and repeat for other company, 
until there are no more companies..>

I've tried to do this with Word Mail Merge to Outlook and using an Excel datasheet with a small dummy database. I've found this https://support.microsoft.com/en-us/kb/294686, but I can't manage to make it do what it is suppose to do.

EDIT: I have been partially successful with https://support.microsoft.com/en-us/kb/294686. Currently, I'm having problem formatting the table. If I put the table as 2x2, and there are 4 products, it stops working.

I have a large database with varied data points and variables which will require many emails. I need a system that can handle this load.

To clarify: I need to Mail merge and insert an specific table regarding a Key Field** (in the dummy case the key field is they company_name or the company_email).

I'm fluent in SQL queries, and use Microsoft SQL Server, but I haven't been able to solve it in Excel, so I don't want to use SQL connections yet. The database is currently in SQL, and I have access to SQL, Excel, Access, Google apps (Gmail in the office), etc.

If there is a paid program for it, or some macro, I could talk to my boss to see if we could buy it. Everything is faster than sending the emails one by one by hand.

Answer 1

After trying different approaches (Fmtp server of the company, google script, macros) I decided it was out of my league, and I contracted an expert on by freelancer .com

The programmer did a VBA code that uses the excel file as the database in one sheet, and the second sheet is for the template of the e-mail.

Then it connects with outlook and send the message. The table gets inserted via HTML on outlook.

The hardest part that we had was adding the signature with a LOGO, but we solved it together using GetBoiler function on VBA:

Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.ReadAll
ts.Close
End Function

Signature
SigString = Environ("appdata") & "\Microsoft\Signatures\test_mailing.htm 
'change_text_mailing.htm with correct path

If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If

In the end, it cost me 100 USD to make the program, it's fully costumizable and I've sent 1.000+ mails daily with no problem.

The only small problem with this approach, is that GMAIL doesn't read HTML code properly, so when sending the mail to someone that opens it on GMAIL, the table is poorly displayed with no format, altough that in MS outlook, Hotmail, Yahoo, the format of table was ok

To solve this issue we decided that in the e-mail it's also goint the same table as an attachment on .xlsx.

I know that I'm not giving complete answer, but this cost me some money and many weeks of work in free time. The complete answer includes a big program with VBA macro of 5.000 + lines and userforms, it's not just a simple piece of code as I thought it would be.

I'm writting this to say IT IS POSSIBLE to do this with VBA using Excel and Outlook. As I've done it.

Answer 2

If you are willing to create this in Google Apps Script as your post's tag would suggest, you can use the code below. The code will use information in a Google Sheet to create a personalized item from a template. This would require moving your database into the Google environment and learning, if you have not already, some basic JavaScript.

The code, as I have written it for my own uses, uses a Google Form for data collection, a Google Sheet with 2 extra columns called 'Check' and 'URL', an HTML template, and a prepared Google Doc template. I will discuss modifications that may be pertinent to you post-code.

function mergeApplication() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet Where Form Data is Processed");
  var formSheet = ss.getSheetByName("Form Responses");
  var lastRow = formSheet.getLastRow();
  var lastColumn = sheet.getMaxColumns();
  function checkAndComplete() {
    var urlColumn = lastColumn; //Provides a direct link to the file created
    var checkColumn = (urlColumn - 1);
    var checkRange = sheet.getRange(2, checkColumn, (lastRow - 1), 1);
    var check = checkRange.getBackgrounds(); //This shows status of the code
    var red = "#ff0404";
    var yellow = "#ffec0a";
    var green = "#3bec3b";
    for (var i = 0; i < check.length; i++) {
      if (check[i] == green) {
        continue;
      } else {
        var statusCell = sheet.getRange((i+2), checkColumn, 1, 1);
        var urlCell = sheet.getRange((i+2), urlColumn, 1, 1);
        var dataRow = sheet.getRange((i+2), 1, 1, (lastColumn - 2));
        function mergeTasks() {    
          function docCreator() {
            var docTemplate1 = DriveApp.getFileById("templateId1"); //allows for if statements to determine which template is needed
            //var docTemplate2 = DriveApp.getFileById("templateId2");
            var folderDestination1 = DriveApp.getFolderById("folderId1");
            var folderDestination2 = DriveApp.getFolderById("folderId2");
            if (condition1 == met) {
              var docToUse = docTemplate1;
              var folderDestination = folderDestination1        
              var emailTemplate = HtmlService.createHtmlOutputFromFile("Email Template 1").getContent();
            } else if (condition2 == met) {
              var docToUse = docTemplate2;
              var folderDestination = folderDestination2
              var emailTemplate = HtmlService.createHtmlOutputFromFile("Email Template 2").getContent();
            }
            var docName = "Name - " + variable + "string";
            var docCopy = docToUse.makeCopy(docName, folderDestination);
            var docId = docCopy.getId();
            var docURL = DriveApp.getFileById(docId).getUrl();
            var docToSend = DriveApp.getFileById(docId);
            var docInUse = DocumentApp.openById(docId);
            var docBody = docInUse.getBody();
            var docText = docBody.getText();
            function tagReplace() {
              var taggedArray = docText.match(/\<{2}[\w\d\S]+\>{2}/g);
              var headerArray = sheet.getRange(1, 1, 1, (lastColumn - 2)).getValues();
              var dataArray = dataRow.getValues();
              var strippedArray = [];
              function tagStrip() {
                for (var t = 0; t < taggedArray.length; t++) {
                  strippedArray.push(taggedArray[t].toString().slice(2, -2));
                }
              }
              function dataMatch() {
                for (var s = 0; s < strippedArray.length; s++) {
                  for (var h = 0; h < headerArray[0].length; h++) {
                    if (strippedArray[s] == headerArray[0][h]) {
                      docBody.replaceText(taggedArray[s], dataArray[0][h]);
                    }
                  }
                }
                docInUse.saveAndClose();
              }
              tagStrip(); 
              dataMatch();
            }           
            function emailCreator() {
              var emailTag = "email@address.com";
              var bodyAddition = '<table style="border:1px; padding:15px; background-color:#DDDDDD"><tr><td> This is an HTML additive:</td><td><a href = "' + docURL + '">' + docName + '</a></td></tr></table><br /><br />';
              var emailBody = emailTemplate + bodyAddition;
              var emailSubject = "New Email " + docName + " string"
              MailApp.sendEmail({
                to: emailTag,
                subject: emailSubject,
                htmlBody: emailBody,
              });
            }
            tagReplace();
            statusCell.setBackground(yellow);
            emailCreator();
            urlCell.setValue(docURL);
          }
          statusCell.setBackground(red);
          docCreator();
          statusCell.setBackground(green);
        }
        mergeTasks();
      }
    }
  }
  checkAndComplete();
}

For this, your Google Sheet can have as many columns as you want, with one header row (frozen is ok), and 2 columns at the very end. The last column is URL, the second to last is Check (where the background status colors are shown).

Your Google Doc can be written/ formatted however you wish but the places to replace with data from your sheet will be tagged using <<>>; for instance - <<staffName>>. This tag (located and replaced using RegEx) will need to match the header of the column in which the data is found. For example, if column 3 has the name, and is called PersonName in the first cell, use the tag <<PersonName>>.

GAS allows for the creation of HTML files and these can be written as complicated/ bare as you wish and can be modified through GAS code on the fly. As you described, you wish to replace tags in the email with data. You can accomplish this with similar means to replacing tags in the Google Doc. You can find more information in the GAS reference for Mail methods here, and HTML methods here.

This code does not have a row limiter. There may be a time/ # of calls that GAS allows in one run of the code. If this is the case, you can simply call the function using a time trigger and it will simply run again at timed intervals from wherever it left off.