HendrixSpirit HendrixSpirit - 1 year ago 65
SQL Question

Mail Merge with Grouping Key Field from Excel or SQL Databases

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.

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.!

| Product | Q |
| Product 1 | Q. Prod 1 |
| Product 2 | Q. Prod 2 |
| Product ... | Q. Prod ...|
| Product i | Q. Prod i |

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 Source

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
End Function

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

If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download