Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-13
17
Medium Priority
?
399 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
  • 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 28

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 28

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 28

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 28

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 28

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 28

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 28

Accepted Solution

by:
MacroShadow earned 2000 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 28

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
Today as you open your Outlook, you witness an error message: “Outlook is using an old copy of your Outlook Data File…”. Probably, Outlook is accessing an old OST file.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Suggested Courses

824 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