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
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