VBA HTML Mailing

E

excelman888

Board Regular
Joined
Apr 4, 2016
Messages
63
Dear Community

I have an issue with my Mailing with VBA Function

Code: 
Sub send_Mail()
    Dim olApp As Object
    Dim olMail As Object

    Set olApp = GetObject(, "Outlook.Application")
    Set olMail = olApp.CreateItem(0)

    With mItem
        olMail.To = "marlon.goetz@equatex.com"
        olMail.Subject = "Firm Price Approval by COB " & "- " & _
                FormatDateTime(Date, vbLongDate)

        olMail.HTMLBody = "<font face=""Calibri"">" & _
            "Dear all,<BR><BR>Please find below the Firm pricing for approval for the current project<BR><BR>" & _
            "If you have any suggestions, comments or queries related to this pricing" & _
            " please do not hesitate to contact me.<BR><BR>These reports are also available under" & _
            " confluence - the link to the site is specified below.<br>" & _
            "<a href=""http://sb002055.equateplus.net/display/RFP/Offers+in+Progress"">relevant link</a><br><br>" & _
            "Best regards,<BR><BR>" & "<b>Marlon Goetz</b><BR>" & "Bid Management" & _
            "</font>"


        olMail.display

    End With

This is what I have so far


My Problem I want to Change the whole html Body . I formed a html which should be my Body

HTML: 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
    <title>My First Campaign | Campaign Monitor</title>
    
****** http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />

<link rel="shortcut icon" href="https://img.createsend1.com/favicon.ico?3" />
<link href="https://css.createsend1.com/css/global-old-setup.min.css?h=427D0FD30518-panjandrum" media="screen,projection" rel="stylesheet" type="text/css" />
<link href="https://css.createsend1.com/css/global-new-setup.min.css?h=A4DDB46A0518-panjandrum" media="screen,projection" rel="stylesheet" type="text/css" />
<link href="https://css.createsend1.com/css/pretty.min.css?h=83F7CBCA0518-panjandrum" media="screen,projection" rel="stylesheet" type="text/css" />
<link href="https://css.createsend1.com/css/olark.min.css?h=44055D7C0518-panjandrum" media="screen,projection" rel="stylesheet" type="text/css" />
<link href="https://css.createsend1.com/css/print.min.css?h=40871D840518-panjandrum" media="print" rel="stylesheet" type="text/css" />

<link rel="apple-touch-icon-precomposed" href="https://img.createsend1.com/mobile_themes/cs/img/iOS-120.png" />
******** type="text/javascript" src="https://js.createsend1.com/js/modernizr.2.6.2.js?h=F2001D540518-panjandrum">*********>
******** type="text/javascript" src="https://js.createsend1.com/js/jquery-1.7.2.min.js?h=C99A46590518-panjandrum">*********>
********>var gaPrefix = 'app';var gaAccountClassic = '-', gaAccountUniversal = '-';*********>
******** type="text/javascript" src="https://js.createsend1.com/js/cm.min.js?h=B55063E40518-panjandrum">*********>


    ******** type="text/javascript" src="https://js.createsend1.com/js/reports.min.js?h=06A9A50D0518-panjandrum">*********>
    ******** type="text/javascript">

        $(window).resize(function(){
            var topbarHeight = $(".bar-mid").height();
            var paddingHeight = 60;
            var newHeight = $(window).height() - topbarHeight - paddingHeight;
            CS.ViewCampaign.changeFrameHeight(newHeight);
        });

        function change_parent_url(url) {
          ****************** = url;
        }
    *********>

    <style type="text/css">

        body, html {
            margin: 0;
            width: 100%;
            height: 100%;
            /*overflow: hidden;*/
            background: #FFF;
        }
        iframe {
            width: 100%;
            border-top: 1px solid #c6c6c6;
        }
        .ie iframe {
            margin-top: -2px;
        }

        .email-client .bar-mid {
          /*height: 80px;*/
          background: #FFF;
          position: relative;
          z-index: 10;
          -webkit-box-shadow: rgba(0, 0, 0, 0.05) 0px 1px 2px 2px;
          -moz-box-shadow: rgba(0, 0, 0, 0.05) 0px 1px 2px 2px;
          box-shadow: rgba(0, 0, 0, 0.05) 0px 1px 2px 2px;
        }
        .email-client .bar-mid .details {
          padding: 20px 0px 20px 0px;
          /*float: left;*/
          min-width: 320px;
          width: 100%;
        }
        .email-client .bar-mid .details .avatar {
          width: 38px;
          height: 38px;
          background: url(../img/subscriber-notifications/avatar-email-client.jpg) no-repeat;
          display: inline-block;
          float: left;
          margin-right: 10px;
          margin-left: 20px;
        }
        .email-client .bar-mid .details .text {
          /*float: left;*/
          width: 45%;
          display: inline-block;
        }
        .email-client .bar-mid .details .text p {
          color: #888888;
          font-size: 13px;
          padding-bottom: 2px;
        }
        .button-group {
            width: 130px;
            position: absolute;
            top: 29px;
            right: 20px;
        }

        .print-preview {
            color: #888 !important;
            position: absolute;
            top: 33px;
            right: 165px;
        }

        .dynamic-content {
            width: 220px;
            display: block;
            background: #595d65;
            float: left;
            padding: 30px;
            margin: 0px;
            color: #FFF;
            overflow: auto;
              -moz-box-shadow:    inset -4px 0px 9px -6px #000;
              -webkit-box-shadow: inset -4px 0px 9px -6px #000;
              box-shadow:         inset -4px 0px 9px -6px #000;

        }

        .dynamic-content .title {
            color: #b8babc;
            font-size: 18px;
            font-weight: bold;
            margin-bottom: 20px;
        }

        .dynamic-content .custom-field {
            font-size: 14px;
            font-weight: bold;
            border-bottom: #686b72 solid 1px;
            padding-bottom: 3px;
            margin: 25px 0px 10px 0px;
            display: block;
            word-break: break-all;
        }

        .dynamic-content .new-form .ko-radio, .dynamic-content .new-form .ko-checkbox {
            position: relative;
            margin-right: 0;
        }

        .dynamic-content label{
            width: 100%;
            text-align: left;
            font-weight: normal;
            margin-bottom: 5px;
            float: none;
            line-height: 16px;
        }

        .email-preview {
            margin-left: 280px;
            background: #FFF;
        }

        .sub-items {
            padding-left: 22px;
            margin-bottom: 8px;
        }

        @media only screen 
        and (max-width : 580px) {

            .button-group {
                position: relative;
                top: auto;
                right: auto;
                margin-left: 69px;
                margin-top: 10px;
            }

            .print-preview {
                position: relative;
                top: auto;
                right: auto;
                margin-left: 15px;
                margin-top: 3px;
                display: inline-block;
            }

            .email-client .bar-mid .details .text {
                width: 75%;
            }
        }

    </style>
</head>
<body>
    <div id="htmlPreview" >
        
        <div id="htmlDetails" class="email-client">
            <div class="bar-mid">
                <div class="details">

                    <div class="avatar"></div>
                    <div class="text">

                        

                        <p><strong>From:</strong> Goetz Marlon <Marlon.goetz@equatex.com></p>
                        
                        

                        <p><strong>Subject:</strong> My First Campaign</p>
                        
                            
                    </div>

                    
                        <div class="button-group" style="display: none">
                            <a href="#" class="button small selected">HTML</a>
                            <a href="#" class="button small" *******="CS.ViewCampaign.toggleVersion();return false;">Plain text</a>
                        </div>
                    
                    
                    <a href="https://equatex.createsend.com/t/ViewEmail/d/1D842FC76AF4958F/C67FD2F38AC4859C/?tx=0&previewAll=1&print=1" target="_blank" class="print-preview">Print preview</a>

                </div>
            </div>
        </div>

        <div id="dc-html" class="dynamic-content" style="display: none">
            <form action="https://equatex.createsend.com/t/ViewEmailInIFrame/d/1D842FC76AF4958F/C67FD2F38AC4859C/?tx=0" method="post" target="htmlFrame">
            <div class="title">Dynamic content</div>
                <section class="new-form">
                    
                </section>
            </form>
        </div>

        <div class="email-preview" id="htmlWrap" style="margin-left: 0">
            ******** id="htmlFrame" name="htmlFrame" src="https://equatex.createsend.com/t/ViewEmailInIFrame/d/1D842FC76AF4958F/C67FD2F38AC4859C/?tx=0" width="100%" frameborder="0" scrolling="auto" class="frame">*********>
        </div>
        
    </div>

    <div id="textPreview" style="display:none" >
        
        <div id="textDetails" class="email-client">
            <div class="bar-mid">
                <div class="details">
                    <div class="avatar"></div>
                    <div class="text">
                        <p><strong>From:</strong> Goetz Marlon <Marlon.goetz@equatex.com></p>
                        <p><strong>Subject:</strong> My First Campaign</p>
                    </div>

                    
                        <div class="button-group" style="display: none">
                            <a class="button small" href="#" *******="CS.ViewCampaign.toggleVersion();return false;">HTML</a>
                            <a href="#" class="button small selected">Plain text</a>
                        </div>
                    

                    <a href="https://equatex.createsend.com/t/ViewEmail/d/1D842FC76AF4958F/C67FD2F38AC4859C/text/?tx=0&previewAll=1&print=1" target="_blank" class="print-preview">Print preview</a>

                </div>
            </div>
        </div>

        <div id="dc-text" class="dynamic-content" style="display: none">
            <form action="https://equatex.createsend.com/t/ViewEmailInIFrame/d/1D842FC76AF4958F/C67FD2F38AC4859C/text/?tx=0" method="post" target="textFrame">
            <div class="title">Dynamic content</div>
                <section class="new-form">
                    
                </section>
            </form>
        </div>

        <div class="email-preview" id="textWrap" style="margin-left: 0">
            ******** id="textFrame" name="textFrame"  src="https://equatex.createsend.com/t/ViewEmailInIFrame/d/1D842FC76AF4958F/C67FD2F38AC4859C/text/?tx=0" width="100%" frameborder="0" scrolling="auto" class="frame">*********>
        </div>
        
    </div>




******** type="text/javascript">

    var docHeight = $(document).height();
    var topbarHeight = $(".bar-mid").height();
    var paddingHeight = 60;

    $(".frame").css("height", docHeight - topbarHeight - 1 + "px");
    $('.dynamic-content').height(docHeight - topbarHeight - paddingHeight);
    $(function () {
        var toggleValue = function ($checkbox) {
            var value = $checkbox.attr('value');
            $checkbox.attr('value', $checkbox.attr('toggle-value'));
            $checkbox.attr('toggle-value', value);
        };
        $('.ko-radio').bind('change', function (e) {
            $('.ko-checkbox:visible').each(function (i, e) {
                if ($(e).prop('checked')) {
                    $(e).prop('checked', false);
                    toggleValue($(e));
                }
            });
            $(this).closest('form').trigger('submit');
        });
        $('.ko-checkbox').bind('change', function (e) {
            toggleValue($(this));
            $('#' + $(this).attr('parent-id')).prop('checked', true);
            $(this).closest('form').trigger('submit');
        });
    });
    
    CS.ViewCampaign.showVersionSwitcher();

*********>
</body>
</html>

Thanks for your Support
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
So, what is the problem?
Code: 
s = "body"
olMail.HTMLBody = s
With that much code, I would poke each line into an array or poke into a text/htm file and then import the text into s or htmbody value directly. Of course your poked data must contain the quote characters.
 
Upvote 0
Kenneth Hobson said:
So, what is the problem?
Code: 
s = "body"
olMail.HTMLBody = s
With that much code, I would poke each line into an array or poke into a text/htm file and then import the text into s or htmbody value directly. Of course your poked data must contain the quote characters.
Click to expand...

I dont know how to do this ?

Could you provide me with my relevant coding or the steps to take
 
Upvote 0
Code: 
Sub Test()
  Dim s(1 To 3) As String
  s(1) = "<link rel=""shortcut icon"" href=""https://img.createsend1.com/favicon.ico?3"" />"
  s(2) = "<link href=""https://css.createsend1.com/css/global-old-setup.min.css?h=427D0FD30518-panjandrum"" media=""screen,projection"" rel=""stylesheet"" type=""text/css"" />"
  s(3) = "<link href=""https://css.createsend1.com/css/global-new-setup.min.css?h=A4DDB46A0518-panjandrum"" media=""screen,projection"" rel=""stylesheet"" type=""text/css"" />"
  Debug.Print Join(s(), vbCrLf)
End Sub
The forum does not allow html code so I put the html strings in this file. https://www.dropbox.com/s/dw4us7n8b7uu2nn/949355.xlsm?dl=0
 
Last edited:
Upvote 0
Kenneth Hobson said:
Code: 
Sub Test()
  Dim s(1 To 3) As String
  s(1) = "<link rel=""shortcut icon"" href=""https://img.createsend1.com/favicon.ico?3"" />"
  s(2) = "<link href=""https://css.createsend1.com/css/global-old-setup.min.css?h=427D0FD30518-panjandrum"" media=""screen,projection"" rel=""stylesheet"" type=""text/css"" />"
  s(3) = "<link href=""https://css.createsend1.com/css/global-new-setup.min.css?h=A4DDB46A0518-panjandrum"" media=""screen,projection"" rel=""stylesheet"" type=""text/css"" />"
  Debug.Print Join(s(), vbCrLf)
End Sub
The forum does not allow html code so I put the html strings in this file. https://www.dropbox.com/s/dw4us7n8b7uu2nn/949355.xlsm?dl=0
Click to expand...

Many Thanks for your Support

I dont have Access to dropbox. Could you provide the Excel to my E-Mail Adress

marlon.goetz@equatex.com
 
Upvote 0
You should never post your email address. Edit your post if possible. PM emails if needed.

Nothing special about the file. Put a quote next to each quote in a string and one on each end. That is all there is to it.
 
Upvote 0
Kenneth Hobson said:
You should never post your email address. Edit your post if possible. PM emails if needed.

Nothing special about the file. Put a quote next to each quote in a string and one on each end. That is all there is to it.
Click to expand...

Thank you for the Information

But dont know how to edit the string and the macro I would be pleased if you could send me the Excel with the macro to create a html with the link to the html.Body written in html.

After my message i will delete this post

Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA code HTML text transformation
Replies
4
Views
206
Sia
S
S
  • Solved
Remove all class names in HTML-tags
Replies
5
Views
812
strooman
S
P
  • Question Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
591
John_w
J
P
  • Question Question
Help i can't found the xpath to continue
Replies
3
Views
398
ptcsite46
P
J
  • Question Question
Stuck trying to fetch excel data to html
Replies
0
Views
349
Jackaa
J

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top