VBA paste cells into outlook then convert table to text Excel 2010

[butcher11]

Hi, I'm trying to build a macro that grabs a selection of cells from an excel spreadsheet, pastes the cells into a new outlook email, then changes the format of the cells.

Specifically I want to convert the table to text, then change the font to Arial size 10.

The code below does the above, but I haven't been able to figure out how to convert the table to text, then change the text font.


Can anyone help? Thanks in advance.

Sub Email_test()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
Set rng = Sheets("Master").Range("A1:B99").SpecialCells(xlCellTypeVisible)
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)
With OutMail
    .To = "[EMAIL="User@company.com"]User@company.com[/EMAIL]"
    .CC = ""
    .BCC = ""
    .Subject = "Cells as text "
    .HTMLbody = RangetoHTML(rng)

    ' In place of the following statement, you can use ".Display" to
    ' display the e-mail message.

    .Display
End With
End Sub

 

[stevx99]

Hi, I'm trying to build a macro that grabs a selection of cells from an excel spreadsheet, pastes the cells into a new outlook email, then changes the format of the cells.

Specifically I want to convert the table to text, then change the font to Arial size 10.

The code below does the above, but I haven't been able to figure out how to convert the table to text, then change the text font.


Can anyone help? Thanks in advance.

Sub Email_test()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
Set rng = Sheets("Master").Range("A1:B99").SpecialCells(xlCellTypeVisible)
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)
With OutMail
    .To = "[EMAIL="User@company.com"]User@company.com[/EMAIL]"
    .CC = ""
    .BCC = ""
    .Subject = "Cells as text "
    .HTMLbody = RangetoHTML(rng)

    ' In place of the following statement, you can use ".Display" to
    ' display the e-mail message.

    .Display
End With
End Sub

Hi,

Just registered myself after seeing that your query had no replies. I am in the same situation but just realised that the facility in Outlook to 'Convert Table to Text' is there because WORD has been nominated as Outlook's text editor [if that makes sense]. So, as I am using Excel as well, in my VBE reference library I added the Word library, and now have access to the function expression.converttotable(). I am still researching how and where to use, but thought 2 heads may be better than one in progressing this..

 

[stevx99]

Hi,

Just registered myself after seeing that your query had no replies. I am in the same situation but just realised that the facility in Outlook to 'Convert Table to Text' is there because WORD has been nominated as Outlook's text editor [if that makes sense]. So, as I am using Excel as well, in my VBE reference library I added the Word library, and now have access to the function expression.converttotable(). I am still researching how and where to use, but thought 2 heads may be better than one in progressing this..

Hi again - finished my research and have come up with this. It takes the 1st table in an email and converts to text. Have email on 'display'

Dim objInspector As Outlook.Inspector
Dim objDoc As Document
Dim objTable As Object 'tried declaration as Table but didn't work
Dim rngTemp As Range
Set objInspector = ActiveInspector
Set objDoc = objInspector.WordEditor

If objDoc.Tables.Count = 0 Then
MsgBox "This email doesn't contain a table!", vbExclamation
'Exit whatever
End If

Set objTable = objDoc.Tables(1)

objTable.ConvertToText wdSeparateByParagraphs
'I had to use paragraphs but would be different for excel table

Hope this helps