using .htmlbody instead of .body in my email excel sheet macro using OutMail

S

scassells

New Member
Joined
Aug 8, 2018
Messages
13
In the code below I specify the body of the email using a series of cell references from my workbook. Can I use the same approach and use .HTMLbody instead of .Body.

I want to be able to format the body of my message or make it appear more aesthetically pleasing for the recipient

A line below with an empty shell for .HTMLbody has been commented out below

------------------------------

With OutMail
.to = Range("V2")
.CC = Range("V6")
.BCC = ""
.Subject = Range("W2")
'.HTMLBody = "<HTML><H2>Test</H2>******> test2. </BODY></HTML>"
.BODY = Range("S32") & vbNewLine & vbNewLine & Range("W4") & vbNewLine & Range("W5") & vbNewLine & Range("W15") & vbNewLine & Range("W16") & vbNewLine & Range("W6") & vbNewLine & Range("W7") & vbNewLine & Range("W8") & vbNewLine & Range("W9") & vbNewLine & Range("W10") & vbNewLine & Range("W11") & vbNewLine & Range("W17") & vbNewLine & Range("W16") & vbNewLine & Range("W18") & vbNewLine & Range("W12") & vbNewLine & Range("W38") & vbNewLine & Range("W54") & vbNewLine & Range("W40") & vbNewLine & Range("I3")
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .send
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Hi,
yes you can, in my quick (and very dirty) example below, I start the email by setting the font and size (we have too many people with weird setups).

I then add some text and varaibles, add some break returns, some more text bolded (using html formatting tags) and then as another test (as per your post) call in 2 ranges. One I leave as is and the other I bold, and as a super test I then add a html paragraph style with colour and it alls seems to work.

This all looks messy but one you have this set then it will look good :).

Another idea I just had would be to take the data you wanted, vba it to another sheet, format it and then copy/paste the range/table into the email, I guess that would keep the formatting??

Code: 
strbody = "****** style=font-size:10pt;font-family:Arial>" & "Dear " & managername "," & "<br> <br>" _
    & "The results returned for <b>" & "</b> have shown as positive (We do not get sight of the certificate).</b><br>" _
    & Range("A1") & "is some text, and <b>" & Range("a5") & "</b>" & "<p style= color:#ff33cc> is some bolded text</p>"
[COLOR="#008000"]    '**** the above starts an email with html formatting so I can ensure everyone uses the same font (no comic sans here!)[/COLOR]
[COLOR="#008000"]    '**** is reads in some varaible I have set, and you can see as a basic formatting I make them bold
    '**** I have added a basic paragraph style to the end of the text which comes out pink in the email[/COLOR]
    
    
    subline = "Private and confidential: result"
    pthbdy = "\\dpath\Docs\path\path\path\path\path\path Team\path Documents\signatures\signature1.htm"
    
    
    bodytxt = fso.OpenTextFile(pthbdy).ReadAll ' contents of above file
[COLOR="#008000"]    '**** the above reads a standard htm (created in word), it reads the text (including colours/formatting) - I add the standard email signature to the end of this file so they get text and images[/COLOR]
    
    
    sensr = 3
    On Error Resume Next
    readr = False
    deliverr = False


    With OutMail
    
        .To = ename
        .cc = carbon
        .BCC = ""
        .Subject = subline
        .HTMLBody = strbody & bodytxt & "<br>" & .HTMLBody
[COLOR="#008000"]        '**** I set my htmlbody to be the strbody and bodytxt file contents[/COLOR]
        .Importance = 2
        .ReadReceiptRequested = readr
        .OriginatorDeliveryReportRequested = deliverr
        .Sensitivity = sensr
        .SentOnBehalfOfName = """******** - mailbox""<*********>"
        
        If emsg = "addflow" Then
        '.attachments.Add "\\dpath\Docs\path\path\path\path\path\path Team\path Documents\process_flowchart.pdf"
        End If
          
        
        .Display
    End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Email body not showing using html.
Replies
3
Views
594
Logit
Logit
N
  • Solved
Excel VBA Add graphic signature block to automated email.
Replies
2
Views
325
NormThib
N
M
  • Question
Add text from specific cell into body of email
Replies
3
Views
933
Candyman8019
C
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
431
josros60
J
N
  • Question
Google Sheets Query - Date Driven Formula
Replies
2
Views
189
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,829
Messages
6,121,826
Members
449,051
Latest member
excelquestion515

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