Hi there,

I'm new to VBA and would like to accomplish the below task. The code posted has been obtained in bits and pieces from other forums.

I have an excel spreadsheet with many worksheets (with each worksheet except sheet1 having a different email address for a name). I'm trying to extract only a certain portion of each worksheet (data under columns A to G), and paste them in an outlook draft(with "To" sender: having the worksheet name which is an email address). This would need to happen for every worksheet except Sheet1. I can't seem to get this code to work. Any assistance would be much appreciated.


Sub send_range_as_table()
'''''''''''''''''' tools -> refrence -> Microsoft outlook
Dim wsSheet As Worksheet
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailbody As String

' <br> used to insert a line ( press enter)

' create a table using html
' check the link below to know more about html tables
' http://www.w3schools.com/html/html_tables.asp
' html color code
'http://www.computerhope.com/htmcolor.htm or http://html-color-codes.info/
'bg color is used for background color
' font color is used for font color
'<b> bold the text http://www.w3schools.com/html/html_formatting.asp
' &nbsp; is used to give a single space between text
'<p style="font-size:15px">This is some text!</p> used to reduce for font size

'********************* created header of table
mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Application&nbsp;</p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">User ID&nbsp;</p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Firstname&nbsp;</p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Middlename&nbsp;</p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Lastname&nbsp;</p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Department ID&nbsp;</p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">PositionNumber&nbsp;</p></Font></TD>" & _


For Each wsSheet In Worksheets
For i = 2 To Sheets(1).Range("a1048576").End(xlUp).Row

mailbody = mailbody & "<TR>" & _
"<TD ><center>" & Sheets(1).Range("a" & i).Value & "</TD>" & _
"<TD><center>" & Sheets(1).Range("b" & i).Value & "</TD>" & _
"<TD><center>" & Sheets(1).Range("c" & i).Value & "</TD>" & _
"<TD><center>" & Sheets(1).Range("d" & i).Value & "</TD>" & _
"<TD><center>" & Sheets(1).Range("e" & i).Value & "</TD>" & _
"<TD><center>" & Sheets(1).Range("f" & i).Value & "</TD>" & _
"<TD><center>" & Sheets(1).Range("g" & i).Value & "</TD>" & _

' <br> used to insert a line ( press enter)

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = wsSheet.Name
.CC = ""
.Subject = "Send Range in the body of outlook email as Formatted Table"
.HTMLBody = "Please find the ----- below ----- <br><br> " & mailbody & "</Table><br> <br>Regards <br> <br> Ashish Koul"
End With
Next i
Next wsSheet
End Sub