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 |email@example.com | Orange | 105.566 |
SH1 |firstname.lastname@example.org | Berries | 200.000 |
BT1 |email@example.com | Orange | 300.000 |
BT4 |firstname.lastname@example.org | Apple | 101.700 |
WT1 |email@example.com | Berries | 201.040 |
WT6 |firstname.lastname@example.org | Apple | 204.000 |
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..>
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.