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

V

vitafusion

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(xlCellTypeConstants)

'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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top