CODEWITHSUNDEEP
excelvbaemailoutlook
Mike
Mike

Reputation: 139

Exporting data from Excel to Outlook

I have drafted an email in Excel which populates information from the data table.

Cell A1 to A4 contains "Hi, Hope your doing well" and messages....etc..

A5 to H10 has a table with the information and A11 to A30 has the email content like "looking forward for your reply".

I want to copy only the values for A1:A4 and A11:A30 but want A5:H10 to appear as table.

This code is from Ron De Bruin.

My code below pastes everything in a table format:

Sub Mail()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
Set rng = ActiveSheet.Range("A1:A24").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
    .Display
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = ""
    .HTMLBody = RangetoHTML(rng)
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

My Rest of the code:

Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

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

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

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=")

TempWB.Close savechanges:=False

Kill TempFile

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

Upvotes: 2

Views: 214

Answers (1)

0m3r
0m3r

Reputation: 12499

Work with shortcut range method []

The common method Range("A1").Value = 123 and shortcut method is [A1] = 123

Example

With OutMail
    .Display
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = ""
    .HTMLBody = [A1] & "<BR>" & _
                [A2] & "<BR>" & _
                [A3] & "<BR>" & _
                [A4] & RangetoHTML(rng) & _
                [A11] & "<BR>" & _
                [A12] & "<BR>" & _
                [A13] & "<BR>" & _
                [A14] & "<BR>"
                ' And more [range]
End With

Remember that square brackets are a replacement for the Range/Parentheses/Quotation Marks construct, The method returns a real reference to the range, It can be used on either side of the equal sign. It can be used to feed other functions And it has all of the methods and properties of a normal range.

Remember shortcut method is never the quickest

Upvotes: 2

Related Questions