Formatting the user data for the body of the mail in excel
 

Hi there,

I've got a mail form that sends a mail to each person in the "Data"
worksheet.

For each mail the mail form picks out the contact data in the "Data"
list. It works fine, but I want to let the user to re-edit the body of
the message, but all formatations like "cell.Offset(0, -3).Value "
don't work if I save them somewhere in the worksheet, where the user
can edit them.

Does anybody have any good ideas how to solve it?

------------------
Sub SendMails()

'Please make sure that the Microsoft Outlook Object Library is checked!
(Tools/References)

'declare variables
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim bodydata As Variant


' switch off screen updating
Application.ScreenUpdating = False

' creates an outlook object
Set OutApp = CreateObject("Outlook.Application")

' in case of error go to cleanup
On Error GoTo cleanup

' loop through the "Data" worksheet records
For Each cell In
Sheets("Data").Columns("G").Cells.SpecialCells(xlC ellTypeConstants)

'examination of the existence of the valid email field in Field "G"
in the worksheet "Data"
If cell.Value Like "?*@?*.?*" Then

' if yes, create a mail form
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value
.Subject = "Just another test"

'the code below for the .Body property works, but there
is no chance to edit it without coding
' my attempt is to let the user fill in the message in
one particular place, like
ActiveWorkbook.Worksheets("Sheet2").Range("A1")

' so the next step would be to format the message from
' ActiveWorkbook.Worksheets("Formations").Range("A1" )
' and assign to the .Body property. How to accomplish
this task?

bodydata = "Dear Sir or Madam, please check your
contact data: " & _
vbNewLine & vbNewLine & "Your name: " & _
cell.Offset(0, -4).Value & " " & cell.Offset(0,
-3).Value & _
vbNewLine & _
"Your Phone Number: " & _
cell.Offset(0, -1).Value & vbNewLine & "Your Email
address:" & _
cell.Value & _
vbNewLine & vbNewLine & _
"Please contact us to discuss bringing your
account up to date"

.Body = bodydata

' .Send should send the mails directly, but you will
get a security alert, which will let you wait 5 seconds for each email,
' so there is a workaround with a snippet below

.Display
' this snippet lets the code to click on the Send
button in Outlook
' IMPORTANT! The "%S" works only with the English
version.
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"


End With
Set OutMail = Nothing
End If
Next cell

' if something goes wrong go to cleanup
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub