+ Reply to Thread
Results 1 to 10 of 10

Raplace Variables in HTML template With values from excel

Hybrid View

  1. #1
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Raplace Variables in HTML template With values from excel

    Hi

    I have the a problem for which i need some help.

    Sub Send_mail()
        
        ' This code goes threw mail adresses in column B and sends a HTML template as body text to them.
        Dim iMsg As Object
        Dim iConf As Object
        Dim strbody As String
    
        Set iMsg = CreateObject("CDO.Message")
        Set iConf = CreateObject("CDO.Configuration")
    
            iConf.Load -1
            Set Flds = iConf.Fields
            With Flds
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                               = "My smtp mail server"
               .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
                .Update
            End With
        
        Range("B1").Select
        
        Do Until IsEmpty(ActiveCell)
        With iMsg
            Set .Configuration = iConf
            .To = ActiveCell.Text
            .CC = ""
            .BCC = ""
            .From = """Me"" <my@email.com>"
            .Subject = "Type subject here"
            .CreateMHTMLBody "file://C:/My template file path"
            
            .Send
        End With
        ActiveCell.Offset(1, 0).Select
        Loop
    
    End Sub
    What i need is something in the code that replaces a variable in the HTML Template [FirstName] with the Name in column A.

    Any help is welcome

    Steffen Thomsen
    Last edited by Steffen Thomsen; 11-18-2010 at 11:28 AM.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Raplace Variables in HTML template With values from excel

    You can always open that HTLM-file and change anything you like in it, save it and use it as an HTMLbody

    Open "C:\htmlbody.html" for input as #1
       c01=replace(Input(LOF(1),#1),"[firstName]",cells(1,1).Value)
    Close #1
    
    Open "C:\htmlbody.html" for output as #1
       Print #1,c01
    Close #1



  3. #3
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Raplace Variables in HTML template With values from excel

    Hi snb.

    Thats a heavy procedure when going threw long mailing lists.

    I could try it though.

    Steffen Thomsen.

  4. #4
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Raplace Variables in HTML template With values from excel

    Hi again snb.

    I've tried applying your code, theres a problem though.

    After it's been modified it has to be saved under a different name and the be called back as the body for the mailtext, then delete the file again.
    And keep going threw the loop.

    Steffen Thomsen.

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Raplace Variables in HTML template With values from excel

    sq=columns(2).specialcells(2).offset(,-1).resize(,2)
       Open "C:\htmlbody.html" for input as #1
         c01=Input(LOF(1),#1)
       Close #1
    
       For j=1 to ubound(sq)
         Open "C:\htmlbody" & j & ".html" for output as #1
           Print #1,replace(c01,"[firstName]",sq(j,1))
         Close #1
        
         With iMsg
            Set .Configuration = iConf
            .To = sq(j,2)
            .From = """Me"" <my@email.com>"
            .Subject = "Type subject here"
            .CreateMHTMLBody "file://C:/htmlbody" & j & ".html"
            .Send
        End With
      next
    I assume: firstname in column A
    emailaddress in column B
    Last edited by snb; 11-17-2010 at 10:20 AM.

  6. #6
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Raplace Variables in HTML template With values from excel

    Hi again snb

    Getting a compile error on the following

    c01=Input(LOF(1),#1),"[firstName]",cells(1,1).Value)
    Steffen Thomsen

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Raplace Variables in HTML template With values from excel

    Improved code in last post.

  8. #8
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Raplace Variables in HTML template With values from excel

    Hi again snb.

    There's some of your code i dont understand fully understand and cant implement into my code.

    If it's not to much to ask, would you combine the 2 codes and put in some explenation for your part off the code?

    Thanks in advance

    Steffen Thomsen.
    Last edited by Steffen Thomsen; 11-17-2010 at 10:51 AM.

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Raplace Variables in HTML template With values from excel

    implementing in ?

    I already implemented it in yours.

  10. #10
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Raplace Variables in HTML template With values from excel

    Hi again snb.

    My bad...
    I set it up badly, it works like a charm.
    Nicely done.
    Care to educate me a bit on the code you made?

    Steffen Thomsen.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1