Thursday, October 31, 2013

Outlook mail draft & coverting data into html format

Below is the code to create draft emails and paste range of data from Excel into the body of the email.

'Mail Draft coding
Private Function Mail_Range_Outlook_Body(DRng As String)
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

'Application.EnableEvents = False
Set rng = Nothing
Set rng = SW_Rpts.Range(DRng)

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

With OutMail
    .To = SW_Rpts.Range("AG1").Value
    .CC = SW_Rpts.Range("AG2").Value
    .BCC = ""
    .Subject = SW_Rpts.Range("AG3").Value
    If SW_Rpts.Range("AG4").Value <> "" Then .Attachments.Add SW_Rpts.Range("AG4").Value
    .HTMLBody = RangetoHTML(rng)
    .Display   '.Send
    .Close olPromtForSave
End With
'Application.EnableEvents = True

Set OutMail = Nothing
Set OutApp = Nothing
End Function

'Coverting data into html format
Public Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "\Surveys_Weekly_Temp.htm"

'Copy the range and create a new workbook to past the data in
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
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    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).Range("A1:J" & (Range("A100").End(xlUp).Row + 3)).Address, _
    .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
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

TempWB.Close SaveChanges:=False
Kill TempFile

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

No comments:

Post a Comment