Solved

Is it possible to populate a HTML document from an Excel sheet ?

Posted on 2014-01-13
17
381 Views
Last Modified: 2014-01-15
Hi All,

I have an HTML document (index.html) which I use as a mailshot template. I create an email from the document as follows:

1. In Internet Explorer, Select All, Copy and Paste the web page into an Outlook message.
2. Change the name of the recipient (Dear xxxxx) in the body of the text.
3. Add the recipient's email address.
4. Send message

I now want to automate this process from my Mailshot Excel VBA application on the click of a button.

My question is, how can I create a form field or control in my HTML document (similar to a label control in a Word document) which I can populate from my Excel application ?

Thanks
Toco
0
Comment
Question by:Tocogroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39775986
Have you ruled out opening the html as text in notepad, copy and paste as text into word.... run it through the mail merge wizard in word, output it as one document and then save it as html again?

Alternatively i think you can have word do an email merge from your html file and your flat file of excel recipients.

finally, 3rd option, use vba code in excel to loop through the recipients list, you should be to read in the html file as a text stream, and replace the bits you need with the values you want , and then send that to an email object in outlook to send it.
0
 

Author Comment

by:Tocogroup
ID: 39776025
Hi,

The email object must be generated from the VBA code within Excel (I've done this with ordinary text emails elsewhere in my application). I want to replace the copy/paste or mailmerge processing with VBA, so your third option is the most appropriate.

How do I.... 'read in the html file as a text stream, and replace the bits you need with the values you want , and then send that to an email object in outlook to send it' ?

I don't want any manual intervention up until the point I want to Send the email.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39776151
I see no reason to use a html document as  a template for emails.

I recommend using CDO in VBA, you can send html as the body of the mail. You can easily construct the html to include the recipient's name. See http://support.microsoft.com/kb/286431 for the general idea.

This is by far the easiest method to automate sending an email.

If you want to go this way, let me know and I'll provide a sample.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Tocogroup
ID: 39776177
Hi MS,

Are there any restrictions in sending the HTML in the body of the email ? I've hand-coded the HTML because I am aware that mail programs are notoriously finicky when it comes to rendering HTML in an email message.

If the HTML body can include images from my web server and hyperlinks then this is the way to go. Do you have a sample ?

Thanks
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39776572
Instructions:

1. Update the values on Sheet1 to reflect real data.
2. Open your html file insert @NAME@ where you want the name.
3. Run the Demo macro.

Unfortunately, you will have to use an existing file with your html (due to a Ms shortcoming; only 24 line continuation characters allowed) .

Attached you will find proof of concept.
Sample.xlsm
test.txt
0
 

Author Comment

by:Tocogroup
ID: 39777011
Ok, I think you're going to have to talk me through this one.
Firstly, I've never seen a sheet with reversed headings before (see attached screenshot).
How do I revert them to 'normal' column headings reading A-Z from left to right ?
Sample-application-Screenshot.jpg
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39777907
Ok, where are you having a problem?
Sample.xlsm
0
 

Author Comment

by:Tocogroup
ID: 39778547
Ok thanks, that's the first problem sorted. Next.....

Do I save my .html file as a .txt file, such as yours ?

Is the User name/password combination on the sheet required for FTP access ?
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39778678
Do I save my .html file as a .txt file, such as yours ?
No need.

Is the User name/password combination on the sheet required for FTP access ?
The username/password are your email account info for sending the email.
0
 

Author Comment

by:Tocogroup
ID: 39781949
Ok.....next issue.

When I run the macro Demo it returns a 'Run time error 52 - Bad file name or number' on the line...

    Open strFile For Input As #1     ... of the ReturnTextFile function

In debug I can see the variable strFile is pointing at the valid URL...
http://www.tocotraining.co.uk/email/SummarisingYourData/index.html

Is there something I've omitted ? My mailbox Username/Password combination are valid.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39781961
It has to be on your local machine. If the html is on your server you will need a different function.

Replace the ReturnTextFile function with this one, don't forget to change all occurrences of ReturnTextFile with ReturnHtmlFromUrl:

Public Function ReturnHtmlFromUrl (ByVal strURL) As String

    On Error GoTo ErrorHandler

    Dim strError As String
    Dim strResponse As String

    strError = ""
    strResponse = ""

    With CreateObject("MSXML2.XMLHTTP")

        .Open "GET", strURL, False
        .send ""

        If .Status <> 200 Then
            strError = .statusText
            GoTo CleanUpAndExit
        Else

            If .getResponseHeader("Content-type") <> "text/html" Then
                strError = "Not an HTML file"
                GoTo CleanUpAndExit
            Else
                strResponse = .responseText
            End If

        End If

    End With

CleanUpAndExit:

    On Error Resume Next ' Avoid recursive call to error handler

    ' Clean up code goes here
    Set oXMLHTTP = Nothing

    ' Report any error
    If Len(strError) > 0 Then
        MsgBox strError
    Else
        ReturnHtmlFromUrl = strResponse
    End If

    Exit Sub

ErrorHandler:

    strError = Err.Description

    Resume CleanUpAndExit

End Function

Open in new window

0
 

Author Comment

by:Tocogroup
ID: 39781987
Thanks.

Ok.....next, I'm getting a compiler error message : 'User-defined type not defined' against the following line.....

Dim cdoMsg As CDO.Message

Is this now redundant ?
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39782098
Either add a reference to the CDO library. See the attachment... or change the following line:
Dim cdoMsg As CDO.Message

Open in new window

to
Dim cdoMsg As Object

Open in new window

em.jpg
0
 

Author Comment

by:Tocogroup
ID: 39782435
Hi

Following the Send, it's returning a debug message of...

'At least one recipient is required, but none were found.'

Prior to the Send the variables are set as follows:

strSubject = "Sample letter"
strServer = the full HTML code, including the replaced name
strServer = "smtp.e-mazingnames.com"
intPort = 465
strFromUsername = my mailbox user name
strPassword = my mailbox password
intSendUsing = 2
intAuthenticate = 1
blnUseSSL = True
intTimeout = 10

Any ideas ?
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39782471
1. What is the value of the strTo variable? it should contain a valid email address.

2. The email settings are intended for use with gmail. For your server please check the requirements for:
a. Port : The SMTP Port which must be enabled in your network by ISP or local Firewall
b. SendUsing: Specifies the method used to send messages: '(1) Local SMTP Pickup Service (2) Use SMTP Over Network (3) Use Exchange Server
c. Authenticate: Specifies the authentication mechanism to use when authentication is required to send messages to an SMTP service using a TCP/IP network socket:  (1) None (2) Basic (Base64 encoded) (3) NTLM
d. UseSSL: Indicates whether Secure Sockets Layer (SSL) should be used when sending messages using the SMTP protocol over the network or not.
0
 

Author Closing Comment

by:Tocogroup
ID: 39782816
That's great. I got around the email send problems by amending an existing email procedure I had in another workbook.
The name replace and HTML body display works fine.

Many thanks for your patience and comprehensive explanations.

Regards
Toco
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39782868
Glad to have been of assistance.
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question