Emails data from each row in a sheet
 

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

Emails data from each row in a sheet
 

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 = "
.Subject = "Your data"
.Body = sBody
.Display
'.Send
End With

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


"Jim" wrote in message
...
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

Emails data from each row in a sheet
 

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" wrote in message
...
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 = "
.Subject = "Your data"
.Body = sBody
.Display
'.Send
End With

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


"Jim" wrote in message
...
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

Emails data from each row in a sheet
 

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.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Jim" wrote in message
.. .
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" wrote in message
...
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 = "
.Subject = "Your data"
.Body = sBody
.Display
'.Send
End With

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


"Jim" wrote in message
...
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