PDA

View Full Version : VBA Help - E-mail extract



Trax
10-27-2015, 06:09 AM
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
10-27-2015, 01:35 PM
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.