Excel macro to send email

[becshu]

Hi,
I am using an excel macro to automatically send email to numerous recipients. Is there a way to include paragraph spacing in the body of the message? This is the type of code I am using....

Sub SendEMail()

Dim objOL As Object
Dim objOLMsg As Object
Dim objOLRecip As Object
Dim student as String
Dim EmailSub as String
Dim EmailBody as String

Set objOL = CreateObject("Outlook.Application&quot

For i = 1 To 3
Set objOLMsg = objOL.CreateItem(olMailItem)
With objOLMsg
Set objOLRecip = .Recipients.Add(student)
objOLRecip.Type = olto
.Subject = EmailSub
.Body = EmailBody
.Send
End With
Next i
Set objOL = Nothing
End Sub

 

[ttmac]

If you are building the body of your email dynamically use

.Body = "This is the first paragraph"
.Body = .Body & vbCrLf & vbCrLf
.Body = .Body & "This is the second paragraph"

This will insert to Carriage Return/Line Feed Pairs at the end of the body of the message.

If you are using predefined text insert the carriage return in you predefined text.

 

[becshu]

Fabulous! I'll give it a try. Thank you!!

 

[becshu]

That worked great! Now I'm trying to add an attachment and I'm getting an error. Any idea what I'm doing wrong?

 

[ttmac]

becshu,

sorry for the delay in getting back to you.

amend the loop to read

For i = 1 To 3
Set objOLMsg = objOL.CreateItem(olMailItem)
With objOLMsg
Set objOLRecip = .Recipients.Add(student)
objOLRecip.Type = olto
.Subject = EmailSub
.Body = EmailBody
If Dir(&quot;c:\tester.txt&quot;, 16) <> &quot;&quot; Then
.Attachments.Add &quot;c:\tester.txt&quot;, olByValue, , &quot;avini.txt&quot; ' insert attachment
End If
.Send
End With
Next i


The highlighted code checks to see if the file c:\tester.txt exists and if it does it attachs the file to the email.

Cheers,

Tom