Emails data from each row in a sheet

[Jim]

Hi

I would like to email data from a sheet so that each row is formatted, then
a line then the next row etc.
The email will be to a fixed address. I'm thinking of a loop that would look
at each row in turn, placing the data into the Body of the email. Between
each row, I would like a line drawn so it look a bit like:

Date: 01/01/04 (from the sheet)
Name: J Smith
etc etc
----------------------------------
Date: 02/01/04 (next row in the sheet)
etc etc
loop until no more rows.

If that all makes sense, can anyone help with some VBA scipt?

Thanks. Jim

 

[Dick Kusleika]

Jim

If you're trying to automate Outlook, check this page.

http://www.dicks-clicks.com/excel/olSending.htm

You may already be beyond that, but I can't tell. For your particular
question, you need the vbNewLine constant and another constant. It might
look like this

Sub MakeMail()

Dim ol As Outlook.Application
Dim mi As MailItem

Dim sBody As String
Dim cell As Range

Const sDIVIDE As String = "----------------------"

Set ol = New Outlook.Application
Set mi = ol.CreateItem(olMailItem)

For Each cell In Sheet1.Range("A1:A10")
sBody = sBody & "Date:" & cell.Value & vbNewLine 'from col A
sBody = sBody & "Name:" & cell.Offset(0, 1).Value & vbNewLine 'col B
'etc
sBody = sBody & sDIVIDE & vbNewLine
Next cell

With mi
.To = "(e-mail address removed)"
.Subject = "Your data"
.Body = sBody
.Display
'.Send
End With

End Sub

 

[Jim]

Dick

Many thanks. You have started me off in the right direction. I don't suppose
you know whether it's possible to make bold the 'fixed' part of the text
such as Name: or Date: and the data from the sheet is normal text?

In any event, thanks again.

Jim

 

[Dick Kusleika]

Jim

MailItems have an HTMLBody property to which you can assign any valid html
text. So put some html tags in the sBody variable and assign it to HTMLBody
instead of just Body.