CODEWITHSUNDEEP
excelvba
TonyP
TonyP

Reputation: 333

Embedded HTML text within VBA to write email body

My code is currently populating text into the body of an email in HTML format from my understanding. I am trying to replace the text with a variable so I don't have to edit the text before running each time. There is currently a variable in the beginning called Name which populate the email body with a "Hello Jim," but I want the actual body to start on the next line so I can't just add an & then the other variable. The code is currently:

                         StrTo:=Email, _
                         StrCC:="", _
                         StrBCC:=SDLEmail, _
                         StrSubject:=Email_Subject, _
                         Signature:=True, _
                         Send:=False, _
                         StrBody:="Hello " & Name & "," & _
                         *"<body> Here is the weekly update. Quotes are current through yesterday. " & _
                                  "<br><br>" & " </body>"*

and the variable I want to add is Email_Body

Upvotes: 0

Views: 710

Answers (2)

Tony
Tony

Reputation: 2774

I can't tell if your email call will work because you're not showing your complete code. Your StrBody contains HTML <body> tags that aren't necessary. If the salutation appears in the body of the email, it's also considered part of the body.

Let's say you have two variables that you want to use, Name and Email_Body. Define those before your call to email. 

Name = "Jim"
Email_Body = "Here is the weekly update. Quotes are current through yesterday."

They could also be in a cell on one of your sheets if you want to easily change the values. If you had a sheet called "Control" that contained these values, you could retrieve them as follows:

Name = ThisWorkbook.Sheets("Control").Range("A2").Value
Email_Body = ThisWorkbook.Sheets("Control").Range("B2").Value

When you create the StrBody portion in your email command, it should look like the following:

StrBody:="Hello " & Name & ",<br><br>" & Email_Body

The <br> tags are line breaks, so your text will appear as follows:

Hello Jim,

Here is the weekly update. Quotes are current through yesterday.

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166885

You can add a new line using <br>

StrBody:="<body>Hello " & Name & ",<br>" & _
         "Here is the weekly update. Quotes are current through yesterday. " & _
         "<br><br> </body>"*

Upvotes: 1

Related Questions