Merging data from Excel into Outlook

[Big Chris]

Hi!

I'm using the excellent code below (grabbed some weeks ago from thi
forum - thanks to those who wrote it!) and want to add another colum
of data to include in the body of the message.

i.e. the contents of the range E1:E10 are included in the body of th
message, with column A to C supplying the first name "Dear 'Chris', th
e-mail address and a yes/no which determines if the data in that ro
are to receive an e- mail.

What I'm looking to do is to have column D included within the body o
the mailing and include the contents of E1:E5 above it and E6:E10 belo
it so it looks like:

-------------------------------
Your upgrade is scheduled for:

Monday 2nd August 2004 at 10:00 hrs

Please call (555) 4032 if you need further instructions etc.
--------------------------------

Original code, which works well is...


Sub SendMessageToList()
Dim OutApp As Object
Dim OutMail As Object


Dim cell As Range
Dim strbody As String
For Each cell In ThisWorkbook.Sheets("Sheet1").Range("E1:E10")
strbody = strbody & cell.Value & vbNewLine
Next

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell I
Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Offset(0, 1).Value <> "" Then
If cell.Value Like "*@*" And cell.Offset(0, 1).Value
"yes" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Subject = "Subject goes in here"
.Body = "Dear " & cell.Offset(0, -1).Value
vbNewLine & vbNewLine & strbody & _
" "

'You can add other files also like this
.Attachments.Add ("C:\MyData\etcetc.pps")
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

-----------------------------------------------
Sorry it's so long winded. If anyone can help I would be ver
grateful.

Regards,

Big Chri

 

[Ron de Bruin]

Hi Big Chris

Try this


Sub test()
Dim strbody As String
For Each cell In ThisWorkbook.Sheets("Sheet1"). _
Range("E1:E5,D13,E6:E10")
strbody = strbody & cell.Value & vbNewLine
Next
MsgBox strbody
End Sub

 

[Big Chris]

Thanks Ron, I really appreciate your help. Except it just makes me mor
adventurous and raises more questions!!

The code works really well and the test messages look great, but i
doesn't carry bold text across to the mail message. Is there a way o
making it do this so that I can match my usual auto-signature?

Thanks again.

Chri

 

[Ron de Bruin]

Hi Chris

Not with .body

You can use HTMLBody
See a example in this sub
http://www.rondebruin.nl/mail/folder3/mail4.htm