VBA Help - E-mail extract

[Trax]

Good morning all,

I am seeking some assistance with some VBA code that I have been using. The code when run attaches the workbook to an outlook e-mail, populates the addresses based on hyperlinks shown in the worksheet. It also populates the subject and body of the text of the e-mail.

I am trying to extract the worksheet only as opposed to the workbook, and as pasted values with the same worksheet formatting, or with locking of the cells that are populated.

I am also trying to make some changes to the body of the text that is shown in the e-mail through separating the text through a hard return so that I can add a greeting, salutation and signature block etc.

Any help would be appreciated.

The code is as follows:

Sub Mail_workbook_Outlook_1()
     'Working in 2000-2010
     'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object
    Dim emailRng As Range, cl As Range
    Dim sTo As String

    Set emailRng = Worksheets("IC Summary Sheet").Range("M3:M13")

    For Each cl In emailRng
        sTo = sTo & ";" & cl.Value
    Next

    sTo = Mid(sTo, 2)

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

    On Error Resume Next
    With OutMail
        .To = sTo
        .CC = ""
        .BCC = ""
        .Subject = "INTERCOMPANY RECONCILIATION - " & Worksheets("IC Summary Sheet").Range("B10")
        .Body = "Please find attached the Inter Company Reconciliation for " & _
        Worksheets("IC Summary Sheet").Range("B10") & _
        ". If you have any questions, please let me know."
        .Attachments.Add ActiveWorkbook.FullName
         'You can add other files also like this
         '.Attachments.Add ("C:\test.txt")

        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

[Leith Ross]

Hello Trax,

It seems to me you want to paste a range of cells from a worksheet into the email keeping the formatting and values. If this is what you want to do, I will post the code to do this.

To format your email body, you will need to do it in HTML code. You will also need to change .Body to .HTMLbody. If you need help with this, let me know. Post a sample of how you want the body and I will convert it into HTML code for you.