Fancy Outlook Email

[Geoff Taylor]

Hi
I have some code that passes data from individual cells Excel rows into an email and sends them to individual recipients. It works fine.

However, the emails don't look pretty and it's been decided that they must do so (logos, typefaces etc). I have no idea how to approach this. Would I need to start passing HTML code to Outlook or can I create some kind of template in Outlook that the Excel VBA could reference?

All contributions gratefully received.

<code>
'Get confirmation
Dim Response
Response = MsgBox("You are about to create and send several hundred emails. Are you sure?", vbYesNo)
If Response = vbNo Then Exit Sub

'Declare Variables
Dim ol As Outlook.Application
Dim olMail As MailItem
Dim sRecipient As String
Dim rMyCell As Range
Dim sMyFollowup As String


'This bit tells the macro where to find the email address
For Each rMyCell In Range("B12:B" & Range("B11").End(xlDown).Row)

sMyFollowup = ""
If Range("N" & rMyCell.Row).Value = "Yes" Then
sMyFollowup = Range("followup").Value
End If

'Create an Outlook session
Set ol = CreateObject("outlook.application")
Set olMail = ol.CreateItem(olMailItem)
sRecipient = Range("E" & rMyCell.Row).Value
olMail.to = sRecipient
olMail.Subject = Range("myheading").Value
olMail.Body = Range("Body1").Value & " " & Range("I" & rMyCell.Row).Value & Chr(10) & Range("Body2").Value & " " & Range("M" & rMyCell.Row).Value & Chr(10) & sMyFollowup & Chr(10) & Range("Body3").Value
olMail.Importance = olNormal
olMail.Sensitivity = olNormal
olMail.Send

Next
End Sub
</code>