Hi

I have used the following code to send a row of information in Excel to the
e-mail address stated within the row. But as well as sending the row, I'd
also like to embed some generic text in the email before and after the row is
displayed.

For example, I want "Dear" on one line, "Please see below confirmation of
your appointment" on the 3rd line, then the actual row of information on the
5th line, and then below this, just a line saying "If you need to re-arrange
your appointment, then please contact me."

I have tried to look at other examples and add the code to the below, but
nothing seems to work. Please see me code below and let me know if you can
help. Thank you

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

You should just have to put strBody into the body before the data, something
like
.HTMLBody = strBody & RangetoHTML(rng)

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"zak" wrote:

Hi

I have used the following code to send a row of information in Excel to the
e-mail address stated within the row. But as well as sending the row, I'd
also like to embed some generic text in the email before and after the row is
displayed.

For example, I want "Dear" on one line, "Please see below confirmation of
your appointment" on the 3rd line, then the actual row of information on the
5th line, and then below this, just a line saying "If you need to re-arrange
your appointment, then please contact me."

I have tried to look at other examples and add the code to the below, but
nothing seems to work. Please see me code below and let me know if you can
help. Thank you

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

Hi John

I have tried as you have said. It does add the text in, but all on one
line, for example, "Dear" and "Please see below confirmation details of your
appointment" is shown on the same line, but I want them to show on separate
lines. If you look at my code now, you will see i have added vbNewLine to
the end of each sentence, but still no joy. Please let me know if you can
help.

Thanks

Here is my new code:

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = strbody & vbNewLine & vbNewLine & _
RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub





"John Bundy" wrote:

You should just have to put strBody into the body before the data, something
like
.HTMLBody = strBody & RangetoHTML(rng)

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"zak" wrote:

Hi

I have used the following code to send a row of information in Excel to the
e-mail address stated within the row. But as well as sending the row, I'd
also like to embed some generic text in the email before and after the row is
displayed.

For example, I want "Dear" on one line, "Please see below confirmation of
your appointment" on the 3rd line, then the actual row of information on the
5th line, and then below this, just a line saying "If you need to re-arrange
your appointment, then please contact me."

I have tried to look at other examples and add the code to the below, but
nothing seems to work. Please see me code below and let me know if you can
help. Thank you

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

For Html use

StrBody = "This is line 1" & "<br" & _
"This is line 2" & "<br" & _
"This is line 3" & "<br<br<br"

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"zak" wrote in message ...
Hi John

I have tried as you have said. It does add the text in, but all on one
line, for example, "Dear" and "Please see below confirmation details of your
appointment" is shown on the same line, but I want them to show on separate
lines. If you look at my code now, you will see i have added vbNewLine to
the end of each sentence, but still no joy. Please let me know if you can
help.

Thanks

Here is my new code:

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = strbody & vbNewLine & vbNewLine & _
RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub





"John Bundy" wrote:

You should just have to put strBody into the body before the data, something
like
.HTMLBody = strBody & RangetoHTML(rng)

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"zak" wrote:

Hi

I have used the following code to send a row of information in Excel to the
e-mail address stated within the row. But as well as sending the row, I'd
also like to embed some generic text in the email before and after the row is
displayed.

For example, I want "Dear" on one line, "Please see below confirmation of
your appointment" on the 3rd line, then the actual row of information on the
5th line, and then below this, just a line saying "If you need to re-arrange
your appointment, then please contact me."

I have tried to look at other examples and add the code to the below, but
nothing seems to work. Please see me code below and let me know if you can
help. Thank you

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

That is why Ron is the man! It would have take me a while to remember it
needed to be HTML.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"zak" wrote:

Hi John

I have tried as you have said. It does add the text in, but all on one
line, for example, "Dear" and "Please see below confirmation details of your
appointment" is shown on the same line, but I want them to show on separate
lines. If you look at my code now, you will see i have added vbNewLine to
the end of each sentence, but still no joy. Please let me know if you can
help.

Thanks

Here is my new code:

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = strbody & vbNewLine & vbNewLine & _
RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub





"John Bundy" wrote:

You should just have to put strBody into the body before the data, something
like
.HTMLBody = strBody & RangetoHTML(rng)

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"zak" wrote:

Hi

I have used the following code to send a row of information in Excel to the
e-mail address stated within the row. But as well as sending the row, I'd
also like to embed some generic text in the email before and after the row is
displayed.

For example, I want "Dear" on one line, "Please see below confirmation of
your appointment" on the 3rd line, then the actual row of information on the
5th line, and then below this, just a line saying "If you need to re-arrange
your appointment, then please contact me."

I have tried to look at other examples and add the code to the below, but
nothing seems to work. Please see me code below and let me know if you can
help. Thank you

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

This is AMAZING! It now works.

Thank you VERY MUCH !

"John Bundy" wrote:

That is why Ron is the man! It would have take me a while to remember it
needed to be HTML.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"zak" wrote:

Hi John

I have tried as you have said. It does add the text in, but all on one
line, for example, "Dear" and "Please see below confirmation details of your
appointment" is shown on the same line, but I want them to show on separate
lines. If you look at my code now, you will see i have added vbNewLine to
the end of each sentence, but still no joy. Please let me know if you can
help.

Thanks

Here is my new code:

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = strbody & vbNewLine & vbNewLine & _
RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub





"John Bundy" wrote:

You should just have to put strBody into the body before the data, something
like
.HTMLBody = strBody & RangetoHTML(rng)

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"zak" wrote:

Hi

I have used the following code to send a row of information in Excel to the
e-mail address stated within the row. But as well as sending the row, I'd
also like to embed some generic text in the email before and after the row is
displayed.

For example, I want "Dear" on one line, "Please see below confirmation of
your appointment" on the 3rd line, then the actual row of information on the
5th line, and then below this, just a line saying "If you need to re-arrange
your appointment, then please contact me."

I have tried to look at other examples and add the code to the below, but
nothing seems to work. Please see me code below and let me know if you can
help. Thank you

Option Explicit

Sub Send_Row()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet
Dim strbody As String

Set Ash = ActiveSheet
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:G1000").AutoFilter Field:=2, Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set OutMail = OutApp.CreateItem(0)

strbody = "Dear" & vbNewLine & vbNewLine & _
"Please see below confirmation details of your appointment" &
vbNewLine

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Confirmation of Appointment"
.HTMLBody = RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0

Set OutMail = Nothing
Ash.AutoFilterMode = False
End If
Next cell

cleanup:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub