+ Reply to Thread
Results 1 to 4 of 4

Sending HTML email via excel VBA - arriving as RTF email

  1. #1
    Registered User
    Join Date
    06-06-2012
    Location
    Bratislava
    MS-Off Ver
    Excel 2010
    Posts
    8

    Sending HTML email via excel VBA - arriving as RTF email

    Hi folks,

    I am writing a macro that has to send emails from a Workbook. Those emails have to be in HTML format. I was able to create HTML emails, but after sending they are converted into RTF emails. To be exact: when .display(ed) those emails are HTML. After sending (in the recipients inbox, and in my sent box) those emails are in RTF format.

    Any idea why this is happening?

    The code is very simple, like:

    Dim OutApp as Object
    Set OutApp = Createobject("Outlook.Application")

    Dim OutMail as Object
    Set OutMail = CreateEmailfromTemplate(Path)

    With OutgoingMail
    .to = ...
    .from = ...
    .subject = ...
    .bodyformat = 2 // 2 is for html format, result didnt change with this property ot without it
    .htmlformat = .htmlformat & Nexthtml //nexthtml is to be added at the end of the email - but dont shows up as the email is not html
    .display
    End with

    The important point is the NextHTML, that is the uniqe part of the email for any recipient.


    Thank you very much for any opinions!

    Regards,
    Shuter

  2. #2
    Forum Contributor
    Join Date
    06-09-2011
    Location
    Germany
    MS-Off Ver
    Excel 2016
    Posts
    194

    Re: Sending HTML email via excel VBA - arriving as RTF email

    Hi Shuter,

    try to change the bodyformat to;
    .BodyFormat = olFormatHTML

    Rather than using .htmlformat = .htmlformat & Nexthtml ty using .HTMLBody = .HTMLBody & Nexthtml

    Regards

    Theo
    Remember To Do the Following....
    1. Upload sample files
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.

  3. #3
    Registered User
    Join Date
    06-06-2012
    Location
    Bratislava
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Sending HTML email via excel VBA - arriving as RTF email

    Hi Fettertiger,

    i use .HTMLbody, that htmlformat is a typo. Excel doesnt want to accept olFormatHTML, but i use the numeric expression for that (2), but it didnt help anyway ...

  4. #4
    Registered User
    Join Date
    06-11-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    86

    Thumbs up Re: Sending HTML email via excel VBA - arriving as RTF email

    -------------------------------
    Sub Mail_Selection_Range_Outlook_Body()
    ' Don't forget to copy the function RangetoHTML in the module.
    ' Working in Office 2000-2010
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    'Only the visible cells in the selection
    Set rng = Selection.SpecialCells(xlCellTypeVisible)
    'You can also use a range if you want
    'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
    MsgBox "The selection is not a range or the sheet is protected" & _
    vbNewLine & "please correct and try again.", vbOKOnly
    Exit Sub
    End If

    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .HTMLBody = RangetoHTML(rng)
    .Display 'or use .Send
    End With
    On Error GoTo 0

    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub
    Function RangetoHTML(rng As Range)
    ' Changed by Ron de Bruin 28-Oct-2006
    ' Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
    strPth = ActiveWorkbook.Path
    ' strPth = Application.Workbooks("Depth.xls").Path
    ' TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    TempFile = strPth & "\mails.htm"


    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    FileName:=TempFile, _
    Sheet:=TempWB.Sheets(1).Name, _
    Source:=TempWB.Sheets(1).UsedRange.Address, _
    HtmlType:=xlHtmlStatic)
    .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
    "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    End Function

+ 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