Raplace Variables in HTML template With values from excel

[Steffen Thomsen]

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

[snb]

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

[Steffen Thomsen]

Hi snb.

Thats a heavy procedure when going threw long mailing lists.

I could try it though.

Steffen Thomsen.

[Steffen Thomsen]

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.

[snb]

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

[Steffen Thomsen]

Hi again snb

Getting a compile error on the following

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

Steffen Thomsen

[snb]

Improved code in last post.

[Steffen Thomsen]

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.

[snb]

implementing in ?

I already implemented it in yours.

[Steffen Thomsen]

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.