Link to home
Start Free TrialLog in
Avatar of kosenrufu
kosenrufu

asked on

How can this VBA script be modified to copy and paste as HTML into body of email?

I have this:

How can this VBA script be modified to copy and paste as HTML into body of email?

Thanks
Public Sub SendFormByEmail()

    Dim xlApp As Excel.Application
    Dim wb As Excel.Workbook
    
    Dim excSht As Excel.Worksheet, excFrm As Excel.Range
    Dim olkApp As Outlook.Application, olkMsg As Outlook.MailItem, olkDoc As Word.Document

    MyFileName = "C:\AOTD Report DBs\Templates\TESTS\Aisle of Dogs Week 51 2010-Fresh TEST.xls"

    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(MyFileName)

    Sheets("Summary").Range("A1:M22").Copy

    Set olkApp = GetObject(, "Outlook.Application")
    Set olkMsg = olkApp.CreateItem(olMailItem)
    With olkMsg
        .Subject = "Test Chart"
        .To = "myemail@abc.ca"
        Set olkDoc = olkMsg.GetInspector.WordEditor
        olkDoc.Windows(1).Selection.Paste
        .Display
        .Send
    End With
    
    Set olkDoc = Nothing
    Set olkMsg = Nothing
    Set olkApp = Nothing
    Set excFrm = Nothing
    Set excSht = Nothing
    
    'xlWb.Close True
    Set xlWs = Nothing
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    
End Sub

Open in new window

Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

What version of outlook?

WHat is stored in Sheets("Summary").Range("A1:M22") i.e. is it HTML or does it need to be converted to html?

Chris
Avatar of kosenrufu
kosenrufu

ASKER

MS 2007
>> WHat is stored in Sheets("Summary").Range("A1:M22") i.e. is it HTML or does it need to be converted to html?  or for example is it simply you want the contents pasting into the HTML body?

Chris
Needs to be paste as HTML. Thanks
Yes but is it html already (in the cell) was the question

Chris
No, not HTML in the cell. Thanks
ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial