Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mailing an excel range in the body of an e-mail | Excel Discussion (Misc queries) | |||
sending mails from excel...but mail body disappearing | Excel Discussion (Misc queries) | |||
Excel(with hyperlinks) as a message body of outlook mail | Excel Discussion (Misc queries) | |||
Paste excel range into a mail item's body | Excel Programming | |||
Sending Mail from Excel: Body Text | Excel Programming |